ARRAY IN LIST VALUE='001','003', '004'

CREATE TABLE EMP
( EMPID VARCHAR2(3),
  EMPNAME VARCHAR2(25),
  DEPTID  VARCHAR2(3));


INSERT INTO EMP VALUES ('001', 'X', '01') ;
INSERT INTO EMP VALUES ('002', 'Y', '02') ;
INSERT INTO EMP VALUES ('003', 'Z', '02') ;
INSERT INTO EMP VALUES ('004', 'A', '03') ;
INSERT INTO EMP VALUES ('005', 'B', '04') ;


CREATE TABLE NEW_EMP
( EMPID VARCHAR2(3),
  EMPNAME VARCHAR2(25),
  DEPTID  VARCHAR2(3));

 EMPID  EMPNAME DEPTID STATUS

  001    X    01    T
  002    Y    02    F
  003    Z    02    T
  004    A    03    T
  005    B    04    F 

CREATE OR REPLACE PROCEDURE Test
    (i_emp_id IN VARCHAR2)
  AS
   BEGIN
   	FOR i IN
       insert into new_emp 
  	  (SELECT empid, empname, deptid,'T'
   	   FROM   emp
   	   WHERE  empid IN
   		  (SELECT *
 		   FROM   TABLE (CAST (str2tbl (i_emp_id) AS MyTableType)))
   	   ) union all
		(SELECT empid, empname, deptid,'F'
   	   FROM   emp
   	   WHERE  empid NOT IN
 		  (SELECT *
 		   FROM   TABLE (CAST (str2tbl (i_emp_id) AS MyTableType)))       
 	LOOP
  	  --DBMS_OUTPUT.put_line ('EmpNo ' || i.empno);
  	END LOOP;
END Test;
/

INSERT INTO new_emp (<columns>)
SELECT <columns from array>, <columns from emp)
(CASE WHEN emp.emp_id IS NULL THEN 'F'
     ELSE 'T'
END) status
FROM array, emp
WHERE emp.empid(+) = array.empid

CREATE OR REPLACE PROCEDURE Test
    (i_emp_id IN VARCHAR2)
  AS
   BEGIN
   	
       insert into new_emp 
  	  (SELECT empid, empname, deptid,'T'
   	   FROM   emp
   	   WHERE  empid IN
   		  (SELECT *
 		   FROM   TABLE (CAST (str2tbl (i_emp_id) AS MyTableType)))
   	   ) union all
		(SELECT empid, empname, deptid,'F'
   	   FROM   emp
   	   WHERE  empid NOT IN
 		  (SELECT *
 		   FROM   TABLE (CAST (str2tbl (i_emp_id) AS MyTableType)))       
 	
END Test;
/

SCOTT@orcl_11g> CREATE TABLE OLD_EMP
  2  ( EMPID VARCHAR2(3),
  3    EMPNAME VARCHAR2(25),
  4    DEPTID  VARCHAR2(3));

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> BEGIN
  2    INSERT INTO old_EMP VALUES ('001', 'X', '01') ;
  3    INSERT INTO old_EMP VALUES ('002', 'Y', '02') ;
  4    INSERT INTO old_EMP VALUES ('003', 'Z', '02') ;
  5    INSERT INTO old_EMP VALUES ('004', 'A', '03') ;
  6    INSERT INTO old_EMP VALUES ('005', 'B', '04') ;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE TABLE NEW_EMP
  2  ( EMPID VARCHAR2(3),
  3    EMPNAME VARCHAR2(25),
  4    DEPTID  VARCHAR2(3),
  5    STATUS  VARCHAR2 (1));

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE Test
  2    (i_depts IN VARCHAR2)
  3  AS
  4  BEGIN
  5    INSERT INTO new_emp (empid, empname, deptid, status)
  6    SELECT old_emp.empid, old_emp.empname, old_emp.deptid,
  7  	      NVL2 (array.COLUMN_VALUE, 'T', 'F')
  8    FROM   old_emp,
  9  	      TABLE (CAST (str2tbl (i_depts) AS MyTableType)) array
 10    WHERE  old_emp.empid = array.COLUMN_VALUE (+);
 11  END Test;
 12  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> execute test ('001,003,004')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM new_emp ORDER BY empid
  2  /

EMP EMPNAME                   DEP S
--- ------------------------- --- -
001 X                         01  T
002 Y                         02  F
003 Z                         02  T
004 A                         03  T
005 B                         04  F

SCOTT@orcl_11g>