Very Good
Usha S, July 21, 2017 - 12:50 pm UTC
It is such a great query .
Usha S, July 21, 2017 - 12:50 pm UTC
Again Thanks a lot team .
Usha S, July 21, 2017 - 3:03 pm UTC
I have tried it by inserting it in an function as follows
create or replace FUNCTION F_GI_CONSTRUCT_XML
( V_FROM_TS IN varchar2,
V_TO_TS IN varchar2,
Domain_name IN varchar2 )
RETURN VARCHAR2 AS
V_MAINQUERY clob;
MAINCURSOR SYS_REFCURSOR ;
final_XML XMLTYPE;
V_CURVAL NUMBER(38);
BEGIN
DBMS_OUTPUT.PUT_LINE('hii');
V_MAINQUERY:='
with grps as (
select ceil(row_number() over ( order by nos)/1000) grp,
xmlforest(nos as "Number") xdoc
from(
select Distinct EMPLOYEEPID as nos from(
SELECT A.EMPLOYEEPID
FROM IMPP.EMPLOYEE A ,IMPP.ORGANIZATION B
WHERE A.LASTUPDATEDATE BETWEEN TO_DATE('''||V_FROM_TS ||''',''YYYY-MM-DD HH24:MI:SS'')
AND TO_DATE('''||V_TO_TS ||''',''YYYY-MM-DD HH24:MI:SS'')
union
SELECT A.EMPLOYEEPID
FROM IMPP.EMPLOYEE a,IMPP.ORGANIZATION B
WHERE A.LASTUPDATEDATE BETWEEN TO_DATE('''||V_FROM_TS ||''',''YYYY-MM-DD HH24:MI:SS'')
AND TO_DATE('''||V_TO_TS ||''',''YYYY-MM-DD HH24:MI:SS'')
)
))
select grp, xmlagg(xdoc) from grps
group by grp
';
--DBMS_OUTPUT.put_line(V_MAINQUERY);
OPEN MAINCURSOR FOR V_MAINQUERY;
DBMS_OUTPUT.put_line('Hii2');
LOOP
FETCH MAINCURSOR INTO V_CURVAL,final_XML;
DBMS_OUTPUT.put_line('hii'|| V_CURVAL);
EXIT WHEN MAINCURSOR%NOTFOUND;
final_XML:='<?xml version="1.0" encoding="UTF-8"?>
<n1:Request xmlns:n1="urn:global:cs:common">
<n1:Conditions>
<n1:Numbers>'||
final_XML ||
'</n1:Numbers>
</n1:Conditions>
</n1:Request>
';
insert into IMPP.INPUT_XML values('EMPLOYEE',final_XML);
COMMIT;
END LOOP;
RETURN NULL;
END F_GI_CONSTRUCT_XML;
but I get some data mismatch errors
July 21, 2017 - 3:46 pm UTC
What error do you get and what's the DDL for all your tables?
Usha S, July 21, 2017 - 3:48 pm UTC
I could finish my function and rectified all my errors. Again Thanks a lot team for making it so easy with a super fine query.I am sorry for the previous review comment.
July 21, 2017 - 3:57 pm UTC
No problem.