Home » SQL & PL/SQL » SQL & PL/SQL » How to capture the results of a dynamic SQL
How to capture the results of a dynamic SQL [message #254371] Thu, 26 July 2007 11:04 Go to next message
rajesh4851
Messages: 89
Registered: January 2007
Member
Hi,
i strucked up here. please help me.
i am unable to write directly here. please find the attachment.

[Updated on: Fri, 27 July 2007 00:14] by Moderator

Report message to a moderator

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 #254376 is a reply to message #254373] Thu, 26 July 2007 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
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 Go to previous messageGo to next message
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.
Re: Very urgent- how to capture the results of a dynamic SQL statement [message #254396 is a reply to message #254394] Thu, 26 July 2007 11:54 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please have a look of the following code:

NO until you read the links I posted and format your post.

Regards
Michel
Previous Topic: query to get message between two dates
Next Topic: help with analytical function to add extra lines
Goto Forum:
  


Current Time: Thu Dec 08 16:14:08 CST 2016

Total time taken to generate the page: 0.08780 seconds