ORA-00942: table or view does not exist

esivaprasad · Post by **esivaprasad** » Tue Dec 22, 2009 8:02 am

Dear All,
We are having below problem in one our jobs . The job is simple load job which loads the data from Sequential file to ORACLE table. Same job is working fine in Development environment. The target table is available in database.
Source : Sequential file
Oracle stage (write method = load , write mode = Truncate)

GenericQuery:esqlErrorHandler
Prepare failed for:
query is: select * from GROUP
sqlcode is: -942
esql complaint: ORA-00942: table or view does not exist
getSchema: could not prepare query `select * from GROUP'.

Thanks in advance...
Regards,
Siva.

srinivas.g · Post by **srinivas.g** » Tue Dec 22, 2009 8:17 am

provide Schema name

esivaprasad · Post by **esivaprasad** » Tue Dec 22, 2009 8:20 am

srinivas.g wrote:provide Schema name

query is: select * from CDM.GROUP

chulett · Post by **chulett** » Tue Dec 22, 2009 8:35 am

That's not what you posted says.

Make sure you've supplied the correct schema, you are connecting to the correct instance and have select grants on the table.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Dec 22, 2009 8:45 am

You say that your source is sequential file and target is Oracle.

But you use 'SELECT' clause !!??

Are we missing something ?

chulett · Post by **chulett** » Tue Dec 22, 2009 8:47 am

Good catch, missed that little nugget.

esivaprasad · Post by **esivaprasad** » Tue Dec 22, 2009 8:50 am

chulett wrote:That's not what you posted says.

Make sure you've supplied the correct schema, you are connecting to the correct instance and have select grants on the table. ...

using global parameters, Other jobs running fine.
I can insert data manually into the table.

chulett · Post by **chulett** » Tue Dec 22, 2009 8:51 am

Still, all posted advice still applies. And do let us know why you posted a select statement for your Oracle target.

esivaprasad · Post by **esivaprasad** » Tue Dec 22, 2009 8:56 am

Sainath.Srinivasan wrote:You say that your source is sequential file and target is Oracle.

But you use 'SELECT' clause !!??

Are we missing something ? ...

It's not a user defined sql and I am using Oracle stage (write method = load , write mode = Truncate)

HariK · Post by **HariK** » Wed Dec 23, 2009 6:01 am

Siva..Check if you have any open query(pre load) in target stage.
it could be possible that this is added in development for testing purposes.

mk_ds09 · Post by **mk_ds09** » Wed Dec 23, 2009 6:36 am

You are saying that same job is working in development enviorment.
Which means there are issues with the settings of your database connections in production.

You can check all the database related parameter settings.
As it was giving error of table is not present means datastage is not able to read the table defination itself..

Hope this helps

---------

MK

naveen_1985in · Post by **naveen_1985in** » Wed Dec 23, 2009 9:42 pm

esivaprasad wrote:Dear All,
We are having below problem in one our jobs . The job is simple load job which loads the data from Sequential file to ORACLE table. Same job is working fine in Development environment. The target table is available in database.
Source : Sequential file
Oracle stage (write method = load , write mode = Truncate)

GenericQuery:esqlErrorHandler
Prepare failed for:
query is: select * from GROUP
sqlcode is: -942
esql complaint: ORA-00942: table or view does not exist
getSchema: could not prepare query `select * from GROUP'.

Thanks in advance...
Regards,
Siva.

Siva ,
Check out the schema for the database and check whether u can create a dummy value to the database table (from oracle)..if u could nt create the dummy value ..contact u r database admin to provide the privilges to insert the data into the table (probably they could have created the synonym for the table)...In the synonym u can do the desc ,but u cant insert the values to the table...

ray.wurlod · Post by **ray.wurlod** » Wed Dec 23, 2009 10:10 pm

naveen_1985in wrote:Check out the schema for the database and check whether u can create a dummy value to the database table (from oracle)..if u could nt create the dummy value ..contact u r database admin to provide the privilges to insert the data into the table (probably they could have created the synonym for the table)...In the synonym u can do the desc ,but u cant insert the values to the table...

Please strive for a professional standard of written English on DSXchange. It helps those whose first language is not English.
There is no need to use SMS-style abbreviations; DSXchange does not limit your posts to 140 characters.

The second person personal pronoun in English is spelled "you", not "u" or, in the possessive, "your" rather than "u r" (which could be mistaken for "you are". The verb "can't" requires an apostrophe, since it is a contraction of "can not". And "not" includes a vowel.

mgendy · Post by **mgendy** » Thu Dec 24, 2009 3:44 am

Also i suggest to use sql loader insted of upsert , because upsert method are heavey to database tahn sql loader , it searches all the records by the specified key to update them , if it fouund them , it updates them , then generate insert statment with the reset records to be inserted ,,,, and from the logic you implement i found that the data have no updates , they are all new data need to be inserted , so using sql loader will be more efficient than upsert method, also if you have bitmap indexes upsert may cause many problems , and may abort ,, so try to get the access to the previously sened db tables and further use sql loader

chulett · Post by **chulett** » Thu Dec 24, 2009 9:27 am

As noted, that "does not exist" error can mean either exactly that, or you don't have the grants you need so it is (in essence) invisible to your user. You'll also find you need "extra" grants to truncate and load a table rather than simply "upsert" data into it.

DSXchange

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

Re: ORA-00942: table or view does not exist

Re: ORA-00942: table or view does not exist