OracleEnterpriseStage: ORA-00903: invalid table name

GJ_Stage · Post by **GJ_Stage** » Thu Mar 31, 2011 5:51 am

I have tested the same senario , created table called SAPSLP./GKV/CM00_PROCU with one field REG_NO.
It is working fine using this command in Oracle Stage : select REG_NO from "SAPSLP./GKV/CM00_PROCU"

and one more created table /GKV/CM00_PROCU assume SAPSLP is schema name than use below command:
It is working fine using this command in Oracle Stage : select REG_NO from SAPSLP."/GKV/CM00_PROCU"

Kaiser · Post by **Kaiser** » Thu Mar 31, 2011 6:14 am

GJ_Stage wrote:I have tested the same senario , created table called SAPSLP./GKV/CM00_PROCU with one field REG_NO.
It is working fine using this command in Oracle Stage : select REG_NO from "SAPSLP./GKV/CM00_PROCU"

and one more created table /GKV/CM00_PROCU assume SAPSLP is schema name than use below command:
It is working fine using this command in Oracle Stage : select REG_NO from SAPSLP."/GKV/CM00_PROCU"

You're right. If i write the Tablename directly into the sql-Query the Query runs fine. If I use a Job-Parameter as Tablename, the Query causes the shown error. So I will change the stage and run the query without Jobparameter. Thanks a lot for the very fast help!

Best Regards
Bernd

chulett · Post by **chulett** » Thu Mar 31, 2011 6:25 am

Are the quotes being stripped when using a job parameter? If so, have you tried escaping them?

Kaiser · Post by **Kaiser** » Thu Mar 31, 2011 6:36 am

chulett wrote:Are the quotes being stripped when using a job parameter? If so, have you tried escaping them?

Hi,

If I use the Jobparameter with value: "/gkv/cm00_procu"
the following error-message appears:
>Prepare failed for: GenericStmt_1
>query is: SELECT CLAIM FROM SAPSLP."/gkv/cm00_procu"
>sqlcode is: -942
>esql complaint: ORA-00942: table or view does not exist

Though the query looks fine, it doesn't work

chulett · Post by **chulett** » Thu Mar 31, 2011 6:51 am

Odd. Perhaps try putting the double-quotes in the sql itself and not the parameter value?

SELECT CLAIM FROM SAPSLP."#Job_Parameter#"

Kaiser · Post by **Kaiser** » Thu Mar 31, 2011 6:59 am

chulett wrote:Odd. Perhaps try putting the double-quotes in the sql itself and not the parameter value?

SELECT CLAIM FROM SAPSLP."#Job_Parameter#"

The same result:
Prepare failed for: GenericStmt_1
>query is: SELECT CLAIM FROM SAPSLP."/gkv/cm00_procu"
>sqlcode is: -942
>esql complaint: ORA-00942: table or view does not exist

GJ_Stage · Post by **GJ_Stage** » Thu Mar 31, 2011 7:02 am

Define Job parameter name as T_NAME and Value as SAPSLP."/GKV/CM00_PROCU" and in Oracle Stage provide query like :
SELECT CLAIM FROM #T_NAME#.

Above is working fine for me.

Kaiser · Post by **Kaiser** » Thu Mar 31, 2011 7:33 am

GJ_Stage wrote:Define Job parameter name as T_NAME and Value as SAPSLP."/GKV/CM00_PROCU" and in Oracle Stage provide query like :
SELECT CLAIM FROM #T_NAME#.

Above is working fine for me.

Hi,

it's a problem of case sensitivity. If i write the tablename in Upper Case the query runs fine. It works with all the previous written variants:
- SELECT CLAIM FROM #T_NAME#.
- SELECT CLAIM FROM #pSchema#.#ptablename#
- SELECT CLAIM FROM #pSchema#."#ptablename#"

The case-sensitivity doesn't matter, when i work with sql-plus. Thanks for your help and sorry for my blindness

chulett · Post by **chulett** » Thu Mar 31, 2011 7:41 am

Interesting that the case doesn't seem to matter in sqlplus, they both should be acting the same. When a table name is supplied quoted the db engine should be resolving it in a case sensistive manner. And your first post shows it all upper-case.

Ah well, glad you got it sorted out.