Please help me with the following scenario
I need to call a Stored Procedure inside another one. the inner SP would return a number value which I need to store it in a local variable.
rough code
AssignSpResult NUMBER;
AssignSpResult = SPtobecalled(Param1,Param2, OutParam);
This syntax is most probably wrong, so please help correct it
Here is an example of how it might be:
HR\XE> create or replace procedure Proc1(p_out out number)
2 is
3 begin
4 p_out := 123;
5* end;
HR\XE> /
Procedure created.
HR\XE> create or replace procedure Proc2
2 is
3 l_val number;
4 begin
5 Proc1(l_val);
6 dbms_output.put_line('Here is a value returned by Proc1: ' || to_char(l_val));
7 end;
8 /
Procedure created.
HR\XE> set serveroutput on;
HR\XE> exec Proc2;
Here is a value returned by Proc1: 123
PL/SQL procedure successfully completed
Depending on your needs it might be more convenient to use functions to return a result of a procedural processing of data. Here is an example:
HR\XE> create or replace function F1 return number
2 is
3 l_ret_value number;
4 begin
5 l_ret_value := 123;
6 return l_ret_value;
7 end;
HR\XE> /
Function created.
HR\XE> create or replace procedure Proc3
2 is
3 l_val number;
4 begin
5 l_val := F1;
6 dbms_output.put_line('Value returned by the F1 function: ' ||
To_Char(l_val));
7 -- Or
8 dbms_output.put_line('Value returned by the F1 function: ' || To_Char(F1));
9 end;
HR\XE> /
Procedure created.
HR\XE> set serveroutput on;
HR\XE> exec proc3;
Value returned by the F1 function: 123
Value returned by the F1 function: 123
PL/SQL procedure successfully completed.
HR\XE>
A stored procedure does not return a value it takes IN, OUT or IN OUT parameters. So probably your have to call:
SPtobecalled(Param1,Param2, AssignSpResult );
