SQLDeveloper Trigger Error report - ORA-00942: table or view does not exist

Question

I put this code into SQL Developer's Worksheet:

CREATE TRIGGER T_testDSNa
before INSERT
on testDSNa
referencing new as new
for each ROW
BEGIN
  SELECT S_testDSN.nextval INTO :NEW.SYSID FROM dual;
END;

I get this:

Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Would anyone know why? This has worked for 3 previous tables until I tried to run the DDL to create a 4th. Alternatively, is there a better way to set up an autoincrementing PK?

Answer 1

The problem was lack of schema. Oracle Definition of a schema :

Collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links. A schema has the name of the user who controls it.

If you want to know the objects accessible without alias. You have to look on [USER_OBJECTS]. Which describes the relational objects owned by the current user :

SELECT 
 OBJECT_NAME
 , OBJECT_TYPE
 , LAST_DDL_TIME
FROM USER_OBJECTS;

If you want to know the objects accessible to the current user :

SELECT 
    OWNER
    , OBJECT_NAME
    , OBJECT_TYPE
    , LAST_DDL_TIME 
FROM ALL_OBJECTS;

In your case to see your objects in the list of available tables you need:

SELECT * FROM ALL_OBJECTS WHERE OWNER = 'USER';

You can also alter the session to avoid alias :

ALTER SESSION SET current_schema = User;

For priviliges/ roles views you can look at :

SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

The last method but not the most secure to avoid alias. Is to log on with a user that has the same name as the schema.

Hoping that it can help

Answer 2

I was getting the same issue. Solution: What I observed that my table which I created was surrounded by double quotes, which made it case sensitive. So for each time I refer to my table, I need to surround it by double quotes.

CREATE TRIGGER T_testDSNa
before INSERT
on "testDSNa"
referencing new as new
for each ROW
BEGIN
SELECT S_testDSN.nextval INTO :NEW.SYSID FROM dual;
END;

refer this link: What exactly do quotation marks around the table name do?