create or replace procedure common_increment(table_name varchar2,column_name varchar2)
is
strsql varchar2(1024) :='';
begin
strsql := 'create sequence seq_'||table_name||' start with 1 increment by 1';
execute immediate strsql;
strsql := 'create trigger trig_'||table_name||' before insert on '||table_name||'for each row
begin select seq_'||table_name||'.nextval into :new.'||column_name||' from dual';
execute immediate strsql;
end;
/我是以scott身份登录的,一直报下面这个错误,请高手指教,谢谢
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.COMMON_INCREMENT", line 6
ORA-06512: at line 1
is
strsql varchar2(1024) :='';
begin
strsql := 'create sequence seq_'||table_name||' start with 1 increment by 1';
execute immediate strsql;
strsql := 'create trigger trig_'||table_name||' before insert on '||table_name||'for each row
begin select seq_'||table_name||'.nextval into :new.'||column_name||' from dual';
execute immediate strsql;
end;
/我是以scott身份登录的,一直报下面这个错误,请高手指教,谢谢
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.COMMON_INCREMENT", line 6
ORA-06512: at line 1
已连接。
SQL> grant create any sequence to scott; //需要显式授权,隐式授权的权限不能在procedure里面使用授权成功。SQL> conn scott/scott;
已连接。SQL> ed
已写入 file afiedt.buf 1 create or replace procedure test
2 as
3 v_sql varchar2(200);
4 begin
5 /* execute immediate 'grant create any sequence to scott'; */
6 v_sql := 'create sequence tseq start with 1 increment by 1';
7 execute immediate v_sql;
8 exception when others then
9 dbms_output.put_line(sqlerrm);
10* end;
SQL> /过程已创建。SQL> select tseq.nextval from dual;
select tseq.nextval from dual
*
第 1 行出现错误:
ORA-02289: 序列不存在
SQL> exec test;PL/SQL 过程已成功完成。SQL> select tseq.nextval from dual; NEXTVAL
----------
1SQL>