Home » RDBMS Server » Server Utilities » sql loader error for direct path(3 Merged) (Release 10.2.0.5.0 sun solaris )
icon7.gif  sql loader error for direct path(3 Merged) [message #552403] Wed, 25 April 2012 09:38 Go to next message
guddu_12
Messages: 219
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 #552404 is a reply to message #552403] Wed, 25 April 2012 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
OK, so how can we assist you?

Does same behavior exist in supported version?
Re: sql loader error for direct path [message #552411 is a reply to message #552403] Wed, 25 April 2012 10:26 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hi ,

I want to load it using DIRECT LOAD, but i get 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.
Re: sql loader error for direct path [message #552412 is a reply to message #552411] Wed, 25 April 2012 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
I want to teach my goat to fly.

OK, so how can we assist you?

Does same behavior exist in supported version V11?
Re: sql loader error for direct path [message #552413 is a reply to message #552412] Wed, 25 April 2012 10:36 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Ok,

I am trying on same version with different setup of direct path. my concern is on the same verion of oracle and on the same environemtrn direct path gives me an error. please help me to resolve that error. what i have to change in the control file so that i will get rid of this error.
Re: sql loader error for direct path [message #552414 is a reply to message #552413] Wed, 25 April 2012 10:37 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
i am running this on 10g and it is for 10g not for 11g, i hope i have answerd your question.
Re: sql loader error for direct path [message #552416 is a reply to message #552414] Wed, 25 April 2012 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
file Service Request with MOS
Re: sql loader error for direct path [message #552417 is a reply to message #552416] Wed, 25 April 2012 10:52 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hi ,

I couldn't understand this answer "file Service Request with MOS". can you please write me in detail
Re: sql loader error for direct path [message #552419 is a reply to message #552417] Wed, 25 April 2012 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Go to https://support.oracle.com/CSP/ui/flash.html, connect and click on "Create SR".

Regards
Michel
Re: sql loader error for direct path [message #552423 is a reply to message #552419] Wed, 25 April 2012 11:48 Go to previous message
Barbara Boehmer
Messages: 8922
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.

Previous Topic: To_number sqlldr control file
Next Topic: cannt read the bad file from java code
Goto Forum:
  


Current Time: Tue Aug 11 23:17:53 CDT 2020