OraFAQ Forum: SQL & PL/SQL » Load multiline data in table using sql loader

Home » SQL & PL/SQL » SQL & PL/SQL » Load multiline data in table using sql loader (Oracle 12c)

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

Load multiline data in table using sql loader [message #676775]

Mon, 15 July 2019 17:19

adfire05
Messages: 9
Registered: July 2019

Junior Member

Hello Experts ,I am trying load data in a table using SQL loader .one of the column XML_CODE file is having multi line data and loader is not able to load data failing .Its not possible to change structure of infile.
please help let me know in cae of any questions.

Below is my table structure

id NUMBER,XML_CODE CLOB,CREATED_DATE DATE,UPDATE_DATE DATE
</code>
INFILE DATA 
<code>
ID|XML_CODE|CREATED_DATE|UPDATE_DATE
3124|<XML_CODE>IT HAS XML CODE 
<CLASS>THIS IS SECOND LINE OF CODE 
</CLASS>
</XML_CODE>
|10/8/2019 12:0:19 PM|11/8/2019 12:00:19 PM
41298|<XML_CODE>IT HAS XML CODE 
THIS IS SECOND LINE OF CODE
THIS IS THIRD LINE OF CODE
</XML_END_TAG>|20/8/2019 12:0:19 PM|12/8/2019 12:00:19 PM

CONTROL FILE

LOAD DATA
INFILE 'PATH'
INSERT INTO TABLE TABLE_NAME
fileds termintaed by '|' TRAILING NULLCOLS
(ID CHAR(40000),POLICY_XML(40000),CREATED_DATE DATE "MM/DD/YYYY HH:MI:SS AM",UPDATED_DATE "MM/DD/YYYY HH:MI:SS AM")

-------------------
Lalit: Added proper code tags as previous were HTML syntax and not using []

[Updated on: Fri, 19 July 2019 12:56] by Moderator

Report message to a moderator

Re: Load multiline data in table using sql loader [message #676776 is a reply to message #676775]

Mon, 15 July 2019 21:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Load multiline data in table using sql loader [message #676777 is a reply to message #676775]

Mon, 15 July 2019 21:36

adfire05
Messages: 9
Registered: July 2019

Junior Member

adfire05 wrote on Mon, 15 July 2019 17:19

Hello Experts ,I am trying load data in a table using SQL loader .one of the column XML_CODE file is having multi line data and loader is not able to load data failing .Its not possible to change structure of infile.
please help let me know in case of any questions.

Below is my table structure
id NUMBER,XML_CODE CLOB,CREATED_DATE DATE,UPDATE_DATE DATE
INFILE DATA
ID|XML_CODE|CREATED_DATE|UPDATE_DATE
3124|<XML_CODE>IT HAS XML CODE 
<CLASS>THIS IS SECOND LINE OF CODE 
</CLASS>
</XML_CODE>
|10/8/2019 12:0:19 PM|11/8/2019 12:00:19 PM
41298|<XML_CODE>IT HAS XML CODE 
THIS IS SECOND LINE OF CODE
THIS IS THIRD LINE OF CODE
</XML_END_TAG>|20/8/2019 12:0:19 PM|12/8/2019 12:00:19 PM
CONTROL FILE
LOAD DATA
INFILE 'PATH'
INSERT INTO TABLE TABLE_NAME
fileds termintaed by '|' TRAILING NULLCOLS
(ID CHAR(40000),POLICY_XML(40000),CREATED_DATE DATE "MM/DD/YYYY HH:MI:SS AM",UPDATED_DATE "MM/DD/YYYY HH:MI:SS AM")

Report message to a moderator

Load data into oracle table using sql loader with fields having value mutiline [message #676782 is a reply to message #676775]

Tue, 16 July 2019 07:06

adfire05
Messages: 9
Registered: July 2019

Junior Member

Hello Experts ,I am trying load data in a table using SQL loader .one of the column XML_CODE file is having multi line data and loader is not able to load data failing .Its not possible to change structure of infile.
please help let me know in case of any questions.

Below is my table structure

id NUMBER,XML_CODE CLOB,CREATED_DATE DATE,UPDATE_DATE DATE

INFILE DATA

ID|XML_CODE|CREATED_DATE|UPDATE_DATE
3124|<XML_CODE>IT HAS XML CODE 
<CLASS>THIS IS SECOND LINE OF CODE 
</CLASS>
</XML_CODE>
|10/8/2019 12:0:19 PM|11/8/2019 12:00:19 PM
41298|<XML_CODE>IT HAS XML CODE 
THIS IS SECOND LINE OF CODE
THIS IS THIRD LINE OF CODE
</XML_END_TAG>|20/8/2019 12:0:19 PM|12/8/2019 12:00:19 PM

CONTROL FILE

LOAD DATA
INFILE 'PATH'
INSERT INTO TABLE TABLE_NAME
fileds termintaed by '|' TRAILING NULLCOLS
(ID CHAR(40000),POLICY_XML(40000),CREATED_DATE DATE "MM/DD/YYYY HH:MI:SS AM",UPDATED_DATE "MM/DD/YYYY HH:MI:SS AM")

Report message to a moderator

Re: Load data into oracle table using sql loader with fields having value mutiline [message #676786 is a reply to message #676782]

Tue, 16 July 2019 07:24

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

It is two steps. First, you need to edit your input file to include your own custom end-of-record marker, such as the string #EOR# and second in your sqlldr controlfile you need to use REPLACE to change the chr(13)chr(10) pair wit ha space.

Report message to a moderator

Re: Load data into oracle table using sql loader with fields having value mutiline [message #676787 is a reply to message #676786]

Tue, 16 July 2019 07:26

adfire05
Messages: 9
Registered: July 2019

Junior Member

thanks john for your response i tried using STR and ENDIF .But nothing worked out.
Could you help ?

Report message to a moderator

Re: Load data into oracle table using sql loader with fields having value mutiline [message #676791 is a reply to message #676787]

Tue, 16 July 2019 07:52

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you tell us exactly how you used STR and ENDIF and what the result was, probably.

Report message to a moderator

Re: Load data into oracle table using sql loader with fields having value mutiline [message #676793 is a reply to message #676791]

Tue, 16 July 2019 09:04

adfire05
Messages: 9
Registered: July 2019

Junior Member

I tried using

STR "|\n"

or ENDIF LAST <> '|'.

Report message to a moderator

Re: Load data into oracle table using sql loader with fields having value mutiline [message #676794 is a reply to message #676791]

Tue, 16 July 2019 09:53

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

cookiemonster wrote on Tue, 16 July 2019 13:52

If you tell us exactly how you used STR and ENDIF and what the result was, probably.

Report message to a moderator

Re: Load data into oracle table using sql loader with fields having value mutiline [message #676796 is a reply to message #676794]

Tue, 16 July 2019 11:39

adfire05
Messages: 9
Registered: July 2019

Junior Member

Sure ,I will share.Right now i am at work so I wouldn't be able to do that now .Just to add problem i am facing is my loader is reading infile as single line .It throws invalid number as it tries to load xml in the second line in my first column .
I don't have much experience in using STR or EnlNDIF .

Report message to a moderator

Re: Load data into oracle table using sql loader with fields having value mutiline [message #676797 is a reply to message #676796]

Tue, 16 July 2019 14:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Oracle software is designed to process only valid XML formatted data.
You can independently validate any file containing XML data be opening it using any standard webrowser like Chrome.
If/when Chrome properly presents the contents, then you can be assured that Oracle can also process the file contents.

What do you observe when you open your XML file using the browser of your choice?

Report message to a moderator

Re: Load data into oracle table using sql loader with fields having value mutiline [message #676822 is a reply to message #676794]

Fri, 19 July 2019 08:03

adfire05
Messages: 9
Registered: July 2019

Junior Member

Hi ,Please find code i tried and out put i am getting .

LOAD DATA 
INFILE 'FILE.TXT' "str X'22ODOA'"
INTO TABLE TEMP_1
REPLACE
FIELDS TERMINATED BY '|'
(
ID,XML_CODE CHAR(400000),CREATED_DATE DATE "MM/DD/YYYY HH:MI:SS AM",UPDATED_DATE
)

Error in am getting

Record 1: Rejected - Error on table TEMP_1, column ID .
ORA-01722: invalid number

Report message to a moderator

Re: Load data into oracle table using sql loader with fields having value mutiline [message #676824 is a reply to message #676822]

Fri, 19 July 2019 08:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

does your browser report that file contains valid XML structured data?

Report message to a moderator

Re: Load data into oracle table using sql loader with fields having value mutiline [message #676825 is a reply to message #676824]

Fri, 19 July 2019 08:59

adfire05
Messages: 9
Registered: July 2019

Junior Member

yes data is valid.

Report message to a moderator

Re: Load data into oracle table using sql loader with fields having value mutiline [message #676915 is a reply to message #676825]

Fri, 26 July 2019 12:31

adfire05
Messages: 9
Registered: July 2019

Junior Member

Guys any help ? any suggestions.

Report message to a moderator

Previous Topic:	Select query took too long to execute on one schema but very little time on another scema
Next Topic:	Leading zeros in a spool file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 29 04:05:48 CDT 2025