For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Is there a way to disable this?
Kind regards
Post the actual DDL for the table. The DDL you posted has syntax errors since the word ALWAYS is missing.
If you look at the log panel you can see the statements sql dev is submitting to do the extract.
You can also use DBMS_METADATA.GET_DDL and extract the ddl yourself.
I pasted in this post the generated ddl. I just tested it in SQL Developer and got back:
Table "MY_TABLE" created.
INDEX "MY_TABLE_PK" created.
Error starting at line : 1 in command -
ALTER TABLE "MY_TABLE" MODIFY ("MY_TABLE_SID" NOT NULL ENABLE)
Error report -
ORA-01442: column to be modified to NOT NULL is already NOT NULL
01442. 00000 - "column to be modified to NOT NULL is already NOT NULL"
*Cause:
*Action:
Table "MY_TABLE" altered.
And I tested it in a 12.2 db and it failed because of the missing ALWAYS keyword.
Did you then MANUALLY extract the ddl using the dbms_metadata package and see what it gives you? That is the package used by sql dev.
Are you certain your test was against 12.2?
Testing against a local 11g XE instance, the CREATE TABLE failed due to missing ALWAYS. On 12.2.0.1 (a Developer Day VM), however, the CREATE TABLE worked, but the ALTER TABLE ... MODIFY failed as the OP states. I used the HR schema in both cases.
For convenience, I repeat the test case with the advanced editor's syntax highlighting for SQL:
CREATE TABLE "MY_TABLE" ( "COLUMN_1" VARCHAR2(255 CHAR), "MY_TABLE_SID" NUMBER(*,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 11 NOCACHE ORDER NOCYCLE NOKEEP NOSCALE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" NO INMEMORY ;CREATE UNIQUE INDEX "MY_TABLE_PK" ON "MY_TABLE" ("MY_TABLE_SID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "USERS" ;ALTER TABLE "MY_TABLE" MODIFY ("MY_TABLE_SID" NOT NULL ENABLE);ALTER TABLE "MY_TABLE" ADD CONSTRAINT "MY_TABLE_SPK" PRIMARY KEY ("MY_TABLE_SID")USING INDEX ENABLE;
CREATE TABLE "MY_TABLE"
( "COLUMN_1" VARCHAR2(255 CHAR),
"MY_TABLE_SID" NUMBER(*,0) GENERATED BY DEFAULT ON NULL AS IDENTITY
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 11
NOCACHE ORDER NOCYCLE NOKEEP NOSCALE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" NO INMEMORY ;
CREATE UNIQUE INDEX "MY_TABLE_PK" ON "MY_TABLE" ("MY_TABLE_SID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ;
ALTER TABLE "MY_TABLE" MODIFY ("MY_TABLE_SID" NOT NULL ENABLE);
ALTER TABLE "MY_TABLE" ADD CONSTRAINT "MY_TABLE_SPK" PRIMARY KEY ("MY_TABLE_SID")
USING INDEX ENABLE;
Like you say, the ALTER TABLE ... MODIFY should not be there, but aside from the annoying but meaningless error on that extra statement, the DDL actually does work.
Thanks
I don't know why it doesn't work for others. Generated it using export, tested in another schema, and it worked.
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
To be clear, the ability to define an IDENTITY column is a 12c feature. It is interesting that running your CREATE TABLE statement in 11g errors with ORA-02000: missing ALWAYS keyword.
See https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle
Also notice in step 1 of the Export Wizard there is a Version drop-down list in the DDL section:
I was curious to see the difference in the DDL between using 11.2 and COMPATIBLE when exporting your MY_TABLE from a 12.2 database. Would the IDENTITY column "MY_TABLE_SID" be suppressed in 11g as that syntax is not supported?
No such luck! It is exactly the same except the NO INMEMORY clause is added for the COMPATIBLE, 12.2 case.
When I saw this issue I also tested with Version set explicitly to 12.2 to see if the ALTER TABLE ... MODIFY ... NOT NULL maybe will be skipped, but it wasn't.