Oracle 12.2.0.1.0与12.1.0.2.0中EXECUTE IMMEDIATE执行异常问题

Yes, this is a known compatibility behavior change (rooted in stricter syntax parsing) between Oracle Database 12c R1 (12.1.0.2) and R2 (12.2.0.1) when using dynamic SQL to create sequences with certain parameters.

What’s Causing the Error? #

Your original dynamic CREATE SEQUENCE statement includes the NOPARTITION parameter—a 12.2-only addition for partitioned sequences. Here’s why it breaks across versions:

• In 12.1, the parser silently ignores unrecognized sequence parameters, so NOPARTITION doesn’t trigger an error even though it’s invalid for that version.
• In 12.2, Oracle enforced stricter syntax validation for DDL statements. Even though NOPARTITION is technically valid (as the default for non-partitioned sequences), placing it alongside legacy parameters like NOCYCLE can trigger the ORA-00933 error during dynamic execution via EXECUTE IMMEDIATE.

Also, some of your parameters are redundant and unnecessary:

• NOORDER: This is the default behavior for sequences (you only need ORDER if you require ordered values).
• NOCYCLE: Also the default—sequences don’t cycle unless explicitly told to.
• NOPARTITION: Default for non-partitioned sequences in 12.2+, and unrecognized in 12.1.

Cross-Version Fixes #

You have a few straightforward options to make the code work in both 12.1 and 12.2:

1. Remove redundant parameters (simplest solution):

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

   This retains only non-default parameters and avoids the problematic NOPARTITION flag.

2. Use bind variables instead of string concatenation (safer for dynamic SQL, reduces syntax risks):

   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 :start_val 
       CACHE 100' USING max_id; 
   END;
   

   This eliminates potential formatting issues from string concatenation and reduces SQL injection risks.

3. Keep explicit parameters (if needed) with 12.2-compliant order:
   If you must retain flags like NOCYCLE, place NOPARTITION (only if targeting 12.2+) after core sequence parameters and before caching/ordering flags. However, since NOPARTITION is unrecognized in 12.1, omitting it entirely is better for cross-version compatibility.

Official Context #

Oracle’s 12.2 release notes highlight stricter DDL syntax validation, where unrecognized parameters that were ignored in earlier versions may now trigger errors. The NOPARTITION parameter is intended solely for opting out of 12.2’s partitioned sequence feature, so using it in pre-12.2 environments is unsupported (even if it didn’t throw an error there).

内容的提问来源于stack exchange，提问作者pahan