CODEWITHSUNDEEP
oracle-databaseplsql
user1099385
user1099385

Reputation:

Oracle SQL PL/SQL: use return value of function as input for another function?

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.

Upvotes: 0

Views: 6745

Answers (2)

Bhawana Solanki
Bhawana Solanki

Reputation: 55

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.

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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;
/

Upvotes: 1

Related Questions