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

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1.0 to 10.2.0.3.0
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 ='||'PRATHEEK';
END;

2].create table t in Pratheek 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 Prath on table t by user Pratheek
SQL> connect Pratheek/Pratheek
Connected.
SQL> grant insert on t to prath;

From Oracle 10g:

C:\>sqlldr userid=prath/prath@pratheek control=C:\x1.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:

C:\>sqlldr userid=prath/prath@pratheek control=C:\x1.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

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms