Data From Text File To Oracle Table

[sureshgane]

I am having a table with 100 columns.I have got a comma delimited variable length text file with 1 million records.Each
line in text file represents one new record
How to import this records in text file to Oracle table.I guess we can do thru sqlldr.Please do send the syntax & also I would like to know the syntax without using position.I mean the real shortcut way to get data from text file to Oracle table

[bkanti]

Hi,
if your text file columns are in the same order as the table and the fields are dilimited by comma
then you can use the following SQL

spool this to a file
column column_id NOPRINT

SELECT column_id,column_name||','
FROM dba_tab_columns
WHERE table_name = <desired_table_name>
and owner = <owner>
ORDER BY column_id
/

Then copy the text file to a control file for the SQL*Loader script.

[mary]

There is a pretty good explanation of control file syntax in Oracle Complete Reference book. However, it doesn't give you any examples.
If you need a basic contol file example post a request here. I'll see if I can find one.

[culonbu]

This sample to start sqlldr


sqlldr dwdba/dwdba control=/dw1/createdb/sql/loader/ctl/dwbnd_brand.ctl
direct=false errors=999999 log=/dw1/createdb/sql/loader/logs/dwbnd_brand.log discard=/dw1/createdb/sql/loader/logs/dwbnd_brand.discard

and control file where name is set in loader script above

LOAD DATA
INFILE '/dw1/createdb/sql/loader/data/dwbnd_brand.data'
BADFILE '/dw1/createdb/sql/loader/logs/dwbnd_brand.bad'
INTO TABLE dwdba.dwbnd_brand
APPEND
FIELDS TERMINATED BY ','
(brand_code CHAR(20) "RTRIM(:brand_code)" ,
brand_descr CHAR(60) "RTRIM(:brand_descr)",
active_flag
)


Cheers,