Requirement: I want to execute the PL/SQL Procedure with IN and OUT Parameters in Oracle.
Solution: For the solution of the above requirement, let us first create the PL/SQL procedure having both the IN and OUT parameters. Thus, the complete code for the PL/SQL procedure is shown below:
CREATE OR REPLACE PROCEDURE proc_getempdetails (
ip_emp_no IN NUMBER,
op_f_name OUT VARCHAR2,
op_l_name OUT VARCHAR2
) IS
BEGIN
SELECT
first_name,
last_name
INTO
op_f_name,
op_l_name
FROM
employees
WHERE
employee_id = ip_emp_no;
END;Now, to execute the above PL/SQL procedure we can use the below anonymous PL/SQL block of codes.
SET SERVEROUTPUT ON;
DECLARE
v_f_name employees.first_name%TYPE;
v_l_name employees.last_name%TYPE;
BEGIN
proc_getempdetails(100, v_f_name, v_l_name);
dbms_output.put_line(v_f_name
|| ' '
|| v_l_name);
END;Thus, the output of the above anonymous PL/SQL block of codes is shown below:
Hence, the solution to our requirement.
If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.
Thanks & Regards,
Susanto Paul
1,607 total views, 5 views today