Home » RDBMS Server » Server Utilities » SQL Loader & control file problem (Unix, Oracle 10.2.0)
| SQL Loader & control file problem [message #448068] |
Fri, 19 March 2010 06:20  |
quiche
Messages: 6
Registered: March 2010
|
Junior Member |
|
|
Hi,
I'm having a problem with SQL loader and the control file. I want to load a delimited file. The script will eventully be automated where the file name is passed in to the script, it's not a static name.
It's a simple SQL loader Unix script that I have created as follows
Unix file called test_load
# Auto Load
#
#
export data_file=/dev/test_$1$2.csv;
export ORACLE_HOME=/u01/oracle/product/10.2.0;
export ORACLE_SID=XXX;
export PATH=$PATH:$ORACLE_HOME/bin;
sqlldr userid=XXX/XXX data=$data_file \
control=/dev/cntrl/test.ctl \
errors=99999 \
bad=/dev/bad/test_$1$2_$$.bad \
log=/dev/logs/test_$1$2_$$.log \
the top of my control file is as follows
load data
truncate
into table test
fields terminated by "|"
when record_type = 'AA'
(
running at the prompt ./test_load myload 20100319
The following error occurs
SQL*Loader-350: Syntax error at line 5.
Expecting "(", found keyword when.
when record_type = 'AA'
^
I believe the format of my control file is correct but for some reason it won't load. Any sugggestions would be great.
Thanks
|
|
|
|
|
|
| Re: SQL Loader & control file problem [message #448072 is a reply to message #448070] |
Fri, 19 March 2010 06:44  |
quiche
Messages: 6
Registered: March 2010
|
Junior Member |
|
|
Thanks Michel for your reply. I have been looking on line for the format of the control file so I assumed it was correct.
Here is the full control file
load data
truncate
into table test
fields terminated by "|"
when record_type = 'AA'
(
RECORD_TYPE CHAR,
ADDR_LEGAL_FLAG CHAR,
LEGAL_ADDR_NAME CHAR,
LEGAL_ADDR_LINE1 CHAR,
LEGAL_ADDR_LINE2 CHAR,
LEGAL_ADDR_LINE3 CHAR,
LEGAL_COUNTRY CHAR,
LEGAL_CITY CHAR,
LEGAL_STATE CHAR,
LEGAL_POSTAL_CODE CHAR
)
insert into table testBB
when record_type = 'BB'
(
RECORD_TYPE CHAR,
REQUEST_TYPE CHAR
)
insert into table testCC
when record_type = 'CC'
(
RECORD_TYPE CHAR,
MCM_MAX_AMT NUMBER,
MCM_MIN_AMT NUMBER,
MCD_MIN_AMT NUMBER,
MCD_MAX_AMT NUMBER
)
insert into table testDD
when record_type = 'FE'
(
RECORD_TYPE CHAR,
FEE_TYPE CHAR,
AMOUNT NUMBER,
EFFECTIVE_DATE DATE,
END_DATE DATE
)
Kind regards,
Lorraine
|
|
|
|
|
|
|
|
|
|
| Re: SQL Loader & control file problem [message #448091 is a reply to message #448072] |
Fri, 19 March 2010 07:30 |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
As far as I know you cannot mix different type of loading (truncate and insert).
Fixing all the errors it comes:
load data
truncate
into table test
when record_type = 'AA'
fields terminated by '|'
(
RECORD_TYPE CHAR,
ADDR_LEGAL_FLAG CHAR,
LEGAL_ADDR_NAME CHAR,
LEGAL_ADDR_LINE1 CHAR,
LEGAL_ADDR_LINE2 CHAR,
LEGAL_ADDR_LINE3 CHAR,
LEGAL_COUNTRY CHAR,
LEGAL_CITY CHAR,
LEGAL_STATE CHAR,
LEGAL_POSTAL_CODE CHAR
)
into table testBB
when record_type = 'BB'
(
RECORD_TYPE CHAR,
REQUEST_TYPE CHAR
)
into table testCC
when record_type = 'CC'
(
RECORD_TYPE CHAR,
MCM_MAX_AMT CHAR,
MCM_MIN_AMT CHAR,
MCD_MIN_AMT CHAR,
MCD_MAX_AMT CHAR
)
into table testDD
when record_type = 'FE'
(
RECORD_TYPE CHAR,
FEE_TYPE CHAR,
AMOUNT CHAR,
EFFECTIVE_DATE DATE,
END_DATE DATE
)
Regards
Michel
|
|
|
|
|
|
| Re: SQL Loader & control file problem [message #448137 is a reply to message #448113] |
Fri, 19 March 2010 13:24 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Quiche (Lorraine),
In order to avoid data errors, you will need to have a "fields terminated by '|'" before each set of fields and add "position(1)" to the first field of every set of fields after the first one, in order to reset the pointer. I have added those modifications below.
load data
truncate
into table test
when record_type = 'AA'
fields terminated by '|'
(
RECORD_TYPE CHAR,
ADDR_LEGAL_FLAG CHAR,
LEGAL_ADDR_NAME CHAR,
LEGAL_ADDR_LINE1 CHAR,
LEGAL_ADDR_LINE2 CHAR,
LEGAL_ADDR_LINE3 CHAR,
LEGAL_COUNTRY CHAR,
LEGAL_CITY CHAR,
LEGAL_STATE CHAR,
LEGAL_POSTAL_CODE CHAR
)
into table testBB
when record_type = 'BB'
fields terminated by '|'
(
RECORD_TYPE POSITION(1) CHAR,
REQUEST_TYPE CHAR
)
into table testCC
when record_type = 'CC'
fields terminated by '|'
(
RECORD_TYPE POSITION(1) CHAR,
MCM_MAX_AMT CHAR,
MCM_MIN_AMT CHAR,
MCD_MIN_AMT CHAR,
MCD_MAX_AMT CHAR
)
into table testDD
when record_type = 'FE'
fields terminated by '|'
(
RECORD_TYPE POSITION(1) CHAR,
FEE_TYPE CHAR,
AMOUNT CHAR,
EFFECTIVE_DATE DATE,
END_DATE DATE
)
|
|
|
|
| Re: SQL Loader & control file problem [message #448229 is a reply to message #448137] |
Mon, 22 March 2010 04:30 |
quiche
Messages: 6
Registered: March 2010
|
Junior Member |
|
|
Thanks Barbara.
I still have some data errors which I can seem to resolve. I am getting
Column not found before end of logical record (use TRAILING NULLCOLS)
ORA-01438: value larger than specified precision allowed for this column
My record has data present in the columns at the end of the record but there are columns in the middle of the record which are null and can be null.
My columns are sepreated by | but the null columns don't have a space in them and what seem to be happening is SQL Loader doesn't recognise that when I have || this is acutally a column and pulling the next one with data into it thus resulting in the above errors.
The record would be something like this
AA|Y|12 Main Street|||UK|London||ABC 123
I have been reading about the NULLIF command, will this work or does there need to be a space in the position for LEGAL_ADDR_LINE2, LEGAL_ADDR_LINE3 and LEGAL_STATE.
These values can be either null or have a value.
Thanks
Kind regards,
Lorraine
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Oct 31 12:48:35 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
