ORA-00942: table or view does not exist and sequences as default value

Hello,

after 12c or above we are able to set sequence’s nextval as the default value of an ID column. One of my customer started to complain about the ORA 00942 (table or view does not exist) error even if they have all necessary privileges on the table. After a quick investigation I realized that real error is not related with the table but the sequence.

this is the sample code, I just assign seq_tmp as default value of tmp table. Now I create a user to test:

as you can see I give all privileges on tmp table to test_user. now lets try to do some inserts with test_user:

so far so good. I provide all necessary value of the table and used my value as ID not the sequence. so let’s continue:

viola! I use null as the value of ID which means sequence have to generate new id data but our insert statement got the ORA 00942. When I see this for the first time, I thought user doesn’t have necessary privileges on tmp table but now I’ve already known that user has privileges. Actually there is a small hint at this error! if you check, you can see that Oracle marks the error point with a star and it is under the “into” keyword, “o” if we want to be precise but if you really don’t have insert privilege on the table it would be marking table name:

as you can see star is under the first letter of the table name. anyway, if we continue to try inserting:

all the combinations of insert statements will be fail when it needs to use the sequence. so let’s use sequence directly:

error again and now we can see that our little star is under the sequence name! So real missing privilege is select on the sequence. after running: grant select on seq_tmp to test_user; all is fine and everything is working.

Don’t forget the sequences 🙂

Leave a Reply

Your email address will not be published.