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