ORA-14308: partition bound element must be one of: string, datetime or interval literal, number, or NULL

Question

CREATE OR REPLACE PROCEDURE pro_alter_partition(INTERFACE_ID VARCHAR2) 
    AS
    L_partition_name            VARCHAR2(100);
    L_subpartition_name            VARCHAR2(100);
    L_subpartition_inc            NUMBER;
    BEGIN

        SELECT MAX(partition_name), MAX(subpartition_name) 
            INTO L_partition_name, L_subpartition_name
                FROM dba_tab_subpartitions 
                    WHERE table_name = UPPER('jcff_int_inb_staging_data');

        dbms_output.put_line(L_subpartition_name);

        L_subpartition_inc := to_number(substr(L_subpartition_name,9))+1;
        L_subpartition_name := substr(L_subpartition_name,1,8)||L_subpartition_inc;

        dbms_output.put_line(L_subpartition_inc);
        dbms_output.put_line(L_subpartition_name);

        EXECUTE IMMEDIATE 'ALTER TABLE JDACUST.JCFF_INT_INB_STAGING_DATA
            MODIFY partition '||L_partition_name ||
                ' ADD subpartition '||L_subpartition_name||' values ('||INTERFACE_ID||')';

    END pro_alter_partition;
    /

It should not throw the error and should alter the partition

Answer 1

INTERFACE_ID is a string literal. Therefore in needs to be wrapped in quotes. Only because you're using dynamic SQL you need to escape them:

EXECUTE IMMEDIATE 'ALTER TABLE JDACUST.JCFF_INT_INB_STAGING_DATA
    MODIFY partition '||L_partition_name ||
        ' ADD subpartition '||L_subpartition_name||' values ('''||INTERFACE_ID||''')';

Dynamic SQL is hard because it turns compilation errors into runtime errors. To spot errors we need to think like a compiler. Make it easy of yourself and use a variable to assemble the executable string. You can disable the variable to see the statement that is actually executed, which is easier to debug:

stmt := 'ALTER TABLE JDACUST.JCFF_INT_INB_STAGING_DATA
        MODIFY partition '||L_partition_name ||
            ' ADD subpartition '||L_subpartition_name||' values ('''||INTERFACE_ID||''')';

dbms_output.put_line(stmt);

EXECUTE IMMEDIATE stmt;