In Oracle, I want to use a sequence and not allow Insert on the column that uses the sequence

I want to make happen the same that happens when I do the following

CREATE TABLE "TEST1" 
(   
     "ID" NUMBER(10,0) GENERATED ALWAYS AS IDENTITY,
    "APPCODE" VARCHAR2(1)
);
  Table TEST1 created.
INSERT INTO TEST1 (ID, APPCODE) VALUES (1,'A');
   Error starting at line : 6 in command -
   INSERT INTO TEST1 (ID, APPCODE) VALUES (1,'A')
   Error at Command Line : 50 Column : 1
   Error report -
   SQL Error: ORA-32795: cannot insert into a generated always identity column
INSERT INTO TEST (APPCODE) VALUES ('A');
   1 row inserted.

but I want to use named sequences, created by me. I want the same behavior as when using the "ALWAYS" keyword (as in "GENERATED ALWAYS AS IDENTITY") and at the same time use my own named sequences, but I don't know how.

With named sequences, it seems to be impossible to avoid that an INSERT uses the ID COLUMN on the insert. But maybe there is a way? This is the question I'm asking. Below I create a named sequence and show the difference (I can't figure out how to prevent the ID column to be allowed on the insert).

CREATE SEQUENCE SEQ_TEST2 START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE;
    Sequence SEQ_TEST2 created.
INSERT INTO TEST2 (APPCODE) VALUES ('A'); /* This is ok */
   1 row inserted.
INSERT INTO TEST2 (ID,APPCODE) VALUES (1928,'A'); /* This is NOT ok */
   1 row inserted.

The second insert above is what I want to prevent from happening, it shouldn't be possible to insert on the ID column. I don't care how to prevent it to happen, doesn't have to be the same way that the "ALWAYS" keyword on the TEST1 table works, but I would like to prevent it from happening. Anyone knows please how to to it?

Solution 1:

When you define a column as a identity column, Oracle automatically creates a sequence, you just don't get to choose the name. You can view the name of the sequence that was created and will be used to populate the identity in the DATA_DEFAULT column of the ALL_TAB_COLS table.

SELECT owner,
       table_name,
       column_name,
       data_default
  FROM all_tab_cols
 WHERE identity_column = 'YES';

Solution 2:

Why do you thing that while using IDENTITY you do not use a SEQUENCE?

Check the documentation or the example below

CREATE TABLE "TEST1" 
(   
     "ID" NUMBER(10,0) GENERATED ALWAYS AS IDENTITY,
    "APPCODE" VARCHAR2(1)
);

For this table Oracle creates a sequence for you under the over:

EXPLAIN PLAN  SET STATEMENT_ID = 'jara1' into   plan_table  FOR
insert into TEST1 (APPCODE) values ('x');
---    
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'jara1','ALL')); 

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |     1 |   100 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST1        |       |       |            |          |
|   2 |   SEQUENCE               | ISEQ$$_75209 |       |       |            |          |
-----------------------------------------------------------------------------------------

Or check the dictionary

select SEQUENCE_NAME from USER_TAB_IDENTITY_COLS
where table_name = 'TEST1';

SEQUENCE_NAME                                                                                                                   
---------------
ISEQ$$_75209

In identity_options you can define the sequence options.

By selection ALWAYS or BY DEAFULT [ON NULL] you can adjust what is posible / now allowed to use in insert (I'm not sure from you description what is your aim).