The following procedure compiles successfully on ctrll + S (shows VALID) but on execution throws the error : Invalid character error while executing this procedure on SQL DEVELOPER.
I don't see anywhere I have a invalid character.
The following is the code to Create table and insert values:-
CREATE TABLE tempt( "set" VARCHAR2(1), "level" VARCHAR2(1), category VARCHAR2(3), value INT );
INSERT INTO tempt
SELECT 'A', 'Z', 'ABC', 847549 FROM dual UNION ALL
SELECT 'A', 'Y', 'ABC', 955808 FROM dual UNION ALL
SELECT 'A', 'X', 'ABC', 983462 FROM dual UNION ALL
SELECT 'A', 'Z', 'GHI', 762369 FROM dual UNION ALL
SELECT 'A', 'Y', 'DEF', 615863 FROM dual UNION ALL
SELECT 'A', 'X', 'DEF', 474257 FROM dual UNION ALL
SELECT 'B', 'Z', 'ABC', 959843 FROM dual UNION ALL
SELECT 'B', 'Y', 'ABC', 821704 FROM dual UNION ALL
SELECT 'B', 'X', 'ABC', 377211 FROM dual UNION ALL
SELECT 'B', 'Z', 'DEF', 945053 FROM dual UNION ALL
SELECT 'B', 'Y', 'DEF', 919120 FROM dual UNION ALL
SELECT 'B', 'Y', 'ABC', 821704 FROM dual UNION ALL
SELECT 'C', 'X', 'ABC', 377211 FROM dual UNION ALL
SELECT 'C', 'Z', 'DEF', 945053 FROM dual UNION ALL
SELECT 'C', 'Y', 'DEF', 919120 FROM dual UNION ALL
SELECT 'D', 'X', 'ABC', 377211 FROM dual UNION ALL
SELECT 'D', 'Z', 'DEF', 945053 FROM dual UNION ALL
SELECT 'D', 'Y', 'GHI', 919120 FROM dual UNION ALL
SELECT 'E', 'X', 'ABC', 377211 FROM dual UNION ALL
SELECT 'E', 'Z', 'DEF', 945053 FROM dual UNION ALL
SELECT 'E', 'Y', 'ABC', 919120 FROM dual UNION ALL
SELECT 'E', 'Z', 'ABC', 945053 FROM dual UNION ALL
SELECT 'E', 'Y', 'DEF', 919120 FROM dual UNION ALL
SELECT 'B', 'X', 'IJK', 326886 FROM dual
This is run inside the procedure window on the the SQL Developer/DB viz:-
CREATE OR REPLACE PROCEDURE "SCHEMA.pivot"(v_recordset out sys_refcursor)
AS
--v_recordset SYS_REFCURSOR;
v_sql long;
v_cols_1 long;
v_cols_2 clob;
BEGIN
SELECT LISTAGG( ''''||"level"||''' AS "'||"level"||'"' , ',' )
WITHIN GROUP ( ORDER BY "level" DESC )
INTO v_cols_1
FROM (
SELECT DISTINCT "level"
FROM tempt
);
SELECT DBMS_XMLGEN.CONVERT (
RTRIM (
XMLAGG (XMLELEMENT (
e,
'MAX(CASE WHEN CATEGORY = '
|| CHR (39)
|| CATEGORY
|| CHR (39)
|| ' THEN '
|| CHR (39)
|| "level"
|| CHR (39)
|| ' END) AS '
|| "level"
|| '_'
|| CATEGORY
|| '',
',')
ORDER BY 1 DESC).EXTRACT ('//text()').getclobval (),
','))
INTO v_cols_2
FROM (SELECT DISTINCT "level", CATEGORY
FROM tempt);
v_sql :=
'SELECT "set", ('|| v_cols_2 ||')
FROM
(
SELECT *
FROM tempt
PIVOT
(
MAX(value) FOR "level" IN ( '|| v_cols_1 ||' )
)
)
GROUP BY "set"
ORDER BY "set"';
v_sql := REPLACE (v_sql, ''', CHR (39));
DBMS_OUTPUT.PUT_LINE(v_sql);
OPEN v_recordset FOR v_sql;
end pivot;
EDIT 1 : I ran the query provided in the answer ,though compiled sucessfully I get a Invalid Character
Error :-
00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
I have seen the other posts with same question and tried out everything but doesnt seem to fix the issue. Line it shows for the error is OPEN v_recordset FOR v_sql;