0

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?

vimuth
  • 5,064
  • 33
  • 79
  • 116
Chewy
  • 21
  • 1
  • 4
  • Did you forget to create the testDSNa table? – Gerrat Aug 02 '14 at 00:51
  • Nope, I have it created, and I can insert and delete from it. However, running Select * from testDSNa; doesn't work either. Same error. – Chewy Aug 02 '14 at 00:52
  • 1
    Then the table isn't actually named what you think it is. Is it possible that you surrounded the table name in double quotes when you created it, turning it into a case sensitive identifier (http://stackoverflow.com/questions/563090/oracle-what-exactly-do-quotation-marks-around-the-table-name-do/563126#563126)? If so, you'd need to surround the table name in double quotes every time you reference it (which is one reason that case sensitive identifiers are frowned upon). – Justin Cave Aug 02 '14 at 00:58
  • Can you post the `CREATE TABLE` script you used? – GregHNZ Aug 02 '14 at 01:38
  • Hmmm, so I read something weird on another post here, and I tried just typing "User"."testDSNa" instead of just testDSNa, and suddenly it all works again. Why does this happen? (User = name of the database, so I think it might be something with the specification of testDSNa, sorta like Java has java.lang.*blah*) – Chewy Aug 02 '14 at 09:06

2 Answers2

0

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

Bertrand Ring
  • 39
  • 2
0

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?

Kratika Agrawal
  • 1