儲存過程中遇到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@]