PLS-00103 error using Alter Table command

[samir]

Hello All,

I am trying to alter table within a for loop in stored procedure and am getting following error.

OPEN mytbl_cursor;
FOR i IN 1..v_count LOOP
FETCH mytbl_cursor INTO n_abbr;
EXIT WHEN mytbl_cursor%notfound;
ALTER TABLE cmbc_tmptrips2 ADD(n_abbr varchar2(10));
dbms_output.put_line(n_abbr);
END LOOP;
CLOSE mytbl_cursor;

The error is

1 58 9 PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
begin declare end exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
<< close current delete fetch lock insert open rollback
savepoint set sql commit <a single-quoted SQL string>


Would appreciate any comments.

Thanks in advance

Samir

[Highlander]

You can't use DDL this way, look into EXCEUTE IMMEDIATE.

[akkerend]

In 8.0 you can use procedure EXEC_DDL_STATEMENT in package DBMS_UTILITY.

OPEN mytbl_cursor;
FOR i IN 1..v_count LOOP
FETCH mytbl_cursor INTO n_abbr;
EXIT WHEN mytbl_cursor%notfound;
dbms_utility.exec_ddl_statement('ALTER TABLE cmbc_tmptrips2 ADD('||n_abbr||' varchar2(10))');
dbms_output.put_line(n_abbr);
END LOOP;
CLOSE mytbl_cursor;