Hi Team ,
I need some help here for performance improvement

I have an XML as below ,this XML would be input to my PL/SQL function

<?xml version="1.0" encoding="UTF-8"?>
<n1:Request xmlns:n1="urn:global:cs:common">
<n1:Conditions>
<n1:Numbers>
<n1:Number>1000</n1:Number>
<n1:Number>1001</n1:Number>
<n1:Number>1002</n1:Number>
</n1:Numbers>
</n1:Conditions>
</n1:Request>

My table is as below
EMPLOYEENUMBER (varchar2(20))
PID (number(10))
xml_EmployeeDetails (XMLTYPE)

and my PL/SQL function is as below

create or replace FUNCTION

F_EMPLOYEE(in_XML IN CLOB)
RETURN number
IS
PRAGMA AUTONOMOUS_TRANSACTION;
V_PID VARCHAR2(300);
V_MAINQUERY clob;
MAINCURSOR SYS_REFCURSOR ;
V_PIDCUR varchar2(30);
xml_details CLOB;
sqlErr varchar2(200);
BEGIN
V_MAINQUERY:='(SELECT PID FROM IMPP.EMPLOYEE A WHERE A.EMPLOYEENUMBER IN (SELECT EMPLOYEENUMBER
FROM XMLTABLE (xmlnamespaces(
''urn:global:cs:common'' AS "tns0")
, ''/tns0:Request'' PASSING
XMLType('|| in_XML||') COLUMNS baselist XMLTYPE PATH


''tns0:Conditions'') t1,
XMLTABLE (xmlnamespaces(
''urn:global:cs:common'' AS "tns0")
,
''tns0:Conditions/tns0:Numbers/tns0:Number''
PASSING t1.baselist COLUMNS
EMPLOYEENUMBER VARCHAR2(88) PATH
''text()'') xmlT)';



OPEN MAINCURSOR FOR V_MAINQUERY;


LOOP
FETCH MAINCURSOR INTO V_PIDCUR;
EXIT WHEN MAINCURSOR%NOTFOUND;

BEGIN
SELECT A.PID,A.MSG_EMPDETAILSDETAILS.getClobVal()
INTO v_PID,xml_EmployeeDetails FROM impp.EMPLOYEE a WHERE A.EMPLOYEEPID=''||V_PIDCUR||'';
EXCEPTION WHEN OTHERS THEN
Raise_application_error ( -20002, 'An error has occurred in StudyDetails. ' || SQLERRM);
END;
return 0;

END LOOP;

return 0;
EXCEPTION
WHEN OTHERS THEN
sqlErr :=substr(sqlerrm,1,100);

END ;


the content which I have with bold quotes can be replaced with a comma separated list of employee numbers but like that I cannot have more than 1000 records.If I use sub query as above it increases the execution time.

for i in ( 
  SELECT A.PID,A.MSG_EMPDETAILSDETAILS.getClobVal() 
  FROM IMPP.EMPLOYEE A 
  WHERE A.EMPLOYEENUMBER IN
  (
  SELECT EMPLOYEENUMBER
  FROM  XMLTABLE (xmlnamespaces('urn:global:cs:common' AS "tns0"), 
        '/tns0:Request' PASSING XMLType(in_xml) 
        COLUMNS baselist XMLTYPE PATH 'tns0:Conditions') t1,
            XMLTABLE (xmlnamespaces('urn:global:cs:common' AS "tns0"),
                'tns0:Conditions/tns0:Numbers/tns0:Number'
               PASSING t1.baselist 
               COLUMNS EMPLOYEENUMBER VARCHAR2(88) PATH 'text()') xmlT
  )
) loop
    ...
  end loop;
end;