Search code examples
oracleplsqlexecute-immediate

Oracle PL/SQL Release 12.2.0.1.0 vs 12.1.0.2.0 - execute immediate with parameters

DECLARE
  max_id INTEGER;
BEGIN
  SELECT MAX(ID) + 1 INTO max_id FROM MY_TABLE;

  EXECUTE IMMEDIATE 'CREATE SEQUENCE  MY_TABLE_ID  MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH ' || max_id || ' CACHE 100 NOORDER  NOCYCLE  NOPARTITION';

END;

Above gives me ORA-00933: SQL command not properly ended when executed on 

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

and works without errors on 

   Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production

After I change the execute statement to the below, it works on both versions without any errors.

 CREATE SEQUENCE  MY_TABLE_ID  MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH ' || max_id || '''

Is this a known issue?

Solution

  • There is not a NOPARTITION option according to either the 12cR1 or 12cR2 documentation.

    The statment does work in 12cR1 but appears to be silently ignored, since it doesn't appear to do anything - though it may be under the hood, as @XING's demo suggests. And it errors are expected in 12cR2 and 11gR2.

    So this seems to be an undocumented (according to a MoS search) bug in 12cR1, in that it doesn't cause an error when an invalid option is included. Except... there are a few hits in MoS that refer to it indirectly as being valid, e.g. bug 23210794 where specifying the option is shown as a workaround; conversely doc ID 2253744.1 mentions

    PARTITION/NOPARTITION keyword, which is not a 12.1 feature, was used during the sequence creation

    Interestingly the all_sequences view gained a partition_count column in 12cR1, but then lost it again in 12cR2. (Purely speculation, but perhaps this is related to the internal implementation of auto-increment columns for partitioned tables, and wan't really meant to be exposed for other sequences.)

    For more information you would need to raise a service request with Oracle.

    But the fix to your problem is to remove the NOPARTITION keyword from your statement.