Home » RDBMS Server » Server Utilities » sql loader error for direct path(3 Merged) (Release 10.2.0.5.0 sun solaris )
 sql loader error for direct path(3 Merged) [message #552403] |
Wed, 25 April 2012 09:38  |
 |
guddu_12
Messages: 227
Registered: April 2012
Location: UK
|
Senior Member |
|
|
Dear All,
I have a table which is being load by sqlloader, when i load the table without direct path set to TRUE IT Works well , but when DIRECT path set to TRUE ,it comes out with the following error
SQL*Loader-702: Internal error - Unknown column for OCI_ATTR_COL_COUNT
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
control file looks like below.
load data
BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
append into table TEMP_rio_RESP_TIME_LND
TRAILING NULLCOLS
(
INSTALLATION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TRANSACTION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
SERVER_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
CLINICAL_TRANSACTION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
RESPONSE_TIME DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TRANSACTION_START_TIME TIMESTAMP
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' "TO_TIMESTAMP(:TRANSACTION_START_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
TRANSACTION_END_TIME TIMESTAMP
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' "TO_TIMESTAMP(:TRANSACTION_END_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
LOCATION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
WAIT_TIME DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
INTERNAL_TRANSACTION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
INTERNAL_TIME DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
EXTERNAL_SERVICE_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
EXTERNAL_SERVICE_TIME DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
LOCAL_SERVICE_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
LOCAL_SERVICE_TIME DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
MESSAGE_GUID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
RETURN_MESSAGE_GUID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TRS_SIZE DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' ,
FILE_NAME CONSTANT
"FILENAME",
DATE_LOADED SYSDATE ,
ORIGINAL_DATE_LOADED SYSDATE
)
data set is
V5_RIO_5NCC|78967|172.16.0.166|RioLoginSrc.asp|0.296|12/04/2012 15:27:25.703|12/04/2012 15:27:26.000|V5_RIO_5NCC||||||||||
V5_RIO_5NCC|78968|172.16.0.167|TextDialogueCentre.asp|0.015|12/04/2012 15:27:27.983|12/04/2012 15:27:28.000|V5_RIO_5NCC||||||||||
V5_RIO_5NCC|78969|172.16.0.167|RioLoginSrc.asp|57.843|12/04/2012 15:27:14.157|12/04/2012 15:28:12.000|V5_RIO_5NCC||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: sql loader error for direct path [message #552423 is a reply to message #552419] |
Wed, 25 April 2012 11:48  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
There are a lot of things that are different between the conventional path load and direct path load and a lot of restrictions on using the direct path load. Any data conversion is done on the client side, not server side, so it uses the client nls parameters. So, if you specify a field as timestamp then it must match that nls format. You should not be using to_timestamp on something that is already a timestamp. If it needs to be converted, then you should treat it as char, then use to_timestamp. I created a sample table, and was able to succesfully load your data, changing the directory path to match my system, using your control file and conventional path load. However, when I used the direct path, then it did not load, although I got a different error than you did. Just changing the timestamp data types to char allowed me to load it using the direct path. I have provided a brief demonstration below. If this does not work for you, then you need to try loading something extremely simple, such as one char column into an empty table that nobody is accessing, then add one thing at a time and re-test, until you can narrow the error down. There are many restrictions on loading using direct path, such as there must not be any transactions pending. The direct path is only appropriate in a very few situations. In the future, please post your table structure.
-- c:\my_oracle_files\test.dat:
V5_RIO_5NCC|78967|172.16.0.166|RioLoginSrc.asp|0.296|12/04/2012 15:27:25.703|12/04/2012 15:27:26.000|V5_RIO_5NCC||||||||||
V5_RIO_5NCC|78968|172.16.0.167|TextDialogueCentre.asp|0.015|12/04/2012 15:27:27.983|12/04/2012 15:27:28.000|V5_RIO_5NCC||||||||||
V5_RIO_5NCC|78969|172.16.0.167|RioLoginSrc.asp|57.843|12/04/2012 15:27:14.157|12/04/2012 15:28:12.000|V5_RIO_5NCC||||||||||
-- c:\my_oracle_files\test.ctl:
load data
BADFILE 'c:\my_oracle_files\FILENAME'
append into table TEMP_rio_RESP_TIME_LND
TRAILING NULLCOLS
(
INSTALLATION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TRANSACTION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
SERVER_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
CLINICAL_TRANSACTION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
RESPONSE_TIME DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TRANSACTION_START_TIME CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' "TO_TIMESTAMP(:TRANSACTION_START_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
TRANSACTION_END_TIME CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' "TO_TIMESTAMP(:TRANSACTION_END_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
LOCATION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
WAIT_TIME DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
INTERNAL_TRANSACTION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
INTERNAL_TIME DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
EXTERNAL_SERVICE_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
EXTERNAL_SERVICE_TIME DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
LOCAL_SERVICE_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
LOCAL_SERVICE_TIME DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
MESSAGE_GUID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
RETURN_MESSAGE_GUID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TRS_SIZE DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' ,
FILE_NAME CONSTANT
"FILENAME",
DATE_LOADED SYSDATE ,
ORIGINAL_DATE_LOADED SYSDATE
)
-- table:
SCOTT@orcl_11gR2> create table TEMP_rio_RESP_TIME_LND
2 (
3 INSTALLATION_ID VARCHAR2 (11),
4 TRANSACTION_ID VARCHAR2 (10),
5 SERVER_ID VARCHAR2 (12),
6 CLINICAL_TRANSACTION_ID VARCHAR2 (22),
7 RESPONSE_TIME NUMBER,
8 TRANSACTION_START_TIME TIMESTAMP,
9 TRANSACTION_END_TIME TIMESTAMP,
10 LOCATION_ID VARCHAR2 (11),
11 WAIT_TIME NUMBER,
12 INTERNAL_TRANSACTION_ID VARCHAR2 (10),
13 INTERNAL_TIME NUMBER,
14 EXTERNAL_SERVICE_ID VARCHAR2 (10),
15 EXTERNAL_SERVICE_TIME NUMBER,
16 LOCAL_SERVICE_ID VARCHAR2 (10),
17 LOCAL_SERVICE_TIME NUMBER,
18 MESSAGE_GUID VARCHAR2 (10),
19 RETURN_MESSAGE_GUID VARCHAR2 (10),
20 TRS_SIZE NUMBER,
21 FILE_NAME VARCHAR2 (9),
22 DATE_LOADED DATE ,
23 ORIGINAL_DATE_LOADED DATE
24 )
25 /
Table created.
-- load:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl data=test.dat log=test.log DIRECT=TRUE
-- results:
SCOTT@orcl_11gR2> select * from TEMP_rio_RESP_TIME_LND
2 /
INSTALLATIO TRANSACTIO SERVER_ID CLINICAL_TRANSACTION_I RESPONSE_TIME
----------- ---------- ------------ ---------------------- -------------
TRANSACTION_START_TIME
---------------------------------------------------------------------------
TRANSACTION_END_TIME
---------------------------------------------------------------------------
LOCATION_ID WAIT_TIME INTERNAL_T INTERNAL_TIME EXTERNAL_S EXTERNAL_SERVICE_TIME
----------- ---------- ---------- ------------- ---------- ---------------------
LOCAL_SERV LOCAL_SERVICE_TIME MESSAGE_GU RETURN_MES TRS_SIZE FILE_NAME
---------- ------------------ ---------- ---------- ---------- ---------
DATE_LOAD ORIGINAL_
--------- ---------
V5_RIO_5NCC 78967 172.16.0.166 RioLoginSrc.asp .296
12-APR-12 03.27.25.703000 PM
12-APR-12 03.27.26.000000 PM
V5_RIO_5NCC
FILENAME
25-APR-12 25-APR-12
V5_RIO_5NCC 78968 172.16.0.167 TextDialogueCentre.asp .015
12-APR-12 03.27.27.983000 PM
12-APR-12 03.27.28.000000 PM
V5_RIO_5NCC
FILENAME
25-APR-12 25-APR-12
V5_RIO_5NCC 78969 172.16.0.167 RioLoginSrc.asp 57.843
12-APR-12 03.27.14.157000 PM
12-APR-12 03.28.12.000000 PM
V5_RIO_5NCC
FILENAME
25-APR-12 25-APR-12
3 rows selected.
|
|
|
|
Goto Forum:
Current Time: Mon Mar 03 16:08:06 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
