Solved

SQL lOADER(DATA FORMAT)

Posted on 2006-10-30
6
744 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

717 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