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?

How are you running this? SQL*Plus or some other tool? also, what do you mean with "return the value"? Do you want to see it on screen, return it to a caller and then use it, say, in another procedure, … ? — Aleksej
– Aleksej, Commented Jul 10, 2019 at 20:29
From your own answer, it seems you want to return a result set or cursor, not the function result. That question is answered here: stackoverflow.com/a/40360471/230471 (Perhaps answering @Aleksej's question would have clarified things. In another comment you mention it's for an ETL tool, though above you said you suggested it was for use in SQL Developer, a development IDE.) — William Robertson
– William Robertson, Commented Jul 12, 2019 at 6:37

Sal · Accepted Answer · 2024-11-10 00:59:12Z

4

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;

edited Nov 10, 2024 at 0:59

answered Jul 10, 2019 at 22:37

Sal

5,7156 gold badges32 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

William Robertson Over a year ago

The 12c new feature here is the implicit result set. Prior to 12c you could explicitly return a ref cursor. How to do this will depend on your ETL framework.

MT0 · Accepted Answer · 2019-07-10 22:31:17Z

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

I wish I could remove the out parameter but I can't as it will break any existing callers.
@Sal Updated - write a wrapper around the function that does not have the OUT parameter.
I was hoping to avoid creating a MY_FUNCTION2 function but I might have to go with this. I did find another solution which I'm posting here but because I can't update the java drivers (I'm using a third party ETL tool) I cannot use that solution.

kfinity · Accepted Answer · 2019-07-10 20:42:56Z

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.

This function was created 12 years ago by who knows who. Also, dbms_output.put_line won't work for me as I cannot access the output buffer from my etl tool unfortunately.

Belayer · Accepted Answer · 2019-07-10 23:22:40Z

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.

user11766905 · Accepted Answer · 2019-07-10 20:36:58Z

-1

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;

answered Jul 10, 2019 at 20:36

user11766905

11 bronze badge

1 Comment

MT0 Over a year ago

You cannot use a function in an SQL statement if it has an OUT parameter.

Collectives™ on Stack Overflow

How to Select a Variable As Query Result in Oracle PLSQL

5 Answers 5

1 Comment

3 Comments

1 Comment

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

3 Comments

1 Comment

Comments

1 Comment

Linked

Related