SQL*Loader question.

[65]

Hi,
Currently I've worked on loading the data from a flat file(external file) into multiple tables of an Oracle Database. Please anyone help me how to write a SQL*loader command line to do so. Also, Please direct me whether I can obtain more useful information about this sql*loader concepts beside the oracle.com website.

Thanks in advance.

th

 

[evatype2]

Yuck. Create an intake table in Oracle and then use SQL to spread the data to its proper place.

 

[65]

Hi,
Thanks for your quick response. However, your information is not clear enough.

Thanks
th

 

[mesuj]

you should write a control file(.ctl file). This file would be say LoadScript.ctl as

LOAD DATA
INFILE DatFileName
INTO TABLE TableName
FIELDS TERMINATED BY delimiter
(line_data)

Then type the following
sqlldr userid=userid/password control=LoadScript.ctl

This would read from the data file and load it into the table which you specify.You can also give fieldlist for the corresponding columns.

mesuj

 

[EdWall]

To load into multiple tables, you could use a when clause in your SQL-Loader control file if you have a fixed format file as in the example

LOAD DATA
INFILE 'daily.dat'
REPLACE INTO TABLE prices
WHEN (12:17 = 'EUSCAN')
TRAILING NULLCOLS
(
PROD_CODE POSITION(01:04) CHAR,
QUOTE_AREA POSITION(6:10) CHAR,
QUOTE_TYPE POSITION(24:25) CHAR,
QUOTE_DATE POSITION(28:35) CHAR
"to_datequote_date,'dd/mm/rr')",
IMPORT_DATE POSITION(28:35) CHAR "to_date(SYSDATE)",
PROD_PRICE POSITION(36:42) DECIMAL EXTERNAL
)
INTO TABLE exchange
WHEN (12:17 = 'XRATES')
TRAILING NULLCOLS
(
CURRENCY_CODE POSITION(01:05) CHAR,
QUOTE_DATE POSITION(28:35) CHAR
"to_datequote_date,'dd/mm/rr')",
IMPORT_DATE POSITION(28:35) CHAR "to_date(SYSDATE)",
EX_RATE POSITION(36:42) DECIMAL EXTERNAL
)

Otherwise, I agree that it would be better to load into a holding table and then use a procedure or a set of SQL statements to move the data to other tables.

Ed

 

[65]

Hi,

Below are my control and data filesboth files are stored on /Disk05)

terminal.ctl file:

LOAD DATA
INFILE = ‘Disk05\terminal.dat’
INTO TABLE TERMINAL_POSITION
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”’
(

TERMINAL_POSITION_ID VARCHAR2 TERMINATED BY “,” ENCLOSED BY ‘”’,
TERMINAL_POSITION_NAME VARCHAR2 TERMINATED BY “,”
ENCLOSED BY ‘”’,
TERMINAL_POSITION_DESCRIPTION VARCHAR2 TERMINATED BY “,”
ENCLOSED BY ‘”’
)

terminal.dat file:

A,Axial,Terminals extend from both ends in the direction of the major axis of a cylindrical or elliptical package.
B,Bottom,Terminals extend from the bottom of the package.
D,Dual,Terminals are on opposite sides of a square or rectangular package or located in two parallel rows.

for any reason, when entering
sqlldr username/password control = /Disk05/terminal.ctl log =/Disk05/terminal.log data = /Disk05/terminal.dat

an error message shows:
segmentation fault.

Alternatively, when issuing
sqlldr userid = username/password control = /Disk05/terminal.ctl log =/Disk05/terminal.log
data = /Disk05/terminal.dat

an error message shows:

lf iopn failed for file (terminal.log)

Please note that: I did create terminal.log that is stored on /Disk05 as well. Please anyone point out what did I do wrong. Your help is greatly appreciated.

Thanks
th

 

[65]

Finally, I successfully loaded the data into the tables, thanks to all your help.

There is errors in my control file. Thus, it caused the error messages: segmentation fault. Here is my fix for my problems

The infile = '/Disk05/terminal.ctl' should be
infile /Disk05/terminal.ctl' # no equal sign (=)in between.

My updated control file is:
LOAD DATA
INFILE ‘\Disk05\terminal.dat’
INTO TABLE TERMINAL_POSITION
FIELDS TERMINATED BY ‘,’
(TERMINAL_POSITION_ID,
TERMINAL_POSITION_NAME,
TERMINAL_POSITION_DESCRIPTION)

This can be of help to someone who runs into the same problem as I did.

Again, Thanks for all your help.
th