Is it possible to derive a SELECT statement from a PL / SQL block?

Question

How to get a PL / SQL block to output the results of a SELECT just as if I made a simple SELECT ?

For example, how to make SELECT like:

 SELECT foo, bar FROM foobar; 

Hint:

 BEGIN SELECT foo, bar FROM foobar; END; 

does not work.

Answer 1

It depends on what you need the result for.

If you are sure that there will be only one line, use the implicit cursor:

 DECLARE v_foo foobar.foo%TYPE; v_bar foobar.bar%TYPE; BEGIN SELECT foo,bar FROM foobar INTO v_foo, v_bar; -- Print the foo and bar values dbms_output.put_line('foo=' || v_foo || ', bar=' || v_bar); EXCEPTION WHEN NO_DATA_FOUND THEN -- No rows selected, insert your exception handler here WHEN TOO_MANY_ROWS THEN -- More than 1 row seleced, insert your exception handler here END; 

If you want to select more than one row, you can use an explicit cursor:

 DECLARE CURSOR cur_foobar IS SELECT foo, bar FROM foobar; v_foo foobar.foo%TYPE; v_bar foobar.bar%TYPE; BEGIN -- Open the cursor and loop through the records OPEN cur_foobar; LOOP FETCH cur_foobar INTO v_foo, v_bar; EXIT WHEN cur_foobar%NOTFOUND; -- Print the foo and bar values dbms_output.put_line('foo=' || v_foo || ', bar=' || v_bar); END LOOP; CLOSE cur_foobar; END; 

or use another type of cursor:

 BEGIN -- Open the cursor and loop through the records FOR v_rec IN (SELECT foo, bar FROM foobar) LOOP -- Print the foo and bar values dbms_output.put_line('foo=' || v_rec.foo || ', bar=' || v_rec.bar); END LOOP; END; 

Answer 2

You can do this in Oracle 12.1 or higher:

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

I don't have DBVisualizer for testing, but this should probably be your starting point.

For more information, see the Implicit Result Sets section of the Oracle 12.1 New Features Guide , Oracle Base , etc.

For earlier versions, depending on the tool, you can use the ref cursor bindings, as in this example from SQL * Plus:

 set autoprint on var rc refcursor begin open :rc for select count(*) from dual; end; / PL/SQL procedure successfully completed. COUNT(*) ---------- 1 1 row selected. 

Answer 3

Create a function in the package and return SYS_REFCURSOR:

 FUNCTION Function1 return SYS_REFCURSOR IS l_cursor SYS_REFCURSOR; BEGIN open l_cursor for SELECT foo,bar FROM foobar; return l_cursor; END Function1; 

Answer 4

From an anonymous block? I would like to know more about the situation when you think it is necessary, because with the subquery factoring clauses and inline views it is quite rare to have to resort to PL / SQL for anything other than the most difficult situations.

If you can use a named procedure, use pipelined functions. Here is an example extracted from the documentation:

 CREATE PACKAGE pkg1 AS TYPE numset_t IS TABLE OF NUMBER; FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED; END pkg1; / CREATE PACKAGE BODY pkg1 AS -- FUNCTION f1 returns a collection of elements (1,2,3,... x) FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS BEGIN FOR i IN 1..x LOOP PIPE ROW(i); END LOOP; RETURN; END; END pkg1; / -- pipelined function is used in FROM clause of SELECT statement SELECT * FROM TABLE(pkg1.f1(5)); 

Answer 5

Classic 'Hello World!' The block contains an executable section that calls the DBMS_OUTPUT.PUT_LINE procedure to display text on the screen:

 BEGIN DBMS_OUTPUT.put_line ('Hello World!'); END; 

You can place your order here: http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21plsql-242570.html

Answer 6

if you want to see the choice of query output in pl / sql, you need to use an explicit cursor. This will contain the active dataset and fetch each row at a time, while it will display the entire record from the active dataset if it extracts data from the dataset, iterating in a loop. This data will not be generated in tabular format, this result will be in text format. Hope this will be helpful. For any other request you can ask ....

 set serveroutput on; declare cursor c1 is select foo, bar from foobar; begin for i in c1 loop dbms_output.put_line(i.foo || ' ' || i.bar); end loop; end; 

Answer 7

For versions below 12c, the simple answer is NO , at least not the way it is, it is SQL Server.
You can print the results, you can insert the results into tables, you can return the results as cursors from a function / procedure or return a set of rows from a function -
but you cannot execute a SELECT statement without doing anything with the results.

---

SQL Server

 begin select 1+1 select 2+2 select 3+3 end 

---

/ * 3 returned result sets * /

---

Oracle

 SQL> begin 2 select 1+1 from dual; 3 end; 4 / select * from dual; * ERROR at line 2: ORA-06550: line 2, column 1: PLS-00428: an INTO clause is expected in this SELECT statement 

Answer 8

You need to use your own dynamic SQL. In addition, BEGIN-END is not required to run the SQL command:

 declare l_tabname VARCHAR2(100) := 'dual'; l_val1 VARCHAR2(100):= '''foo'''; l_val2 VARCHAR2(100):= '''bar'''; l_sql VARCHAR2(1000); begin l_sql:= 'SELECT '||l_val1||','||l_val2||' FROM '||l_tabname; execute immediate l_sql; dbms_output.put_line(l_sql); end; / Output: SELECT 'foo','bar' FROM dual 

Answer 9

use immediate instruction

like:

 declare var1 integer; var2 varchar2(200) begin execute immediate 'select emp_id,emp_name from emp' into var1,var2; dbms_output.put_line(var1 || var2); end; 

Answer 10

Even if the question is old, but I will share a solution that will ideally answer the question:

 SET SERVEROUTPUT ON; DECLARE RC SYS_REFCURSOR; Result1 varchar2(25); Result2 varchar2(25); BEGIN OPEN RC FOR SELECT foo, bar into Result1, Result2 FROM foobar; DBMS_SQL.RETURN_RESULT(RC); END;