Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 749
  • Last Modified:

SQL lOADER(DATA FORMAT)

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
avi_ny
Asked:
avi_ny
1 Solution
 
johnsoneSenior Oracle DBACommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
can u post your full .ctl file because i can try it it my system here.

Thanks
0
 
MikeOM_DBACommented:

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

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

0
 
johnsoneSenior Oracle DBACommented:
Only with the optionally enclosed by.  That is the ONLY time I have ever seen that.
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now