0

it's probably only a misunderstanding of the different types of variables that exist in Oracle SQL and PL/SQL, but how can I use the return value of a PL/SQL function as input for another PL/SQL function inside a SQL script without having to manually set it as value of a DEFINE variable?

Here is the code (being run inside a SQL script inside Oracle SQL Developer):

-- some INSERTS/UPDATES/SELECTS ...
DEFINE in = 'somevalue';

VAR return1 NUMBER;
EXECUTE :return1 := someschema.somepackage.somefunction(in);
PRINT return1;
-- reasonable return value gets printed out

VAR return2 NUMBER;
EXECUTE :return2 := someschema.somefunction(return1);
--                                          ^
-- this does not work ----------------------+
-- (neither does ":return1")

DEFINE in2 = <manually enter value of "return1">
EXECUTE :return2 := someschema.somefunction(in2);
--                                          ^
-- this works ------------------------------+

-- some INSERTS/UPDATES/SELECTS ...

Thank's in advance.

Improve this question
8
  • Use an anonymous block, declare a variable for the OUT parameter. And use the same varible as IN parameter to the function. Commented Mar 13, 2015 at 13:35
  • Passing :return1 is correct, what happened when you tried? "Doesn't work" isn't very helpful. You haven't shown the ampersands for your substitution variable versions, so it isn't entirely clear what you are actully doing or seeing. Using a block may still easier and more appropriate. Commented Mar 13, 2015 at 13:44
  • You say the first call is to a procedure but it apparently returns values like a function. Does the first procedure have OUT parameters? Commented Mar 13, 2015 at 13:45
  • @AlexPoole: Sorry about that. Actually SQL Developer prompts me to enter a value for :return1. Commented Mar 16, 2015 at 9:38
  • @ruudvan: You're right, it indeed was a function and not a procedure (just edited it). It just was a "consistent typo" ... :-( Commented Mar 16, 2015 at 9:44

2 Answers 2

Reset to default
1

DEFINE and EXECUTE would work as expected in SQL*Plus. To execute your entire code in SQL Developer or as a script from a client, I would suggest you:

  1. Use an anonymous block.
  2. DECLARE a variable for the OUT parameter of procedure, and another variable to store the return value of the function.
  3. In the BEGIN-END block, call the procedure.
  4. And use the same varible to store the return value of the function.

For example,

DECLARE
   o_var ;
   f_var ;
BEGIN
   -- call the procedure
   package.procedure(o_var);

   --call the function
   SELECT package.function(o_var) INTO f_var FROM DUAL;

   -- do something
END;
/
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Your answer put me on the right track although I just realized that I should have talked about two functions instead of a function and a procedure (edited the question).
so my answer stressing out the difference between procedure and function is now obsolete and i'm deleting it :)
0

Below solution works for me.

DECLARE

   parameter_var ;

   assignment_var ;

BEGIN
  
   --call the function and assign it to assignment_var

   assignment_var:=function_xyz(parameter_var);

   -- use assignment_var for further computation
END;
/

Hope it helps others.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.