Home » RDBMS Server » Server Utilities » Bug in Loading XmlType column with VARCHARC (Oracle 11g on Linux)
Bug in Loading XmlType column with VARCHARC [message #614072] Thu, 15 May 2014 09:41 Go to next message
oracleAdmin
Messages: 3
Registered: May 2014
Junior Member
Hallo everyone,

I have a very strange problem.

I am loading an XMLType column with the varcharc method.

If the XMLType column is UPPERCASE like "MYCOLUMN" its all right.

But if rename the column with lower case letters like "Mycolumn" i get :

SQL*Loader-951: Error calling once/load initialization
ORA-01009: missing mandatory parameter
.

Is it a bug?

PS : My Controlfile

OPTIONS(DIRECT=TRUE) UNRECOVERABLE LOAD DATA CHARACTERSET UTF8 INFILE '/tmp/data.dat' "str '<endrec>\n'" TRUNCATE INTO TABLE SCHEMA.NAME fields terminated by ';' optionally enclosed by '"' TRAILING NULLCOLS (
"Mycolumn" VARCHARC(10, 2147483647))

Re: Bug in Loading XmlType column with VARCHARC [message #614074 is a reply to message #614072] Thu, 15 May 2014 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Please copy and paste the whole SQL*Loader session.
Please post a test case we can reproduce.
Please ALWAYS post your Oracle version with 4 decimals.

Re: Bug in Loading XmlType column with VARCHARC [message #614089 is a reply to message #614072] Fri, 16 May 2014 01:10 Go to previous messageGo to next message
oracleAdmin
Messages: 3
Registered: May 2014
Junior Member
Oracle version 11.2.0.3


control.ctl:
OPTIONS(DIRECT=TRUE) UNRECOVERABLE LOAD DATA CHARACTERSET UTF8 INFILE '/tmp/data.dat' "str '<endrec>\n'" TRUNCATE INTO TABLE MYSCHEMA.MYTABLE fields terminated by ';' optionally enclosed by '"' TRAILING NULLCOLS (
"ncol" VARCHARC(10, 2147483647))

data.dat:

0000000071<AAAA>
<AAAAAAA attribute="name">Content 460768932</CONTENT>
</AAAA>
<endrec>


session :


SQL*Loader: Release 11.2.0.3.0 - Production on Fri May 16 08:07:34 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

SQL*Loader-951: Error calling once/load initialization
ORA-01009: missing mandatory parameter





Re: Bug in Loading XmlType column with VARCHARC [message #614116 is a reply to message #614089] Fri, 16 May 2014 14:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
If you are going to load XML, then it needs to be valid XML, where you have matching start and end tags, including matching case. Similarly, if you enclose column names in double quotes, then the case must match. Also, there are a lot of things that are not compatible with direct path, but work with the conventional path. In the following example, I have changed just enough to make it run and load data.

SCOTT@orcl12c> host type data.dat
0000000071<AAAA>
<AAAAAAA attribute="name">Content 460768932</AAAAAAA>
</AAAA>
<endrec>

SCOTT@orcl12c> host type control.ctl
LOAD DATA
CHARACTERSET UTF8
INFILE 'data.dat' "str '<endrec>\n'"
TRUNCATE INTO TABLE MYTABLE
fields terminated by ';'
optionally enclosed by '"'
TRAILING NULLCOLS
("ncol" VARCHARC(10, 214748364))

SCOTT@orcl12c> create table mytable ("ncol"  xmltype)
  2  /

Table created.

SCOTT@orcl12c> host sqlldr scott/tiger control=control.ctl log=test.log

SQL*Loader: Release 12.1.0.1.0 - Production on Fri May 16 12:08:00 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1

Table MYTABLE:
  1 Row successfully loaded.

Check the log file:
  test.log
for more information about the load.

SCOTT@orcl12c> select * from mytable
  2  /

ncol
--------------------------------------------------------------------------------
<AAAA>
  <AAAAAAA attribute="name">Content 460768932</AAAAAAA>
</AAAA>


1 row selected.


Re: Bug in Loading XmlType column with VARCHARC [message #614215 is a reply to message #614072] Mon, 19 May 2014 03:04 Go to previous messageGo to next message
oracleAdmin
Messages: 3
Registered: May 2014
Junior Member
Thank you for you answer:)

I can load now the xml through conventional path.

Is there no way to load valid xml data through direct path with lower case column names?
If i use just uppercase column names i can laod valid xml with direct path.

Re: Bug in Loading XmlType column with VARCHARC [message #614220 is a reply to message #614215] Mon, 19 May 2014 04:39 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
You can use both conventional and direct path load for loading xmltype data. However, there is an exception to it, read Overview of Loading XMLType Data Into Oracle Database.
Previous Topic: xml file with same segment name loading into database
Next Topic: how to execute the CATPROC.SQL
Goto Forum:
  


Current Time: Mon Dec 23 02:15:29 CST 2024