Home » SQL & PL/SQL » SQL & PL/SQL » How to execute a result of another table.
How to execute a result of another table. [message #667270] Tue, 19 December 2017 01:47 Go to next message
pmreddy.mahi@gmail.com
Messages: 15
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 #667271 is a reply to message #667270] Tue, 19 December 2017 01:55 Go to previous messageGo to next message
John Watson
Messages: 7607
Registered: January 2010
Location: Global Village
Senior Member
Are you familiar with using dynamic SQL? As described here -
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/dynamic-sql.html

I have to add that what you are trying to do is not usually considered to be a Good Thing in the Oracle world.
Re: How to execute a result of another table. [message #667274 is a reply to message #667271] Tue, 19 December 2017 03:41 Go to previous messageGo to next message
pmreddy.mahi@gmail.com
Messages: 15
Registered: December 2011
Junior Member
I am using below PL/SQL Block to retrieve result, but getting error. Please help me.

set serveroutput on
DECLARE
TYPE v_emp_bulk_tab IS TABLE OF t%ROWTYPE;
l_emp v_emp_bulk_tab;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM t' BULK COLLECT INTO l_emp ;

DBMS_SQL.return_result (l_emp);
END;
/
Re: How to execute a result of another table. [message #667275 is a reply to message #667274] Tue, 19 December 2017 03:46 Go to previous messageGo to next message
cookiemonster
Messages: 13264
Registered: September 2008
Location: Rainy Manchester
Senior Member
Pleas help us to help you by telling us what the error is
Re: How to execute a result of another table. [message #667276 is a reply to message #667275] Tue, 19 December 2017 03:49 Go to previous messageGo to next message
pmreddy.mahi@gmail.com
Messages: 15
Registered: December 2011
Junior Member
Below is the Error:

PLS-00306: wrong number or types of arguments in call to 'RETURN_RESULT'

[Updated on: Tue, 19 December 2017 03:50]

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 Go to previous messageGo to next message
Roachcoach
Messages: 1571
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 #667286 is a reply to message #667276] Tue, 19 December 2017 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65831
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle version?

Re: How to execute a result of another table. [message #667287 is a reply to message #667274] Tue, 19 December 2017 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 26182
Registered: January 2009
Location: SoCal
Senior Member
pmreddy.mahi@gmail.com wrote on Tue, 19 December 2017 01:41
I am using below PL/SQL Block to retrieve result, but getting error. Please help me.

set serveroutput on
DECLARE
TYPE v_emp_bulk_tab IS TABLE OF t%ROWTYPE;
l_emp v_emp_bulk_tab;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM t' BULK COLLECT INTO l_emp ;

DBMS_SQL.return_result (l_emp);
END;
/
You are doing a Bad Thing wrongly!

You need to slow down & realize that SQL is a totally different language from PL/SQL.

EXECUTE IMMEDIATE is designed to invoke a SQL statement from inside PL/SQL procedure.

BULK COLLECT is a PL/SQL directive that is NOT valid in plain SQL.

You should NOT be storing SQL statement in any static data column & then trying to execute them.
It is the wrong tool used the wrong way.

What problem are you really trying to solve?
Re: How to execute a result of another table. [message #667288 is a reply to message #667287] Tue, 19 December 2017 10:03 Go to previous messageGo to next message
cookiemonster
Messages: 13264
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.
Re: How to execute a result of another table. [message #667292 is a reply to message #667288] Tue, 19 December 2017 22:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8883
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> CREATE TABLE t AS
  2  SELECT 'select empno, ename from emp' COL1 FROM DUAL UNION ALL
  3  SELECT 'select deptno, dname from dept' COL1 FROM DUAL
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM t
  2  /

COL1
------------------------------
select empno, ename from emp
select deptno, dname from dept

2 rows selected.

SCOTT@orcl_12.1.0.2.0> SET SERVEROUTPUT ON
SCOTT@orcl_12.1.0.2.0> DECLARE
  2    l_emp SYS_REFCURSOR;
  3  BEGIN
  4    FOR i IN (SELECT * FROM t) LOOP
  5  	 OPEN l_emp FOR i.col1;
  6  	 DBMS_SQL.RETURN_RESULT (l_emp);
  7    END LOOP;
  8  END;
  9  /

PL/SQL procedure successfully completed.

ResultSet #1

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

ResultSet #2

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

4 rows selected.

Re: How to execute a result of another table. [message #667313 is a reply to message #667292] Wed, 20 December 2017 08:18 Go to previous message
joy_division
Messages: 4899
Registered: February 2005
Location: East Coast USA
Senior Member
Nicely done Barbara.
Previous Topic: create table in another schema
Next Topic: LIKE Operator in MERGE
Goto Forum:
  


Current Time: Mon Oct 15 05:48:04 CDT 2018