How to Catch OUT parameter of a PL/SQL procedure and calling another PL/SQL procedure

In PL/SQL, you can capture an OUT parameter from one procedure and pass it to another procedure by using PL/SQL variables. Below is an example demonstrating this process:

---

Example: Catching OUT parameter and calling another procedure

1. Procedure 1: Returns an output parameter.
2. Procedure 2: Accepts the output of Procedure 1 as an input parameter.

---

Step 1: Create First Procedure (with OUT parameter)

CREATE OR REPLACE PROCEDURE get_employee_salary (
    p_emp_id IN NUMBER,
    p_salary OUT NUMBER
) AS
BEGIN
    -- Assume we fetch salary from EMPLOYEES table
    SELECT salary INTO p_salary FROM EMPLOYEES WHERE employee_id = p_emp_id;
END;
/

---

Step 2: Create Second Procedure (Takes OUT parameter as INPUT)

CREATE OR REPLACE PROCEDURE process_salary (
    p_emp_id IN NUMBER,
    p_salary IN NUMBER
) AS
BEGIN
    -- Process the salary (For example, log it or insert into another table)
    INSERT INTO SALARY_AUDIT (employee_id, salary, processed_date)
    VALUES (p_emp_id, p_salary, SYSDATE);
    
    COMMIT;
END;
/

---

Step 3: Call Procedures in an Anonymous Block

DECLARE
    v_emp_id NUMBER := 101;  -- Employee ID
    v_salary NUMBER;          -- Variable to hold the OUT parameter value
BEGIN
    -- Call the first procedure to get salary
    get_employee_salary(v_emp_id, v_salary);
    
    -- Call the second procedure using the OUT parameter from the first
    process_salary(v_emp_id, v_salary);
    
    DBMS_OUTPUT.PUT_LINE('Salary processed for Employee ID: ' || v_emp_id || ', Salary: ' || v_salary);
END;
/

---

Explanation:

1. get_employee_salary fetches the salary based on the Employee ID and returns it via an OUT parameter (p_salary).
2. process_salary receives the Employee ID and the salary from the first procedure and logs it into the SALARY_AUDIT table.
3. The anonymous PL/SQL block calls Procedure 1, stores the result in v_salary, and then passes it to Procedure 2.

---

Key Points to Remember:

• OUT parameters are used to return values from a procedure.
• When calling a procedure with an OUT parameter, the calling program must declare a variable to store the returned value.
• The captured OUT parameter value can then be used to call another procedure.