create table tst_main
  (
  id number primary key,
  detail1 number,
  detail2 varchar(10),
  detail3 varchar(10)
  );

 create table tst_main_lk3
(
  main_id number references tst_main,
  main_lk_3 varchar(10),
  primary key (main_id, main_lk_3)
);



CREATE OR REPLACE PROCEDURE p_test_search
(   --Input Parameters
    int_id IN number,
    int_detail1 number,
    vc_detail2 varchar,
    vc_detail3 varchar,
    vc_main_lk_3 varchar,
    CUR_main     OUT SYS_REFCURSOR
    CUR_lk3  OUT SYS_REFCURSOR
  )
AS
  S_DB_ERROR      VARCHAR2(100);
BEGIN
    --the fist cursor is for records in main that match input parameters in either table tst_main or 
    -- table tst_main_lk3 
    open cur_main for
    select * from TST_MAIN
    where 
    (int_id is null or id = int_id)
    AND
    (int_detail1 is null or detail1=int_detail1)
      AND
    (int_detail2 is null or detail2=int_detail2)
    AND
    (int_detail3 is null or detail3=int_detail3)
    AND
     (vc_main_lk_3 is null or id in 
      (select main_id from tst_main_lk3 where main_lk_3= vc_main_lk_3));
    -- The second cursor is for records in tst_main_lk3 that match the first cursor's main_id
    open cur_lk3 for 
      select * from tst_main_lk3
      where main_id in -----???? cur_main.id

EXCEPTION WHEN OTHERS THEN 
    --fill error varchar
    --S_DB_ERROR := SQLCODE || '-' || SQLERRM;
    -- call P_LOG_ERROR to log the exception error to event log table
    --P_LOG_ERROR (S_APP_USER, S_DB_ERROR, S_MODULE_Name, S_PARAMS);
     -- raise exception for caller
RAISE;
END;

create or replace 
PROCEDURE p_test_search
(  P_DEPTNO IN NUMBER,
    CUR_main     OUT SYS_REFCURSOR,
    CUR_lk3  OUT SYS_REFCURSOR
  )
AS
  S_DB_ERROR      VARCHAR2(100);
BEGIN
--RETURNS DATA FOR MAIN AS WELL AS SECOND TABLE
    OPEN cur_main FOR
    SELECT DEPTNO FROM emp  WHERE DEPTNO=P_DEPTNO --SECOND TABLE
    UNION ALL
    SELECT DEPTNO FROM DEPT WHERE DEPTNO=P_DEPTNO;--MAIN TABLE
   
   --RETURNS DATA FOR MAIN TABLE ONLY(how many records first cursor have returned for main same record will be returned)
    OPEN cur_lk3 FOR 
    SELECT DEPTNO FROM DEPT WHERE DEPTNO=P_DEPTNO;


EXCEPTION WHEN OTHERS THEN 

RAISE;
END;




var x refcursor;
var y refcursor;
set autoprint on;
BEGIN
p_test_search(10,:x,:y);
end;

create or replace 
PROCEDURE p_test_search
(  p_loc IN vARCHAR2,
    CUR_main     OUT SYS_REFCURSOR,
    CUR_lk3  OUT SYS_REFCURSOR
  )
AS
  S_DB_ERROR      VARCHAR2(100);
  l_deptno number;
BEGIN
--RETURNS DATA FOR MAIN table
    OPEN cur_main FOR
    SELECT * FROM DEPT WHERE loc=p_loc;--MAIN TABLE   
   
    SELECT DEPTNO into l_deptno FROM DEPT WHERE loc=p_loc;    
    
   --RETURNS DATA FOR other TABLE 
    OPEN cur_lk3 FOR 
    SELECT * FROM emp WHERE DEPTNO=l_deptno;

EXCEPTION WHEN OTHERS THEN 

RAISE;
END;


var x refcursor;
var y refcursor;
set autoprint on;
BEGIN
p_test_search('DALLAS',:x,:y);
end;

EXCEPTION WHEN OTHERS THEN 

RAISE;

 SELECT DEPTNO into l_deptno FROM DEPT WHERE loc=p_loc;    

open cur_lk3 for 
      select * from tst_main_lk3
      where main_id in -----???? cur_main.id

open cur_lk3 for 
      select * from tst_main_lk3
      where main_id = cur_main.main_id

 1: select * 
 2:   from tst_main_lk3
 3:  where main_id in (select main_id 
 4: 		     from TST_MAIN
 5: 		    where (int_id is null or id = int_id)
 6:		      AND (int_detail1 is null or detail1=int_detail1)
 7:		      AND (int_detail2 is null or detail2=int_detail2)
 8:		      AND (int_detail3 is null or detail3=int_detail3)
 9:		      AND (   vc_main_lk_3     is null 
10:			   or id in (select main_id from tst_main_lk3 where main_lk_3= vc_main_lk_3))
11:		  ); 

open cur_lk3 for 
      select * from tst_main_lk3
      where main_id in -----???? cur_main.id

  TYPE doc_type 
  IS TABLE OF arc_tst_main%ROWTYPE INDEX BY PLS_INTEGER;
  l_doc doc_type;
  s_doc_serial_in varchar2(1000);

 LOOP
  FETCH cur_main BULK COLLECT INTO l_doc;
  EXIT WHEN CUR_DOCUMENT%NOTFOUND;
  END LOOP;

  --loop over the table l_doc to construct the in clause string
  FOR indx IN 1 .. l_doc.count 
  LOOP
   s_doc_serial_in := s_doc_serial_in || l_doc(indx).document_serial ||',';
  END LOOP;

  --Remove the extra comma at the end for the string
  s_doc_serial_in := SUBSTR (s_doc_serial_in,  0,  LENGTH(s_doc_serial_in)-1) ;

  OPEN cur_lk3 for 
      select * from tst_main_lk3
      where main_id in (s_doc_serial_in);

--...repeat the same for the rest of the 1:M cursors

create or replace 
PROCEDURE p_test_search
(  p_loc IN VARCHAR2,--first table search
   p_job  in VARCHAR2,--second table search
    CUR_main     OUT SYS_REFCURSOR,
    CUR_lk3  OUT SYS_REFCURSOR
  )
AS
  S_DB_ERROR      VARCHAR2(100);
  l_deptno number;
BEGIN

for i in ( SELECT * FROM dept WHERE loc=p_loc) loop --main cursor
    dbms_output.put_line(i.deptno);                         --main cursor data
   FOR j IN (SELECT * FROM emp WHERE DEPTNO=i.deptno AND job=p_job) loop --passing main cursor data and parameter data
    dbms_output.put_line('ccc'|| j.deptno||' '||j.ename);   --getting second cursor data
   end loop;

end loop;

-- the same process as above  but needs to returned as cursor
OPEN CUR_main
for select * from dept where loc=p_loc;  --main cursor data

--first secondary cursor
OPEN CUR_lk3 FOR
SELECT * FROM dept,emp WHERE emp.deptno=dept.deptno -- second cursor data (main table join with second table)
and dept.loc=p_loc AND job=p_job;

END;



var x refcursor;
var y refcursor;
set autoprint oN;
BEGIN
p_test_search('SALES','SALESMAN',:x,:y);
END;