Amit Bhati Amit Bhati - 2 years ago 75
SQL Question

How to write a Oracle procedure to return two cursors based on passed arrray

Requirement
:- I am trying to write a procedure, which will take an array of ids as an IN param,
and two output cursors as out param. Basically the procedure should loop the input array and check whether id exist in
say table Employee, it should return two cursors

a) first cursors - which contains all the ids that present

b) Second cursor - which contains all the ids that are absent

Please help me to go about solving this problem

Created the Employee Table:-

CREATE TABLE EMPLOYEE
(ID NUMBER(10));


Inserted the ids in it:-

INSERT INTO EMPLOYEE VALUES(1);
INSERT INTO EMPLOYEE VALUES(2);
INSERT INTO EMPLOYEE VALUES(3);
INSERT INTO EMPLOYEE VALUES(4);
INSERT INTO EMPLOYEE VALUES(5);
INSERT INTO EMPLOYEE VALUES(6);
INSERT INTO EMPLOYEE VALUES(7);
INSERT INTO EMPLOYEE VALUES(9);
INSERT INTO EMPLOYEE VALUES(10);
INSERT INTO EMPLOYEE VALUES(11);


Created the Custom input array:-

CREATE OR REPLACE TYPE VALID_ID AS VARRAY(500) OF NUMBER(10);


Declaration of procedure is something like this:-

CREATE OR REPLACE PROCEDURE VALIDATE_ID(IN_ARR IN VALID_ID,PRSNT_ID OUT SYS_REFCURSOR,MISS_ID OUT SYS_REFCURSOR)


If input array to proc contains :-
1,2,3,4,5,6,7,8,9,12,13


Then PRSNT_ID should contain:-
1,2,3,4,5,6,7,9
i.e. ids present in employee table

and MISS_ID should contain:-
8,12,13
i.e. ids missing in employee table

Answer Source
create or replace procedure cursro_retur(arr in VALID_ID, pc1 out sys_refcursor, pc2 out sys_refcursor) is 
begin 
 open pc1  for 'select id from EMPLOYEE t ,table(:arr) b where t.id = b.column_value' using arr; -- valid
 open pc2 for 'SELECT b.column_value FROM employee a,table(:arr) b
 where  a.id (+) = b.column_value 
  and a.id is null' using arr;
end;

declare 
  pc1 sys_refcursor;
  pc2 sys_refcursor;
  v_list1  VALID_ID;
  v_list2  VALID_ID;
begin 
 cursro_retur( VALID_ID(1,2,3,8,11,89,11),pc1,pc2);

fetch pc1 bulk collect into v_list1;
fetch pc2 bulk collect  into v_list2;
 close pc1;
 close pc2; 
 dbms_output.put_line('valid');
 for rec in v_list1.first ..v_list1.last loop
  dbms_output.put_line(v_list1(rec));
 end loop; 
 dbms_output.put_line('no valid');
  for rec in v_list2.first ..v_list2.last loop
  dbms_output.put_line(v_list2(rec));
 end loop; 
end;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download