CODEWITHSUNDEEP
sql-serveroracle-databasevariablesplsql
dacke.geo
dacke.geo

Reputation: 343

Return Value From Oracle Variable

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?

Upvotes: 0

Views: 2313

Answers (1)

ibre5041
ibre5041

Reputation: 5288

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.

Upvotes: 1

Related Questions