Calling Procedures from Functions in Oracle

Calling Procedures from Functions in Oracle

I just want to clarify the confusion whether we can call procedure from function or no.

Can we call a function from a procedure? 

Answer is Yes. 

We can call procedure from functions in Oracle. 

Following is the code for procedure which takes a number as input and it will multiply the number with 10 and gives the result as output.

CREATE or REPLACE PROCEDURE sp_Test(v_Num_IN IN Number,v_Num_out OUT Number)

IS

Begin

v_Num_Out:= v_Num_IN*10;

END;

/

SQL> variable v_out number;

SQL> exec sp_Test(10,:V_out);

PL/SQL procedure successfully completed.

SQL> PRINT :V_out

     V_OUT

    ----------

       100

Following is the code for function which takes a number as input, and it calls the procedure ‘sp_Test’ and holds the output in a local variable. We are going to return the local variable from the function.

CREATE or REPLACE FUNCTION fn_Test_Calling_Sp(v_In number) RETURN NUMBER

AS

v_Ret_Val number;

BEGIN

sp_test(V_In,v_Ret_val); /* Calling the procedure*/

Return v_Ret_val;

END;

/

Let’s call the above function through a SELECT statement. 

SQL> select fn_Test_Calling_Sp(10) from dual;

FN_TEST_CALLING_SP(10)

----------------------

                   100

Hope it is clear.

Thanks.