Oracle — Pass Value to IN query in Procedure | Code Factory

Reference Link : Link

Table Structure :

CREATE TABLE Emp (
EMP_CODE number(5),
EMP_NAME varchar2(25),
EMP_AGE number(5)
);

Insert Data :

insert into emp(emp_code, emp_name, emp_age) values(101, 'JAN', 1);
insert into emp(emp_code, emp_name, emp_age) values(102, 'FEB', 2);
insert into emp(emp_code, emp_name, emp_age) values(103, 'MAR', 3);
insert into emp(emp_code, emp_name, emp_age) values(104, 'APR', 4);
insert into emp(emp_code, emp_name, emp_age) values(105, 'MAY', 5);

Procedure :

CREATE OR REPLACE PROCEDURE "SEARCH_EMP" (EMP_IDS IN VARCHAR2)
IS
BEGIN
FOR i in (SELECT EMP_NAME FROM EMP
WHERE EMP_CODE IN
(SELECT REGEXP_SUBSTR(EMP_IDS,'[^,]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(EMP_IDS, '[^,]+', 1, LEVEL) IS NOT NULL ))
LOOP
DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || i.EMP_NAME);
END LOOP;
end SEARCH_EMP;

Call Procedure :

CALL SEARCH_EMP('102,103');

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store