In this example, the function named demo_function calls the procedure demo_procedure and returns procedure's output.
Procedure to return a value:
CREATE OR REPLACE PROCEDURE demo_procedure (p_output OUT VARCHAR2)
AS
l_dummy VARCHAR2(20);
BEGIN
SELECT dummy INTO l_dummy FROM dual;
IF l_dummy IS NOT NULL THEN
p_output := 'YES';
ELSE
p_output := 'NO';
END IF;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('An unknown exception occurred '||sqlerrm);
END;
/
Checking the output of the demo_procedure:
DECLARE
l_op VARCHAR2(20);
BEGIN
demo_procedure(l_op);
dbms_output.put_line(l_op);
END;
/
Function to call the demo_procedure:
CREATE OR REPLACE FUNCTION demo_function
RETURN VARCHAR2 AS
l_op VARCHAR2(10);
BEGIN
demo_procedure(l_op);
RETURN l_op;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error in executing procedure demo_procedure '||sqlerrm);
END;
/
Output:
SELECT demo_function FROM dual;
So, this is how a procedure can be called from a function.
Cheers!!
