How to execute a result of another table. [message #667270] |
Tue, 19 December 2017 01:47 |
|
pmreddy.mahi@gmail.com
Messages: 16 Registered: December 2011
|
Junior Member |
|
|
Hi All,
One table column contains a select statement.I need to export the results of that select statement. Below is the example.
with t as(
SELECT 'select * from emp' COL1 FROM DUAL )
SELECT COL1 FROM t;
COL1
---------------
select * from emp
I need to execute and see the result of the above output in a pl/sql blook.
Please help me.
Thanks,
[Updated on: Tue, 19 December 2017 01:52] Report message to a moderator
|
|
|
|
|
|
|
Re: How to execute a result of another table. [message #667277 is a reply to message #667271] |
Tue, 19 December 2017 04:02 |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
John Watson wrote on Tue, 19 December 2017 07:55
I have to add that what you are trying to do is not usually considered to be a Good Thing in the Oracle world.
That is an understatement.
It's one of those things that if you have to ask how to do, you very likely shouldn't be playing with it in the first place.
|
|
|
|
|
Re: How to execute a result of another table. [message #667288 is a reply to message #667287] |
Tue, 19 December 2017 10:03 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Bulk collect is perfectly valid with execute immediate, you just have to put it after the dynamic string, which the OP did:
SQL> DECLARE
TYPE v_emp_bulk_tab IS TABLE OF user_tables%ROWTYPE;
l_emp v_emp_bulk_tab;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM user_tables' BULK COLLECT INTO l_emp ;
--DBMS_SQL.return_result (l_emp);
END; 2 3 4 5 6 7 8
9 /
PL/SQL procedure successfully completed.
SQL>
The actual problem the OP is having (and the error message makes this clear) is that l_emp isn't a valid datatype to pass to dbms_sql.return_result. From the little bit I've found about it on the web it appears to need a ref cursor.
|
|
|
|
|