Skip to Main Content

SQL Developer

Announcement

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.

"Turning Wheel" when accessing something

cpeDec 9 2014 — edited May 20 2015

Is there a way to disable this?

Kind regards

Comments

unknown-7404

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.

jkoradba

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.

unknown-7404

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.

Glen Conway

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;

Glen Conway

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.

jkoradba

Thanks  

I don't know why it doesn't work for others.  Generated it using export, tested in another schema, and it worked.

jkoradba

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

PL/SQL Release 12.2.0.1.0 - Production

Glen Conway

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

Glen Conway

Also notice in step 1 of the Export Wizard there is a Version drop-down list in the DDL section:

Capture.png

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.

jkoradba

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.

1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 17 2015
Added on Dec 9 2014
25 comments
5,842 views