Home » SQL & PL/SQL » SQL & PL/SQL » Load multiline data in table using sql loader (Oracle 12c)
Load multiline data in table using sql loader [message #676775] Mon, 15 July 2019 17:19 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 26606
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
Re: Load multiline data in table using sql loader [message #676777 is a reply to message #676775] Mon, 15 July 2019 21:36 Go to previous messageGo to next message
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")

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 Go to previous messageGo to next message
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")

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 Go to previous messageGo to next message
John Watson
Messages: 8023
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.
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 Go to previous messageGo to next message
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 ?
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 Go to previous messageGo to next message
cookiemonster
Messages: 13686
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.
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 Go to previous messageGo to next message
adfire05
Messages: 9
Registered: July 2019
Junior Member
I tried using
STR "|\n" 
or ENDIF LAST <> '|'. 
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 Go to previous messageGo to next message
cookiemonster
Messages: 13686
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.
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 Go to previous messageGo to next message
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 .
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 Go to previous messageGo to next message
BlackSwan
Messages: 26606
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?
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
does your browser report that file contains valid XML structured data?
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 Go to previous messageGo to next message
adfire05
Messages: 9
Registered: July 2019
Junior Member
yes data is valid.
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 Go to previous message
adfire05
Messages: 9
Registered: July 2019
Junior Member
Guys any help ? any suggestions.
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:
  


Current Time: Sat Oct 19 22:03:15 CDT 2019