What does this mean?

[BIS]

Hello,
I am trying to use sqlload to import and replace two rows in a table. I am also very new to this. I have made a .ctl file which looks like this:
----------------------------------
LOAD DATA
INFILE=load.dat
REPLACE
INTO TABLE TEST_TABLE
fields terminated by whitespace
------------------------------------
The .dat file is simply a txt file like this:
------------------------------------
CLM1 CLM2
123 456
456 789
789 123
-------------------------------------
When I do a
sqlloas userid=username/password control=load.ctl I get this error:
SQL*Loader: Release 7.3.2.3.0 - Production on Mon Mar 11 15:51:47 2002

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.

SQL*Loader-350: Syntax error at line 5.
Expecting "(", found end of file.

Can anybody help - it is probably trivially easy!

 

[THoey]

I haven't done any with the fields terminated clause, but I think the problem is that you don't declare your fields in your control file. The following is one of my control files:

load data -- Keywords to start SQL Loader
infile 'D:\FTP\SATR\loginout.txt' -- Import file and path
into table loginout APPEND -- Table to import into
(
datetime position(01:08) date 'YYYYMMDD',
agent_id position(10:13) CHAR,
login position(14:21) CHAR,
logout position(22:29) CHAR
)

Since you are doing the terminated by, I would think that you just leave out the "position(01:08)" portion for each line. I think you still need to list the fields and the types...

Hope that helps... Terry M. Hoey

 

[rtefft]

BIS - using REPLACE will cause Oracle to empty the table of all data before inserting your rows.

(excert from Oracle8i Utilities Reference)
REPLACE
With REPLACE, all rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table.

Updating Existing Rows
The REPLACE method is a table replacement, not a replacement of individual rows. SQL*Loader does not update existing records, even if they have null columns. To update existing rows, use the following procedure:

1. Load your data into a work table.
2. Use the SQL language UPDATE statement with correlated subqueries.
3. Drop the work table.
(/excerpt)

Rich ____________________________
Rich Tefft
PL/SQL Programmer

 

[BIS]

I am getting more and more confused here...

This is the scenario. From an Informix database, I run an sql statement, and output it to a file (f.ex. load.dat). Using ftp I ftp this file to the machine where Oracle resides. I then use sqlload to try to load the data into a table. This is currently my .ctl file:
---------------------------------------
LOAD DATA --from infile to ORACLE RDBMS
INFILE load.dat
REPLACE
INTO TABLE TEST_TABLE
fields terminated by whitespace
TRAILING NULLCOLS
(acd, acdcalls, abncalls, incalls, asa, vdn)
--------------------------------------------
Now I have this error in the load.log file:
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ACD FIRST * WHT CHARACTER
ACDCALLS NEXT * WHT CHARACTER
ABNCALLS NEXT * WHT CHARACTER
INCALLS NEXT * WHT CHARACTER
ASA NEXT * WHT CHARACTER
VDN NEXT * WHT CHARACTER

Record 1: Discarded - all columns null.
Record 2: Discarded - all columns null.
Record 3: Discarded - all columns null.
Record 10: Discarded - all columns null.
Record 17: Discarded - all columns null.
Record 24: Discarded - all columns null.
Record 31: Discarded - all columns null.
Record 38: Discarded - all columns null.
Record 45: Discarded - all columns null.
Record 4: Rejected - Error on table "STI"."TEST_TABLE", column ACD.
ORA-01722: invalid number

Record 5: Rejected - Error on table "STI"."TEST_TABLE", column ACD.
ORA-01722: invalid number
--More--

Any ideas? If you need I will explain in more detail if needed.....

 

[THoey]

Can you show us what the data looks like? Please post a couple of records, one that loads and one that fails. Terry M. Hoey

 

[BIS]

GLADLY! This is how the load.dat looks like when I do a 'cat load.dat'. (Just the first few lines, there are about 450 rows). Does this help?
-----------------------------------------
acd 1
vdn 2104
acdcalls 597
abncalls 124
incalls 989
asa 35

acd 1
vdn 2106
acdcalls 825
abncalls 92
incalls 1130
asa 41

acd 1
vdn 2108
acdcalls 180
abncalls 23
incalls 249
asa 97
---------------------------------------

 

[BIS]

UPDATE:
I thought the format of the load.dat file looked a bit strange (probably caused by a GROUP BY statement). Anyway, made a new querry, and a new load.dat file which looks like this:
--------------------------------------------
acd vdn acdcalls abncalls incalls

