Fri. Apr 23rd, 2021

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

 38 total views,  2 views today

By Susanto Paul

Susanto is an Oracle ACE, a qualified MCA, MBA, and a highly-skilled Senior Oracle Specialist. He is an enthusiastic Blogger and YouTuber who helps learners to solve their complex problems more efficiently. He has 9+ years of experience in multiple technologies like AWS, Oracle ADF, Oracle APEX, Oracle JET, Oracle VBCS, Oracle IDCS, Oracle PL/SQL, Oracle Integration Cloud, Java, JDBC, Servlets, JSP, Spring, Hibernate, HTML5, CSS3, JavaScript, TypeScript, NodesJS, Angular, MySQL, Oracle WebLogic Server, JUnit, JMeter, Selenium Web Driver, etc. He is a certified: Oracle Certified Professional Java SE 6 Programmer, Oracle ADF 11g Certified Implementation Specialist, Oracle Cloud Platform Application Integration 2020 Certified Specialist, Oracle Cloud Infrastructure Foundations 2020 Certified Associate, and Oracle Cloud Infrastructure Developer 2020 Certified Associate

Leave a Reply

Your email address will not be published. Required fields are marked *