How to load a single column using sql loader

[rogers42]

Hi Folks,

I have a data file with three comma delimited columns. I am trying to load just the first column from the file into an Oracle table. The remaining table columns should contain hardcoded values.

My table looks as follows

Name Null? Type
---------- -------- ----------------------------
ADDRESS VARCHAR2(12)
IP VARCHAR2(15)
UPDATED_DATE DATE


My sql loader control file looks as follows

LOAD DATA
APPEND INTO TABLE T1
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
address CHAR "upper(replaceaddress, '.',''))",
ip CHAR "10.10.0.0",
updated_date SYSDATE
)

Sample Data:
============
0019.5ed2.02de,12480000,3145728
0019.5ed7.0ac4,12480000,3145728
0013.7189.e530,18000000,3145728

The sql loader spits the following error

Record 50: Rejected - Error on table T1, column IP.
ORA-00917: missing comma

Record 51: Rejected - Error on table T1, column IP.
ORA-00917: missing comma

Can somebody point out any errors?

Thanks in advance

rogers42

 

[taupirho]

I assume records 1 to 49 loaded OK. If so can you post say, records 48, 49 and 50. Also the .log and .bad file would be useful too.


In order to understand recursion, you must first understand recursion.

 

[rogers42]

Hi,

Thanks for the reply. Actually none of the records had loaded. I had cut & paste randomly from the error log.

The contents of the log file from the begining are as follows

Control File: 24.153.0.210.ctl
Data File: 24.153.0.210.csv
Bad File: 24.153.0.210.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table T1, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
----------- ---------- ----- ---- ---- ---------------------
MAC_ADDRESS FIRST * , CHARACTER
SQL string for column : "upper(replace:mac_address, '.',''))"
CMTS_IP NEXT * , CHARACTER
SQL string for column : "24.153.0.210"
UPDATED_DATE SYSDATE

Record 1: Rejected - Error on table T1, column CMTS_IP.
ORA-00917: missing comma

Record 2: Rejected - Error on table T1, column CMTS_IP.
ORA-00917: missing comma

Record 3: Rejected - Error on table T1, column CMTS_IP.
ORA-00917: missing comma

I hope this helps.

Thanks

rogers42

 

[ChrisHunt]

It's a long while since I used SQL*Loader, but as I recollect it's very difficult (or even impossible) to get it to do anything cleverer than taking an input file and stuffing it into a table.

You're probably better off splitting this into 2 steps: Use SQL*Loader to read the file as-is into a holding table, then write a {PL/}SQL script to transfer it to the live table, with whatever further processing that you require.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Dagon]

If you're running this under UNIX with a file copied from a Windows-based system, you may have fallen foul of the problem where it leaves Control-M characters at the end of the line if it's been copied without conversion to UNIX format. There is a utility called dos2unix that will convert the file.

 

[taupirho]

Ahh, I see what's going on, though its not obvious.

Basically, in your control file the 10.10.0.0 bit of your "10.10.0.0" string needs to be eclosed in single quotes , so it should look like "'10.10.0.0'"




In order to understand recursion, you must first understand recursion.

 

[rogers42]

Hi Folks,

Thanks for all the well meaning and helpful replies. I had already double checked some of the suggestions (e.g. getting rid of funny characters, multi stage loading, etc).

What did the trick is the use of keyword "CONSTANT". The working control file is as follows

LOAD DATA
INFILE '10.10.10.10.csv'
BADFILE '10.10.10.10.bad'
DISCARDFILE '10.10.10.10.dis'
APPEND INTO TABLE T1
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
address CHAR "upper(replacmac_address, '.',''))",
cmts_ip CONSTANT "10.10.10.10",
updated_date SYSDATE
)

PS:
Time permitting, I will try the single quotes around my IP string as well.

Thanks once again and keep on learning & teaching.

rogers42