Oracle OCI Load (Automatic Mode) Error

kcbland · Post by **kcbland** » Tue Feb 21, 2006 7:51 pm

Oracle 9 or 10? From your post you're using 10, but I'm sure 8 and 9 have this restriction. I somewhat stand corrected.

rleishman · Post by **rleishman** » Wed Feb 22, 2006 1:40 am

kcbland wrote:Oracle 9 or 10? From your post you're using 10, but I'm sure 8 and 9 have this restriction

My test case was on 10.1, that's all I have at the moment.

My last Oracle project was 8.1.7, where Direct Path Load over Oracle Net also worked. Prior to that I used it on 7.3. In my experiece DBAs jealously guard OS level access to their servers; or perhaps they just guard it from me

My only experience with local DP loads is in Dev and Support, not live Prod.

I checked the Oracle Utilities Manual v9,8, and 7 to make sure I wasn't on something. There is no mention of such a restriction.

Having said all that, since I cannot provide a test case pre-10, I would be happy to be proved wrong with a counter example showing local accepting and remote failing/ignoring the DIRECT option.

kcbland · Post by **kcbland** » Wed Feb 22, 2006 10:32 am

I'm having a head scratching moment because we've had to code around this "limitation" under 8i and 9i databases. I searched the 9i manuals to see where this was discussed, so I'd be very surprised to learn a lot of different folks at different customer sites all had the same mistake.

I'd love to be wrong.

KPReddy · Post by **KPReddy** » Fri Feb 24, 2006 1:28 pm

Thanks Ross.
when i mentioned direct=true in the sqlldr command,its failing.
If i remove this statement, sqlldr is using conventional mode to insert data.how can i make sqlldr use direct mode without specifying direct=true.

Iam keen to use Oracle OCI Load stage in automatic mode.
I will use this to load an empty table, so there should not be any issues like indexes etc.

Error still continues, please help me resolve the problem.

Thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 24, 2006 3:16 pm

"It's failing" doesn't help us to help you. What error message(s) get produced?

KPReddy · Post by **KPReddy** » Fri Feb 24, 2006 3:27 pm

Since the error message was there in my first post itself i didn't mention it.
sorry for that.This is the error message i get.

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

Thanks

rleishman · Post by **rleishman** » Fri Feb 24, 2006 9:18 pm

Now I'm really confused. I thought the problem was with OCI Load Automatic mode. Is it also happening with sqlldr DIRECT=TRUE?

Can you confirm the following:
1. Oracle OCI Stage (non-Load) - OK
2. Oracle OCI Load (Automatic) - Error
3. sqlldr Conventional Mode - OK
4. sqlldr DIRECT=TRUE - Error

Is that right? If so, then there is clearly something amiss with Direct Path on your database.

