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

Question

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?

Answer 1

As mentioned by @Alex, creating a sequence with Partition clause is undocumented feature like WMCONCAT. See below explaination:

sql> create sequence s1;

Sequence created.

sql> select s1.nextval from dual;

     NEXTVAL
     ---------
     1

sql> select dbms_metadata.get_ddl('SEQUENCE','S1') from dual;

DBMS_METADATA.GET_DDL('SEQUENCE','S1')
--------------------------------------------------------------------------------

 CREATE SEQUENCE  "SCOTT"."S1"  MINVALUE 1 MAXVALUE 99999999999999999999999999
99 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER  NOCYCLE  NOPARTITION

You can see here that internally oracle saves the sequence definition in some partition and hence it shows in DDL.

Create another sequence

sql> create sequence s2 partition;

  Sequence created.

sql> select s2.nextval from dual;

     NEXTVAL
---------------
      4103920000000000000000000000000001

sql> select dbms_metadata.get_ddl('SEQUENCE','S2') from dual;

DBMS_METADATA.GET_DDL('SEQUENCE','S2')
--------------------------------------------------------------------------------

 CREATE SEQUENCE  "SCOTT"."S2"  MINVALUE 1 MAXVALUE 99999999999999999999999999
99 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER  NOCYCLE  PARTITION 100000000

You see now this time Oracle created sequence in some partiton and hence showed that in DDL definiton.

Some features oracle had reserved for its own internal usage which is hence kept undocumented.

In your case if you remove that part other part will work fine. See below:

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  ' ;
END;