DSXchange

Need some help here regarding about the error below:-

ora_F_01: GenericQuery:esqlErrorHandler
Prepare failed for:
query is: select * from ETL_USER.F_01
sqlcode is: -942
esql complaint: ORA-00942: table or view does not exist

ora_F_01: GenericQuery::getSchema - could not prepare query 'select * from ETL_USER.F_01'.

Now, the problem is the F_01 table owner is PCAMI and somehow when I use another user (ETL_USER) to access the tables who has been granted the rights to select from the table and also a public synonym is created for the F_01 table, DataStage automatically appends an ETL_USER.F_01 at the statement. Can someone tell me how do I remove that? Any solution to this?

How are you building the query for this transaction in DataStage?

Are you using the 'Fully Generated Query' option in the 'SQL' tab of your Oracle stage?

This way it might fill in the 'Derivation' field in the 'Columns' tab.

Blank out the derivation. That should fix it.

I don't see this option in the Oracle Stage of DataStage PX. Are u using this version of DataStage as well?

Oops! No I'm not. Not using PX. Sorry about that.

I normally include the schema (owner) name as a job parameter, and inlude it explicitly in the Table Name field.

Using a job parameter makes it easy to move from development through to production where the schema name may be different.

krystlecsy wrote: user (ETL_USER) to access the tables who has been granted the rights to select from the table and also a public synonym is created for the F_01 table

The Oracle user must have access to a number of system tables.
These are all detailed in the Enterprise: Job developers guide.