oracle-databaseoracle-manageddataaccess

Oracle.ManagedDataAccess.Client ORA-00903 invalid table name

I'm using the sqldeveloper and have one database connection with the following connection string: MES@//localhost:1521/xepdb1 MES is the schema owner and a select statement like this shows me what I want to see: select count(*) from site

I'm also using Visual Studio and I'm trying to connect to the database by using the Oracle.ManagedDataAccess.Client I'm using exactly the same connection string. The connect to the database works fine. But I'm always getting the 00903 error.

Any idea what the problem can be ?

Many thanks in advance

I've tried also something like this: select count(*) from mes.site

Solution

  • If you have used quoted identifiers to create the table then you will need to use quoted identifiers (and use the same case) whenever you access the table.

    For example:

    CREATE TABLE MES."site" (something NUMBER);

    Then you would need to use:

    SELECT count(*) FROM MES."site";

    or

    SELECT count(*) FROM mes."site";

    or

    SELECT count(*) FROM mEs."site";

    The MES schema name is unquoted so you can use any case (and Oracle will implicitly convert it to upper-case to look it up in the data dictionary); however, "site" is a quoted identifier and Oracle will respect the case-sensitivity of the identifier and you MUST use the correct case and the surrounding quotes.

    You can see the exact case you need to use in the result of the query:

    SELECT owner, table_name FROM all_tables WHERE UPPER(table_name) = 'SITE';

    If the data dictionary shows that the table name is upper-case then you can use an unquoted identifier (assuming that all the other naming rules have been respected and the table name is not a reserved/keyword) otherwise you will need to use a quoted identifier.

    Normally you would get the ORA-00942: table or view does not exist exception but you can get ORA-00903: invalid table name when you use a keyword for a table name:

    CREATE TABLE "NUMBER" (x) AS
SELECT 1 FROM DUAL;

    Then:

    SELECT COUNT(*) FROM NUMBER;

    Give the exception:

    ORA-00903: invalid table name

    And:

    SELECT COUNT(*) FROM "NUMBER";

    Works.

    However, MES and SITE are not keywords that should trigger that.

    fiddle