What causes ORA-00942?

The table name is misspelled or uses incorrect case (Oracle stores names in uppercase by default). The table belongs to a different schema and no schema prefix or synonym is used. The pipeline user lacks SELECT or INSERT privilege on the table. The table was dropped or not yet created in this environment. Using a table alias or view name that does not exist in this database

How do I fix ORA-00942?

Step 1: Check if the table exists and which schema owns it: `SELECT owner, table_name FROM all_tables WHERE table_name = UPPER(' ');`.. Step 2: If the table is in another schema, prefix queries with the schema name: `schema.table_name`.. Step 3: Grant the required privilege: `GRANT SELECT ON schema.table_name TO pipeline_user;`.. Step 4: Create a synonym if cross-schema prefixing is not desired: `CREATE SYNONYM table_name FOR schema.table_name;`.. Step 5: Update ADF dataset to use the correct schema and table name.

High severityschema

Oracle Database Error:
ORA-00942

What does this error mean?

The table or view referenced in the SQL statement does not exist in the current schema, or the connected user does not have SELECT/INSERT privilege on it.

Common causes

1The table name is misspelled or uses incorrect case (Oracle stores names in uppercase by default)
2The table belongs to a different schema and no schema prefix or synonym is used
3The pipeline user lacks SELECT or INSERT privilege on the table
4The table was dropped or not yet created in this environment
5Using a table alias or view name that does not exist in this database

How to fix it

1Step 1: Check if the table exists and which schema owns it: `SELECT owner, table_name FROM all_tables WHERE table_name = UPPER('<TABLE>');`.
2Step 2: If the table is in another schema, prefix queries with the schema name: `schema.table_name`.
3Step 3: Grant the required privilege: `GRANT SELECT ON schema.table_name TO pipeline_user;`.
4Step 4: Create a synonym if cross-schema prefixing is not desired: `CREATE SYNONYM table_name FOR schema.table_name;`.
5Step 5: Update ADF dataset to use the correct schema and table name.

Example log output

ORA-00942: table or view does not exist
SQL: SELECT * FROM fact_sales
ADF: UserErrorOdbcOperationFailed - Error executing Oracle query

Frequently asked questions

Why does the table exist in SQL Developer but not in my pipeline?

Your SQL Developer session likely connects as a user with broader privileges or uses a different default schema. Check which user the pipeline connects as and verify it has the SELECT privilege on the specific table.

Source · docs.oracle.com/error-help/db/ora-00942