[SOLVED] How to Select a Variable As Query Result in Oracle PLSQL

How to Select a Variable As Query Result in Oracle PLSQL

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?

Solution

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;