Alter Table Add Column in PL SQL loop

Question

I am trying to insert columns in a table using a for loop that iterates over a cursor. The code is:

declare
    cursor Months_ET is 
        SELECT distinct to_char(FEE_CD_ACT_SUM_ACCTG_DA, 'MON-YY') as "Month_U" 
        FROM EXPORT_TABLE
        WHERE EXPORT_TABLE.FEE_CD_ACT_SUM_ACCTG_DA >= to_date('10/01/2013','mm/dd/yyyy') 
        AND EXPORT_TABLE.FEE_CD_ACT_SUM_ACCTG_DA  < to_date('10/01/2014', 'mm/dd/yyyy');
    n integer := 1;
begin
    for mon in Months_ET loop
        dbms_output.put_line(mon."Month_U");
        execute immediate 'Alter table "Fee_CT" add('|| mon."Month_U" ||' varchar(20))';
        n := n +1;
    end loop;
end;

The cursor in the beginning jsut gets a list of unique month names which the dbms_output.put_line prints out as:

SEP-14
AUG-14
JUL-14

So I know the variable is not empty.

So using those results I want to add three columns for each month- yr. However I get an invalid datatype error. I have also tried altering to the for loop to concatenate the table name outside of the quotes like this:

for mon in Months_ET loop
--Month_List(n) := mon."Month_U";
dbms_output.put_line(mon."Month_U");
execute immediate 'Alter table' ||"Fee_CT" || 'add('|| mon."Month_U" ||' varchar(20))';
n := n +1;

But I get a message that "Table,View Or Sequence reference 'Fee_CT' not allowed in this context." Not sure what I am doing wrong. The actual data is much larger and covers a wider time frame so using multiple alter table statements isn't realistic. plus the underlying data will be changing, so I need to be able to change the column names with the underlying data.

You're misusing the relational database. Those new columns ought to be normalised so that the month is an attribute of the table, or of a new table. — David Aldridge, Aug 28 '15 at 21:13
I understand they should be attributes, and why, but in this particular problem I have been asked to create a crosstab report. So yeah I know it's not ideal but gotta solve the problem. — user2907249, Aug 28 '15 at 23:18
Generally it's not necessary to change a table's structure to generate a specific report. It seems like you're solving the wrong problem. — APC, Aug 29 '15 at 07:51

score 2 · Answer 1 · edited May 23 '17 at 12:29

Your table name and column names use non-standard characters - lower case letters, dashes. This is a really bad idea, because it means having to wrap every reference in double-quotes. Every person who has to use your schema will curse you whenever they have to fix a PLS-00357, ORA-00903 or ORA-00904 exception because they forgot to double-quote an identifier. Look, it's even caught you out :)

But if you really want to persist, the statement you need is:

execute immediate 'Alter table "Fee_CT" add("'|| mon."Month_U" ||"' varchar(20))';

The table name is part of the boilerplate text not a variable. You need to wrap the non-standard column name in double-quotes. Make sure the boilerplate has spaces around the key-words.

Above all, remember that a syntax error in dynamic SQL throws a runtime error, not a compilation error. Use logging or DBMS_OUTPUT to review the assembled statements.

mahi_0707 · Answer 2 · 2015-08-28T22:44:29.177

Always use a DBMS_OUTPUT.PUT_LINE to test your execute immediate statement.

Give space between keywords/variables.
Use single quotes

Now Check this Example:

create table Table_A(id integer);
 -- Table created.

Declare
  sql_stmnt varchar2(400) ;
  table_name varchar2(20) := 'Table_A';
  column_name varchar2(20) := 'New_col1';
  data_type varchar2(20) := 'varchar2(20)' ;
Begin
  sql_stmnt := ' alter table ' || table_name || ' add( ' || Column_name || ' ' || data_type  || ' ) ' ;
  execute immediate  sql_stmnt ;
  dbms_output.put_line(sql_stmnt );
End;

 -- alter table Table_A add( New_col1 varchar2(20) ) 
 -- Table altered.

Desc Table_A;

   Column   Data Type   Length  Precision   Scale
     ID      NUMBER     22  -   0   -   -   -
   NEW_COL1  VARCHAR2   20  -   -   -       -   -

mahi_0707 · Accepted Answer · 2015-08-28T22:47:35.967

Not sure why you want to create columns dynamically.But it is possible though:

Errors :

1.Column names can only have '_'(underscore),no other special character. ie.,AUG-15 --> AUG_15

When using Alias names for further processing use SUBQUERY (Month_U )

Quotes should be properly used.

space between keywords/variable in execute statement.

create table Table_A
(id integer,
 date1 date
);
-- Table created.

begin
  insert into table_A values (1,trunc(sysdate) );
  insert into table_A values (2,trunc(sysdate+100) );
end;

select to_char(date1, 'MON-YY') as "Month_U" from table_A;
--AUG-15
--DEC-15

Declare
  cursor months_ET is select month_u from
            ( select to_char(date1, 'MON_YY') AS Month_U from table_A) DUAL;
  sql_stmnt varchar2(400) ;
  table_name varchar2(20) := 'Table_A';
  column_name varchar2(20) := 'New_col1';
  data_type varchar2(20) := 'date' ; -- you can change to varchar2
Begin
  FOR MON in months_ET 
  LOOP 
    sql_stmnt := ' alter table ' ||  table_name || ' add( ' || MON.MONTH_U 
    || ' ' || data_type  || ' ) ' ;
    dbms_output.put_line(sql_stmnt );
    Execute immediate  sql_stmnt ;
  END LOOP;
End;

OUTPUT:

 alter table Table_A add( AUG_15 date ) 
 alter table Table_A add( DEC_15 date ) 

Table altered.

Alter Table Add Column in PL SQL loop

3 Answers3