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 Go to next message
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 #448070 is a reply to message #448068] Fri, 19 March 2010 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I believe the format of my control file is correct but for some reason it won't load.

I believe your control file is not correct but can not help without it.
So post it, from top to bottom.

Before, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: SQL Loader & control file problem [message #448072 is a reply to message #448070] Fri, 19 March 2010 06:44 Go to previous messageGo to next message
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 #448075 is a reply to message #448072] Fri, 19 March 2010 06:53 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I'm not wrong, "INSERT INTO" you are using is invalid. Should be only "INTO".

No:
insert into table testBB
when record_type = 'BB'

Yes:
into table testBB
when record_type = 'BB'

(The same goes for other tables as well)
Re: SQL Loader & control file problem [message #448079 is a reply to message #448075] Fri, 19 March 2010 07:00 Go to previous messageGo to next message
quiche
Messages: 6
Registered: March 2010
Junior Member
Thanks, I will try that and see how it goes.

Kind regards,

Lorraine
icon9.gif  Re: SQL Loader & control file problem [message #448090 is a reply to message #448079] Fri, 19 March 2010 07:27 Go to previous messageGo to next message
quiche
Messages: 6
Registered: March 2010
Junior Member
Hi,
I have made those changes but still getting the same error


bash-3.00$ ./test_load myload 20100317

SQL*Loader: Release 10.2.0.4.0 - Production on Fri Mar 19 12:23:15 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL*Loader-350: Syntax error at line 5.
Expecting "(", found keyword when.
when record_type = 'AA'
^
bash-3.00$



Thanks

Re: SQL Loader & control file problem [message #448091 is a reply to message #448072] Fri, 19 March 2010 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
icon6.gif  Re: SQL Loader & control file problem [message #448113 is a reply to message #448091] Fri, 19 March 2010 09:04 Go to previous messageGo to next message
quiche
Messages: 6
Registered: March 2010
Junior Member
Hi Michel,
Thanks for your help I changed the 'fields terminated' clause to after the 'when' clause and that seems to have worked.
I just have data errors now, which is not so bad, as least the script runs without any errors.

Thanks again

Kind regards,

Lorraine
Re: SQL Loader & control file problem [message #448137 is a reply to message #448113] Fri, 19 March 2010 13:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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 Go to previous messageGo to next message
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
Re: SQL Loader & control file problem [message #448303 is a reply to message #448229] Mon, 22 March 2010 12:21 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
The error message tells you what to do to fix it:

Column not found before end of logical record (use TRAILING NULLCOLS)

So use:

fields terminated by '|' TRAILING NULLCOLS


Previous Topic: How to ignore rows with a specific character
Next Topic: Recover Partitioned Table DP export into unpartitioned table
Goto Forum:
  


Current Time: Sun Dec 04 04:51:39 CST 2016

Total time taken to generate the page: 0.10963 seconds