1 054 0 0 2
1 058 0 1 1
1 060 0 10 25
1 062 0 2 7
1 063 0 0 3
1 065 0 0 1
1 069 0 0 1
1 081 0 0 1
------------------------------------------
Now when I run the sqlload I get a new error :-(
------------------------------ ---------- ----- ---- ---- ---------------------
VDN FIRST * WHT CHARACTER
ACD NEXT * WHT CHARACTER
ACDCALLS NEXT * WHT CHARACTER
ABNCALLS NEXT * WHT CHARACTER
INCALLS NEXT * WHT CHARACTER

Record 1: Discarded - all columns null.
Record 2: Discarded - all columns null.
Record 4: Discarded - all columns null.
Record 3: Rejected - Error on table "STI"."TEST_TABLE", column VDN.
ORA-01722: invalid number

Record 6: Rejected - Error on table "STI"."TEST_TABLE".
ORA-00001: unique constraint (STI.I_TEST_TABLE) violated

Record 7: Rejected - Error on table "STI"."TEST_TABLE".
ORA-00001: unique constraint (STI.I_TEST_TABLE) violated

sigh...

 

[BIS]

UPDATE 2:
Never mind folks, I got it solved. Thanks for your input though.

 

[THoey]

Is the format of your data actually like (and are these three records?):

1 054 0 0 2
1 058 0 1 1
1 060 0 10 25

If so, that is a fixed width file and you can use the example I initially posted to build a control file that will work a lot easier.

load data                
infile 'D:\SomePathGoesHere'
into table MyTable APPEND
(
 acd      position(06:06)  INTEGER EXTERNAL,
 vdn      position(08:10)  CHAR,
 acdcalls position(24:25)  INTEGER EXTERNAL,
 abncalls position(36:37)  INTEGER EXTERNAL,
 incalls  position(47:48)  INTEGER EXTERNAL
)

Make the necessary changes to the path, tablenames, etc and give that a try...


Terry M. Hoey

 

[THoey]

Glad you got it working. Please post your solution so that others might learn from it... Terry M. Hoey

 

[BIS]

The solution was in the control file. I added the line TRAILING NULLCOLS.
-----------------------------------------
LOAD DATA --from infile to ORACLE RDBMS
INFILE load.dat
REPLACE
INTO TABLE TEST_TABLE
fields terminated by whitespace
TRAILING NULLCOLS
(vdn, acdcalls, abncalls, incalls, asa)
------------------------------------------
Also had to make sure that there were no 'key' columns in the table or I received the 'ORA-00001: unique constraint (STI.I_TEST_TABLE) violated' error. This can impact perfomance I understand, but since this is a relatively small table I don't think it matters that much.

Just out of curiosity, in this example that was posted above:
load data
infile 'D:\SomePathGoesHere'
into table MyTable APPEND
(
acd position(06:06) INTEGER EXTERNAL,
vdn position(08:10) CHAR,
acdcalls position(24:25) INTEGER EXTERNAL,
abncalls position(36:37) INTEGER EXTERNAL,
incalls position(47:48) INTEGER EXTERNAL
)
can someone explain to me the position part of it. I don't get it. What is the 06:06 and how do you get to these numbers?

 

[rtefft]

The position is the location (in bytes) of the column in your data record. (aka "fixed-width" or "fixed-position" data). ACD POSITION(06:06) means that the ACD column data is found beginning in the 6th byte of each record, and ending at the 6th byte. This means it's a 1-byte field. VDN POSITION(8:10) means bytes 8-10 for a length of 3, etc.

For this type of load to work, your data columns must be in exactly those positions, so any whitespaces besides spaces (such as tabs) can cause your problems.

In my experience, fixed-position loads run faster than comma-delimited ones on large data files. Probably because SQL*Loader does't have to scan each field looking for the end of it; it can just chop up the record and go.
____________________________
Rich Tefft
PL/SQL Programmer

 

[BIS]

Many thanks! Another question if I may...
I can now successfully use the sqlload. The next step is to schedule this with a cron job. I tried the following but it didn't work.
25 * * * * /oracle/bin/sqlload userid=system/manager control=/home/admin/load.ctl
Any ideas why? The cron job runs OK, but the load doesn't happen.

 

[BIS]

This is teh error I get if it is of any use:
/oracle/bin/sqlload userid=system/manager control=/home/admin/load.ctl

produced the following output:

Message 1 not found; No message file for product=RDBMS, facility=ULMessage 925 n
ot found; No message file for product=RDBMS, facility=ULMessage 924 not found; N
o message file for product=RDBMS, facility=UL
SQL*Loader: Release 7.3.2.3.0 - Production on Wed Mar 13 14:25:01 2002

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.

 

[BIS]

Sorry folks,
Got this one sorted as well. Needed to tell cron (through a small shell script) the oracle environment variables.