Home » SQL & PL/SQL » SQL & PL/SQL » dbms_output.put_line for dynamic sql query (SQL*Plus: Release 11.2.0.3.0 Production)
dbms_output.put_line for dynamic sql query [message #663323] Tue, 30 May 2017 06:13 Go to next message
shawaj
Messages: 59
Registered: January 2016
Member
Hello everyone,
can anyone explain how i print the output of dynamic query
I want to print output of "V_STMT" dynamic sql statement of my code as below.

DECLARE
    TYPE cursor_ref IS REF CURSOR;

    c1       cursor_ref;
    V_DEPT   EMP.DEPTNO%TYPE;
    V_COL    VARCHAR2 (200);
    V_STMT   VARCHAR2 (2000);
    --V_REC    C1%ROWTYPE;
BEGIN
    OPEN c1 FOR 'select distinct deptno from emp';

    LOOP
        FETCH C1 INTO V_DEPT;

        EXIT WHEN c1%NOTFOUND;
        V_COL := V_COL || 'SUM(DECODE(deptno,' || V_DEPT || ',SAL,0)),';
    END LOOP;

    CLOSE C1;

    V_COL := RTRIM (V_COL, ',');
    V_STMT := 'SELECT JOB,' || V_COL || ' FROM EMP
	GROUP BY JOB
	ORDER BY JOB';

    DBMS_OUTPUT.PUT_LINE(V_STMT );
    
END;
Re: dbms_output.put_line for dynamic sql query [message #663329 is a reply to message #663323] Tue, 30 May 2017 08:21 Go to previous messageGo to next message
cookiemonster
Messages: 12961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you need to run the statement using execute immediate
Re: dbms_output.put_line for dynamic sql query [message #663330 is a reply to message #663323] Tue, 30 May 2017 08:56 Go to previous messageGo to next message
flyboy
Messages: 1850
Registered: November 2006
Senior Member
Well, as the constructed query contains unknown number of output columns, you will be out of luck with extracting the result INTO variable(s) from the EXECUTE IMMEDIATE statement.

In the posted case, the most sensible approach would be using aggregate by JOB and DEPT and PIVOTting by DEPT in the reporting tool.
select job, dept, sum(sal) sal
from emp
group by job, dept
order by job, dept;
Anyway, if you insist on the dynamic approach, you will have to (ab)use DBMS_SQL package, something like the PRINT_TABLE function in this AskTom thread: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958
Also, beware the correctness of the constructed SQL statement (you have inspected it, have not you?) - the ending comma in V_COL the will cause its failure.

By the way, DBMS_SQL.PUT_LINE dos not "print" anything, it just fills a buffer: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_output.htm#ARPLS67301
Re: dbms_output.put_line for dynamic sql query [message #663348 is a reply to message #663323] Tue, 30 May 2017 23:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8779
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> VARIABLE g_refcursor REFCURSOR
SCOTT@orcl_12.1.0.2.0> DECLARE
  2  	 TYPE cursor_ref IS REF CURSOR;
  3  
  4  	 c1	  cursor_ref;
  5  	 V_DEPT   EMP.DEPTNO%TYPE;
  6  	 V_COL	  VARCHAR2 (200);
  7  	 V_STMT   VARCHAR2 (2000);
  8  	 --V_REC    C1%ROWTYPE;
  9  BEGIN
 10  	 OPEN c1 FOR 'select distinct deptno from emp';
 11  
 12  	 LOOP
 13  	     FETCH C1 INTO V_DEPT;
 14  
 15  	     EXIT WHEN c1%NOTFOUND;
 16  	     V_COL := V_COL || 'SUM(DECODE(deptno,' || V_DEPT || ',SAL,0)),';
 17  	 END LOOP;
 18  
 19  	 CLOSE C1;
 20  
 21  	 V_COL := RTRIM (V_COL, ',');
 22  	 V_STMT := 'SELECT JOB,' || V_COL || ' FROM EMP
 23  	     GROUP BY JOB
 24  	     ORDER BY JOB';
 25  
 26  	 DBMS_OUTPUT.PUT_LINE(V_STMT );
 27  
 28  	 OPEN :g_refcursor FOR v_stmt;
 29  END;
 30  /
SELECT JOB,SUM(DECODE(deptno,30,SAL,0)),SUM(DECODE(deptno,20,SAL,0)),SUM(DECODE(deptno,10,SAL,0)) FROM EMP
	GROUP BY JOB
	ORDER BY
JOB

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_refcursor

JOB       SUM(DECODE(DEPTNO,30,SAL,0)) SUM(DECODE(DEPTNO,20,SAL,0)) SUM(DECODE(DEPTNO,10,SAL,0))
--------- ---------------------------- ---------------------------- ----------------------------
ANALYST                              0                         6000                            0
CLERK                              950                         1900                         1300
MANAGER                           2850                         2975                         2450
PRESIDENT                            0                            0                         5000
SALESMAN                          5600                            0                            0

5 rows selected.
Re: dbms_output.put_line for dynamic sql query [message #663453 is a reply to message #663348] Sat, 03 June 2017 03:28 Go to previous message
shawaj
Messages: 59
Registered: January 2016
Member
Thanks to all ,its very helpful..
Previous Topic: oracle external table issue
Next Topic: Different result when using FOR UPDATE
Goto Forum:
  


Current Time: Sat Dec 16 22:23:33 CST 2017

Total time taken to generate the page: 0.50158 seconds