How to output result of SELECT statement which is executed using native dynamic SQL?

Question

I have a string which contains SQL SELECT statement.
I wonder how can I output result of the execution of that statement on the screen, execution will be done using native dynamic SQL (EXECUTE IMMEDIATE).

example:

DECLARE
v_stmt VARCHAR2 := 'SELECT * FROM employees';
BEGIN
EXECUTE IMMEDIATE v_stmt; -- ??? how to output result of that select on the screen.
END;

Important remark: structure of table can be any. I have to write a procedure which accepts name of the table as parameter, so I can't hardcode a table structure and don't want to do it.

Thanks for responses. Any ideas very appreciated/

Try [this](http://stackoverflow.com/a/3840771/1413158) solution. It covers [tag:dynamic-sql] — Rafael Perez, Jan 25 '13 at 19:26

score 5 · Accepted Answer · answered Jan 25 '13 at 19:22

5

If you can change that execute immediate into a dbms_sql cursor, then the following solution should be able to help you, as you can get the column names from a dbms_sql cursor:

https://forums.oracle.com/forums/thread.jspa?threadID=700648

answered Jan 25 '13 at 19:22

Horus

1,149
10
13

Thank you, I shall try, but I am not sure that it will work, because in select I transpose statement and I think there might be issue with column names – spin_eight Jan 25 '13 at 19:36
It looks like it pulls the column names off of the cursor directly, as long as you give them reasonable names in your 'as' statement, you should be able to get them out. – Horus Jan 25 '13 at 19:49
1

Thank you very much, you offered exectly what I needed, I have just finished coding and implemented code from the example you advised me in relation to my task and I got perfectly what I need. So thank you again for precision answer and quick response. Great! – spin_eight Jan 25 '13 at 20:00
Excellent, glad to be helpful! – Horus Jan 25 '13 at 20:02

score 5 · Answer 2 · answered Apr 06 '17 at 15:56

5

If you are on Oracle 12c with a 12c client, this should work:

declare
    rc sys_refcursor;
begin
    open rc for 'select * from dual';
    dbms_sql.return_result(rc);
end;

answered Apr 06 '17 at 15:56

William Robertson

14,525
3
37
41

score 1 · Answer 3 · edited May 25 '15 at 14:45

Yes we can execute select statement dynamically.

Let say we have a table test. It has four column Row_id,Name,Rank etc When we do select * from test; Result will be

Row_id Name   Rank  
1      R1    5 
2      R2    1 
3      R3    2 
4      R4    4

Now we can use DBMS_SQL package to execute dynamically SELECT Sql Statament.

Code is below:

   DECLARE
        v_CursorID  NUMBER;
        v_table VARCHAR2(50):='test';
        v_SelectRecords  VARCHAR2(500);
        v_NUMRows  INTEGER;
        v_MyNum INTEGER;
        v_Myname VARCHAR2(50);
        v_Rank INTEGER;



    BEGIN
         v_CursorID := DBMS_SQL.OPEN_CURSOR;
        v_SelectRecords := 'SELECT * from ' || v_table ;
        DBMS_SQL.PARSE(v_CursorID,v_SelectRecords,DBMS_SQL.V7);
        DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_MyNum);
        DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,v_Myname,50);
        DBMS_SQL.DEFINE_COLUMN(v_CursorID,3,v_Rank);

        v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
   LOOP
        IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
             EXIT;
        END IF;

        DBMS_SQL.COLUMN_VALUE(v_CursorId,1,v_MyNum);
        DBMS_SQL.COLUMN_VALUE(v_CursorId,2,v_Myname);
        DBMS_SQL.COLUMN_VALUE(v_CursorId,3,v_Rank);



        DBMS_OUTPUT.PUT_LINE(v_MyNum || ' ' || v_Myname || ' ' || v_Rank  );

   END LOOP;

   EXCEPTION
        WHEN OTHERS THEN
                  RAISE;
        DBMS_SQL.CLOSE_CURSOR(v_CursorID);
        end;

How to output result of SELECT statement which is executed using native dynamic SQL?

3 Answers3

Linked

Related