Home » RDBMS Server » Server Utilities » Sql*Loader Error - While using direct path load (Oracle 10g, HP-Ux)
| Sql*Loader Error - While using direct path load [message #324388] |
Mon, 02 June 2008 04:53  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Hi,
I am wondering whether anybody else in this forum have faced a similar sort of problem while using direct path load. Please find the steps to re-create the problem.
create table temp_data
(
col1 varchar2(15),
col2 varchar2(15),
period date,
value1 number,
value2 number,
value3 number,
value4 number,
value5 number,
value6 number,
value7 number,
value8 number,
value9 number,
value10 number
)
nologging;
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
SQL> select count(*) from temp_data;
COUNT(*)
----------
0
$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
-- Conventional path Load
$ sqlldr rsubramanian control=test.ctl
Password:
SQL*Loader: Release 10.2.0.2.0 - Production on Mon Jun 2 10:12:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 10
$
SQL> select count(*) from temp_data;
COUNT(*)
----------
10
SQL> !wc -l test.dat
10 test.dat
SQL> truncate table temp_data;
Table truncated.
-- Direct path Load
$ sqlldr rsubramanian control=test.ctl
Password:
SQL*Loader: Release 10.2.0.2.0 - Production on Mon Jun 2 10:15:30 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-702: Internal error - Unknown column for OCI_ATTR_COL_COUNT
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
And the data and control file is as follows
1,01NOV2007:00:00:00,46,1.704,145.59,66,0.049,151.49,0,0.000,0.00,287,1
2,01NOV2007:00:00:00,2,0.002,0.04,0,0.000,0.00,0,0.000,0.00,999,2
3,01NOV2007:00:00:00,2,0.004,0.07,0,0.000,0.00,0,0.000,0.00,999,3
4,01NOV2007:00:00:00,2,0.018,0.32,0,0.000,0.00,0,0.000,0.00,999,4
5,01NOV2007:00:00:00,3,0.006,0.09,0,0.000,0.00,0,0.000,0.00,999,5
6,01NOV2007:00:00:00,1,0.007,0.12,0,0.000,0.00,0,0.000,0.00,999,6
7,01NOV2007:00:00:00,1,0.001,0.02,0,0.000,0.00,0,0.000,0.00,999,7
8,01NOV2007:00:00:00,1,0.007,0.13,0,0.000,0.00,0,0.000,0.00,999,8
9,01NOV2007:00:00:00,1,0.006,0.10,0,0.000,0.00,0,0.000,0.00,999,9
10,01NOV2007:00:00:00,1,0.002,0.03,0,0.000,0.00,0,0.000,0.00,999,10
load data
infile 'test.dat'
into table
temp_data
fields terminated by ','
trailing nullcols
(
col1,
period date "ddmonyyyy" "substr(:period,1,9)"
value1,
value2,
value3,
value4,
value5,
value6,
value7,
value8,
value9,
value10,
col2
)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_field_list.htm#sthref1258
In the above url if you search for "Use Sql String with a date Mask" it explains internally how the date formatting gets translated. I am not able to find any limitations mentioned in the documentation that we cannot use such a kind of formatting while using direct path load.
Any thoughts on this much appreciated. I do know that it is an internal error and I have to contact oracle support. We are trying to raise this issue with Oracle. But I thought for what it is worth I will check with the forum members if by any chance any of you guys have faced a similar kind of issue in your previous work place. I have already solved this problem by using the right date format.
Once again thank you for your time in reading this long story.
Regards
Raj
P.S : I am not trying to do a direct path load for a file containing 10 records. This table will have 200M hence I opted for the direct path. I thought using the word URGENT but later on changed on my mind 
[Updated on: Mon, 02 June 2008 04:54] Report message to a moderator |
|
|
|
|
|
| Re: Sql*Loader Error - While using direct path load [message #324419 is a reply to message #324411] |
Mon, 02 June 2008 07:25  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Frank,
Thanks for the reply. Yes it did worked if I changed the date format in the control file to the one you have mentioned. But for some reason I decided initially to trim it, don't know why. Also, I am not able to find any documented restriction that I cannot use functions in the direct path mode. If you could find it, could you please let me know.
Regards
Raj
|
|
|
|
|
|
| Re: Sql*Loader Error - While using direct path load [message #324426 is a reply to message #324421] |
Mon, 02 June 2008 07:56 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Frank,
I am sorry, I beg to differ on your opinion about direct path load with respective to using functions or am I missing something here. Please find my observation. I have changed the data type of column from date to varchar2(100)
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
SQL> drop table temp_data purge;
Table dropped.
SQL> create table temp_data
2 (
col1 varchar2(15),
3 4 col2 varchar2(15),
5 period VARCHAR2(100),
6 value1 number,
7 value2 number,
8 value3 number,
9 value4 number,
10 value5 number,
11 value6 number,
12 value7 number,
13 value8 number,
14 value9 number,
15 value10 number
16 )
17 nologging;
Table created.
$cat test.ctl
options (direct=true)
load data
infile 'test.dat'
into table
temp_data
fields terminated by ','
trailing nullcols
(
col1,
period "substr(:period,1,9)" ,
value1,
value2,
value3,
value4,
value5,
value6,
value7,
value8,
value9,
value10,
col2
)
$ sqlldr <user_name> control=test.ctl
Password:
SQL*Loader: Release 10.2.0.2.0 - Production on Mon Jun 2 13:50:13 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 10.
$ echo $?
0
$cat test.log
SQL*Loader: Release 10.2.0.2.0 - Production on Mon Jun 2 13:50:13 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: test.ctl
Data File: test.dat
Bad File: test.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct *************************
Table TEMP_DATA, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
col1 FIRST * , CHARACTER
PERIOD NEXT * , CHARACTER
SQL string for column : "substr(:period,1,9)"
VALUE1 NEXT * , CHARACTER
VALUE2 NEXT * , CHARACTER
VALUE3 NEXT * , CHARACTER
VALUE4 NEXT * , CHARACTER
VALUE5 NEXT * , CHARACTER
VALUE6 NEXT * , CHARACTER
VALUE7 NEXT * , CHARACTER
VALUE8 NEXT * , CHARACTER
VALUE9 NEXT * , CHARACTER
VALUE10 NEXT * , CHARACTER
col2 NEXT * , CHARACTER
Table TEMP_DATA:
10 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.
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 10
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Mon Jun 02 13:50:13 2008
Run ended on Mon Jun 02 13:50:16 2008
Elapsed time was: 00:00:02.75
CPU time was: 00:00:00.07
SQL> select period from temp_data;
PERIOD
-----------
01NOV2007
01NOV2007
01NOV2007
01NOV2007
01NOV2007
01NOV2007
01NOV2007
01NOV2007
01NOV2007
01NOV2007
10 rows selected.
When I tried to do the same formatting on a date column I am getting the error I have mentioned before.
Regards
Raj
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri May 17 04:51:38 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
