Return results of query inside PL/SQL script

Question

Is it possible that SELECT statement inside PL/SQL block will return table records like during executing SELECT query in standard way?

I mean why code:

DECLARE
    sql_qry     VARCHAR2 (150);
BEGIN

    sql_qry:= 'SELECT ''1'' FROM dual';
    EXECUTE IMMEDIATE sql_qry;
END;
/

after execusion returns only information: PL/SQL procedure successfully completed.

Is it possible that SELECT statement enclosed in PL/SQL block will behave the same way like executing:

SELECT '1' FROM dual;

If you want to open a cursor, or populate an array, or something else, then by all means code it. What do you need it to do exactly? This kind of thing? — William Robertson
– William Robertson, Commented Oct 20, 2019 at 22:19
This is a very basic pl/sql question. I suggest you get introductory book on pl/sql or see the Oracle PL/SQL language guide, in addition to the link @WilliamRobertson provided above. — Belayer
– Belayer, Commented Oct 20, 2019 at 22:55
Possible duplicate of Is it possible to output a SELECT statement from a PL/SQL block? — Jon Heller
– Jon Heller, Commented Oct 20, 2019 at 23:30

Kaushik Nayak · Accepted Answer · 2019-10-21 04:35:56Z

If you're using Oracle 12c and above you may use DBMS_SQL.RETURN_RESULT

For 11g, you may make use of this standard procedure

create or replace procedure return_result( l_query varchar2 )
   is
       l_theCursor     integer default dbms_sql.open_cursor;
       l_columnValue   varchar2(4000);
       l_status        integer;
       l_colCnt        number := 0;
       l_separator     varchar2(1);
       l_descTbl       dbms_sql.desc_tab;
   begin
       dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );

       dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );


           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_output.put( l_separator || l_descTbl(i).col_name );
               l_separator := ',';
           end loop;
           dbms_output.put_line('');

        for i in 1 .. l_colCnt loop
           dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
       end loop;
       l_status := dbms_sql.execute(l_theCursor);

       while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i, l_columnValue );
               dbms_output.put( l_separator || l_columnValue );
               l_separator := ',';
           end loop;
           dbms_output.new_line;
       end loop;
       dbms_sql.close_cursor(l_theCursor);
   end;
/

Call it as

set serveroutput on 
EXECUTE return_result('SELECT ''1'' as col FROM dual');

Res

COL
1

Collectives™ on Stack Overflow

Return results of query inside PL/SQL script

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related