Press "Enter" to skip to content

Stored Procedure in Oracle Example With IN OUT Parameter

Below is an example of a stored procedure in Oracle with IN OUT parameter. It will take employee number (i_empno) as IN parameter and will return employee name (o_ename) as OUT parameter.

Stored Procedure IN-OUT Parameter Example

CREATE OR REPLACE PROCEDURE GET_EMP_NAME (i_empno IN emp.empno%TYPE,
o_ename OUT emp.ename%TYPE)
IS
CURSOR c_ename (p_empno emp.empno%TYPE)
IS
SELECT ename
FROM emp
WHERE empno = p_empno;
BEGIN
OPEN c_ename (i_empno);

FETCH c_ename INTO o_ename;

CLOSE c_ename;
END get_emp_name;

Execute Procedure GET_EMP_NAME

SET SERVEROUTPUT ON;
DECLARE
v_name emp.ename%TYPE;
BEGIN
get_emp_name (7566, v_name);
DBMS_OUTPUT.put_line (v_name);
END;

Output

JONES
PL/SQL procedure successfully completed.

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Be First to Comment

    Leave a Reply

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