DSXchange

Hi
i had a doubt while working with ORAOCI Plugin
My database veriosn is 9.2 oracle on windows platform.

i had imported a table into the repository while viewing the data on ocistage it is prompting an error
Message :
DSBrowser..Oracle_OCI_0: ORA-00942: table or view does not exist
DSBrowser..Oracle_OCI_0.DSLink1: DSP.Open GCI $DSP.Open error -100.

but i can able to connect to the table and see the data from sql prompt as well as i am able to connect through TOAD and can see the data

what might be the wrong that i had done ....?

expecting your advise from the praticipants, Thanks in advance

RK

Hi,
Possible causes:
1. The user you use via DataStage has no privilages on the table.
2. The DSN you supply connects to a different DB which doesn't have the table in it (even if only pointed to the wrong DB in the tnsnames.ora file).

can you describe the users you used in each case?
were you using the same connection string or DSN in all cases?

IHTH,

Hi,

Check it once again with the user name/password /connect string is correct. Import the metadata from plugin metadata definitions. Use connect string while importing.

Are you fully qualifying the table name with {schema}.{tablename}?

Hi RK,
One more possiblity is that you have used "Column Generated Query" and have missed the table name in that. Just check that also.

Ridar

Hi
I found the solution for my problem, many thanks for you for giving me the reasonable suggestions which helped me to trace the problem and rectified it.
Actully i am connecting to one schema in plugin and importing the table definiton from another schema . this is the mistake i had made and now i rectified it . Once again Thanking you all for guiding me into different dimensions to trace the problem.

RK

A Possible Red Flag here!!

Actully i am connecting to one schema in plugin and importing the table definiton from another schema . this is the mistake i had made and now i rectified it .

This may mean that either table or the columns in the SQl Statement use fully qualified names. This happens when loading metadata from table defs imported from databases.

When porting jobs from the Dev to Test,QA or PRD environments(which usually involve different schema or servers), such jobs show a tendency of failing after the move due to differences in schema names.

Recommend using the simple column/table names or if that is not possible, a environment variable to point to the schema name.

Actually, it's probably more an example of not using fully qualified table names. If the table is not qualified, the assumption is the current user owns it unless synonyms are involved. So you do the import as one user and connect as another and all you get for your trouble is 'Table or View does not exist'.

And typically the schema isn't changing across environments, it's just the instance that does. Regardless, properly qualify names and use job parameters for all the bits that could change from environment to environment and you'll be covered.