-
hi experts, I have a question regarding the sqlldr.exe. I don't understand why the sqlldr.exe doesn't exit even after it finishes loading data to the database. Is there anything wrong with my control file or because of something else? Please help! I have been struggling with this for few days.
Here is my files for the sqlldr. can anyone tell me what the problem is? thanks.
the command file:
---------------------
sqlldr control=C:\codes\bcp\oracle\testload02.ctl, log=testload.log, bad=testload.bad, data=C:\codes\bcp\oracle\testdata.csv, userid=system/manager@RESEARCH
the control file.
-----------------
LOAD DATA
INFILE 'testdata.csv'
REPLACE
INTO TABLE testtable
(
loan_num INTEGER EXTERNAL TERMINATED BY ',',
none FILLER DECIMAL EXTERNAL TERMINATED BY ',',
cur_bal DECIMAL EXTERNAL TERMINATED BY ',',
interest DECIMAL EXTERNAL TERMINATED BY '\n'
)
the datafile:
--------------
1, 7.6, 23232.24, 9.6
2, 8.3, 34353.53, 8.3
3, 6.7, 55353.53, 8.6
4, 9.4, 63472.53, 9.8
5, 8.2, 44443.53, 8.1
the log file, "testload.log":
----------------------------
SQL*Loader: Release 8.1.5.0.0 - Production on Thu Feb 1 10:41:31 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Control File: C:\codes\bcp\oracle\testload02.ctl
Data File: C:\codes\bcp\oracle\testdata.csv
Bad File: testload.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 65536 bytes
Continuation: none specified
Path used: Conventional
Table HGANTEST, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
LOAN_NUM FIRST * , CHARACTER
NONE NEXT * , CHARACTER
(FILLER FIELD)
CUR_BAL NEXT * , CHARACTER
INTEREST NEXT * n CHARACTER
Table HGANTEST:
5 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 65016 bytes(84 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Thu Feb 01 10:41:31 2001
Run ended on Thu Feb 01 10:41:35 2001
Elapsed time was: 00:00:03.67
CPU time was: 00:00:00.12
-
What do you mean by it doesn't exists. Its been given a task of loading a file to a table with respective parameter files. When its done it will come out of load mode.
You have to invoke it again if you want to load another table.
If you want to load multiple tables, you have to include 'em in the script.
-
It doesn't exit because I had to type 'ctrl-C' to terminate the execution. Here is a screen capture of my command prompt.
-----------------------------------------
C:\Inetpub\ftproot>testload
C:\Inetpub\ftproot>sqlldr control=C:\codes\bcp\oracle\testload02.ctl, log=testload.log, bad=testload
.bad, data=C:\codes\bcp\oracle\testdata.csv, userid=system/manager@RESEARCH
SQL*Loader: Release 8.1.5.0.0 - Production on Thu Feb 1 10:54:25 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 5
^CTerminate batch job (Y/N)? y
C:\Inetpub\ftproot>
-----------------------------------------------------
-
-
Sometimes these executables doesn't work with server memory problems also. Last week we had the same problem with svrmgr/sqlplus executables. It will come out after 3-5 min
you type in exit. Again its resolved on its own after 24 hours. Its strange. Noone did anything.
If you wanna test this, use your load from somewhere else using connect string.
Call sqlldr from sql script. Connect to sqlplus using another box executables and call that sql script.
-
Exit statemnet needed in C:\Inetpub\ftproot>testload
-
Of course ! Exit is needed if you are calling sqlldr from another script and not interactively.
thg,
Maintain one thread only specific to your problem. It helps others too focus on one thread than keep looking back and forth between threads, if they have the kinda problem.
I had the same problem and confused just before, I was not sure what thread I participated in.
[Edited by sreddy on 02-01-2001 at 04:31 PM]
-
Thsnks everyone!
I will maintain only one thread per topic.
About the sqlldr.exe exitting problem, I found out that the sqlldr.exe exits only when it is executed on the same machine as the oracle database server.
I am connecting to the database from aclient machine, the client machine also has the oracle client edition installed. so when I call the sqlldr.exe, it uses the client copy. I have test it many times, it doesn't work on the client machine doesn't matter whether I put 'EXIT' at the end of the sqlldr script or not.
To make my question clear, I will explain what I am doing below. :
There are 3 machines in this scenario:
1) client machine
2) web server machine
3) database server
My project only has two parts,
1) a .csv data file is uploaded to the web server machine from the client machine
2) invoke the sql loader to load data from the .csv file to the database.
My question is:
Can I invoke the sqlldr.exe on the database server machine to load the data file from a different machine(the web server machine in this case, you know, not all client machines have oracle install. )? How?
Thanks.
thg.
