oracle 存储过程 求教

Question

根据现有表自动生成新表 并加入两个新列，我写了代码，但执行的时候报错：
ORA-01735: invalid ALTER TABLE option
ORA-06512: at "GX.CREATETAB_GX_ASSIGNMENT_PRO", line 13
ORA-06512: at line 2
忽略之后生成了跟原表结构相同的表 并没有增加新列，代码如下（前面的数字代表行数是我加的）：
1 CREATE OR REPLACE procedure GX.createtab_gx_assignment_pro
2 Authid Current_User
3 as
4 tabname Varchar(200);
5 asql Varchar(200);
6 bsql Varchar(200);
7
8 begin
9 select 'GX_ASSIGNMENT_'||to_char(sysdate,'yyyymmdd') into tabname from dual;
10 asql:='create table '||tabname||' as select * from gx.GX_ASSIGNMENT where rownum=0';
11 execute immediate asql ;
12 bsql:='alter table gx.'||tabname||'add(ScnNum Number,Operate Varchar(35))';
13 execute immediate bsql;
14
15 commit;
16 end;

Answer 1

两个

 create or replace procedure createtab_gx_assignment_pro
Authid Current_User
as
tabname Varchar(200);

去掉一个后执行没问题。

create or replace procedure createtab_gx_assignment_pro
Authid Current_User
as
tabname Varchar(200);
asql Varchar(200);
bsql Varchar(200);
begin
select 'GX_ASSIGNMENT_'||to_char(sysdate,'yyyymmdd') into tabname from dual;
asql:='create table tabname as select * from gx.GX_ASSIGNMENT where rownum=0';
execute immediate asql using tabname;
commit;
end;