SQL Loader

[jdwins]

We are trying to load comma delimited data in which the
character variables are also surrounded by double quote marks
into a database using Rel 8.0.5.0.0 of SQL Loader for WIN 95 into
the Oracle 8 data base (NT). None of the records loads. The
reason given is that the criteria in two when statements is
not met - however the data shows it is

In the log, the length of the data elements shows an asterisk.

Some of the code follows:

OPTIONS (bindsize=118864)
LOAD DATA
INFILE 'c:\oradocs\MANAGE\cbspdex.dat'
BADFILE 'C:\oradocs\manage\cbsp107.bad'
DISCARDFILE 'c:\oradocs\manage\cbsp107.dis'
APPEND

INTO TABLE administrator.cbsp107
   WHEN  (servcat = "101")
   and   (funsrce = "21")
   FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'  
   (TRTYPE   CONSTANT 'N', psa_no, SYR, SMO, servcat, funsrce, totexp,
   matcsh, matcik, proginc, othfund TERMINATED BY " ",
   dateproc  SYSDATE,
   trans_used  CONSTANT  'N')

INTO TABLE administrator.cbsp107
   WHEN  (servcat = "102")
   and   (funsrce = "22")
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'     
   (TRTYPE   CONSTANT 'N', psa_no, SYR, SMO, servcat, funsrce, totexp,
   matcsh, matcik, proginc, othfund TERMINATED BY " ",
   dateproc  SYSDATE,
   trans_used  CONSTANT  'N')

INTO TABLE administrator.cbsp107
   WHEN  (servcat = '103')
   and   (funsrce = '23')
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
  (TRTYPE   CONSTANT 'N', psa_no, SYR, SMO, servcat, funsrce, totexp,
   matcsh, matcik, proginc, othfund  TERMINATED BY " ",
   dateproc  SYSDATE,
   trans_used  CONSTANT  'N')

The data follows:

"01","2000","02","102","22",3333,3,3,3,3
"05","2000","05","104","24",10424,32,5,67,234
"06","2000","06","099","20",1234,3,5,78,89

What are we not doing correctly?

John Winston
California Dept of Aging
Sacramento






--------------------------------------------------------------------------------
[sig][/sig]

 

[THoey]

Since it is reading everything between the comma's as a datafield, isn't it failing because it is trying to compare a string with the value of 102 to a string with the value of "102"? I think you need to add a single quote around the quotes in your WHEN statement:

WHEN (servcat = '"102"')

Try that...
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[listener22]

If you want to test the specifications in your control file, apart from
the when clauses, change the insert table to be a new temp table,
change the infile specification to be *, take out the when's, and
put in a begindata stmt, followed by 3 or 4 lines of sample data.
(Make a copy of your control file, and in the copy delete everything that
doesn't pertain to the first table.)
That way you will know what values are being read in for
servcat and funsrce, and whether the problem lies there or
in your when clauses.

A simplified example:
LOAD DATA
INFILE *
APPEND INTO TABLE my.temp_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '&quot;'
(NO, DESC)
BEGINDATA
1, &quot;Test1&quot;
2, &quot;Test2&quot;
3, &quot;Test3&quot;
4, &quot;Test4&quot;
[sig]<p>Jim<br><a href= > </a><br>oracle, vb, some javascript[/sig]

 

[massonek]

There's a big clue in the Oracle Documentation for SQL*Loader that states :

&quot;A key point when using multiple INTO TABLE statements is that field scanning continues from where it left off when a new INTO TABLE statement is processed.&quot;

That is, the second INTO TABLE will NOT start processing on the next line. Nor will the third! I'm guessing that a SQL*Loader control file with three INTO statements, expects three distinct sections of data on each line. As a rough example, SQL*Loader expects a data file that looks like :

A,B,C,1,2,3,D,E,F

But the actual file you're sending is :

A,B,C
1,2,3
D,E,F

You'll probably need three SQL*Loader control files. One for each WHEN clause. Or else you'll have to do some drastic editing of the data file.
[sig][/sig]

 

[JamesWeb]

I am having a simlar problem.
I am unable to run a parfile on my new computer which is a P4 with win 2000 however it did run on my NT4 machine.

The error messgae I get is:

E:\GIS Tools\NZ\SQLLoad\Update>sqlldr parfile=complace_ins.par
SQL*Loader: Release 8.1.7.0.0 - Production on Mon Feb 11 08:36:19 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL*Loader-282: Unable to locate character set handle for character set ID (0).

The parfile has the following parameters:
data=E:\NZ\Data\32_CPNUpdate_080005_31Jan2002\nz_com_ins.dat
log=E:\NZ\Data\32_CPNUpdate_080005_31Jan2002\Logs\complace_ins.log
bad=E:\NZ\Data\32_CPNUpdate_080005_31Jan2002\Logs\complace_ins.bad
discard=E:\NZ\Data\32_CPNUpdate_080005_31Jan2002\Logs\complace_ins.dis
control=complace_ins.ctl
userid=db/password@envrio

But when I check the log file it only just gets to the end of the table names and crashes out before any data is inserted. Does anyone have any idea's I'm really lost!!

 

[JamesWeb]

I think I have tracked down the problem to a difference in the client and server side NLS values. How do I change the NLS values on the client side?

I have code to do it in UNIX:
% setenv NLS_SORT FRENCH (or simlar)
But I need to do it in windows and want it to be hardcoded preferably.