Win a copy of Head First Go this week in the Go forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Devaka Cooray
  • Junilu Lacar
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Tim Holloway
  • Claude Moore
  • Stephan van Hulst
Bartenders:
  • Winston Gutkowski
  • Carey Brown
  • Frits Walraven

ORA-19032: Expected XML tag, got no content  RSS feed

 
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Hi,

I am facing weird problem while executing control file through SQL LOADER. I am using Oracle Client 12c. In the same setup vendor was able to execute the script without any issues.

Input Oracle table has column "EXTENTION_XML" of data type "XMLTYPE", and i am inserting null/empty value into it.

#/u01/app/oracle/product/12.1.0/client_1/bin/sqlldr USER/PWD@SERVICE_NAME control=test.ctl


My control file looks like below :

test.ctl :
LOAD DATA
INFILE 'test.ldr' "str x'594F594F454F4C0A'"
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE "TEST"
FIELDS TERMINATED BY'|'
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS (
"COL1" ,
"COL2" ,
"COL3" ,
...
...
"EXTENTION_XML" CHAR(2000),
"COL5"
)

and test.log has below output.

value used for ROWS paramater changed from 64 to 40
Record 1: Rejected - Error on the table "TEST"
ORA-19032: Expected XML tag, got no content

Record 2: Rejected - Error on the table "TEST"
ORA-19032: Expected XML tag, got no content

Table "TEST":
 0 rows successfully loaded.
 2 rows not loaded due to data errors.

Space allocated for binalry array:  255840 bytes(40 rows)
Read buffer bytes: 1048576.


 
Saloon Keeper
Posts: 20514
115
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There's a difference between null content and no content.

If you omit the column name "EXTENTION_XML" from your load statement, then one of two things will happen:

A) If EXTENTION_XML was not given the NOT NULL constraint when the table was created, then its value would be set to null.

B) Otherwise, the statement should be rejected since NOT NULL means that you MUST provide either an explicit or default value for EXTENTION_XML.

That's the normal case, but it's obvious that Oracle is XML aware for this column, and XML requires something like this at a minumum:



Which means that a "no content" string (empty or blank string), would be illegal XML and be rejected. It's even possible that Oracle would consider even a null value to be illegal XML, but without checking documentation or running a test, I cannot confirm that.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!