-
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
<< close current delete fetch lock insert open rollback
savepoint set sql commit
Would appreciate any comments.
Thanks in advance
Samir
-
You can't use DDL this way, look into EXCEUTE IMMEDIATE.
-
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;
