Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

Oracle: On Functions Returning Two or Multiple Values

Mathematically, a deterministic function always returns same value no matter how many times you call. An exception is random function. Any function which involves a random functions will return different values at different times. Here, we are going to discuss about a deterministic Oracle function returning multiple values. To put it simple, In Oracle, can a function have two OUT parameters? In short, yes.

In Oracle, we always know that a function can and should return a value. Is it possible to send two return values?

Checkout.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> set serverout on
SQL> set scan off
SQL> create or replace function fun ( p_in_1  in number
2                                 , p_in_2 out number )
3                                 return number
4  is
5  begin
6    p_in_2 := 2*p_in_1;
7    return 0;
8  end;
9  /

Function created.

SQL> sho err
No errors.

SQL> var x number;
SQL> var y number;
SQL> exec : x :=0;

PL/SQL procedure successfully completed.

SQL> exec :y :=1;

PL/SQL procedure successfully completed.

SQL> select : x
2       , :y
3    from dual
4  /

: X        :Y
---------- ----------
0          1

SQL> exec :y := fun(10, : x);

PL/SQL procedure successfully completed.

SQL> select : x
2       , :y
3    from dual
4  /

: X        :Y
---------- ----------
20          0

So, there it is. I have just showed you, how to setup OUT variable for a Oracle function. The following will fail.
SQL> select fun(10, : x)
2    from dual
3  /
select fun(10, : x)
*
ERROR at line 1:
ORA-06572: Function FUN has out arguments

Of course, you can achieve this better with the help of procedure, and I would advice you to do this with the procedure way. This is one of the methods, I was thinking that should always use RETURN and can’t set variables OUT parameter. The above demonstration goes to show how you can use OUT parameter with RETURN in a function.

One response to “Oracle: On Functions Returning Two or Multiple Values

  1. Pingback: Tweets that mention Oracle: On Functions Returning Two or Multiple Values « Abstract Confusions -- Topsy.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: