Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql loader xmldata into oracle clob column

Posted on 2014-10-16
3
Medium Priority
?
656 Views
Last Modified: 2014-10-16
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
0
Comment
Question by:GouthamAnand
3 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 40383820
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
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 2000 total points
ID: 40383839
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
 

Author Closing Comment

by:GouthamAnand
ID: 40383920
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question