How to Return Rows from a Declare/Begin/End Block in Oracle

How to return rows from a declare/begin/end block in Oracle?

An anonymous PL/SQL block, like the one you've shown, can't "return" anything. It can interact with the caller by means of bind variables, however.

So the method I would use in this case would be to declare a cursor reference, open it in the PL/SQL block for the desired query, and let the calling application fetch rows from it. In SQLPlus this would look like:

variable rc refcursor

declare
     blah number := 42;
begin
  open :rc for
     select *
     from x
     where x.value = blah;
end;
/

print x

If you recast your PL/SQL as a stored function then it could return values. In this case what you might want to do is create a collection type, fetch all the rows into a variable of that type, and return it:

CREATE TYPE number_table AS TABLE OF NUMBER;

CREATE FUNCTION get_blah_from_x (blah  INTEGER)
  RETURN number_table
  IS
    values  number_table;
  BEGIN
    SELECT id
      BULK COLLECT INTO values
      FROM x
      WHERE x.value = blah;
    RETURN values;
  END;
/

Return data rows from a pl/sql block

I studied this excellent paper on optimizing pagination:
http://www.inf.unideb.hu/~gabora/pagination/article/Gabor_Andras_pagination_article.pdf

I used technique 6 mainly. It describes how to limit query to fetch page x and onward. For added improvement, you can limit it further to fetch page x alone. If used right, it can bring a performance improvement by a factor of 1000.

Instead of returning custom table rows (which is very hard, if not impossible to interface with Java), I eneded up opening a sys_refcursor in my pl/sql which can be interfaced such as:

OracleCallableStatement stmt = (OracleCallableStatement) connection.prepareCall(sql);
stmt.registerOutParameter(someIndex, OracleTypes.CURSOR);
stmt.execute();
resultSet = stmt.getCursor(idx);

EXECUTE IMMEDIATE PL/SQL Block return type

Oracle is complaining because your PL/SQL does not return anything for it to store in l_output. What are you expecting the value of l_output to be?

One would use EXECUTE IMMEDIATE...INTO with something like this to return a value from a PL/SQL block.

DECLARE
    l_output    VARCHAR2(10);
BEGIN
    EXECUTE IMMEDIATE 'SELECT ''ABC'' FROM DUAL' INTO l_output;
    dbms_output.put_line('l_output = ' || l_output);
END;
/

UPDATE

If you want, you can do this:

DECLARE
    l_output    VARCHAR2(10);
BEGIN
    EXECUTE IMMEDIATE 'BEGIN :1 := 5; END;' USING IN OUT l_output;
    dbms_output.put_line('l_output = ' || l_output);
END;

Why do we need BEGIN END (execution section) in last portion for an Anonymus block?

An PL/SQL block has the structure DECLARE ... BEGIN ... END;. The DECLARE is optional if you want to declare any variables, etc. but the BEGIN ... END; is mandatory.

If we start with a simple block and build up:

BEGIN
  NULL;
END;
/

Is the simplest PL/SQL statement. You then declare a type:

DECLARE
  TYPE type_a IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
BEGIN
  NULL;
END;
/

and a variable:

DECLARE
  TYPE type_a IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  output NUMBER := 1;
BEGIN
  NULL;
END;
/

Then you DECLARE a function; this includes its own PL/SQL BEGIN ... END; block that will be nested within the DECLARE section of the anonymous outer block:

DECLARE
  TYPE type_a IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  output NUMBER := 1;

  FUNCTION fun_2 RETURN type_a IS
    dum type_a;
  BEGIN
    SELECT employee_id
    BULK COLLECT INTO dum
    FROM employees;

    RETURN dum;
  END fun_2;
BEGIN
  NULL;
END;
/

Then you declare a procedure; again this includes its own PL/SQL BEGIN ... END; block that will be nested within the DECLARE section of the anonymous outer block after the function:

DECLARE
  TYPE type_a IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  output NUMBER := 1;

  FUNCTION fun_2 RETURN type_a IS
    dum type_a;
  BEGIN
    SELECT employee_id
    BULK COLLECT INTO dum
    FROM employees;

    RETURN dum;
  END fun_2;

  PROCEDURE proc_1 AS
  BEGIN
    NULL;
  END proc_1;
BEGIN
  NULL;
END;
/

Back to your question:

why is it needed?

Because, otherwise you have two BEGIN ... END; blocks for the function and procedure nested within the DECLARE section starting from the first line but no BEGIN ... END; that matches that DECLARE on the first line.

Using consistent indentation would help spot this.

Bind variable used in BEGIN/END gets cleared

Upgraded to SQL Developer 17.2.0.188 and it went away.

Appears to be this bug: https://stackoverflow.com/a/43501389/84206

How do I declare a variable and use it in a subsequent query

PL/SQL block is enclosed with BEGIN/END keywords. Once you leave the block you end your PL/SQL context. And enter plain SQL context where PL/SQL variables are not known.

In simple words :-)

This is correct what you have learned:

• you need to select values into variables
• variables must be declared in DECLARE part of PL/SQL block
• if you want to return a variable to the client - e.g. to have it displayed - you need to use Oracle's package dbms_output.

Like this:

Declare
    v_Today Date;
Begin
    Select sysdate into v_Today from dual;
    dbms_output.put_line(v_Today);
End;

You will not see a thing until you issue before PL/SQL block:

SET SERVEROUTPUT ON

This blog post can help: https://blogs.oracle.com/connect/post/building-with-blocks
Steve published whole series of posts for PL/SQL beginners. This is just the first one.

Select statement in If block in Oracle PL/SQL

I see you use INTO keyword at last.. It has to be next to SELECT.

Add this to DECLARE

rec  t_tf_row;

And then,

SELECT z.ZIPZONE_ID, z.BEGINZIPCODE, z.ENDZIPCODE, z.TAXING_CODE_ID, g.su_rate
into rec
from SBXEXT.GSW_ZIP_ZONES z
join gsw_geocodes g on z.taxing_code_id= g.taxing_code_id 
where ZIPZONE_ID in (zip_id1,zip_id2)

---

Related Topics