Create Table in a Loop inside Stored Procedure Oracle SQL

Question

I am attempting to create a Oracle stored procedure which creates partitioned tables based off of a table containing the table names and the column to be partitioned with. A separate PL/SQL block iterates through the table and calls the procedure with the table name and the column name.

Procedure:

create or replace PROCEDURE exec_multiple_table_create (
    table_name  IN VARCHAR2,
    column_name IN VARCHAR2
) IS
    stmt       VARCHAR2(5000);
    tablename  VARCHAR2(50);
    columnname VARCHAR2(50);
BEGIN
    tablename := table_name;
    columnname := column_name;
--    DBMS_OUTPUT.PUT_LINE(tablename);
--    DBMS_OUTPUT.PUT_LINE(columnname);
    stmt := 'create table '
            || TABLENAME
            || '_temp as (select * from '
            || COLUMNNAME
            || ' where 1=2)';
    EXECUTE IMMEDIATE stmt
        USING IN table_name, column_name;
    stmt := 'alter table '
            || tablename
            || '_temp modify partition by range('
            || columnname
            || ')
            (PARTITION observations_past    VALUES LESS THAN (TO_DATE(''20000101'',''YYYYMMDD'')),
                 PARTITION observations_CY_2000 VALUES LESS THAN (TO_DATE(''20010101'',''YYYYMMDD'')),
                 PARTITION observations_CY_2001 VALUES LESS THAN (TO_DATE(''20020101'',''YYYYMMDD'')),
                 PARTITION observations_CY_2002 VALUES LESS THAN (TO_DATE(''20030101'',''YYYYMMDD'')),
                 PARTITION observations_CY_2003 VALUES LESS THAN (TO_DATE(''20040101'',''YYYYMMDD'')),
                 PARTITION observations_CY_2004 VALUES LESS THAN (TO_DATE(''20050101'',''YYYYMMDD'')),
                 PARTITION observations_CY_2005 VALUES LESS THAN (TO_DATE(''20060101'',''YYYYMMDD'')),
                 PARTITION observations_CY_2006 VALUES LESS THAN (TO_DATE(''20070101'',''YYYYMMDD'')),
                 PARTITION observations_CY_2007 VALUES LESS THAN (TO_DATE(''20080101'',''YYYYMMDD'')),
                 PARTITION observations_CY_2008 VALUES LESS THAN (TO_DATE(''20090101'',''YYYYMMDD'')),
                 PARTITION observations_CY_2009 VALUES LESS THAN (TO_DATE(''20100101'',''YYYYMMDD'')),
                 PARTITION observations_CY_2010 VALUES LESS THAN (TO_DATE(''20110101'',''YYYYMMDD'')),
                 PARTITION observations_FUTURE  VALUES LESS THAN ( MAXVALUE ) )';
    EXECUTE IMMEDIATE stmt
        USING IN table_name, column_name;
    RETURN;    
END exec_multiple_table_create;

The PL/SQL block which is using the stored proc is:

BEGIN
    FOR partition_item IN (
        SELECT
            table_name,
            partition_column
        FROM
            partition_table
    ) LOOP
        exec_multiple_table_create(partition_item.table_name, partition_item.partition_column);
    END LOOP;
END;

Now, when I try executing the thing, this is what I am seeing:

Error report -
ORA-06550: line 9, column 9:
PLS-00905: object SCG_MYACCT_CUSTOMPC.EXEC_MULTIPLE_TABLE_CREATE is invalid
ORA-06550: line 9, column 9:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:
%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I have a feeling that I am missing something. Please let me know what it is. The table containing the reference data exists and contains data.

I have tried refreshing the table, rewriting and modifying the pl/sql block & the procedure code. Nothing seems to be working.

Thanks in advance.

UPDATE 1: There was a glitch in the stored procedure where I needed to refer to the tablename rather than the columnname in the code above. However, I am getting a different error right now.

Error report -
ORA-06546: DDL statement is executed in an illegal context
ORA-06512: at "SCG_MYACCT_CUSTOMPC.EXEC_MULTIPLE_TABLE_CREATE", line 18
ORA-06512: at line 9
ORA-06512: at line 9
06546. 00000 -  "DDL statement is executed in an illegal context"
*Cause:    DDL statement is executed dynamically in illegal PL/SQL context.
           - Dynamic OPEN cursor for a DDL in PL/SQL
           - Bind variable's used in USING clause to EXECUTE IMMEDIATE a DDL
           - Define variable's used in INTO clause to EXECUTE IMMEDIATE a DDL
*Action:   Use EXECUTE IMMEDIATE without USING and INTO clauses to execute
           the DDL statement.

Please help me out with this as well.

UPDATE 2: I removed the USING part of the EXECUTE IMMEDIATE statement. That seemed to take care of the error I posted. Getting a different error with versions now:

Error starting at line : 1 in command -
BEGIN
    FOR partition_item IN (
        SELECT
            table_name,
            partition_column
        FROM
            partition_table
    ) LOOP
        exec_multiple_table_create(partition_item.table_name, partition_item.partition_column);
    END LOOP;
END;
Error report -
ORA-00406: COMPATIBLE parameter needs to be 12.2.0.0.0 or greater
ORA-00722: Feature "Conversion into partitioned table"
ORA-06512: at "SCG_MYACCT_CUSTOMPC.EXEC_MULTIPLE_TABLE_CREATE", line 37
ORA-06512: at line 9
ORA-06512: at line 9
00406. 00000 -  "COMPATIBLE parameter needs to be %s or greater"
*Cause:    The COMPATIBLE initialization parameter is not high
           enough to allow the operation. Allowing the command would make
           the database incompatible with the release specified by the
           current COMPATIBLE parameter.
*Action:   Shutdown and startup with a higher compatibility setting.

Create Table in a Loop inside Stored Procedure Oracle SQL

0 Answers