oracle存储过程和视图不存在,存储过程中遇到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/，如需转载，请注明出处，否则将追究法律责任。