Hi Gurus,
I am trying to load XML file in XMLTYPE column through SQL Loader but getting errors. Following are the details
Database Information
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 24 17:17:55 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Table structure
CREATE TABLE TH_XML
(
COL_ID_1 VARCHAR2(100 BYTE),
IN_FILE_1 XMLTYPE
)
XMLTYPE IN_FILE_1 STORE AS CLOB (TABLESPACE SMDAT)
XML file (simple.xml)
<?xml version="1.0"?>
<catalog>
<book id="bk101">
<author>Some Author1</author>
<title>Some Title1</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>creating applications</description>
</book>
<book id="bk112">
<author>Some Author2</author>
<title>Some Title2</title>
<genre>Computer</genre>
<price>49.95</price>
<publish_date>2001-04-16</publish_date>
<description>Microsoft Visual Studio 7 is explored in depth</description>
</book>
</catalog>
Control File
LOAD DATA
INFILE 'c:\simple.xml'
APPEND
INTO TABLE TH_XML
XMLTYPE(in_file_1)
(
col_id_1 filler CHAR (100),
in_file_1 LOBFILE(CONSTANT "c:\simple.xml") TERMINATED BY EOF
)
LOG File
SQL*Loader: Release 10.2.0.3.0 - Production on Tue Jul 24 16:42:25 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: c:\my_file.ctl
Data File: c:\simple.xml
Bad File: c:\simple.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table TH_XML, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL_ID_1 FIRST 100 CHARACTER
(FILLER FIELD)
IN_FILE_1 DERIVED * EOF CHARACTER
Static LOBFILE. Filename is c:\simple.xml
Record 1: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 2: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 3: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 4: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 5: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 6: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 7: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 8: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 9: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 10: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 11: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 12: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 13: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 14: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 15: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 16: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 17: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 18: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 19: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 20: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 21: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 22: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Record 23: Rejected - Error on table TH_XML.
ORA-00904: "SYS_NC_ROWINFO$": invalid identifier
Table TH_XML:
0 Rows successfully loaded.
23 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 256 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 23
Total logical records rejected: 23
Total logical records discarded: 0
Run began on Tue Jul 24 16:42:25 2012
Run ended on Tue Jul 24 16:42:26 2012
Elapsed time was: 00:00:00.23
CPU time was: 00:00:00.05
I am getting errors ORA-00904: "SYS_NC_ROWINFO$": invalid identifier in log file (mention above). Could anyone help me to find out where I am doing wrong?
Thanks in advance.
Edited by: 876991 on Jul 24, 2012 2:18 PM