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

CREATE TABLE Emp (
EMP_CODE number(5),
EMP_NAME varchar2(25),
EMP_AGE number(5)
);
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);
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;

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Decoding the Distributed Job Scheduler System

Day 5 of 30— Sum Prime Numbers Functionally

Laravel Model Caching

What is Laravel Zero and how to get started?

Improving Code Coverage In Android App

CodeIgniter 4 Tutorial part 1 ~ RESTful API JWT Authentication

From Dagger components to manual dependency injection

How I fell in love with flutter

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
Code Factory

Code Factory

More from Medium

BitLocker using TPM

Microsoft Build 2022 Debrief — Day 1

Redis key expiry

Python Design Patterns