How to Select a Variable As Query Result in Oracle PLSQL

Question

I've looked high and low but have not been able to figure this out.

I have this function defined in Oracle:

FUNCTION MY_FUNCTION(
    INPUTVAR1 IN OUT NUMBER,
    INPUTVAR2 VARCHAR2
) RETURN NUMBER;

The person who created this function is basically returning the input parameter if it's not 0. Otherwise if INPUTVAR1 is 0, it will return a new value. I want that new value.

In T-SQL it's so simple it's stupid:

DECLARE @MyVar INT = 0;
SET @MyVar = MY_FUNCTION(@MyVar, NULL);
SELECT @MyVar as Result;

But In Oracle, I cannot figure out how to return the return value as a result. This is what I have so far:

DECLARE MyVar NUMBER := 0;
BEGIN
MyVar := MY_FUNCTION(INPUTVAR1 => MyVar, INPUTVAR2 => NULL)
END;

But I can't figure out how to use MyVar in the result. I definitely cannot select it. I tried DBMS_OUTPUT.PUT_LINE(MyVar); but no luck.

Anyone know how I can return the value of MyVar?

Answer 1

So I was able to find this solution which works only if you have 12c or above JDBC drivers:

DECLARE MyVar NUMBER := 0;
    rc sys_refcursor; 
BEGIN
MyVar := MY_FUNCTION(INPUTVAR1 => MyVar, INPUTVAR2 => NULL)

open rc for SELECT MyVar FROM dual; 
dbms_sql.return_result(rc); 

END;

I'd like to meet the person who thought it was a good idea to have a FUNCTION with both IN/OUT parameters and a return value.

Answer 2

Redefine the function to not having an OUT parameter:

CREATE FUNCTION MY_FUNCTION(
    INPUTVAR1 IN NUMBER,
    INPUTVAR2 IN VARCHAR2
) RETURN NUMBER
IS
BEGIN
  RETURN INPUTVAR1;
END;
/

Then you can use it in an SQL query:

SELECT MY_FUNCTION( INPUTVAR1 => 0, INPUTVAR2 => NULL ) AS value
FROM   DUAL

Output:

| VALUE |
| ----: |
|     0 |

db<>fiddle here

---

Update

If you cannot redefine the function to remove the OUT parameter then write a wrapper around it:

CREATE FUNCTION MY_FUNCTION_WRAPPER(
    INPUTVAR1 IN NUMBER,
    INPUTVAR2 IN VARCHAR2
) RETURN NUMBER
IS
  var1 NUMBER := INPUTVAR1;
BEGIN
  RETURN MY_FUNCTION( INPUTVAR1 => var1, INPUTVAR2 => INPUTVAR2 );
END;
/

Then you can call it from an SQL statement:

SELECT MY_FUNCTION_WRAPPER( INPUTVAR1 => 0, INPUTVAR2 => NULL ) AS value
FROM   DUAL;

Output:

| VALUE |
| ----: |
|     0 |

db<>fiddle here

Answer 3

dbms_output works fine for me:

create or replace FUNCTION MY_FUNCTION(
    INPUTVAR1 IN OUT NUMBER,
    INPUTVAR2 VARCHAR2
) RETURN NUMBER is
begin
    if inputvar1 = 0 then
        return -1;
    else
        return inputvar1;
    end if;
end;
/

declare
  my_var number := 0;
  my_var2 number := 0;
begin
  my_var2 := my_function(my_var, 'A');
  dbms_output.put_line('my_var:' || my_var);
  dbms_output.put_line('my_var2:' || my_var2);
end;
/

my_var:0
my_var2:-1

Edit: I am a little concerned about having an OUT variable in a function. That's usually a sign of poor coding, which is why I used 2 variables above, so you could see whether the function was "returning" the new value by modifying INPUTVAR1, or by using the return value. Or both.

Answer 4

Your original Oracle code, with dbms_output, is correct. Basically, after calling a function you use the assigned variable exactly as if you had assigned it a value (same as SQL Server). So a function call
"x := some_function(y)" that returns a value (lets say 9), is after the call exactly the same as if you had written
"x := 9". So again your function was correct.
However, you indicated you use SQL Developer. My guess is you didn't have dbms_output turned on. To do that on SQL Developer menu bar select View then click Dbms output. This will open a dbms_output window. But your not quite done. It the Dbms output window click the large green +, select the appropriate schema (if needed) and click ok. Dbms_output will now be shown in that window.

Answer 5

try this

 DECLARE MyVar NUMBER := 0;
   varout VARCHAR2();
 BEGIN
 --MyVar := MY_FUNCTION(INPUTVAR1 => MyVar, INPUTVAR2 => NULL)
 select MY_FUNCTION(INPUTVAR1 => MyVar, INPUTVAR2 => NULL) INTO varout from dual;
  dbms_output.put_line(MyVar);
  END;