sqlcode is: -903 ORA-00903: invalid table name

[peddidsx]

I am unable to load the data in one of my target table ETL_ERROR_STAGING.It is throwing the error 'EtlErrorFileStagingOra: GenericQuery:esqlErrorHandler
Prepare failed for:
query is: select * from .ETL_ERROR_FILE_STAGING
sqlcode is: -903
esql complaint: ORA-00903: invalid table name,.I am using oracle enterprie stage with options LOAD&APPEND.I am able to insert the data when i tried to do it through TOAD.Could you please suggest me the solution if any one of you ever faced the same issue before?

[peddidsx]

I am unable to load the data in one of my target table ETL_ERROR_STAGING.It is throwing the error 'EtlErrorFileStagingOra: GenericQuery:esqlErrorHandler
Prepare failed for:
query is: select * from .ETL_ERROR_FILE_STAGING
sqlcode is: -903
esql complaint: ORA-00903: invalid table name,.I am using oracle enterprie stage with options LOAD&APPEND.I am able to insert the data when i tried to do it through TOAD.Could you please suggest me the solution if any one of you ever faced the same issue before?

And it is working fine when i used insert statemenet using upsert mode.

[Jasti]

I am using oracle enterprie stage with options LOAD&APPEND.

If you use these options in the Oracle enterprise stage you will have to give the table name only in the property Table=

And it is not possible to use

query is: select * from .ETL_ERROR_FILE_STAGING

and that to it is a select, not an insert sql

Please check in your target stage..

[peddidsx]

I am using oracle enterprie stage with options LOAD&APPEND.

If you use these options in the Oracle enterprise stage you will have to give the table name only in the property Table=

And it is not possible to use

query is: select * from .ETL_ERROR_FILE_STAGING

and that to it is a select, not an insert sql

Please check in your target stage..

Jasti,
This is what it is little bit confusing.I am using LOAD&APPEND only,but still throwing me the error with select query.Thanks

[chulett]

".ETL_ERROR_FILE_STAGING" is indeed an invalid table name. Either ensure the owner schema is provided or get rid of the dot.

[Jasti]

'EtlErrorFileStagingOra: GenericQuery:esqlErrorHandler

The error was thrown at the stage EtlErrorFileStagingOra

If that is one of your source stages..then check the select query..

".ETL_ERROR_FILE_STAGING" is indeed an invalid table name. Either ensure the owner schema is provided or get rid of the dot. ...

[peddidsx]

'EtlErrorFileStagingOra: GenericQuery:esqlErrorHandler

The error was thrown at the stage EtlErrorFileStagingOra

If that is one of your source stages..then check the select query..

".ETL_ERROR_FILE_STAGING" is indeed an invalid table name. Either ensure the owner schema is provided or get rid of the dot. ...

No, It is a target table and table name is ETL_ERROR_FILE_STAGING.There is no dot in front of it.

[peddidsx]

It is working fine till yesterday morning.All of a sudden the jobs starts getting aborted with the error message:INVALID TABLE NAME.This is the scenario what we are doing:We are trying to append to the same table from multiple jobs and it workked till yesterday morning

[Raamc]

Have you checked whether any schema updations/changes has gone on your database side?

[chulett]

Exactly - what changed? And as Ray would note, "nothing" is not the correct answer. And if you know it wasn't the job...

[peddidsx]

Exactly - what changed? And as Ray would note, "nothing" is not the correct answer. And if you know it wasn't the job...

Chulett,
When i tried to view the log file..i could not even find it in scratch and it is not even generating the log file or control file where i can dig through.I contacted DBA to check if any schema updates happened on their end.What i am afraid of is in the exact error it is saying .ETL_ERROR_FILE_STAGING where schema name is empty.It should be schema_name.table_name...right?

[chulett]

As I noted earlier, and completely irrespective of DataStage, that leading dot your posted message showed is indeed invalid. You either need to ensure the schema is there before the dot, which makes it an 'absolute path' in a manner of speaking or you need to not have the dot. That would make it equivalent to a 'relative path', meaning Oracle will first look for a private synonym and then a public synonym for that name to know where to go. Failing that it will look for the table in the current / logged in user's schema.

It can't do any of that when the first thing it sees is a dot.

[peddidsx]

As I noted earlier, and completely irrespective of DataStage, that leading dot your posted message showed is indeed invalid. You either need to ensure the schema is there before the dot, which makes it an 'absolute path' in a manner of speaking or you need to not have the dot. That would make it equivalent to a 'relative path', meaning Oracle will first look for a private synonym and then a public synonym for that name to know where to go. Failing that it will look for the table in the current / logged in user's schema.

It can't do any of that when the first thing it sees is a dot.

Surprsingly , It is working fine when i tried to give the fully qualified name.But till yesterday it is with the table name only.Any how thanks chulett and all for your valuable suggestions.Thanks