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 Go to next message
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 Cool

[Updated on: Mon, 02 June 2008 04:54]

Report message to a moderator

Re: Sql*Loader Error - While using direct path load [message #324411 is a reply to message #324388] Mon, 02 June 2008 07:01 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
My guess is that it has noting to do with the date format, but the "substr" is a problem (cannot have function calls with direct=yes).

I bet it will work if you change the control file to:
 period date "ddmonyyyy:hh24:mi:ss",

Re: Sql*Loader Error - While using direct path load [message #324419 is a reply to message #324411] Mon, 02 June 2008 07:25 Go to previous messageGo to next message
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 #324421 is a reply to message #324419] Mon, 02 June 2008 07:33 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
See "Can one modify data as the database gets loaded?" and "What is the difference between the conventional and direct path loader?" in the SQL*Loader FAQ.

Best regards.

Frank
Re: Sql*Loader Error - While using direct path load [message #324426 is a reply to message #324421] Mon, 02 June 2008 07:56 Go to previous messageGo to next message
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
Re: Sql*Loader Error - While using direct path load [message #324513 is a reply to message #324426] Mon, 02 June 2008 22:12 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
I would guess that Oracle is gradually removing the restrictions as they move to higher versions. However, I have no documentation to confirm this.
Re: Sql*Loader Error - While using direct path load [message #324534 is a reply to message #324513] Tue, 03 June 2008 00:11 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Metalink note 230120.1, "USING SQL STRINGS IN DIRECT PATH LOAD"

Regards
Michel
Previous Topic: DataPump Import
Next Topic: Export Buffer size
Goto Forum:
  


Current Time: Wed Dec 07 14:54:39 CST 2016

Total time taken to generate the page: 0.08904 seconds