Oracle 12 introduced nice feature (which should have been there long ago btw!) - identity columns. So here's a script:
CREATE TABLE test (
a INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
b VARCHAR2(10)
);
-- Ok
INSERT INTO test (b) VALUES ('x');
-- Ok
INSERT INTO test (b)
SELECT 'y' FROM dual;
-- Fails
INSERT INTO test (b)
SELECT 'z' FROM dual UNION ALL SELECT 'zz' FROM DUAL;
First two inserts run without issues providing values for 'a' of 1 and 2. But the third one fails with ORA-01400: cannot insert NULL into ("DEV"."TEST"."A")
. Why did this happen? A bug? Nothing like this is mentioned in the documentation part about identity column restrictions. Or am I just doing something wrong?
An explicit value for the identity column in table 'Students' can only be specified when a column list is used and IDENTITY_INSERT is ON. In simple words, the error says that since the flag IDENTITY_INSERT is off for the Id column, we cannot manually insert any values.
How you create an IDENTITY field affects what happens when you INSERT values. You cannot change the IDENTITY value of a column that is a primary key. Copy CREATE Table Test_SGSqlInsert2( id INTEGER, name STRING, deptId INTEGER GENERATED ALWAYS AS IDENTITY (CACHE 1), PRIMARY KEY(id));
An identity column is a numeric column, defined in a CREATE TABLE or ALTER TABLE statement, that has ascending or descending values. For an identity column to be as useful as possible, its values should also be unique.
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
I believe the below query works, i havent tested!
INSERT INTO Test (b)
SELECT * FROM
(
SELECT 'z' FROM dual
UNION ALL
SELECT 'zz' FROM dual
);
Not sure, if it helps you any way.
For, GENERATED ALWAYS AS IDENTITY
Oracle internally uses a Sequence only. And the options on general Sequence applies on this as well.
NEXTVAL is used to fetch the next available sequence, and obviously it is a pseudocolumn.
The below is from Oracle
You cannot use CURRVAL
and NEXTVAL
in the following constructs:
DELETE
, SELECT
, or UPDATE
statementSELECT
statement that is combined with another SELECT
statement with the UNION, INTERSECT
, or MINUS
set operatorThe subquery
and SET
operations rule above should answer your Question.
And for the reason for NULL, when pseudocolumn
(eg. NEXTVAL) is used with a SET operation or any other rules mentioned above, the output is NULL, as Oracle couldnt extract them in effect with combining multiple selects.
Let us see the below query,
select rownum from dual
union all
select rownum from dual
the result is
ROWNUM
1
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With