How to display the output of dynamic query in form of a table

Question

I have a huge dynamic query in Oracle PL/SQL. My requirement is to display this query output in form of a table. I have saved the dynamic query in a variable. Now loop it by using cursors. But the output is not in form of table. Below is a short example:

set serveroutput on

declare 
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
var1 varchar2(1000) :='select document_id  from blu_payment_report_history' ;
var2 varchar(100) := ' where rownum <=3 ';
output varchar(100);
finalvar varchar(100);

begin
finalvar:= var1 || ' ' || var2;
OPEN v_emp_cursor FOR finalvar;
  LOOP
    FETCH v_emp_cursor INTO output;
    dbms_output.put_line(output);
    EXIT WHEN v_emp_cursor%NOTFOUND;
  END LOOP;
 CLOSE v_emp_cursor;
end; 

How to modify the above code to bring output in form of table. Note: This is only an example with one column. My actual query has 10 columns.

Below is the output of my query. But this is not in form of table:

Error screenshot and compiled stored procedure screenshot :

Answer 1

Create a procedure:

CREATE PROCEDURE get_payment_report_history(
  v_cur OUT SYS_REFCURSOR
)
IS
  v_sql varchar2(1000) := 'select document_id from blu_payment_report_history where rownum <=3 ';
BEGIN
  OPEN v_cur FOR v_sql;
END;
/

This will return a result set which contains rows and columns.

How you choose to display it is irrelevant; the cursor will contain rows and columns.

In PL/SQL developer, you need to both return the result set and display the cursor to the console using your code:

DECLARE
  v_cur  SYS_REFCURSOR;
  v_col1 NUMBER;
BEGIN
  get_payment_report_history(v_cur);

  LOOP
    FETCH v_cur INTO v_col1;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(output);
  END LOOP;
  CLOSE v_cur;
END; 
/

In PowerBI, you would call the stored procedure and then process the returned cursor; you do not need a PL/SQL wrapper around the returned results to display it.

---

Instead of returning a cursor, in Oracle 12 and later, you could also use DBMS_SQL.RETURN_RESULT to implicitly return a cursor.

CREATE PROCEDURE get_payment_report_history
IS
  v_cur SYS_REFCURSOR;
  v_sql varchar2(1000) := 'select document_id from blu_payment_report_history where rownum <=3 ';
BEGIN
  OPEN v_cur FOR v_sql;
  DBMS_SQL.RETURN_RESULT(v_cur);
END;
/

Then if your client supports it use:

EXECUTE get_payment_report_history();

To call the procedure, which will implicitly return the result set contained in the cursor.