Solved

SQL lOADER(DATA FORMAT)

Posted on 2006-10-30
6
743 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
[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
6 Comments
 
LVL 35

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 35

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

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

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

730 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