OracleDirectPathLoader - Error: Table or view not exists

[rdeutsch]

Hi

I work on BDS 2007/Delphi and Orcale 9.2.0.6.0 and DOA Version 4.1.1

When doing a Prepare on a OracleDirectPathLoader then i get always the error message:

Directpath
ORA-00942: Table or view not exists

But the table is there. I can check this with a select-statement.

When doing the same with Delphi7 / DOA 4.0.3 and on the same DB all works fine.

What shall i do?

Regards
Robert

 

[Marco Kalter]

Did you include the owner in the TableName property?

 

[rdeutsch]

Hi Marco

Yes - i tried to include the owner but with no better result.
Now we made a trace on the DB and saw that there is firing to following sql-statement:
"SELECT DECODE(COUNT(*), 0, 0, 1)
FROM SYS.LOADER_NESTED_VARRAYS
WHERE TABLE_NAME = :tname AND TABLE_OWNER = wner".
But the table "SYS.LOADER_NESTED_VARRAYS" does not exist. I think that is the reason for the errormessage "ORA-00942: Table or view not exists".

Have you an idea?

Regards
Robert

 

[Marco Kalter]

I found the note 458010.1 Oracle MetaLink (see below). Could this apply?

Subject: Direct Path Load Using OCI or SQL*Loader Causes Unexpected ORA-00942 with Client/Server Mismatch
Doc ID: Note:458010.1
Type: PROBLEM
Last Revision Date: 12-FEB-2008
Status: MODERATED

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.3
This problem can occur on any platform.

Symptoms

OCI Direct Path fails with an ORA-00942 when the client is 10g and the database is 9.2.
This functionality is used by Oracle SQL*Loader utility, and the same issue applies.

Full Error:
ORA-942: table or view does not exist

Cause

The issue is due to the addition of the 10g data dictionary views
SYS.loader_nested_varrays & SYS.loader_skip_unusble_indexes.

Non-bug Bug 4030896 "10G OCI DIRECT PATH TO 9.2 DB THROWS ORA-942"
discusses the issue and optional work-around.

This is not a bug. Like export, direct path loader uses views in the database and
these often change between versions. In general, a different client version was not
intended to interact with a different version of the database. Refer to documentation
[restrictions] for the utilities, including:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_modes.htm#SUTIL009
under
Restrictions on Using Direct Path Loads
...
"Beginning with Oracle9i, you can perform a SQL*Loader direct path load when the
client and server are different versions. However, both versions must be at least
release 9.0.1 and the client version must be the same as or lower than the server
version. For example, you can perform a direct path load from a release 9.0.1
database into a release 9.2 database. However, you cannot use direct path load
to load data from a release 10.0.0 database into a release 9.2 database."

Solution

Either use a client version which is the same or older than the database,
-or-
ensure you have the views loaded for your client version, not the database version.

This can be accomplished by running catldr.sql from a 10g installation into a 9.2.0.x database as SYS
which enables both SQL*Loader and the OCI direct path load to run successfully.

References

Bug 4030896 - 10G OCI DIRECT PATH TO 9.2 DB THROWS ORA-00942
Bug 5123569 - SQL*LOADER-951 WHEN RUNNING DIRECT=TRUE
Note 191538.1 - Restrictions on Direct Path Loads Using SQL*Loader

Errors

ORA-942 "table or view does not exist"

 

[rdeutsch]

Hi Marco

Thanks a lot.

Yes that was it. On my maschine is client version 10.2.0.3 installed and our DB has version 9.2.

Doc ID: Note:458010.1:
"Beginning with Oracle9i, you can perform a SQL*Loader direct path load when the
client and server are different versions. However, both versions must be at least
release 9.0.1 and the client version must be the same as or lower than the server
version."

Regards Robert