Try this (check the syntax, because I haven't):

Code: Select all

CREATE TABLE DPTEST (a varchar2(10));

Create a sqlldr control file dptest.ctl

Code: Select all

OPTIONS (DIRECT=TRUE)
LOAD DATA APPEND INTO TABLE dptest
(
  a char
)
BEGINDATA
TEST1
TEST2

Now run it:

Code: Select all

sqlldr userid/pwd dptest.ctl

If you still get the error, the error is clearly Oracle based.

Try Getting the DBA to give you SELECT ANY DICTIONARY privs. If that doesn't work, try SELECT ANY TABLE.

If one of those works, then you are missing privs on something that is required for DP loads. You can probably find it using the Oracle Trace I mentioned in a previous post.

If neither of these works, then there is a more sinister problem with the database. Report it to the DBA - it's not your problem to fix.

KPReddy · Post by **KPReddy** » Tue Feb 28, 2006 12:25 pm

Hi Ross,
Iam able to use sqlldr in DIRECT MODE when invoked on the local server. But unable to use DIRECT mode when trying to load data from remote server.

I confirm the following.
1. Oracle OCI Stage (non-Load) - OK
2. Oracle OCI Load (Automatic) - Error
3. sqlldr Conventional Mode - OK
4. sqlldr DIRECT=TRUE - Error

As per Kenneth, we can't use sqlldr direct mode on remote oracle server.
even i have tried this, but did not work.
But Ross showed that he has used it.

So guys, please confirm whether we can use sqlldr direct mode on remote oracle server.if so, is it only on Oracle 10g.
If this is confirmed, my error will be resolved.I get same error if i use sqlldr manually from remote machine or through DS.
Since DS Oracle OCI Load documentation says we can use DIRECT mode on remote oracle server, i think it should work in my case.

Please let me know your ideas.

Thanks

rleishman · Post by **rleishman** » Tue Feb 28, 2006 4:05 pm

This certainly add weight to Ken's assertion doesn't it? I'm starting to think someone's been slipping LSD in my Corn Flakes.

I really, really, wish I had a 9i database now so I could put myself out of my misery.

There are still two threads of hope for me:

1.It seems a very odd error to get for non-supported functionality. It returned an Oracle (ORA-) error, not a SQL Loader error, which means that it made it past the client level validation. Looking at the SQL*Loader chapter of the Oracle Error manual, there are tons of validation conditions relating to Direct Mode. If Oracle wanted to restrict direct-path load over Oracle Net, then surely there would an error better than NO DATA FOUND.

2. There is no mention of direct path load in the 10.1 New Features Guide.

On these two slim filaments of hope, I'm going to stick to my guns and say that it is possible.

Did you try any of the suggestions in my last post?

KPReddy · Post by **KPReddy** » Wed Mar 01, 2006 2:13 pm

Hi,
After searching for a while, i found the following restriction on sqlloader direct mode.

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 higher 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.

This might be the reason for error in my case as my source client is lower version than the target DB.
Guess DS is using sqlldr under the covers and so iam getting the same error in DS.

Thanks alot guys, especially Ross.

rleishman · Post by **rleishman** » Wed Mar 01, 2006 3:45 pm

There is no source "database", it is just a source "client".

For example, say your target is an 8.1.7 database, and you are trying to load from a machine that has only an 8.1.5 database installed. All you need to do is a minimal installation of 8.1.7 on the source machine.

You do not need to upgrade the database on your source machine. Multiple versions of the Oracle client (and even an RDMBS) can happily co-exist on the same box.

1. Get the DBA to install the appropriate Oracle version on your source machine. I don't think sqlldr is included in the simple client install, but the DBA should know how to do it.
2. Get the DBA to add the TNS name of your target database along with the new Oracle Home directory in the /etc/oratab file
3. Set your $ORACLE_HOME environment variable to point to the new installation, and $ORACLE_SID to the TNS name of the target database.
4. Run . $ORACLE_HOME/bin/oraenv (don't forget the leading ".")
5. You're ready to go. Try SQL*Plus first; it should tell you the client version as you connect. Compare it to the client version shown when you use sqlplus locally on the target database.
6. Now try sqlldr across Oracle Net.
7. If all this works, replace the $ORACLE_SID and $ORACLE_HOME variables in your $DSHOME/dsenv file. Now try OCI Load in Automatic mode.

KPReddy · Post by **KPReddy** » Thu Mar 02, 2006 2:08 pm

Thanks Ross.
Seems iam getting close to solution.
Only problem is if i install my target oracle version client on my source.
1. How can i use sql loader direct mode for both DB on source machine and target DB.

i.e I have 2 client versions on source machine.How can i use those 2 versions to use sql loader direct mode against 2 corresponding DB's.
(I may have to use sqll loader against both DB on source and target DB which are of different versions).
How can i achieve that?

Thanks in advance.

rleishman · Post by **rleishman** » Thu Mar 02, 2006 3:49 pm

DataStage can use only one Oracle client, so work out which database you want to use OCI Load (Automatic) on, and set the $ORACLE_HOME of that DB version in your $DSHOME/dsenv file. You will not be able to perform OCI Load (Automatic) on the other database.

If you don't want to use OCI Load at all, then this is immaterial.

In order to use sqlldr manually, you will have to call a shell script. You will have to pass the name of the database to this script, so all you have to do is:
1. Set the environment variable $ORACLE_SID to the database name at the top of the script.
2. Get the DBA to create an entry for each database in the /etc/oratab file. Each entry must list the correct $ORACLE_HOME.
3. Run . $ORACLE_HOME/bin/oraenv in your script. It doesn't matter which $ORACLE_HOME you use, oraenv will read /etc/oratab and re-point $ORACLE_HOME to the correct client.

In this way, the shell script dynamically selects the correct Oracle client.

KPReddy · Post by **KPReddy** » Sun Mar 05, 2006 2:21 pm

Thanks Ross.
We want to use DS oracle oci load stage, so planning to take necessary steps.

Thanks alot guys.

palaniappan · Post by **palaniappan** » Mon Mar 06, 2006 7:25 am

Though this is not an answer or a solution, just wanted to highlight that the support to SQL Loader is to be discontinued. So do you really want to use SQL Loader? External Tables would be a better option. In that way the files would remain where they are and a select * from <ext table> would be as simple and quick as the normal OCI load. Even you can have external tables defined on your bad and discard files.

Thanks, Pal.