Solved

sql loader xmldata into oracle clob column

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

Suggested Solutions

Title # Comments Views Activity
one-way data "masking" MD5 sql 26 145
SQL query of Oracle 10g database. 8 78
VB.Net - CSV to Oracle table 4 52
PL/SQL Display based on value 4 18
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

910 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

25 Experts available now in Live!

Get 1:1 Help Now