How to call a user defined function using the 'CALL' statement?

Question

I was going thru the Oracle documentation for the CALL statement. It is indicated there that the CALL statement can be used to call a user defined function by using INTO (not a function inside a package). I've tried loads of combinations but can't seem to get the right one. Can someone give me an example on how to do this? Thanks.

EDIT:

I've tried the example below in SQL Developer but i am getting an error.

variable x number; 
call f(10) into :x;

I'm getting a bang in line 2 and the error:

SQL Error: ORA-01008: not all variables bound<br>
01008. 00000 -  "not all variables bound"

Please post what you tried and the problems you are having, so that people can help you to edit your code — Aleksej
– Aleksej, Commented Nov 22, 2016 at 8:54
I have a scenario where a function performs a DML so i'm looking for other ways to execute it. — victor
– victor, Commented Nov 22, 2016 at 10:13

Aleksej · Accepted Answer · 2016-11-22 09:06:47Z

2

From the Oracle documentation:

VARIABLE x VARCHAR2(25);

CALL warehouse_typ(456, 'Warehouse 456', 2236).ret_name()
   INTO :x;

Another example:

create function f(n number) return number is
begin
    return n * 2;
end;

SQL> variable x number;
SQL> call f(10) into :x;

Call completed.

SQL> print x;

         X
----------
        20

answered Nov 22, 2016 at 9:06

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

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

2 Comments

Nick Krasnov Over a year ago

Worth noting that if a function has no arguments for a call to be successful empty parenthesis are required: call f1() into :var

victor Over a year ago

I'm trying to run your example in SQL developer but to no avail. I'm getting this error: SQL Error: ORA-01008: not all variables bound 01008. 00000 - "not all variables bound" when running variable x number; call f(10) into :x; it also shows a syntax error on line 2. Thanks!

Collectives™ on Stack Overflow

How to call a user defined function using the 'CALL' statement?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related