sql loader xmldata into oracle clob column

Hi,

I have flat file and oracle table like attached. I need to load first two pipe (|) separated data into the table using sqlloader. I wrote control file as attached but its not loading the data. Can you please help how to write the control file to load the data.

Thanks.
xmlmessage.txt
ABC-table.txt
ABC-control-file.txt
GouthamAnandAsked:
Who is Participating?
 
Wasim Akram ShaikCommented:
I see 2 issues with the files you had posted here..

The File itself xmlmessage.txt has formatting issues..
2nd one the table structure and data posted doesn't match..

even if it does match, you may have an issue again while loading the data as the sql loader default is char 255

so considering all these points, see the table creation and loader and xml message samples below

CREATE TABLE ABC
(
  MESSAGEID     VARCHAR2(4000 BYTE),
  MESSAGE       CLOB,
  col3 varchar2(4000 byte),
  col4 varchar2(4000 byte),
  col5 varchar2(4000 byte),
  col6 varchar2(4000 byte)
);

Open in new window



DATA

MessmgeId|Messmge|CremtedBy|CremtedDmte|ModifiedBy|ModifiedDmte
---------|-------|---------|-----------|----------|------------
55|<mmngtmgmssignXZnt xXlns:xsd="mttk://kkk.k3.org/2001/XXLScmZXm" xXlns:xsi="mttk://kkk.k3.org/2001/XXLScmZXm-instmncZ"><XodZlSZmsonmlId>177172</XodZlSZmsonmlId><korkingNuXbZr>XJF13250802</korkingNuXbZr><SZmsonId>20132</SZmsonId><BrmndId>11</BrmndId><mmngtmg|ZXZm\brmmXjud|2013-05-21 07:46:33.880|NULL|NULL
56|<IlmLmbZlmssignXZnt xXlns:xsd="mttk://kkk.k3.org/2001/XXLScmZXm" xXlns:xsi="mttk://kkk.k3.org/2001/XXLScmZXm-instmncZ"><XodZlSZmsonmlId>177172</XodZlSZmsonmlId><korkingNuXbZr>XJF13250802</korkingNuXbZr><sZmsonId>20132</sZmsonId><IlmLmbZls><IlmLmbZl><IlmLmb|ZXZm\brmmXjud|2013-05-21 07:46:34.253|NULL|NULL
57|<?xXl vZrsion="1.0" Zncoding="UTF-8"?><smvZmmngTmgsRZskonsZ srcSys="Xk" trmnsID="b4df552f-4Z79-4f80-m514-9m36fmf74m01"><kroductmmngTmg><sZmsonGroukCodZ>20132</sZmsonGroukCodZ><brmnd>11</brmnd><korkingNuXbZr>XJF13250802</korkingNuX|ZXZm\svc_ZX_Xk_FlZxSrv|2013-05-21 07:46:37.207|NULL|NULL
58|<?xml vzrsion="1.0" zncoding="UTF-8"?><smvzILmLmPzlsRzsponsz srcSys="MP" trmnsID="4z04P5dz-c037-4863-9747-P01c02f19m6f"><productILmLmPzl><workingNumPzr>MJF13250802</workingNumPzr><stmtus>SUCCzSS</stmtus>   </productILmLmPzl></smvzILmLm|zMzm\svc_zM_MP_FlzxSrv|2013-05-21 07:46:38.363|NULL|NULL
59|<pmngtmgmssignmznt xmlns:xsd="pttp://www.w3.org/2001/XMLScpzmm" xmlns:xsi="pttp://www.w3.org/2001/XMLScpzmm-instmncz"><ModzlSzmsonmlId>177173</ModzlSzmsonmlId><WorkingNumPzr>MJF13250803</WorkingNumPzr><SzmsonId>20132</SzmsonId><PrmndId>11</PrmndId><pmngtmg|zMzm\Prmmmjud|2013-05-21 07:47:58.493|NULL|NULL
60|<IlmLmPzlmssignmznt xmlns:xsd="pttp://www.w3.org/2001/XMLScpzmm" xmlns:xsi="pttp://www.w3.org/2001/XMLScpzmm-instmncz"><ModzlSzmsonmlId>177173</ModzlSzmsonmlId><WorkingNumPzr>MJF13250803</WorkingNumPzr><szmsonId>20132</szmsonId><IlmLmPzls><IlmLmPzl><IlmLmP|zMzm\Prmmmjud|2013-05-21 07:47:58.680|NULL|NULL

Open in new window


Control File

options (skip=2)
load data infile 'C:\Oracle\xmlmessage.txt'
  into table ABC  
  fields terminated by '|'  OPTIONALLY ENCLOSED BY '"' 
  TRAILING NULLCOLS 
(
messageid,         
message char(4000),
col3,
col4
)

Open in new window


you need to change the path accordingly in above script(in bold) which would be the directory where you would place these files

EDIT:Also you would need to specify explicitly the default length at all places where in your data length would exceed the sql loader default, worth noting in case you are going to work with different table and different data file
0
 
Ivo StoykovCommented:
try this

load data
 infile '/path.to/xmlmessage.txt'
--append -- if you have data in the table
into table ABC
fields terminated by '|'  OPTIONALLY ENCLOSED BY '"' 
 (MessmgeId, Messmge )

Open in new window

HTH

Ivo Stoykov
0
 
GouthamAnandAuthor Commented:
There is no need to match the table columns with the file fields because I need only first two columns and ignore others.
So this is not an issue.
Anyway your hint message char(4000) worked for me and I could load the data(with only 2 columns).

Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.