ORAOCIBL problem

knowledge · Post by **knowledge** » Wed Jun 27, 2007 2:24 pm

Hi ,
I m using datastage 7.5 server edition,
I have a job with simple load logic : oci --transformer--oci which is working fine , for performance tunning I replaced o/p stage with ORAOCIBL (just to check), but I am recieving the following error message

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

I tried to search in the forum but did not get required solution ,
I checked all parameters like login info and table name , its correct .

Do I need to configure anything explicitly ,
I m using stage in automatioc mode ,

please suggest ,
thanks .

chulett · Post by **chulett** » Wed Jun 27, 2007 2:59 pm

Double-check the values you've supplied for Service Name, User Name, Password, Table Name and Schema Name. Something there isn't correct or you wouldn't have received the error that you did.

knowledge · Post by **knowledge** » Thu Jun 28, 2007 6:30 am

[quote="chulett"]Double-check the values you've supplied for Service Name, User Name, Password, Table Name and Schema Name. Something there isn't correct or you wouldn't have received the error that you did.[/quote]

Hi Craig ,

I checked it couple of times by changing parameters and even table name , but

same error ,
I m not able to find out what is the cause ,
Please suggest some thing

thanks

chulett · Post by **chulett** » Thu Jun 28, 2007 6:36 am

Sorry, but there really isn't anything else to suggest. Except... hmmm... talk to your DBA. Explain you've just switched from doing direct inserts into that table to bulk loading it. Have them make sure you've got the proper grants you'd need for that.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 28, 2007 7:00 am

Preserve the control script. Examine it to see whether the correct table (and owner name, if relevant) is being specified. Check that this table does actually exist in the schema to which sqlldr is connected.

knowledge · Post by **knowledge** » Thu Jun 28, 2007 7:13 am

Thanks for the replay ,

I will talk to my DBA, but same table with same parameter I could load by OCI stage but not with bulk loaded ,

Ray where do I have to check for SQL loader ?(My DBA is new cry: )
Its loads in the same schema as OCI stage load then why only problem with Bulk loader.

I m not sure if this is a valid question ?

Thanks in advance .

chulett · Post by **chulett** » Thu Jun 28, 2007 7:19 am

knowledge wrote:I will talk to my DBA, but same table with same parameter I could load by OCI stage but not with bulk loaded

Requires different grants, from what I recall.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 28, 2007 7:20 am

You specify (in a property in the ORAOCIBL stage) the location where the control script will be created. Click Property Help to learn the default location. Check there.

knowledge · Post by **knowledge** » Thu Jun 28, 2007 7:55 am

I m using stage in automatioc mode ,

Thanks Craig , I will talk to my dba ,

Ray I m using Automatic mode , so no need to specify control file property rt ?
Thanks.

chulett · Post by **chulett** » Thu Jun 28, 2007 8:20 am

Right, some of those fields are only used in Manual mode. Property Help explains all that.

crouse · Post by **crouse** » Thu Jun 28, 2007 8:24 am

Also, cut and paste here all your values from the OCIBL stage AND the OCI stage. I agree with Ray, because I just tested it here, that you have something typed wrong relative to Service Name or User Name or Password or Table Name or Schema Name.

knowledge · Post by **knowledge** » Thu Jun 28, 2007 8:40 am

[quote="crouse"]Also, cut and paste here all your values from the OCIBL stage AND the OCI stage. I agree with Ray, because I just tested it here, that you have something typed wrong relative to Service Name or User Name or Password or Table Name or Schema Name.[/quote]

I wish it is true ,

here is the list from both jobs ,
I m surprised too why it is not working .........

starting Job Ard_Accidents.
Oradev_sid = oradev
Oradev_Userid = crash_etl_dev1
Oradev_Password = crashetl
Ard_sid = dot1
Ard_Userid = ardnew_read
Ard_Password = read

Starting Job Ard_Accidents_ORABULK.
Oradev_sid = oradev
Oradev_Userid = crash_etl_dev1
Oradev_Password = crashetl
Ard_sid = dot1
Ard_Userid = ardnew_read
Ard_Password = read

Thanks.

crouse · Post by **crouse** » Thu Jun 28, 2007 9:27 am

You pasted the job parameters and their values.

Both the OCI and OCIBL allow only one id and password and sid and owner/schema, so which set of parameters are you actually using in each stage?

Better yet, don't use job parameters for now while you are testing things out, use the exact values. Hardcode the values in OCI and when it works, hardcode the same values in the OCIBL. After it all works, go back to job parameters.

chulett · Post by **chulett** » Thu Jun 28, 2007 9:33 am

I don't see a value for 'Schema' in the ORAOCIBL stage listed.

knowledge · Post by **knowledge** » Thu Jun 28, 2007 9:39 am

[quote="knowledge"][quote="crouse"]Also, cut and paste here all your values from the OCIBL stage AND the OCI stage. I agree with Ray, because I just tested it here, that you have something typed wrong relative to Service Name or User Name or Password or Table Name or Schema Name.[/quote]

I wish it is true ,

here is the list from both jobs ,
I m surprised too why it is not working .........

starting Job Ard_Accidents.
Oradev_sid = oradev
Oradev_Userid = crash_etl_dev1
Oradev_Password = crashetl
Ard_sid = dot1
Ard_Userid = ardnew_read
Ard_Password = read

Starting Job Ard_Accidents_ORABULK.
Oradev_sid = oradev
Oradev_Userid = crash_etl_dev1
Oradev_Password = crashetl
Ard_sid = dot1
Ard_Userid = ardnew_read
Ard_Password = read

Thanks.[/quote]

Hey all ,

Thanks for ur help and replay ,
I was using automatic mode but have to use manual mode since my database and datastage is residing on diff server ,

I changed the option to manual and created all parameter like ctl and dat file etc

and bingo.........

its working fine ,

Thanks to all genius .........

DSXchange

ORAOCIBL problem

ORAOCIBL problem

Hi

Re: ORAOCIBL problem