OraFAQ Forum: Server Utilities » Load XML data through sqlldr

Home » RDBMS Server » Server Utilities » Load XML data through sqlldr (11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Load XML data through sqlldr [message #683493]

Sun, 24 January 2021 13:15

Amine
Messages: 376
Registered: March 2010

Senior Member

Hi All,
I have this table :

CREATE TABLE test_tab 
(
    id 		NUMBER		,
    text 	VARCHAR2(50)	,
    text2 	VARCHAR2(50)
);

And this XML file : test_tab.xml

<ROWSET>
	<ROW>
		<ID>1</ID>
		<TEXT>This is some text</TEXT>
		<TEXT2>sdfsdf s sdfsdf</TEXT2>
	</ROW>
	<ROW>
		<ID>2</ID>
		<TEXT>This is some more text</TEXT>
		<TEXT2>sdfsdf s sdfsdf</TEXT2>
	</ROW>
	<ROW>
		<ID>3</ID>
		<TEXT>This is some other text</TEXT>
		<TEXT2>sdfsdf s sdfsdf</TEXT2>
	</ROW>
	<ROW>
		<ID>4</ID>
		<TEXT>This is also some text</TEXT>
		<TEXT2>sdfsdf s sdfsdf</TEXT2>
	</ROW>
	<ROW>
		<ID>5</ID>
		<TEXT>This is some text</TEXT>
		<TEXT2>sdfsdf s sdfsdf</TEXT2>
	</ROW>
	<ROW>
		<ID>6</ID>
		<TEXT>This is some more text</TEXT>
		<TEXT2>sdfsdf s sdfsdf</TEXT2>
	</ROW>
	<ROW>
		<ID>7</ID>
		<TEXT>This is some other text</TEXT>
		<TEXT2>sdfsdf s sdfsdf</TEXT2>
	</ROW>
	<ROW>
		<ID>8</ID>
		<TEXT>This is also some text</TEXT>
		<TEXT2>sdfsdf s sdfsdf</TEXT2>
	</ROW>
</ROWSET>

I want to load the data contained in the XML file through
sqlldr and this control file : test_tab.ctl

LOAD DATA
INFILE 'test_tab.xml'
truncate
CONCATENATE 6
INTO TABLE test_tab
TRAILING NULLCOLS
(
    dummy FILLER CHAR(15) TERMINATED BY "<ROW>",
    id CHAR(10) ENCLOSED BY "<ID>" AND "</ID>",
    text CHAR(40) ENCLOSED BY "<TEXT>" AND "</TEXT>",
    text2 CHAR(100) ENCLOSED BY "<TEXT2>" AND "</TEXT2>"
)

Everything seems to be right. But actually when I run this :

sqlldr my_user/my_pwd control=test_tab.ctl log=test_tab.log

the behaviour of the loading changes each time I modify the concatenate value, as it is shown in the end of this post.

Could any one explain me how the concatenate argument works ? And if the size of the filler column matter ?

Also, the behaviour changes when the data size (the rows) changes...

Thanks in advance.

test_tab_concatenate_5.log


SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jan 24 19:22:18 2021

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

Control File:   test_tab.ctl
Data File:      test_tab.xml
  Bad File:     test_tab.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:   Concatenate every 5 physical records
Path used:      Conventional

Table TEST_TAB, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DUMMY                               FIRST    15           CHARACTER            
  (FILLER FIELD)
    Terminator string : '<ROW>'
ID                                   NEXT    10           CHARACTER            
    First enclosure string : '<ID>'
    Second enclosure string : '</ID>'
TEXT                                 NEXT    40           CHARACTER            
    First enclosure string : '<TEXT>'
    Second enclosure string : '</TEXT>'
TEXT2                                NEXT   100           CHARACTER            
    First enclosure string : '<TEXT2>'
    Second enclosure string : '</TEXT2>'

Record 9: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length

Table TEST_TAB:
  8 Rows successfully loaded.
  1 Row 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:                   9984 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             9
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Sun Jan 24 19:22:18 2021
Run ended on Sun Jan 24 19:22:18 2021

Elapsed time was:     00:00:00.04
CPU time was:         00:00:00.01

test_tab_concatenate_6.log

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jan 24 19:20:56 2021

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

Control File:   test_tab.ctl
Data File:      test_tab.xml
  Bad File:     test_tab.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:   Concatenate every 6 physical records
Path used:      Conventional

Table TEST_TAB, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DUMMY                               FIRST    15           CHARACTER            
  (FILLER FIELD)
    Terminator string : '<ROW>'
ID                                   NEXT    10           CHARACTER            
    First enclosure string : '<ID>'
    Second enclosure string : '</ID>'
TEXT                                 NEXT    40           CHARACTER            
    First enclosure string : '<TEXT>'
    Second enclosure string : '</TEXT>'
TEXT2                                NEXT   100           CHARACTER            
    First enclosure string : '<TEXT2>'
    Second enclosure string : '</TEXT2>'

Record 3: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 4: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 5: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length

Table TEST_TAB:
  4 Rows successfully loaded.
  3 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:                   9984 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             7
Total logical records rejected:         3
Total logical records discarded:        0

Run began on Sun Jan 24 19:20:56 2021
Run ended on Sun Jan 24 19:20:56 2021

Elapsed time was:     00:00:00.05
CPU time was:         00:00:00.02

test_tab_no_concatenate.log

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jan 24 19:23:28 2021

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

Control File:   test_tab.ctl
Data File:      test_tab.xml
  Bad File:     test_tab.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
[b]Continuation:    none specified[/b]
Path used:      Conventional

Table TEST_TAB, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DUMMY                               FIRST    15           CHARACTER            
  (FILLER FIELD)
    Terminator string : '<ROW>'
ID                                   NEXT    10           CHARACTER            
    First enclosure string : '<ID>'
    Second enclosure string : '</ID>'
TEXT                                 NEXT    40           CHARACTER            
    First enclosure string : '<TEXT>'
    Second enclosure string : '</TEXT>'
TEXT2                                NEXT   100           CHARACTER            
    First enclosure string : '<TEXT2>'
    Second enclosure string : '</TEXT2>'

Record 1: Discarded - all columns null.
Record 2: Discarded - all columns null.
Record 3: Discarded - all columns null.
Record 4: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 5: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 6: Discarded - all columns null.
Record 7: Discarded - all columns null.
Record 8: Discarded - all columns null.
Record 9: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 10: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 11: Discarded - all columns null.
Record 12: Discarded - all columns null.
Record 13: Discarded - all columns null.
Record 14: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 15: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 16: Discarded - all columns null.
Record 17: Discarded - all columns null.
Record 18: Discarded - all columns null.
Record 19: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 20: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 21: Discarded - all columns null.
Record 22: Discarded - all columns null.
Record 23: Discarded - all columns null.
Record 24: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 25: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 26: Discarded - all columns null.
Record 27: Discarded - all columns null.
Record 28: Discarded - all columns null.
Record 29: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 30: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 31: Discarded - all columns null.
Record 32: Discarded - all columns null.
Record 33: Discarded - all columns null.
Record 34: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 35: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 36: Discarded - all columns null.
Record 37: Discarded - all columns null.
Record 38: Discarded - all columns null.
Record 39: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 40: Rejected - Error on table TEST_TAB, column DUMMY.
Field in data file exceeds maximum length
Record 41: Discarded - all columns null.
Record 42: Discarded - all columns null.

Table TEST_TAB:
  0 Rows successfully loaded.
  16 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  26 Rows not loaded because all fields were null.


Space allocated for bind array:                   9984 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            42
Total logical records rejected:        16
Total logical records discarded:       26

Run began on Sun Jan 24 19:23:28 2021
Run ended on Sun Jan 24 19:23:28 2021

Elapsed time was:     00:00:00.04
CPU time was:         00:00:00.03

Report message to a moderator

Re: Load XML data through sqlldr [message #683501 is a reply to message #683493]

Sun, 24 January 2021 14:08

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If file is on server you can do it with SQL:

SQL> CREATE TABLE test_tab
  2  (
  3      id             NUMBER          ,
  4      text   VARCHAR2(50)    ,
  5      text2  VARCHAR2(50)
  6  );

Table created.


SQL> insert into test_tab
  2  select id, text, text2
  3  from ( select xmltype(bfilename('MY_DIR', 'test_tab.xml'), NLS_CHARSET_ID ('WE8MSWIN1252')) x
  4         from dual ) x,
  5       xmltable ('/ROWSET/ROW' passing x
  6                 columns
  7                   id    integer      path '/ROW/ID',
  8                   text  varchar2(50) path '/ROW/TEXT',
  9                   text2 varchar2(50) path '/ROW/TEXT2'
 10                )
 11  /

8 rows created.

SQL> select * from test_tab;
        ID TEXT                                               TEXT2
---------- -------------------------------------------------- --------------------------------------------------
         1 This is some text                                  sdfsdf s sdfsdf
         2 This is some more text                             sdfsdf s sdfsdf
         3 This is some other text                            sdfsdf s sdfsdf
         4 This is also some text                             sdfsdf s sdfsdf
         5 This is some text                                  sdfsdf s sdfsdf
         6 This is some more text                             sdfsdf s sdfsdf
         7 This is some other text                            sdfsdf s sdfsdf
         8 This is also some text                             sdfsdf s sdfsdf

8 rows selected.

Report message to a moderator

Re: Load XML data through sqlldr [message #683502 is a reply to message #683501]

Sun, 24 January 2021 14:44

Amine
Messages: 376
Registered: March 2010

Senior Member

Thanks a lot Michel. But is there an explanation for the sqlldr one ? just to understand the filler and the concatenate parameters

[Updated on: Sun, 24 January 2021 14:45]

Report message to a moderator

Previous Topic:	ORA-00922 during schema export
Next Topic:	SQL Loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 11:43:37 CST 2025