My Oracle Support Banner

ORA-942 When Using SQL*Loader Direct Path (Doc ID 444663.1)

Last updated on MARCH 11, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 10.2.0.3 [Release 9.2 to 10.2]
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

Using 10g client to load data using SQLLOADER to oracle 9.2 database fails with the below error with direct=y,

SQL*Loader-951: Error calling once/load initialization
ORA-00942: table or view does not exist

EXAMPLE:
-------
On Oracle 9i Database:

1].As user sys create database on logon trigger:

CREATE OR REPLACE TRIGGER on_logon
after logon ON database
BEGIN
execute immediate 'alter session set current_schema ='||'<SCHEMA_NAME>';
END;

2].create table t in <SCHEMA_NAME> schema:

create table t( A number);

3].sample x.ctl file:

load data
infile *
insert into table t
(A )
begindata
1
2
3

4]. Grant insert privilege to <USER> on table t by user associated with <SCHEMA_NAME>
SQL> connect <LOGIN>/<PASSWORD>
Connected.
SQL> grant insert on t to <USER>;

From Oracle 10g:

sqlldr userid=<LOGIN>/<PASSWORD> control=<CONTROL_FILE_NAME>.ctl direct=y

SQL*Loader: Release 10.2.0.1.0 - Production on Wed Jul 18 21:22:04 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Loader-951: Error calling once/load initialization
ORA-00942: table or view does not exist

But Conventional Path succeeds:

sqlldr userid=<LOGIN>/<PASSWORD> control=<CONTROL_FILE_NAME>.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Wed Jul 18 21:22:20 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.