This discussion is archived
11 Replies Latest reply: Nov 30, 2013 2:38 PM by rp0428 RSS

A potential bug on the new feature (12c) of Identity Column?

987353 Newbie
Currently Being Moderated

Hi,

 

I am testing a newly introduced feature of Identity Column in Oracle 12c. I used EclipseLink (JPA) to access the database.

 

I may have found a potential bug with this feature. I am getting "ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column" when I try to insert a row. My code doesn't explicitly drop "NOT NULL" constraint.

 

When I delete all the tables under the user and re-define the user and the tables, the error disappear.

 

Here are the details:

 

Error:

 

DatabaseException Internal Exception: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1 ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column Error Code: 604 Call: INSERT INTO MyTable (ID, SOMEID, SOMEDATE) VALUES (?, ?, ?) bind => [null, 100100147, 2013-11-29 Query: InsertObjectQuery(cus.entity.MyTable@1a9ea5b)

 

Table definition:

 

CREATE TABLE MyTable (id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,

      someId INT NOT NULL,

      someDate DATE NOT NULL,

      PARTITION BY range(someDate)

      interval(numtodsinterval(1,'year'))

      SUBPARTITION BY HASH ( someId)

      SUBPARTITIONS 20

      (

       PARTITION p0 VALUES LESS THAN (TO_DATE('01-12-2013', 'DD-MM-YYYY'))

      );

 

Could anyone tell me if there might be a bug associated with the new feature or if there was something wrong with my code?

 

I would be appreciated if anyone can help.

  • 1. Re: A potential bug on the new feature (12c) of Identity Column?
    rp0428 Guru
    Currently Being Moderated
    Could anyone tell me if there might be a bug associated with the new feature or if there was something wrong with my code?

    Sorry - based on what you posted we can't tell you either one of those things.

     

    You said you had a problem but that now you don't have the problem anymore after you recreated everything. So do you have a problem or not?

     

    And you couldn't have the problem with the table you posted since that DDL has multiple syntax errors and won't create a table.

     

    Also you you only got the problem 'when I try to insert a row.' But you haven't posted any code that tries to 'insert a row'.

     

    We can't help you debug code that we cannot see.

     

    And the exception you posted is from Java so you need to mark this question ANSWERED and repost it in the JDBC forum:

    https://forums.oracle.com/community/developer/english/java/database_connectivity/java_database_connectivity_(jdbc)

     

    When you repost provide ALL of the info about the full DB version, full Java version, name and version of the JDBC jar file and the actual Java code you are using to insert the data.

     

    Also post the actual DDL for the table - not DDL with syntax errors.

     

    It may just be that the JDBC jar file you are using doesn't support the 12C database or its new features.

  • 2. Re: A potential bug on the new feature (12c) of Identity Column?
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Make sure you've got the right syntax for the partitioning.  Comment out the IDENTITY part, so that id is a plain column, and make sure you can create the table and INSERT rows into it (explicitly giving values for id).  When you have that much working, then un-comment the IDENTITY part.

     

    Also, isolate any complications that may be related to Java .  Try to get something working in SQL*Plus first.  When that's working perfectly, then adapt it to Java.


  • 3. Re: A potential bug on the new feature (12c) of Identity Column?
    987353 Newbie
    Currently Being Moderated

    "the exception you posted is from Java so you need to mark this question ANSWERED and repost it in the JDBC forum"

     

    I am testing this new feature of Oracle 12c. I assigned a user called "root" and used "root" to create all my tables. I also use "root" in Java/JPA to insert/update/delete the table rows. Initially it worked fine for a day or so. The "ID" column as primary key happily incremented as I expected. Then the posted error occurred.

     

    I tried to do the same using SQL Developer with a number of tables I created under "root" to see if this was an issue with eclipse/JPA or with Oracle 12c, I got the same "ORA-30667" error. Therefore, my feeling is that this is an Oracle 12c issue, not a Java or EclipseLink issue.

     

    I delete the user "root" and all the tables created under "root" and recreated user "root" and the tables. It then worked fine for a day or so and then, I had the same issue again. Last night, I delete the "root" user and recreate this user one more time, it works fine again. This error seams random and only be triggered under certain conditions. I have no idea what I did to have triggered this error. It has definitely occurred twice in the last a few days. And now it is working again as I have cleaned the database.

     

    I may not mark this question as answered just yet. I will report again if I am running into the same issue.

     

    "Also you only got the problem 'when I try to insert a row.' But you haven't posted any code that tries to 'insert a row'"

     

    The error message occurred when inserting. Please read the posted error message again.

     

    "When you repost provide ALL of the info about the full DB version, full Java version, name and version of the JDBC jar file and the actual Java code you are using to insert the data."

     

    I am using Oracle 12c, JRE 1.7, C:\Oracle\Middleware\Oracle_Home\oepe\plugins\oracle.database.driver_6.2.0.201307232054

    the Java code is "Persist(MyTable);" using JPA.

     

    Again, It works once I clean the database tables and the user and recreated them. So it is hard for me to think this error has anything to do with other software but this new feature of Oracle 12c

  • 4. Re: A potential bug on the new feature (12c) of Identity Column?
    987353 Newbie
    Currently Being Moderated

    First time when I got this error, I tried to remove the partition in SQL Developer, it worked fined. But when I put the partition back, I received this same error using SQL Developer. So I suspect that there is a bug in Oracle 12c on this feature when combine partition with Identity column. I tried default_on_null_seq.NEXTVAL instead of Identity, the same issue.

     

    Clean up the database (delete all the tables and the user) can remove this error. But it re-occurs under certain conditions which I don't know.

  • 5. Re: A potential bug on the new feature (12c) of Identity Column?
    rp0428 Guru
    Currently Being Moderated

    All of what you have said is just anecdotal evidence.

     

    You still haven't posted ANYTHING that will allow someone to reproduce your problem.

     

    The table DDL you posted is NOT what you are using. As I said above it has multiple syntax errors and can't be used to create a table.

    The error message occurred when inserting. Please read the posted error message again.

    Yes - you said that. And this is what I said:

    But you haven't posted any code that tries to 'insert a row'"

    People can't try to reproduce your problem if you don't post the DDL and DML and Java code that you say causes the problem.

     

    I can't reproduce your problem. It works just fine for me.

     

    If you think you have a bug you shoul open an SR with Oracle.

  • 6. Re: A potential bug on the new feature (12c) of Identity Column?
    987353 Newbie
    Currently Being Moderated

    Sorry. I posted a wrong version of creating table code. Here is the correct one:

     

    CREATE TABLE MyTable (id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,

          someId INT NOT NULL,

          someDate DATE NOT NULL)

          PARTITION BY range(someDate)

          interval(numtodsinterval(1,'day'))

          SUBPARTITION BY HASH ( someId)

          SUBPARTITIONS 20

          (

           PARTITION p0 VALUES LESS THAN (TO_DATE('01-12-2013', 'DD-MM-YYYY'))

          );

  • 7. Re: A potential bug on the new feature (12c) of Identity Column?
    987353 Newbie
    Currently Being Moderated

    "People can't try to reproduce your problem if you don't post the DDL and DML and Java code that you say causes the problem."

    I understand what you are saying. I am trying the new feature of Oracle 12c with my personal/self training project which is under development using MySQL. There are still a lot of bugs in my project. I am debugging and correcting my source code while trying this new feature.

     

    It appears that my "incorrect source code" triggered the error and I don't know which part of my source code managed to set up the "correct" conditions for this error to occur, particularly the code get modified in the process of debugging. I am reporting this as it has occurred twice in a few days when I am debugging my source code. However, I don't have such an error when using MySQL, despite the same "incorrect source code" I may have

     

    The posted Insert command will show the error using SQL Developer when the "correct" conditions for this error has been set up. Therefore, it has nothing to do with Java.

  • 8. Re: A potential bug on the new feature (12c) of Identity Column?
    rp0428 Guru
    Currently Being Moderated

    Try purging the recyclebin.

     

    The IDENTITY column will cause Oracle to create a sequence used to generate the values.

     

    If you are dropping the table the sequence won't necessarily get dropped even though the table has been. The table will still be in the recycle bin if that is enabled.

     

     

     


  • 9. Re: A potential bug on the new feature (12c) of Identity Column?
    987353 Newbie
    Currently Being Moderated

    "Try purging the recyclebin."

     

    Thank you very much for your help. I have just purged the recyclebin.

     

    I have not encountered the same issue since I cleaned up my database under the user "root" last night. So not sure if "purging the recuclebin" would have fixed the problem.

     

    I may not mark this question as answered for the time-being. If I don't have the same issue for a while, I may mark it as answered.

  • 10. Re: A potential bug on the new feature (12c) of Identity Column?
    rp0428 Guru
    Currently Being Moderated
    I have not encountered the same issue since I cleaned up my database under the user "root" last night. So not sure if "purging the recuclebin" would have fixed the problem.

    I may not mark this question as answered for the time-being. If I don't have the same issue for a while, I may mark it as answered.

    No - it hasn't 'fixed' the problem so do NOT mark the thread ANSWERED.

     

    There is definitely a bug in 12c somewhere regarding this issue.

     

    But I have been able to reproduce your problem using the last table DDL you posted. I'm still trying to create a reproducible test case but it involved inserting several identical rows (default for 12g is deferred allocation), doing a rollback, dropping the table, recreating the table and then trying to insert ONE of the identical rows from before.

     

    It then throws your exception

    ORA-00604: error occurred at recursive SQL level 1

    ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column

    Even more interesting is I also get that exception for clone tables with different names: mytable1 and mytable2 when I try to insert the same row into those tables.

     

    If I execute 'purge recyclebin' the inserts now work into all THREE tables: mytable, mytable1, mytable2.

     

    ---------------------------------------------

    here is a reproducible test case in the SCOTT schema - if it reproduces for you open an SR with Oracle

    ---------------------------------------------

    The 'bug' appears to be related to recyclebin processing. Here is the sequence of steps to reproduce:

     

    1. drop any existing mytable

    drop table mytable

    2. purge the recycle bin

    purge recyclebin

    3. create your table

    CREATE TABLE MyTable (id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
          someId INT NOT NULL,
          someDate DATE NOT NULL)
          PARTITION BY range(someDate)
          interval(numtodsinterval(1,'day'))
          SUBPARTITION BY HASH ( someId)
          SUBPARTITIONS 20
          (
           PARTITION p0 VALUES LESS THAN (TO_DATE('01-12-2013', 'DD-MM-YYYY'))
          )

    4. insert one row - NOTE: this will create a NEW partition since the date is not within the existing one

    INSERT INTO MyTable (ID, SOMEID, SOMEDATE)
    VALUES (null, 100100147, TO_DATE('2014-11-29', 'YYYY-MM-DD'))

    5. drop the table

    drop table mytable    

    6. check the recycle bin  - there should be 44 rows in the recycle bin: one of TYPE table, one of TYPE INDEX, two of TYPE Table Partition and 40 of TYPE Table Composite Partition (the hash subpartitions)

    select * from recyclebin

    7. recreate the table using the same DDL from step 3 (this will only have ONE partition)

    CREATE TABLE . . .

    8. try to insert the same row using the DML from step 4

    INSERT INTO MyTable (ID, SOMEID, SOMEDATE)
    VALUES (null, 100100147, TO_DATE('2014-11-29', 'YYYY-MM-DD'))


    ORA-00604: error occurred at recursive SQL level 1

    ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column

    9. If you use that same DDL but create tables named MyTable1 and MyTable2 you will get the SAME exception if you try to insert rows into either of those tables also.

    10. purge the recyclebin

    purge recyclebin

    11. repeat the row insert using the DML from step 4

    INSERT INTO MyTable (ID, SOMEID, SOMEDATE)
    VALUES (null, 100100147, TO_DATE('2014-11-29', 'YYYY-MM-DD'))

    The row will now be inserted. Inserts into the other 2 tables will also succeed.

  • 11. Re: A potential bug on the new feature (12c) of Identity Column?
    987353 Newbie
    Currently Being Moderated

    "here is a reproducible test case in the SCOTT schema - if it reproduces for you open an SR with Oracle"

     

    Yes. I have just followed the instruction you posted and managed to re-produce the same error I reported earlier. Here are the details of the script output following your posted instruction:

     

    table MYTABLE dropped.
    purge recyclebin
    table MYTABLE created.
    1 rows inserted.
    table MYTABLE dropped.
    OBJECT_NAME                                                                                                                      ORIGINAL_NAME                                                                                                                    OPERATION TYPE                      TS_NAME                        CREATETIME          DROPTIME               DROPSCN PARTITION_NAME                                                                                                                   CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
    -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------------------------------------------------------------------------------------------------------- ---------- --------- ---------- ----------- ------------ ----------
    BIN$6NCDTxmXTb2QBpUWF0kGqw==$0                                                                                                   SYS_C0010655                                                                                                                     DROP      INDEX                     USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030808                                                                                                                                  NO         YES            98789       98789        98812          8
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98790       98789        98789          0
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Composite Partition USERS                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98833       98789        98789       1024
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Partition                                          2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98789       98789        98789           
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      Table Partition                                          2013-12-01:10:31:37 2013-12-01:10:32:17    4030812                                                                                                                                  NO         NO             98789       98789        98789           
    BIN$Ka0sgN7XRBeCsjyXTQ76cA==$0                                                                                                   MYTABLE                                                                                                                          DROP      TABLE                                                    2013-12-01:10:30:56 2013-12-01:10:32:17    4030812                                                                                                                                  YES        YES            98789       98789        98789        

    44 rows selected

    table MYTABLE created.
    Error starting at line 1 in command:
    INSERT INTO MyTable (ID, SOMEID, SOMEDATE)
    VALUES (null, 100100147, TO_DATE('2014-11-29', 'YYYY-MM-DD'))
    Error report:
    SQL Error: ORA-00604: error occurred at recursive SQL level 1
    ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column
    00604. 00000 -  "error occurred at recursive SQL level %s"
    *Cause:    An error occurred while processing a recursive SQL statement
               (a statement applying to internal dictionary tables).
    *Action:   If the situation described in the next error on the stack
               can be corrected, do so; otherwise contact Oracle Support.
    purge recyclebin
    1 rows inserted.

     

    The error message reproduced above is exact the same as I reported. I was pretty certain that this issue was likely a bug introduced in Oracle 12c and your study on this issue further supports what I have believed. Thank you very much for your help.

     

    I have never had exposure to Oracle database until recent few weeks when I decided to test Oracle database in my personal project as there are a lot of new features in 12c I want to explore. I have no idea how to open an SR with Oracle. Could you please advise what is the procedure to open the SR with Oracle? Maybe I cannot log the SR as I am just testing the Oracle 12c features, not a paid customer for Oracle support at this stage.