Oracle OCI Load (Automatic Mode) Error

[KPReddy]

Hi,
Iam facing problem using Datastage Oracle OCI Load stage to load data into remote oracle database in Automatic mode.
I get the following error.

Internal Error: Unable to initialize the Direct Path Context
ORA-00942: table or view does not exist

But the table exists, i could load data using sqlloader and oracle OCI (Not Bulk stage) into the same table.

My scenario also meets the requirement that DS and Oracle must be running on same type of OS. (Same version also in my case).

Any help is appreciated.

[chulett]

You are probably not connecting as the same user that you used for sqlldr... and the user that you *are* using doesn't 'own' the table in question. Do you specify the schema during the load or is that left empty?

[KPReddy]

Iam the owner of the table and i specified the schema, username and password correctly.I tried many times, but invain.

[KPReddy]

Hi,
My problem with the Oracle OCI Load Automatic mode continues.
Please advise me for the below situation.

Since my DS and Oracle server are on different machines.
which one of the below steps is suggested.

1) Copy sql loader Control and data files to remote oracle server, invoke sql loader there.
2) Use Oracle OCI Load Automatic mode to load data remotely.

Typically say 100000 to 150000 rows per load (say daily).
Please consider things like control over task, maintainability, performance etc.

Thanks.

[rleishman]

Persist with OCI Load. It works - I use it for all my high volume loads.

OK, so you own the table. Clearly the userid, password and database name are correct because you are getting into the database in order to get the 942 error.

What's left? I can think of 3 things:

1 - You have mis-spelled the table name. Cut and paste the table_name from the OCI stage into SQL*Plus and DESC {table_name}

2 - The table is actually a SYNONYM, despite your assertions to the contrary. Try SELECT * FROM USER_TABLES WHERE TABLE_NAME = 'your_table_name'

3 - The table has been created with a lower- or mixed-case table name. If this is the case, enclose the table anme in double-quotes in the OCI Load stage. I have some mixed-case table names and this works.

[KPReddy]

Thank you Ross.
My problem with Oracle OCI Load stage continues.

I checked all the steps given by you,in all cases i got the result confirming the table exists and it is table and not a synonym.
My table name is all upper case. eventhough i tried by putting double quotes for table name in DS.

Is it worth all this effort using this stage to load data on remote Oracle database for volumes of data around 1 million to 10 million.I just want to be sure as in many cases i found sql loader to be way fast than others.

Any help/suggestions are appreciated.

[rleishman]

There are 3 performance considerations I can think of for SQL Loader Vs. OCI Load.

1. SQL*Loader is naturally a little faster than OCI load, but the reasons for this are unclear as they use much the same load mechanism. On a remote database, OCI must traverse the network for each buffer of rows, but with SQL*Loader you can FTP the entire file and load locally (this is the best way of using SQL*Loader).

Depending on your network, SQL*Loader may be appreciably faster.

2. OCI Load loads data as it is transformed by DataStage. If you put an IPC stage immediately before the OCI Load stage, then - depending on the complexity of the transformation - it might not even slow down your transformation when compared to writing to a sequential file.

To use SQL Loader, you generally have to wait for the entire transformation to complete before you load. This can be mitigated with Named Pipes on Unix, but it adds complexity.

So, depending on a number of factors here, OCI Load may be faster for the end-to-end job of transforming and loading.

3. If you have Foreign Key constraints enabled on the load table, OCI will perform conventional path load, but SQL*Loader can still perform direct path load. In this case, SQL*Loader will be orders of magnitude faster.

---------------------

I still think its worth finding out what the problem is with OCI Load. I see from your initial post that the regular OCI stage works - clearly the table exists. It may be that the OCI performs some internal SQL against the data dictionary, and the 942 error has nothing to do with your table.

One way to tell is to trace the load process. This is a bit tricky, because OCI Load does not have a before-sql that enables you to turn tracing on.

You could ask the DBA to restart the database with SQL TRACE on for all sessions - he/she should know how to do this. Run your job, and then get the DBA to restart the DB again with SQL TRACE off. Find the trace file and run it through TK*Prof - the last SQL listed should be where the error occurred. Instructions for tkprof are in the Oracle Utilities manual.

[anujgarg]

Hi,

Is this table is accessible while you import table definition through import table definitions through plugin.If it is not accessible then it may not have entry in tnsnames.ora file , put the tnsentry in that file.

[KPReddy]

Thank you Ross and anujgarg.
Restarting the DB with sql trace ON might not be possible.
Have to see what my DBA has to say.
Iam able to import the table definition.
I don't know what more troubleshooting to do with this stage.
Thank you once again.

[kcbland]

Your issue is simple. The load files have to physically be on the same server as the database. Also, you must run sqlldr on that server, not invoked remotely. Therefore, to load your files you must ftp them to the database server and then rsh execute sqlldr.

In order to use DIRECT path, you must understand all of the requirements. If you're having this much trouble, you're in for a lot more difficulty when you realize what happens to indexes, constraints, etc in the event of failed loads. I recommend you research these issues and fully appreciate what you are attempting.

[KPReddy]

