We have one procedure that (can) take a very large XML message. The procedure loops through the message in a cursor and updates rows.
This can take 2 hours and we plan in the long run to stop doing this.
However we are looking for a short term solution.
Now the max number of rows in the XML is approx 8000. Could the following proc be checked to see if we are doing the correct thing?
Or if there is a more efficient way to process this XML.
I have included details below - please if you could pass on to who ever is best at XML processing in Oracle!!
(I am wondering if our parsing of the xml is efficient or if we can config the server in any way?)
The proc is simply:
Take xml,
iterate through it
Find qid, contactid, status and update the matching row.
PROCEDURE updaterecipientstatus
(v_xml IN CLOB,
v_error_code OUT VARCHAR2,
v_error_msg OUT VARCHAR2)
AS
CURSOR C1 IS
SELECT extractValue(value(a),'JobId/@id') job_id,
extractValue(value(a),'JobId/@status') job_status,
extractValue(value(b),'Recipient/Email/@contactid') contact_id,
extractValue(value(b),'Recipient/State') contact_status
FROM TABLE ( xmlsequence (extract(XMLTYPE.createxml(v_xml),'Message/JobIds/JobId'))) a,
TABLE ( xmlsequence (extract(XMLTYPE.createxml(v_xml),'Message/JobIds/JobId/Recipients/Recipient'))) b;
l_input CLOB;
l_log_time timestamp := systimestamp;
BEGIN
v_error_code := 0;
v_error_msg := NULL;
create_log(' EMAILJOBS.UPDATERECIPIENTSTATUS ',l_log_time, ' v_xml :- ' || v_xml );
FOR x IN C1
LOOP
UPDATE emailqueue
SET status = x.job_status
WHERE qid = x.job_id;
UPDATE emailqueuerecipient
SET status = x.contact_status
WHERE qid = x.job_id
AND contact_id = x.contact_id;
END LOOP;
create_log(' EMAILJOBS.UPDATERECIPIENTSTATUS ',l_log_time, ' COMPLETED ' );
EXCEPTION
WHEN OTHERS THEN
v_error_code := 1;
v_error_msg := 'Email_Job.updaterecipientstatus : ORACLE ERROR : ' || SQLERRM;
create_log(' EMAILJOBS.UPDATERECIPIENTSTATUS ',l_log_time, ' v_error_code :- ' || v_error_code || ' v_error_msg :- ' || v_error_msg );
END updaterecipientstatus;
And the structure of the xml is
<Message service="EMAIL" action="GETSTATUS">
<ReturnAddress>dummy data</ReturnAddress>
<OriginatorRef></OriginatorRef>
<JobIds>
<JobId id="809" status="COMPLETED">
<Recipients>
<Recipient>
<Email contactid="1078">Sender_SCAMPS/HBEU/[email protected]</Email>
<State>SENT</State>
<StateTime>2007-06-05 15:09:41.0</StateTime>
<Error></Error>
</Recipient>
</Recipients>
</JobId>
</JobIds>
</Message>
Where the data in the <recipient> tag can be repeated (for different contacts) upto 8000 times.
EMAILQUEUE - is the master table (I know its repeatedly updated for no reason - we can change this !!) there is one row per qid
and EMAILQUEUERECIPIENT is the detail table, it has over 1 million rows, and has PK of QID, CONTACT_ID.
Can anybody help me as soon as possible.Tnx in advance.
This can take 2 hours and we plan in the long run to stop doing this.
However we are looking for a short term solution.
Now the max number of rows in the XML is approx 8000. Could the following proc be checked to see if we are doing the correct thing?
Or if there is a more efficient way to process this XML.
I have included details below - please if you could pass on to who ever is best at XML processing in Oracle!!
(I am wondering if our parsing of the xml is efficient or if we can config the server in any way?)
The proc is simply:
Take xml,
iterate through it
Find qid, contactid, status and update the matching row.
PROCEDURE updaterecipientstatus
(v_xml IN CLOB,
v_error_code OUT VARCHAR2,
v_error_msg OUT VARCHAR2)
AS
CURSOR C1 IS
SELECT extractValue(value(a),'JobId/@id') job_id,
extractValue(value(a),'JobId/@status') job_status,
extractValue(value(b),'Recipient/Email/@contactid') contact_id,
extractValue(value(b),'Recipient/State') contact_status
FROM TABLE ( xmlsequence (extract(XMLTYPE.createxml(v_xml),'Message/JobIds/JobId'))) a,
TABLE ( xmlsequence (extract(XMLTYPE.createxml(v_xml),'Message/JobIds/JobId/Recipients/Recipient'))) b;
l_input CLOB;
l_log_time timestamp := systimestamp;
BEGIN
v_error_code := 0;
v_error_msg := NULL;
create_log(' EMAILJOBS.UPDATERECIPIENTSTATUS ',l_log_time, ' v_xml :- ' || v_xml );
FOR x IN C1
LOOP
UPDATE emailqueue
SET status = x.job_status
WHERE qid = x.job_id;
UPDATE emailqueuerecipient
SET status = x.contact_status
WHERE qid = x.job_id
AND contact_id = x.contact_id;
END LOOP;
create_log(' EMAILJOBS.UPDATERECIPIENTSTATUS ',l_log_time, ' COMPLETED ' );
EXCEPTION
WHEN OTHERS THEN
v_error_code := 1;
v_error_msg := 'Email_Job.updaterecipientstatus : ORACLE ERROR : ' || SQLERRM;
create_log(' EMAILJOBS.UPDATERECIPIENTSTATUS ',l_log_time, ' v_error_code :- ' || v_error_code || ' v_error_msg :- ' || v_error_msg );
END updaterecipientstatus;
And the structure of the xml is
<Message service="EMAIL" action="GETSTATUS">
<ReturnAddress>dummy data</ReturnAddress>
<OriginatorRef></OriginatorRef>
<JobIds>
<JobId id="809" status="COMPLETED">
<Recipients>
<Recipient>
<Email contactid="1078">Sender_SCAMPS/HBEU/[email protected]</Email>
<State>SENT</State>
<StateTime>2007-06-05 15:09:41.0</StateTime>
<Error></Error>
</Recipient>
</Recipients>
</JobId>
</JobIds>
</Message>
Where the data in the <recipient> tag can be repeated (for different contacts) upto 8000 times.
EMAILQUEUE - is the master table (I know its repeatedly updated for no reason - we can change this !!) there is one row per qid
and EMAILQUEUERECIPIENT is the detail table, it has over 1 million rows, and has PK of QID, CONTACT_ID.
Can anybody help me as soon as possible.Tnx in advance.