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 solution. It covers dynamic-sql

Rafael Perez
– Rafael Perez

2013-01-25 19:26:00 +00:00
Commented Jan 25, 2013 at 19:26 — Rafael Perez
– Rafael Perez, Commented Jan 25, 2013 at 19:26

William Robertson · Accepted Answer · 2017-04-06 15:56:31Z

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 6, 2017 at 15:56

William Robertson

16.2k4 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

miracle_the_V · Accepted Answer · 2015-05-25 14:45:56Z

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;

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related