Turn ON OFF IDENTITY INSERT equivalent in Oracle

Question

I have the following ProductCategory dimension in my DWH design to not lose data :

ProductSK ProductID ProductName BI_StartDate BI_EndDate
-1        -1        Undefined   99991231     99991231

The ProductSK is an identity column.

I am used to use Turn ON/OFF Identity Insert in SQL Server, how can I do the same in Oracle?

This is my dimension DDL :

CREATE TABLE ProductCategory (
    ProductSK NUMBER GENERATED ALWAYS AS IDENTITY,
    ProductID NUMBER NOT NULL,
    ProductName VARCHAR2(100) NOT NULL,
    BI_StartDate NUMBER NOT NULL,
    BI_EndDate NUMBER NOT NULL,

);

The equivalent in SQL Server :

SET IDENTITY_INSERT sometableWithIdentity ON;
SET IDENTITY_INSERT sometableWithIdentity OFF;

Roberto Hernandez · Accepted Answer · 2021-09-15T13:45:39.143

In SQL Server

set identity on Allows explicit values to be inserted into the identity column of a table.

Basically you turn on and off the possibility to insert into an identity column which is defined as a sequence of numbers based on an interval.

In Oracle, you have the option to use IDENTITY GENERATED BY DEFAULT

GENERATED BY DEFAULT: Oracle generates a value for the identity column if you provide no value. If you provide a value, Oracle will insert that value into the identity column. For this option, Oracle will issue an error if you insert a NULL value into the identity column.

Example

SQL> create table x ( c1 number generated by default as identity start with 1 increment by 1 , c2 number ) ;

Table created.

SQL> insert into x ( c2 ) values ( 1 ) ;

1 row created.

SQL> insert into x ( c1, c2 ) values ( 2, 2 ) ;

1 row created.

SQL> select * from x ;

        C1         C2
---------- ----------
         1          1
         2          2

This option allows you to either insert or not ( which is kind of turning on / off ) in a sense very similar to the SQL Server turn on/off.

Turn ON OFF IDENTITY INSERT equivalent in Oracle

1 Answers1