Home » RDBMS Server » Server Utilities » SQL * Loader issue
SQL * Loader issue [message #184603] Thu, 27 July 2006 03:45 Go to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Hi all

I have an excel file containting some records..
i want to dump this data into a table using sql*loader tool

How can i accomplish this task with SQL*Loader..

I tried for it..but could not succeed.

Thanks

Re: SQL * Loader issue [message #184612 is a reply to message #184603] Thu, 27 July 2006 04:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
With the wealth of information provided, we cannot even guess what is happening.
No oracle version,no error message, no control file, no ddl, no sample data.
How do you expect us to help you to "accomplish this task"?
Re: SQL * Loader issue [message #184620 is a reply to message #184612] Thu, 27 July 2006 04:24 Go to previous messageGo to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
The table i created for this is
CREATE TABLE license_raw
(
Line_number float ,
Contract# nvarchar2 (255) ,
LIC_NUM float ,
"Hdr/It/Lic" nvarchar2 (255) ,
"Beg Dt" timestamp ,
"End Dt" timestamp ,
"Actn dt" timestamp ,
"Purchase Order Number" nvarchar2 (255) ,
"PO Date" timestamp ,
"Sold-to #" nvarchar2 (255) ,
"Ship-to #" nvarchar2 (255) ,
"Dealer #" nvarchar2 (255) ,
"Payer #" nvarchar2 (255) ,
"MC #" nvarchar2 (255) ,
"SC #" nvarchar2 (255) ,
"Group PubID" nvarchar2 (255) ,
"Group Name" nvarchar2 (255) ,
Group_Address_Pub_Id nvarchar2 (255) ,
"Is Default Group" float ,
"Renewal Coor" nvarchar2 (255) ,
"Group Coor1" nvarchar2 (255) ,
"Group Coor2" nvarchar2 (255) ,
"Group Coor3" nvarchar2 (255) ,
"Service Company1" nvarchar2 (255) ,
"Service Company2" nvarchar2 (255) ,
"Service Company3" nvarchar2 (255) ,
"Group ISV1" nvarchar2 (255) ,
"Group ISV2" nvarchar2 (255) ,
"Group ISV3" nvarchar2 (255) ,
"Sub Material #" nvarchar2 (255) ,
"Material Number" nvarchar2 (255) ,
"Serial Number" nvarchar2 (255) ,
"CD key" nvarchar2 (255) ,
"Number of Seats" float ,
"Sold to" nvarchar2 (255) ,
"Ship to" nvarchar2 (255) ,
RENEWED_END_DATE nvarchar2 (255) ,
Contract_Program nvarchar2 (255) ,
PUB_ID nvarchar2 (255) ,
"License Key" nvarchar2 (255) ,
ENT_ISV_PROPERTY_1 nvarchar2 (255) ,
ENT_ISV_PROPERTY_2 nvarchar2 (255) ,
ENT_ISV_PROPERTY_3 nvarchar2 (255) ,
ENT_KIT_NUMBER nvarchar2 (255) ,
ENT_KIT_OPTION_CODE nvarchar2 (255)
)

control file is
---------------

LOAD DATA

INFILE 'd:\com.csv'

INSERT
INTO TABLE license_raw

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(Line_number,Contract#,LIC_NUM,
"Hdr/It/Lic",
"Beg Dt" ,
"End Dt" ,
"Actn dt" ,
"Purchase Order Number" ,
"PO Date" ,
"Sold-to #" ,
"Ship-to #" ,
"Dealer #" ,
"Payer #" ,
"MC #" ,
"SC #" ,
"Group PubID" ,
"Group Name" ,
Group_Address_Pub_Id ,
"Is Default Group" ,
"Renewal Coor" ,
"Group Coor1" ,
"Group Coor2" ,
"Group Coor3" ,
"Service Company1" ,
"Service Company2" ,
"Service Company3" ,
"Group ISV1" ,
"Group ISV2" ,
"Group ISV3" ,
"Sub Material #" ,
"Material Number" ,
"Serial Number" ,
"CD key" ,
"Number of Seats" ,
"Sold to" ,
"Ship to" ,
RENEWED_END_DATE ,
Contract_Program ,
PUB_ID ,
"License Key" ,
ENT_ISV_PROPERTY_1 ,
ENT_ISV_PROPERTY_2 ,
ENT_ISV_PROPERTY_3 ,
ENT_KIT_NUMBER ,
ENT_KIT_OPTION_CODE
)

At command prompt
-----------------

c:\>sqlldr i1pb2/i1pb2@poeticdb control=d:\license_ctl.ctl

i am getting the message like

Commit point reached - logical record count 18

but when i query the table.. i am not getting any records..

Help on this is greatly appreciated.

Thanks
Re: SQL * Loader issue [message #184624 is a reply to message #184620] Thu, 27 July 2006 04:37 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There should be a log file generated (license_ctl.log?).
Check the contents.
>> "Group ISV1"
Remove the Quotes around the column names ( like above)
And this is the first time i am seeing someone to user NVARCHAR2.
Why are you using it?
Why not just varchar2? You seem to using Nvarchar2 indiscriminately.
>> "Sold-to #"
This just a bad convention for table/Column naming.
You have created a special name (within the quotes).
Unless it is absolutely necessary, do not create the columns like that. May be, is that why you are using Quotes around the column names in your control file?
Previous Topic: Import Error 297
Next Topic: SQLLDR - Removing CR/LF in Text Fields
Goto Forum:
  


Current Time: Sun Dec 04 04:55:02 CST 2016

Total time taken to generate the page: 0.09556 seconds