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.

create sequence seq_tmp;

create table tmp (id number default on null seq_tmp.nextval, my_Data varchar2(10));

create sequence seq_tmp;

create table tmp (id number default on null seq_tmp.nextval, my_Data varchar2(10));

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

create user test_user identified by test_user;
grant connect to test_user;
grant all on tmp to test_user;

create user test_user identified by test_user;

grant connect to test_user;

grant all on tmp to test_user;

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

SQL> select * from mustafa.tmp;

no rows selected

SQL> insert into mustafa.tmp values (1, 'test row1');

1 row created.

SQL> select * from mustafa.tmp;

no rows selected

SQL> insert into mustafa.tmp values (1, 'test row1');

1 row created.

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:

SQL> insert into mustafa.tmp values (null, 'test row2');
insert into mustafa.tmp values (null, 'test row2')
          *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into mustafa.tmp values (null, 'test row2');

insert into mustafa.tmp values (null, 'test row2')

ERROR at line 1:

ORA-00942: table or view does not exist

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:

SQL> insert into mustafa.bla_bla_table_which_is_not_exist values(1);
insert into mustafa.bla_bla_table_which_is_not_exist values(1)
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into mustafa.bla_bla_table_which_is_not_exist values(1);

insert into mustafa.bla_bla_table_which_is_not_exist values(1)

ERROR at line 1:

ORA-00942: table or view does not exist

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

SQL> insert into mustafa.tmp (my_data) values ( 'test row2');
insert into mustafa.tmp (my_data) values ( 'test row2')
          *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into mustafa.tmp (my_data) values ( 'test row2');

insert into mustafa.tmp (my_data) values ( 'test row2')

ERROR at line 1:

ORA-00942: table or view does not exist

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

SQL> insert into mustafa.tmp values (mustafa.seq_tmp.nextval, 'test row2');
insert into mustafa.tmp values (mustafa.seq_tmp.nextval, 'test row2')
                                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into mustafa.tmp values (mustafa.seq_tmp.nextval, 'test row2');

insert into mustafa.tmp values (mustafa.seq_tmp.nextval, 'test row2')

ERROR at line 1:

ORA-00942: table or view does not exist

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 🙂

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

Comment

Leave a Reply Cancel reply