CODEWITHSUNDEEP
sqloracle-databaseplsql
Pooja
Pooja

Reputation: 333

Error : ORA-14155: missing PARTITION, PARTITIONS, SUBPARTITION, or SUBPARTITIONS keyword

I m trying to rename columns dynamically in Oracle.
I have a table whose column is named on month names, every month I need to refresh the data and change column names accordingly.

DECLARE
  v_cur_month_name VARCHAR2(100);
  i                INT := 0;
  vsql             VARCHAR2(250);
BEGIN
  FOR my_tab IN (WITH month_counter AS
                    (SELECT column_name, rownum AS cnt
                    FROM all_tab_columns
                    WHERE lower(owner) = 'ppl'
                    AND lower(table_name) = 'test')
                   SELECT column_name AS MONTH, cnt FROM month_counter WHERE rownum <= 12) LOOP

    v_cur_month_name := to_char(to_date(extract(MONTH FROM to_date(to_char(add_months(SYSDATE, i), 'YYYY-MM-DD'), 'YYYY-MM-DD')), 'MM'), 'MONTH');

    vsql := 'alter table ppl.test rename column' || my_tab.month || 'TO' || v_cur_month_name;
    EXECUTE IMMEDIATE vsql;
    i := i + 1;
  END LOOP;
END;

For this: i am getting error as:

ORA-14155: missing PARTITION, PARTITIONS, SUBPARTITION, or SUBPARTITIONS keyword

ORA-06512: at line 20

Upvotes: 0

Views: 16282

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You need to add spaces to get correct syntax:

vsql := 'alter table ppl.test rename column' || my_tab.month || 'TO' || v_cur_month_name;
=>
vsql := 'alter table ppl.test rename column ' || my_tab.month || ' TO ' || v_cur_month_name;

DBFiddle Demo

Anyway dynamic column rename indicates poor design.

Upvotes: 1

Related Questions