存储过程中遇到ora-00942表或视图不存在

存储过程中遇到ora-00942表或视图不存在

SQL> create or replace procedure system.opt is
2 begin
3 insert into system.fga_log_view_bak(
4 SESSION_ID ,
5 TSMP ,
6 DB_USER ,
7 OS_USER ,
8 USERHOST ,
9 CLIENT_ID ,
10 ECONTEXT_ID ,
11 EXT_NAME ,
12 OBJECT_SCHEMA ,
13 OBJECT_NAME ,
14 POLICY_NAME ,
15 SCN ,
16 SQL_TEXT ,
17 SQL_BIND ,
18 COMMENT$TEXT ,
19 STATEMENT_TYPE ,
20 EXTENDED_TIMESTAMP ,
21 PROXY_SESSIONID ,
22 GLOBAL_UID ,
23 INSTANCE_NUMBER ,
24 OS_PROCESS ,
25 TRANSACTIONID ,
26 STATEMENTID ,
27 ENTRYID )
28 select sessionid,
29 to_char( to_char ( CAST (
30 (FROM_TZ(ntimestamp#,'00:00') AT LOCAL) AS date
31 ), 'yyyy-mm-dd hh24:mi:ss')),
32 dbuid, osuid, oshst, clientid, auditid, extid,
33 obj$schema, obj$name, policyname, scn, to_nchar(substr(lsqltext,1,2000)),
34 to_nchar(substr(lsqlbind,1,2000)), comment$text,
35 DECODE(stmt_type,
36 1, 'SELECT', 2, 'INSERT', 4, 'UPDATE', 8, 'DELETE', 'INVALID'),
37 FROM_TZ(ntimestamp#,'00:00') AT LOCAL,
38 proxy$sid, user$guid, instance#, process#,
39 xid, statement, entryid
40 from sys.fga_log$;
41
42
43 if (SQL%ROWCOUNT<>0) then
44 delete from sys.fga_log$;
45 commit;
46 end if;
47 EXCEPTION
48 WHEN NO_DATA_FOUND THEN
49 RAISE_APPLICATION_ERROR(-20010, 'insert into fga_log_bak error!!!');
50 end opt;
51 /

Warning: Procedure created with compilation errors

SQL> show error
Errors for PROCEDURE SYSTEM.OPT:

LINE/COL ERROR
-------- -----------------------------------------------
40/12 PL/SQL: ORA-00942: table or view does not exist
3/1 PL/SQL: SQL Statement ignored
44/17 PL/SQL: ORA-00942: table or view does not exist
44/1 PL/SQL: SQL Statement ignored


sys.fga_log$ 是记录fga审计记录的基本表。


解决方法：
以sys身份登录，为system赋予select 和delete sys.fga_log$的权限，否则下边的存储过程添加会有问题。
Sql> grant select ,delete on sys.fga_log$ to system;

原因：
后来查资料得知如果用户有dba角色，角色里包含的权限在存储过程里不会被继承；所以又单独对tb_bil_acct_566 ，seq_bil_acct_his_hisid_566.nextval等作了显式赋权，问题得以解决。

http://blog.csdn.net/zhangtian0913/archive/2009/03/09/3973484.aspx

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/161195/viewspace-1050926/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/161195/viewspace-1050926/