Oracle-L: Problems with 9.2.0.4

From: Stefan Jahnke <Stefan.Jahnke_at_bov.de>
Date: Tue, 07 Oct 2003 00:59:25 -0800
Message-ID: <F001.005D2329.20031007005925@fatcity.com>

Hi list

I'm encountering a problem where I think it might be related to patch set 9.2.0.4 (since it worked on 9.2.0.3):

I load data with SQL*LOADER using the following control file:

LOAD DATA
INFILE 'c:\oracle\admin\<SID>\daten\neuessen\\import\mydata.txt' BADFILE 'c:\oracle\admin\<SID>\daten\neuessen\ctl\mydata.bad' DISCARDFILE 'c:\oracle\admin\<SID>\daten\neuessen\ctl\mydata.dsc' APPEND
INTO TABLE schizo.stagingtable
FIELDS TERMINATED BY ";"
(
id SEQUENCE(MAX),

  fileid   EXPRESSION "831",
  trial    INTEGER EXTERNAL,
  response INTEGER EXTERNAL,
  type     INTEGER EXTERNAL,
  correct  INTEGER EXTERNAL,

latency DECIMAL EXTERNAL
)

The data looks like this (excerpt):

1;0;111;-1;0.000
2;0;111;-1;0.000
3;0;111;-1;0.000
4;4;112;0;0.655
5;0;111;-1;0.000
6;0;111;-1;0.000
7;0;111;-1;0.000
8;0;111;-1;0.000
9;4;113;0;0.476

10;0;111;-1;0.000

The SQL*LOADER is called like this:

HOST c:\oracle\ora9\bin\sqlldr userid=system/mypwd@<SID> control=c:\oracle\admin\<SID>\daten\neuessen\ctl\mydata.ctl log=c:\oracle\admin\<SID>\daten\neuessen\ctl\mydata.log

My log file looks like this:

SQL*Loader: Release 9.2.0.4.0 - Production on Tue Oct 7 09:49:46 2003

Control File:   c:\oracle\admin\<SID>\daten\neuessen\ctl\mydata.ctl
Data File:      c:\oracle\admin\<SID>\daten\neuessen\\import\mydata.txt
  Bad File:     c:\oracle\admin\<SID>\daten\neuessen\ctl\mydata.bad
  Discard File: c:\oracle\admin\<SID>\daten\neuessen\ctl\mydata.dsc

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table SCHIZO.STAGINGTABLE, loaded from every logical record. Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
ID                                                        SEQUENCE (MAX, 1)
FILEID                                                    EXPRESSION
    SQL string for column : "831"
TRIAL                               FIRST     *   ;       CHARACTER

RESPONSE                             NEXT     *   ;       CHARACTER

TYPE                                 NEXT     *   ;       CHARACTER

CORRECT                              NEXT     *   ;       CHARACTER

LATENCY                              NEXT     *   ;       CHARACTER

Record 1: Rejected - Error on table SCHIZO.STAGINGTABLE. ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist

...

Record 51: Rejected - Error on table SCHIZO.STAGINGTABLE.
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

Specify SKIP=51 when continuing the load.

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table SCHIZO.STAGINGTABLE:
0 Rows successfully loaded.
51 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 rejected:        51
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:       51
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Tue Oct 07 09:49:46 2003
Run ended on Tue Oct 07 09:49:47 2003

Elapsed time was:     00:00:01.40
CPU time was:         00:00:00.08




****************************************************************************

**********************************************

The funny thing is: If I run the loader directly as user "SCHIZO" or as "SYS" (O7_DICTIONARY_ACCESSIBILITY=TRUE), it works just fine. BTW: The problem is caused by the Expression, if I get rid of this, not loading any value into "FILEID", it works just fine.

Regards,
Stefan

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stefan Jahnke
  INET: Stefan.Jahnke_at_bov.de

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Oct 07 2003 - 03:59:25 CDT