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  |
 |
shawaj
Messages: 89
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 #663330 is a reply to message #663323] |
Tue, 30 May 2017 08:56  |
flyboy
Messages: 1903
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;
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 |
 |
Barbara Boehmer
Messages: 9082
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Apr 15 22:57:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