Thanks Kenneth,
To use sqlldr to load data on remote DB load files need not be on DB server. I have invoked sqlldr from DS and loaded data on remote DB.
(All load files are on DS server).
But iam more interested to FTP files and invoke sqlldr locally on DB server.(Just trying to figure out synchronisation between DS and sqlldr in this case)
Even iam bit hesitant to use BUL Load because of the issues that may arise.so probably i will go with sqlldr only.

[KPReddy]

I forgot to add one thing.I was considering BULK Load as i will be loading data onto intermediate table, which will be empty. so fail loads will not be much of an issue.

[kcbland]

Thanks Kenneth,
To use sqlldr to load data on remote DB load files need not be on DB server. I have invoked sqlldr from DS and loaded data on remote DB.

Only for conventional load does this work.
DIRECT path requires load files to be co-resident with the database instance.

[KPReddy]

Thanks Kenneth,
Yes you are right, for direct mode to work in sqlldr load files should be on DB server.Since it was mentioned in ORACLE OCI Load stage documentation that DS Server and DB server can be on different machines (But same type of OS), i was trying to make this stage work.
(In my case they are on same OS and same version also).
Guess the restriction of sqlldr is also restricting this stage.

Thanks alot guys.you have been very helpful.

[rleishman]

The load files have to physically be on the same server as the database. Also, you must run sqlldr on that server, not invoked remotely.

Not true. SQLLDR may run in Direct Path mode across Oracle Net using a file resident on the same (remote) server as where sqlldr is initiated. It is slower across Oracle Net, but not as slow as conventional path.

The following is an example I ran from our server lxdb0035v, loading into a database on server lxdb0029v.

The OS command - note the use of Oracle Net

Code: Select all

$ sqlldr cdmdev@cdmdev EF_EPSV_EXPNS.ctl
Password:

SQL*Loader: Release 10.1.0.4.0 - Production on Wed Feb 22 09:07:06 2006

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


Load completed - logical record count 4.

And the log file (parts of it, at least...)

Code: Select all

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

...

Referential Integrity Constraint/Trigger Information:
NULL, UNIQUE, and PRIMARY KEY constraints are unaffected.

Constraint EF_EPSV_EXPNS.SYS_C0040921 was disabled and novalidated before the load.
Constraint EF_EPSV_EXPNS.SYS_C0040922 was disabled and novalidated before the load.
Constraint EF_EPSV_EXPNS.SYS_C0040923 was disabled and novalidated before the load.
Constraint EF_EPSV_EXPNS.SYS_C0040924 was disabled and novalidated before the load.
Constraint EF_EPSV_EXPNS.SYS_C0040925 was disabled and novalidated before the load.
Constraint EF_EPSV_EXPNS.SYS_C0040926 was disabled and novalidated before the load.
Constraint EF_EPSV_EXPNS.SYS_C0040927 was disabled and novalidated before the load.
Constraint EF_EPSV_EXPNS.SYS_C0040928 was disabled and novalidated before the load.
Constraint EF_EPSV_EXPNS.SYS_C0040929 was disabled and novalidated before the load.
Constraint EF_EPSV_EXPNS.SYS_C0040930 was disabled and novalidated before the load.
The following index(es) on table EF_EPSV_EXPNS were processed:
index CDMDEV.EF_EPSVE_PK partition POS_CURR loaded successfully with 4 keys
EF_EPSV_EXPNS.SYS_C0040921 was re-enabled.
EF_EPSV_EXPNS.SYS_C0040922 was re-enabled.
EF_EPSV_EXPNS.SYS_C0040923 was re-enabled.
EF_EPSV_EXPNS.SYS_C0040924 was re-enabled.
EF_EPSV_EXPNS.SYS_C0040925 was re-enabled.
EF_EPSV_EXPNS.SYS_C0040926 was re-enabled.
EF_EPSV_EXPNS.SYS_C0040927 was re-enabled.
EF_EPSV_EXPNS.SYS_C0040928 was re-enabled.
EF_EPSV_EXPNS.SYS_C0040929 was re-enabled.
EF_EPSV_EXPNS.SYS_C0040930 was re-enabled.

Table EF_EPSV_EXPNS has no constraint exception table.
Constraint EF_EPSV_EXPNS.SYS_C0040921 was validated
Constraint EF_EPSV_EXPNS.SYS_C0040922 was validated
Constraint EF_EPSV_EXPNS.SYS_C0040923 was validated
Constraint EF_EPSV_EXPNS.SYS_C0040924 was validated
Constraint EF_EPSV_EXPNS.SYS_C0040925 was validated
Constraint EF_EPSV_EXPNS.SYS_C0040926 was validated
Constraint EF_EPSV_EXPNS.SYS_C0040927 was validated
Constraint EF_EPSV_EXPNS.SYS_C0040928 was validated
Constraint EF_EPSV_EXPNS.SYS_C0040929 was validated
Constraint EF_EPSV_EXPNS.SYS_C0040930 was validated

Table EF_EPSV_EXPNS:
  4 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.

  Partition POS_CURR: 4 Rows loaded.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576