Solved

SQL lOADER(DATA FORMAT)

Posted on 2006-10-30
6
741 Views
Last Modified: 2008-03-17
Hi All,
My data file records looks like this
95938,95940,12-23-2005,01-26-2006,"Landmark VI CDO, Ltd.",Asset-Backed Securities,CDO,,,,310,6,B,15000000,,9.2,Aa2,AA,,Floating,,100.000,,50.000,,Libor,,Other Tranche Comments,Comments

How can I load this file using sql loader
I created the ctl file like this

load data
badfile /u01/sf_laod.bad'
append
INTO TABLE TT
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(IGMSecurityID  "trim(:IGMSecurityID)",
-----
-----)

It ingnore all the records
saying  ORA-01008: not all variables bound

I think it is due to  (")  data  have in ,"Landmark VI CDO, Ltd." How can I solve this problem.
Reply ASAP
Thanks



0
Comment
Question by:avi_ny
6 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 17834584
I have found issues with the optionally enclosed by part of SQL*Loader.  In my experience if there are multiple delimiters, it treats it as 1 column.

For example:

1,,2

is treated as

1,2

I would load the data into a temporary table without the optionally enclosed by, the strip off "s with a command like:

update <tab>
set <col> = replace(<col>, '"')
;
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17835964
can u post your full .ctl file because i can try it it my system here.

Thanks
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 17836332

@johnsone
>>...if there are multiple delimiters, it treats it as 1 column.

Never have I seen SQL*Loader treat consecutive delimiters as ONE.

0
 
LVL 34

Expert Comment

by:johnsone
ID: 17836394
Only with the optionally enclosed by.  That is the ONLY time I have ever seen that.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20296175
Forced accept.

Computer101
EE Admin
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

809 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