Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Getting ORA-14170 <(sub)partition> error when no subpartition described

Aaron MurphySep 29 2025 — edited Sep 29 2025

Need help,
Oracle: 19c

SQL Developer IDE

My Dynamic Code:


          x_SQL := 'CREATE TABLE SCHEMA_STAGE.' || x_Table_NAME || 
                     ' PARTITION BY RANGE (' || x_partition_Col || ') ' || 
                        ' (PARTITION ' || substr(x_Table_NAME, 0, 28) || '_P VALUES LESS THAN (MAXVALUE)) TABLESPACE ' || NVL(p_TableSpace,x_TableSpace)  || ' NOLOGGING' ||
                        ' FOR EXCHANGE WITH TABLE ' || x_Source_Owner || '.' || x_Table_NAME; 
          EXECUTE IMMEDIATE x_SQL;
         x_SQL := NULL;

Creates the below Resulting Command:


CREATE TABLE SCHEMA_STAGE.WORK_ORDERS_TBL 
 PARTITION BY RANGE (WO_NUM)  
 (PARTITION WORK_ORDERS_TBL_P VALUES LESS THAN (MAXVALUE)) TABLESPACE TBSPACE1 NOLOGGING 
 FOR EXCHANGE WITH TABLE Schema1.WORK_ORDERS_TBL;

The Error:
ORA-14170: cannot specify <(sub)partition-description> clause in CREATE TABLE or CREATE INDEX

I can run the above resulting command in SQL Developer manually with no issue. It creates the partition successfully. The initial code is dynamic and also runs with different table names, schema names, tablespace names, partition columns, etc. for 1000+ other tables and schemas, via a procedure call.

But when I run the above WORK_ORDERS_TBL table, with Zero indexes (to exclude any issues there), via the same procedure call, I get the above error. And the Resulting Command is a direct output of x_SQL just before the "EXECUTE IMMEDIATE".

I am creating a single partition to exchange with another table at a later time. As you can see, there are no other PARTITIONS defined. Which is why I don't understand the error.

Any help or insight would be greatly appreciated.

~AaronM

Comments
Post Details
Added on Sep 29 2025
13 comments
140 views