How to get output of a pl/sql query /Stored procedure in a result set format using oracle sql Developer?

Question

I do have a working query to get resultset as per my requirement ,but the problem is am unable to view the result set in Oracle sql developer .

CREATE OR REPLACE PROCEDURE SP_GETDATA(
id in number,
result_cursor out sys_refcursor
)AS
BEGIN
DECLARE v_sql varchar2(2000);
BEGIN
     v_sql:-'select * from(select col1,col2,col3 from tab1) 
     pivot (max(col3) for col1 in(';
for i in (select col1 from tab2)
LOOP
    v_sql:=v_sql||i.col1||',';
END LOOP;
    v_sql:=RTRIM(v_sql,',')||')) ORDER BY col2';
    OPEN result_cursor for v_sql;
END;
END ;
/

and am trying to call the stored procedure with the following commands,

VARIABLE cursor_test refcursor;
exec SP_GETDATA(1,:cursor_test);
print cursor_test;

which give me PL/SQL procedure successfully completed and no result. How could I get the result set from the above? while executing query generated inside v_sql gives me the exact output.

Answer 1

Dummy table/column names don't help much so I used Scott's sample schema to try your code.

Procedure:

SQL> CREATE OR REPLACE PROCEDURE sp_getdata (id             IN     NUMBER,
  2                                          result_cursor     OUT SYS_REFCURSOR)
  3  AS
  4  BEGIN
  5     DECLARE
  6        v_sql  VARCHAR2 (2000);
  7     BEGIN
  8        v_sql := 'select * from(select deptno,job,sal from emp)
  9       pivot (max(sal) for deptno in(';
 10
 11        FOR i IN (SELECT deptno
 12                    FROM dept
 13                   WHERE deptno <= 20)
 14        LOOP
 15           v_sql := v_sql || i.deptno || ',';
 16        END LOOP;
 17
 18        v_sql := RTRIM (v_sql, ',') || ')) ORDER BY job';
 19
 20        DBMS_OUTPUT.put_line (v_sql);
 21
 22        OPEN result_cursor FOR v_sql;
 23     END;
 24  END;
 25  /

Procedure created.

Testing:

SQL> var rc refcursor
SQL> exec sp_getdata(1, :rc);

PL/SQL procedure successfully completed.

SQL> print rc

JOB               10         20
--------- ---------- ----------
ANALYST                    3000
CLERK           1300       1100
MANAGER         2450       2975
PRESIDENT       5000
SALESMAN

SQL>

---

Does it work in SQL Developer? Yes:

---

Or, you can run the procedure (from Object Navigator) and view "Output Variables"):