2 PRAGMA AUTONOMOUS_TRANSACTION;
3 totoalcount number;
4 m number;
5 i number;
6 v_count number;
7 begin
8 m :=v_mid;
9 i := v_start;
10 totoalcount:=v_end;
11 select sum(1) into v_count from boswll.TABLE_LOG where name=log_name;
12 if v_count=0 then
13 insert into boswll.TABLE_LOG values(log_name,0);
14 commit;
15 end if;
16 if totoalcount > 0 then
17 while i <= totoalcount+m loop
18 execute immediate 'insert into bosdata.' || HIS_name||' select * From bosdata.' || table_name||' where rowid in (select w_rowid from BOSWLL.'||mid_name ||' s where s.nmm >=' ||i|| ' and s.nmm
19 <'||i||' + '||m||') ';
20 update boswll.TABLE_LOG set zs=i where name=log_name;
21 commit;
22 i := i + m;
23 end loop;
24 end if;
25 END;
26 /
过程已创建。
SQL> exec insert_table_by_condition ('PD_WO','PD_WO','PD_WO_HIS','PD_WO_T',100,1,400);
BEGIN insert_table_by_condition ('PD_WO','PD_WO','PD_WO_HIS','PD_WO_T',100,1,400); END;
*
第 1 行出现错误:
ORA-01031: 权限不足
ORA-06512: 在 "BOSWLL.INSERT_TABLE_BY_CONDITION", line 18
ORA-06512: 在 line 1
SQL> select * From dba_role_privs where grantee='BOSWLL';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
BOSWLL RESOURCE NO YES
BOSWLL CONNECT NO YES
BOSWLL DBA NO YES
SQL> select * From dba_role_privs where grantee='BOSDATA';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
BOSDATA DBA NO YES
BOSDATA RESOURCE NO YES
BOSDATA CONNECT NO YES
为什么有了dba权限,在执行存储过程的时候还是报没有权限,并且单独拿出insert 执行也是没有问题的,最后发现,执行存储过程中的权限必须是显示的给权限
SQL> grant insert any table to boswll;
Grant succeeded.
SQL> grant insert any table to bosdata;
Grant succeeded.
SQL> exec insert_table_by_condition ('PD_WO','PD_WO','PD_WO_HIS','PD_WO_T',100,1,400);
PL/SQL 过程已成功完成。