|
|
| Re: Very urgent- how to capture the results of a dynamic SQL statement [message #254373 is a reply to message #254371] |
Thu, 26 July 2007 11:06   |
rajesh4851
Messages: 89
Registered: January 2007
|
Member |
|
|
please find the details here also..
Create table t1(c1 number(4), c2 varchar2(100));
Insert into t1 values(10, ‘select * from temp1’);
Create table temp1 (empno number(4), sal number(6), deptno number(2));
Insert into temp1 values(10,1200,20);
Insert into temp1 values(20,1500,30);
Insert into temp1 values(30,2000,40);
===========================================
1 declare
2 type refcur_type is ref cursor;
3 report_refcur refcur_type;
4 l_refcur report_refcur%type;
5 l_refcur1 number;
6 Cursor bom_cur is select * from t1;
7 a number;
8 b number;
9 c number;
10 d number;
11 l_sql varchar2 (1000);
12 Begin
13 for bom_cur1 in bom_cur loop
14 l_sql := bom_cur1.c2;
15 open report_refcur for l_sql;
16 fetch report_refcur into l_refcur1;
17 exit when report_refcur%notfound;
18 execute immediate l_sql into a, b, c;
19 d := 'a||,||b||,||c';
20 close report_refcur;
21 dbms_output.put_line(a||' '||b||' '||c||' '||d);
22 end loop;
23* end;
24
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 18
========================================
this is because of execute immediate of l_sql retuns more than one row as follows.
------------------------------
SQL> select * from t1;
C1 C2
---------- ----------------------
10 select * from temp1
SQL> select * from temp1;
EMPNO SAL DEPTNO
--------- --------- ---------
10 1200 20
20 1500 30
30 2000 40
--------------------------------------
now i want a solution how to store/capture the data executed by execute immediate sql statement.
eg: i have to store results of statement " select * from temp1".
can i use PLSQL tables?
Please let me know if u are not clear about the problem.
thanks in advance,
|
|
|
|
| Re: Very urgent- how to capture the results of a dynamic SQL statement [message #254375 is a reply to message #254371] |
Thu, 26 July 2007 11:08 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
First, nothing is urgent is in forum.
Then,
Have a look at dbms_sql package and loop on the result.
Search for "print_table" on AskTom, this is a very example of what you can do.
Example of output:
SQL> exec print_table('select empno, ename, sal from scott.emp where rownum<=2');
EMPNO : 7369
ENAME : SMITH
SAL : 800
-----------------
EMPNO : 7499
ENAME : ALLEN
SAL : 1600
-----------------
PL/SQL procedure successfully completed.
Regards
Michel
[Updated on: Thu, 26 July 2007 11:09] Report message to a moderator |
|
|
|
|
|
| Re: Very urgent- how to capture the results of a dynamic SQL statement [message #254394 is a reply to message #254375] |
Thu, 26 July 2007 11:50 |
rajesh4851
Messages: 89
Registered: January 2007
|
Member |
|
|
Hi ,
Please have a look of the following code:
here i am trying to capture the results of SQL query executed by REFCURSOR into a PLSQL table. but throwing error.
1 declare
2 TYPE T_sql IS RECORD
3 (T1 NUMBER,
4 T2 NUMBER,
5 T3 NUMBER);
6 TYPE Tab_SQL IS TABLE OF T_SQL INDEX BY BINARY_INTEGER;
7 type refcur_type is ref cursor;
8 report_refcur refcur_type;
9 Cursor bom_cur is select * from t1;
10 l_sql t1.c2%type;
11 Begin
12 for bom_cur1 in bom_cur loop
13 l_sql := bom_cur1.c2;
14 open report_refcur for l_sql;
15 fetch report_refcur BULK COLLECT INTO tab_sql;
16 exit when report_refcur%notfound;
17 close report_refcur;
18 end loop;
19* end;
20 /
declare
*
ERROR at line 1:
ORA-06550: line 15, column 39:
PLS-00321: expression 'TAB_SQL' is inappropriate as the left hand side of an assignment statement
ORA-06550: line 15, column 1:
PL/SQL: SQL Statement ignored
Please help me.
Thanks in advance.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
