Return Value From Oracle Variable

Question

I have the following PL/SQL statement that I want to execute.

DECLARE
    V_REFNUM NUMBER := 0;
BEGIN
    SELECT NEXT_NUMBER_VALUE INTO V_REFNUM FROM SETTINGS WHERE SETTINGS_ID = 1;
    V_REFNUM := (V_REFNUM + 1);

    -- SELECT V_REFNUM FROM DUAL
END;

The problem is that I can't seem to get the variable to return form me. This works fine in T-SQL where my statement is like this

DECLARE @refNum int = 0
SELECT @refNum = NEXT_NUMBER_VALUE FROM SETTINGS WHERE SETTINGS_ID = 1
SET @refNum = (@refNum + 1)
SELECT @refNum

It seems a very simple thing to do on Microsoft SQL Server. Why is this so hard to figure out on Oracle? Can anyone help?

does your select always return a value? how sure are you that it is not throwing a no data found exception — Shankar, Jun 17 '15 at 18:55
Because this data is setup at the time of install and then incremented — dacke.geo, Jun 17 '15 at 19:22

score 1 · Answer 1 · answered Jun 17 '15 at 18:58

In SQLPLUS

REM SQLPLUS INTERNAL COMMAND
var V_REFNUM number;

REM ANNONYMOUS PL/SQL block to be evaluated on the DB server side
BEGIN
    SELECT NEXT_NUMBER_VALUE INTO :V_REFNUM FROM SETTINGS WHERE SETTINGS_ID = 1;
    :V_REFNUM := (:V_REFNUM + 1);
    -- SELECT V_REFNUM FROM DUAL
END;

REM SQLPLUS COMMAND
PRINT V_REFNUM

Oracle annonymous block is "closed". Also Oracle stored procedures do not support "implicit" returns. Either you have to use IN/OUT bind paramter for PL/SQL block or you have to call dbms_output.put_line.

The reason behind is that SQL and PL/SQL are evaluated by different virtual machines the there is nothing like a variable in a context of SQL. Variables live online within PL/SQL and they are disposed then execution finishes. PL/SQL code can does not have anything like standard output and therefore you can not print SQL result anywhere.

I am able to output using the DBMS_OUTPUT commands and see the values but is there no way to pull the value after the fact? — dacke.geo, Jun 17 '15 at 19:22
In Oracle "everything" is a cursor. So procedure/function or an anonymous PL/SQL block can return variable of type SYS_REFCURSOR and you can fetch data you of that cursor. But the cursor has to be explicitly declared as part of the interface (see this example http://stackoverflow.com/a/2153308/836215). — ibre5041, Jun 18 '15 at 10:03

Return Value From Oracle Variable

1 Answers1