updating xml stored in oracle tables

REM **********************************************************************
REM Set Environment parameters
REM **********************************************************************

SET scan off;

set serveroutput on size 100000;

select ‘START TIME: ‘||to_char(sysdate,’mm/dd/yyyy hh24:mi:ss’) from dual;

DECLARE
xml_text1 clob;
xml_text2 clob;
xml_text3 clob;
xml_text4 clob;
xml_column_name VARCHAR2(50) := ‘demo.xml’;
sql_stmt1 VARCHAR2(2000);
sql_stmt2 VARCHAR2(2000);
sql_stmt3 VARCHAR2(2000);
sql_stmt4 VARCHAR2(2000);
BEGIN
xml_text1 :=’ –….. put your xml data here…

‘;

xml_text2 :=’ –….. put your xml data here…

‘;

xml_text3 :=’ –….. put your xml data here…

‘;

xml_text4 :=’ –….. put your xml data here…

‘;

sql_stmt1 := ‘update BL1_XML_CONFIG set XML = :1, SYS_UPDATE_DATE = SYSDATE where OBJECT_NAME = :2 and SCHEMA = :3’;

sql_stmt2 := ‘update BL1_XML_CONFIG set XML = XML||:1, SYS_UPDATE_DATE = SYSDATE where OBJECT_NAME = :2 and SCHEMA = :3’;

sql_stmt3 := ‘update BL1_XML_CONFIG set XML = XML||:1, SYS_UPDATE_DATE = SYSDATE where OBJECT_NAME = :2 and SCHEMA = :3’;

sql_stmt4 := ‘update BL1_XML_CONFIG set XML = XML||:1, SYS_UPDATE_DATE = SYSDATE where OBJECT_NAME = :2 and SCHEMA = :3’;

EXECUTE IMMEDIATE sql_stmt1 USING xml_text1, object_name, schema_name;
EXECUTE IMMEDIATE sql_stmt2 USING xml_text2, object_name, schema_name;
EXECUTE IMMEDIATE sql_stmt3 USING xml_text3, object_name, schema_name;
EXECUTE IMMEDIATE sql_stmt4 USING xml_text4, object_name, schema_name;

commit;

END;
/
SET scan on
REM **********************************************************************
REM Get time
REM **********************************************************************
select ‘END TIME: ‘||to_char(sysdate,’mm/dd/yyyy hh24:mi:ss’) from dual;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s