ORA-00942: table or view does not exist

adams06 · Post by **adams06** » Tue May 02, 2006 2:39 pm

i am getting this error can some one help me out

x..y: ORA-00942: table or view does not exist

Thanks in advance

chulett · Post by **chulett** » Tue May 02, 2006 2:59 pm

How would you solve this problem if DataStage wasn't involved?

adams06 · Post by **adams06** » Tue May 02, 2006 3:02 pm

chulett wrote:How would you solve this problem if DataStage wasn't involved?

the target table is there but still i am getting the error

chulett · Post by **chulett** » Tue May 02, 2006 3:08 pm

If the target table was "there" you wouldn't be getting the error.

So, your mission - should you choose to accept it - is to find out what piece you've got wrong. Check the instance/sid/dsn you are connecting to in the stage, the userid you are connecting with and the schema owner you have specified. At least one of them is wrong.

kcbland · Post by **kcbland** » Tue May 02, 2006 3:13 pm

Methinks you need to look at the SQL after following Craig's advice. Your SQL may/may not contain the schema owner, plus the column derivations in the ODBC/OCI stage contain fully qualified names. Even if you have your table name correct, if the derivations contain another table name you need to clear that out or go import the table definition without fully qualified set, then import that new metadata into the job.

When it doubt, ALWAYS look at the SQL.

ray.wurlod · Post by **ray.wurlod** » Tue May 02, 2006 4:22 pm

What is x..y? Are there two dots between the schema name and the table name? This is not valid syntax.

kiran_418 · Post by **kiran_418** » Tue May 02, 2006 6:00 pm

I think you have passed wrong parameters to the datastage job. Check the parameters.

chulett · Post by **chulett** » Tue May 02, 2006 6:14 pm

ray.wurlod wrote:What is x..y?

StageName..LinkName I do believe is how it is reported. Or perhaps the other way 'round.

The names were probably changed to protect the innocent, anyway.

sb_akarmarkar · Post by **sb_akarmarkar** » Wed May 03, 2006 1:20 am

Oracle table name are case sensitive if you have created table with lower case and looking with upcase then it will display table does not exists.. Have you check option case sensitive in datastage or created table name....

Thanks,
Anupam

jinm · Post by **jinm** » Wed May 03, 2006 2:02 am

what you need to ensure

A: User ID is granted access to the table.
B: If no synonym exists you need to prefix the table-name with schema owner in the select statement.

"select column from owner.tablename;"

chulett · Post by **chulett** » Wed May 03, 2006 6:30 am

sb_akarmarkar wrote:Oracle table name are case sensitive if you have created table with lower case...

That's only true if you explicity enclosed the table name in double-quotes when you did so. And if you did, you deserve all the grief that brings upon your head.

Otherwise, you can create a table in any mix of case you want and subsequently look it up with any other mix of case as Oracle will upcase everything in the 'background' at query time.