?
Solved

sql loader xmldata into oracle clob column

Posted on 2014-10-16
3
Medium Priority
?
623 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 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…
Suggested Courses

765 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