Solved

sql loader xmldata into oracle clob column

Posted on 2014-10-16
3
589 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now