Hello Tom
I need to load a XML File, which is available on the oracle server using a Host Program in Oracle Applications. I have done the below
1. Created a Host Concurrent Program
2. Call the SQL Loader Control File from the PROG File.
But the problem is the XML File data is loading multiple times into the table. Actually there are 67 rows in the XML and the XML file is loading 67 times. I want it to be loaded only once.
My Table
CREATE TABLE XXCSI_IB_XML_FILES
( seq_no NUMBER NOT NULL
,file_name VARCHAR2(4000) NOT NULL
,file_data XMLTYPE
,status VARCHAR2(25) DEFAULT 'NEW' NOT NULL
,sqlldr_request_id NUMBER NOT NULL
,created_by NUMBER
,creation_date DATE DEFAULT SYSDATE NOT NULL
,last_updated_by NUMBER
,last_update_login NUMBER
,last_update_date DATE
,error_context VARCHAR2(4000)
,error_code VARCHAR2(500)
,error_message VARCHAR2(4000)
,CONSTRAINT XXCSI_IB_XML_files_pk PRIMARY KEY (seq_no)
My Control file looks like as
LOAD DATA
INFILE '/inbound/SR_XML_01.xml'
APPEND
INTO TABLE XXCSI_IB_XML_FILES
(SEQ_NO "IB_XML_FILES_S.NEXTVAL"
,FILE_NAME CONSTANT 'SR_XML_01.xml'
,XML_FILE FILLER CHAR(1000)
,FILE_DATA LOBFILE(XML_FILE) TERMINATED BY EOF
,STATUS CONSTANT 'NEW'
,SQLLDR_REQUEST_ID "FND_GLOBAL.CONC_REQUEST_ID"
,CREATION_DATE "SYSDATE"
,CREATED_BY "FND_GLOBAL.USER_ID"
,LAST_UPDATE_DATE "SYSDATE"
,LAST_UPDATE_LOGIN "FND_GLOBAL.LOGIN_ID"
,LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
)
I see some error in the SQL Loader Log as well.
I have also tried a different control file version
LOAD DATA
INFILE '/inbound/SR_XML_01.xml'
APPEND
INTO TABLE XXCSI_IB_XML_FILES
(SEQ_NO "IB_XML_FILES_S.NEXTVAL"
,FILE_NAME CONSTANT 'SR_XML_01.xml'
,FILE_DATA LOBFILE(CONSTANT '/inbound/SR_XML_01.xml') TERMINATED BY EOF
,STATUS CONSTANT 'NEW'
,SQLLDR_REQUEST_ID "FND_GLOBAL.CONC_REQUEST_ID"
,CREATION_DATE "SYSDATE"
,CREATED_BY "FND_GLOBAL.USER_ID"
,LAST_UPDATE_DATE "SYSDATE"
,LAST_UPDATE_LOGIN "FND_GLOBAL.LOGIN_ID"
,LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
)
This is also loading the XML file multiple times.
My SQL Loader Version is
SQL*Loader: Release 10.1.0.5.0 - Production on Wed Aug 16 22:33:38 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Can you help me where I'm doing wrong ? Also if there is a different version which will load one record per file, please let me know.
Below is the sample XML i'm using to load
------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<transaction-data type="service_request" batch_transfer_id="41328" col_guid ="342223" transfer_start_time="2017-03-28 18:40:53.012">
<transactions>
<service-request>
<service_request_no>184</service_request_no>
<service_request_type_cd>SEP Planned</service_request_type_cd>
<priority_cd>Engineering time</priority_cd>
<problem_cd>BQ Energy</problem_cd>
<secondary_problem_cd>Autoshutter</secondary_problem_cd>
<failure_dt>2017-03-25 16:38:00</failure_dt>
<problem_summary_desc>XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX</problem_summary_desc>
<resolution_desc>XXXXXXXXXXXXXXXXXXXXXXXXXXXX</resolution_desc>
<task>
<task_no>224</task_no>
<new_task_fg>1</new_task_fg>
<task_type_cd>Non-Scheduled Time</task_type_cd>
<task_user_id>L123456</task_user_id>
<task_subject_desc>XXXXXXXXXXXXXXX</task_subject_desc>
<task_desc>XXXXXXXXXXX"</task_desc>
<task-part>
<part_no>X1234</part_no>
<service_cd>INSTALL</service_cd>
<part_serial_no>Y111111</part_serial_no>
<shot_cnt>344</shot_cnt>
<subinventory_cd>F-ALBANY</subinventory_cd>
<deinstall_reason_cd>\N</deinstall_reason_cd>
<replacement_trigger_cd>Low MO efficiency</replacement_trigger_cd>
<module_cd>VACUUM_PUMP</module_cd>
<part_replaced_fg>1</part_replaced_fg>
<part_received_oracle_fg>1</part_received_oracle_fg>
<shock_watch_tripped_fg>1</shock_watch_tripped_fg>
</task-part>
<task-part>
<part_no>X2222</part_no>
<service_cd>INSTALL</service_cd>
<part_serial_no>Y111111</part_serial_no>
<shot_cnt>344</shot_cnt>
<subinventory_cd>F-ALBANY</subinventory_cd>
<deinstall_reason_cd>\N</deinstall_reason_cd>
<replacement_trigger_cd>Low MO efficiency</replacement_trigger_cd>
<module_cd>VACUUM_PUMP</module_cd>
<part_replaced_fg>0</part_replaced_fg>
<part_received_oracle_fg>0</part_received_oracle_fg>
<shock_watch_tripped_fg>0</shock_watch_tripped_fg>
</task-part>
<labor-activity>
<labor_id>123</labor_id>
<labor_service_activity_cd>Labor</labor_service_activity_cd>
<labor_reason_cd>Customer Support</labor_reason_cd>
<task_start_tm>2017-03-25 18:40:53.012</task_start_tm>
<task_end_tm>2017-03-28 18:40:53.012</task_end_tm>
<laser_downtime>3.5</laser_downtime>
<labour_subcategory_cd>Customer Support</labour_subcategory_cd>
</labor-activity>
<labor-activity>
<labor_id>134</labor_id>
<labor_service_activity_cd>Travel</labor_service_activity_cd>
<labor_reason_cd>Customer Support</labor_reason_cd>
<task_start_tm>2017-03-25 18:40:53.012</task_start_tm>
<task_end_tm>2017-03-28 18:40:53.012</task_end_tm>
<laser_downtime>3.5</laser_downtime>
<labour_subcategory_cd>Customer Support</labour_subcategory_cd>
</labor-activity>
</task>
</service-request>
</transactions>
</transaction-data>
Use the str clause of infile to specify the record terminator instead of in the lobfile clause.
Put the closing tag for the XML document here, for example:
LOAD DATA
INFILE 'SR.xml' "str '</transaction-data>'"
INTO TABLE XXCSI_IB_XML_FILES
TRUNCATE
(SEQ_NO "IB_XML_FILES_S.NEXTVAL"
,FILE_NAME CONSTANT 'SR_XML_01.xml'
,FILE_DATA lobfile(CONSTANT 'SR.xml')
,STATUS CONSTANT 'NEW'
,SQLLDR_REQUEST_ID "1"
,CREATION_DATE "SYSDATE"
,CREATED_BY "1"
,LAST_UPDATE_DATE "SYSDATE"
,LAST_UPDATE_LOGIN "1"
,LAST_UPDATED_BY "1"
)