Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Error loading XML file into XMLTYPE column through SQL loader

User_08DMAJul 24 2012 — edited Aug 8 2012
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
This post has been answered by odie_63 on Jul 24 2012
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 5 2012
Added on Jul 24 2012
6 comments
2,769 views