Is it possible to output a SELECT statement from a PL SQL block

Question

How can I get a PL SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT   For example how to do a SELECT like   SELECT foo  bar FROM foobar    Hint    BEGIN SELECT foo  bar FROM foobar  END    doesn t work

User · Answer

Create a function in a package and return a 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

User · Answer

From an anonymous block  I d like to now more about the situation where you think that to be required  because with subquery factoring clauses and inline views it s pretty rare that you need to resort to PL SQL for anything other than the most complex situations   If you can use a named procedure then use pipelined functions  Here s an example pulled 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

User · Answer

use execute immediate statement  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

User · Answer

use execute immediate statement  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

User · Answer

Cursors are used when your select query returns multiple rows  So  rather using cursor in case when you want aggregates or single rowdata you could use a procedure function without cursor as well like    Create Procedure sample id      varchar2 20  as      Select count    into x from table      where         Userid id       End     And then simply call the procedure      Begin    sample 20      End   This is the actual use of procedure function mostly wrapping and storing queries that are complex or that requires repeated manipulation with same logic but different data

User · Answer

if you want see select query output in pl sql you need to use a explicit cursor  Which will hold active data set and by fetching each row at a time it will show all the record from active data set as long as it fetches record from data set by iterating in loop  This data will not be generated in tabular format this result will be in plain text format  Hope this will be helpful  For any other query you may 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

User · Answer

The classic    Hello World     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 checkout it here  http   www oracle com technetwork issue-archive 2011 11-mar o21plsql-242570 html

User · Answer

It depends on what you need the result for   If you are sure that there s going to be only 1 row  use 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 1 row  you can use either 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

User · Answer

Cursors are used when your select query returns multiple rows  So  rather using cursor in case when you want aggregates or single rowdata you could use a procedure function without cursor as well like    Create Procedure sample id      varchar2 20  as      Select count    into x from table      where         Userid id       End     And then simply call the procedure      Begin    sample 20      End   This is the actual use of procedure function mostly wrapping and storing queries that are complex or that requires repeated manipulation with same logic but different data

User · Answer

From an anonymous block  I d like to now more about the situation where you think that to be required  because with subquery factoring clauses and inline views it s pretty rare that you need to resort to PL SQL for anything other than the most complex situations   If you can use a named procedure then use pipelined functions  Here s an example pulled 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

User · Answer

For versions below 12c  the plain answer is NO  at least not in the manner it is being done is SQL Server  You can print the results  you can insert the results into tables  you can return the results as cursors from within function procedure or return a row set from function - but you cannot execute SELECT statement  without doing something with the results   SQL Server begin     select 1 1     select 2 2     select 3 3 end      3 result sets returned     Oracle SQL gt  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

User · Answer

Create a function in a package and return a 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

User · Answer

Create a function in a package and return a 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

User · Answer

You need to use Native dynamic SQL  Also  you do not need BEGIN-END to run 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

User · Answer

Even if the question is old but i will share the solution that answers perfectly 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

User · Answer

Create a function in a package and return a 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

User · Answer

The classic    Hello World     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 checkout it here  http   www oracle com technetwork issue-archive 2011 11-mar o21plsql-242570 html

User · Answer

For versions below 12c  the plain answer is NO  at least not in the manner it is being done is SQL Server  You can print the results  you can insert the results into tables  you can return the results as cursors from within function procedure or return a row set from function - but you cannot execute SELECT statement  without doing something with the results   SQL Server begin     select 1 1     select 2 2     select 3 3 end      3 result sets returned     Oracle SQL gt  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

User · Answer

It depends on what you need the result for   If you are sure that there s going to be only 1 row  use 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 1 row  you can use either 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

User · Answer

It depends on what you need the result for   If you are sure that there s going to be only 1 row  use 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 1 row  you can use either 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

User · Answer

It depends on what you need the result for   If you are sure that there s going to be only 1 row  use 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 1 row  you can use either 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

User · Answer

You need to use Native dynamic SQL  Also  you do not need BEGIN-END to run 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

User · Answer

if you want see select query output in pl sql you need to use a explicit cursor  Which will hold active data set and by fetching each row at a time it will show all the record from active data set as long as it fetches record from data set by iterating in loop  This data will not be generated in tabular format this result will be in plain text format  Hope this will be helpful  For any other query you may 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

User · Answer

You can do this in Oracle 12 1 or above   declare     rc sys refcursor  begin     open rc for select   from dual      dbms sql return result rc   end    I don t have DBVisualizer to test with  but that should probably be your starting point   For more details  see Implicit Result Sets in the Oracle 12 1 New Features Guide  Oracle Base etc   For earlier versions  depending on the tool you might be able to use ref cursor bind variables like 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

User · Answer

Even if the question is old but i will share the solution that answers perfectly 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

User · Answer

From an anonymous block  I d like to now more about the situation where you think that to be required  because with subquery factoring clauses and inline views it s pretty rare that you need to resort to PL SQL for anything other than the most complex situations   If you can use a named procedure then use pipelined functions  Here s an example pulled 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

User · Answer

You can do this in Oracle 12 1 or above   declare     rc sys refcursor  begin     open rc for select   from dual      dbms sql return result rc   end    I don t have DBVisualizer to test with  but that should probably be your starting point   For more details  see Implicit Result Sets in the Oracle 12 1 New Features Guide  Oracle Base etc   For earlier versions  depending on the tool you might be able to use ref cursor bind variables like 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

User · Answer

From an anonymous block  I d like to now more about the situation where you think that to be required  because with subquery factoring clauses and inline views it s pretty rare that you need to resort to PL SQL for anything other than the most complex situations   If you can use a named procedure then use pipelined functions  Here s an example pulled 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

[sql] Is it possible to output a SELECT statement from a PL/SQL block?

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to oracle10g

Examples related to oracle-apex