Hi,
I have a requirement to update a particular table column Using xml data. for this i have written below code but i am not able to insert . could you please any one look into this .
create table emp3
as
select *From emp
where 1=1;
alter table emp3
add (fax_response varchar2(50));
/*create sequence EmailRecords_XMLFILE_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;*/
/* create global temporary table EmailRecords_XMLFILE
(
ID NUMBER not null,
xmlfile CLOB
)
on commit preserve rows;*/
/* create global temporary table UPD_Email_Records_With_Xml
(
id NUMBER not null,
response VARCHAR2(500)
)
on commit preserve rows; */
xml data is
<FAX>
<EMAILOG>
<ID>7839</ID>
<RESPONSE>FAX SENT</RESPONSE>
</EMAILOG>
<EMAILOG>
<ID>7566</ID>
<RESPONSE>FAX NOT SENT</RESPONSE>
</EMAILOG>
</FAX>
CREATE OR REPLACE PROCEDURE proc_upd_email_records (
loc_xml IN CLOB,
p_err_code_out OUT NUMBER,
p_err_mesg_out OUT VARCHAR2
)
IS
loc_id NUMBER;
BEGIN
loc_id := emailrecords_xmlfile_seq.NEXTVAL; --created sequence
INSERT INTO emailrecords_xmlfile --created Global Temp table
(ID, xmlfile
)
VALUES (loc_id, loc_xml
);
COMMIT;
insert into UPD_Email_Records_With_Xml --created Global Temp table
(ID, RESPONSE)
select x1.id,
x1.RESPONSE
from EmailRecords_XMLFILE,
xmltable('/FAX/EMAILOGID' passing
xmltype.createxml(EmailRecords_XMLFILE.xmlfile)
columns header_no for ordinality,
id number path 'ID',
RESPONSE VARCHAR2(250) path 'RESPONSE'
) x1
where EmailRecords_XMLFILE.id = loc_id;
COMMIT;
UPDATE emp3 er
SET er.fax_response = (SELECT response
FROM upd_email_records_with_xml pr
WHERE pr.ID = er.empno)
WHERE er.empno IN (SELECT ID
FROM upd_email_records_with_xml);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
raise_application_error
(-20000,
'Sorry ! The Xml File which is passed is empty. Please try with Valid Xml File. Thank you!!! '
);
WHEN OTHERS
THEN
p_err_code_out := 4;
p_err_mesg_out := 'error in insertion=> ' || SQLERRM;
END proc_upd_email_records;
{code}{code}
Any one suggest me a bit easier way to insert data...
Thank you....