This discussion is archived
5 Replies Latest reply: Jul 29, 2013 10:35 AM by rp0428 RSS

Sqldeveloper 4.0EA1 doesn't support new 12c column default "NOT NULL DEFAULT seq.NEXTVAL"

uw*404948*on Newbie
Currently Being Moderated

Caling "Edit" for a table containing a column with the new 12c column default "NOT NULL DEFAULT seq.NEXTVAL"

causes error messagebox

Expression cannot use columns or user functions.  Literal strings should be quoted : "UWE"."TAB_SEQ"."NEXTVAL"

 

It is not possible to change anything on this table in the "Edit" dialog".

Each click returns the above error message.

 

The new 12c Identity columns could be edited.

 

In "Edit Table" dialog the Tab "identify column" does not create any ddl for filling the identity column.Here also only the idenity cloulmn using a trigger is supported, but not the new 12c column default.

  • 1. Re: Sqldeveloper 4.0EA1 doesn't support new 12c column default "NOT NULL DEFAULT seq.NEXTVAL"
    rp0428 Guru
    Currently Being Moderated

     

    In "Edit Table" dialog the Tab "identify column" does not create any ddl for filling the identity column.Here also only the idenity cloulmn using a trigger is supported, but not the new 12c column default.

    One of the developers will need to address what support is provided by that version.

     

    What sort of 'ddl for filling the identity column'' are you talking about?

     

    What is it exactly that you want to be able to do? Even if the functionality for supporting Identity columns worked perfectly you will NOT be able to set or alter any 'default' for an IDENTITY column.

     

    Oracle's implemention of IDENTITY columns creates a SEQUENCE object and then uses that sequence object and NEXTVAL as the default value for the column. That is why the docs state that you, the user, can NOT specify a DEFAULT value for an IDENTITY column; Oracle is already specifying a default value and you are not allowed to remove or alter it.

     

    That column will be 'filled' when you perform inserts or updates to the column in a manner depending on the options you have selected for the column (e.g. DEFAULT or ALWAYS).

  • 2. Re: Sqldeveloper 4.0EA1 doesn't support new 12c column default "NOT NULL DEFAULT seq.NEXTVAL"
    uw*404948*on Newbie
    Currently Being Moderated

    What I did was

    1. Create a text table

        CREATE TABLE TAB_1(

          ID  NUMBER(10,0) DEFAULT TAB_SEQ.NEXTVAL NOT NULL,

          TXT VARCHAR2(3000 BYTE)

         );

     

    2. I tried to edit this table with SQLDeveloper 4.0EA1. This returns the  error message

    Expression cannot use columns or user functions.  Literal strings should be quoted : "UWE"."TAB_SEQ"."NEXTVAL"

     

    For this table it is also not possible to change any description on dialog "edit" tab "Identity".

     

    My expectation is that every object created manualy could be edited in the SQLDeveloper dialogs too.

  • 3. Re: Sqldeveloper 4.0EA1 doesn't support new 12c column default "NOT NULL DEFAULT seq.NEXTVAL"
    rp0428 Guru
    Currently Being Moderated

    Thanks for the info. Your initial questions and the data you just posted illustrate an issue for 12c that I see starting to recur in the forums.

     

    That issue is about knowing how Oracle actually designed and INTENDED new features in 12c to work. There are some features that appear to work but it isn't at all clear if Oracle really 1) intended them to work, 2) will support them or 3) if their 'appearing to work' is just a bug that needs to be fixed.

     

    The use of 'mySeq.nextval' as a default value for a numeric column is just such an issue. Based ONLY on the documentation using such a sequence as a default value does NOT appear to be an 'official' feature of 12c. It isn't mentioned in ANY of the documentation as far as I can tell.

     

    What IS supported are IDENTITY columns. Those columns are defined on a numeric column (only one) in a manner analagous to how VIRTUAL columns are defined. Behind the scenes Oracle implements IDENTITY columns by actually creating a SEQUENCE in the same schema the table is being created in and then also assigns a DEFAULT value to that column using the sequence's NEXTVAL as you do above in your example.

     

    The new sequence will have a name such as 'ISEQ$$_92488'. You can NOT change the name nor will that sequence be dropped if you drop the table. I have not found any way to actually drop those system generated sequences and that could be a bug.

     

    So although 12c will allow you, the user, to assign a similar 'mySeq.nextval' value for a numeric column default you should NOT assume that Oracle intended, or supports that functionality. Maybe they will, maybe they won't.

     

    See my last reply in this recent thread where I explain this further and provide example code

    https://forums.oracle.com/thread/2559782

     

    IDENTITY columns are supported by you CANNOT implement them by providing a default value for the column. You need to use the new syntax. Those columns need to have the proper metadata created for them and one piece of that metadata is setting the IDENTITY flag in the data dictionary table for that column.

     

    See the 'identity_clause' section of CREATE TABLE in the SQL Language doc

    http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm#sthref5460

     

    The third issue you raise is related to the first two and the developers of sql developer will need to respond to that one.

     

    1. Is the IDENTITY column functionality properly supported in the new Sql Developer or are there bugs? I don't know. There may be at least one bug related to what you mentioned: there should not be an issue having a sequence entry as the DEFAULT value for the identity column. That value should display properly, should not be editable by the user and should NOT interfere with the rest of the table 'edit' dialog activity.

     

    2. Should sql developer allow the user to assign a DEFAULT value for numeric columns that mimics that used by Oracle for IDENTITY columns? That is, should you be able to specify 'mySeq.nextval' as a default value? As I mentioned above it isn't clear from any documentation I can find if that is inteded or supported by Oracle. The 12c database will certainly allow you to do it (see my example code in the link I provided) but that could very well be a bug.

     

    So that message you are getting (Expression cannot use columns or user functions. . . ) may be appropriated after all.

     

    Until Oracle support addresses that issue it isn't clear what, if anything the sql developer team can, or even should, do. They may not even be able to address it until the JDeveloper team addresses the issue first in the libraries that are used by sql developer.

     

    Maybe someone from the sql developer team can raise that issue officially with Oracle.

  • 4. Re: Sqldeveloper 4.0EA1 doesn't support new 12c column default "NOT NULL DEFAULT seq.NEXTVAL"
    David Last Expert
    Currently Being Moderated

    The use of 'mySeq.nextval' as a default value for a numeric column is just such an issue. Based ONLY on the documentation using such a sequence as a default value does NOT appear to be an 'official' feature of 12c. It isn't mentioned in ANY of the documentation as far as I can tell.

     

    The ability to use CURRVAL or NEXTVAL of a sequence in a Column's DEFAULT clause is a new feature in Oracle 12c.

     

    The description of the DEFAULT clause in theOracle 12c SQL Documentation includes an additional paragraph:

     

    "The DEFAULT expression can include the sequence pseudocolumns CURRVAL and NEXTVAL, as long as the sequence exists and you have the privileges necessary to access it. Users who perform subsequent inserts that use the DEFAULT expression must have the INSERT privilege on the table and the SELECT privilege on the sequence. If the sequence is later dropped, then subsequent INSERT statements where the DEFAULT expression is used will result in an error. If you do not fully qualify the sequence by specifying the sequence owner, for example, SCOTT.SEQ1, then Oracle Database will default the sequence owner to be the user who issues the CREATE TABLE statement. For example, if user MARY creates SCOTT.TABLE and refers to a sequence that is not fully qualified, such as SEQ2, then the column will use sequence MARY.SEQ2. Synonyms on sequences undergo a full name resolution and are stored as the fully qualified sequence in the data dictionary; this is true for public and private synonyms. For example, if user BETH adds a column referring to public or private synonym SYN1 and the synonym refers to PETER.SEQ7, then the column will store PETER.SEQ7 as the default."

  • 5. Re: Sqldeveloper 4.0EA1 doesn't support new 12c column default "NOT NULL DEFAULT seq.NEXTVAL"
    rp0428 Guru
    Currently Being Moderated

    Thanks for the clarification - time to throw out the beta documenation now that the release is out!

     

    Yes - they did decide to support sequences for default values but specifying a sequence will NOT make that column an IDENTITY column.

     

    And the USER can NOT specify the sequence to be used for an IDENTITY column but MUST let Oracle create it. Hopefully Oracle will remove that restriction in a patch or the next release since I know of no reason for it.

     

    As for sql developer support that still complicates things since they will need to support allowing the user to modify the default value for non-identity columns while NOT allowing them to modify the default value for IDENTITY columns.