SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> conn song/song

Connected.

SQL> CREATE TABLE T_POLICY

2 (

3 T1 VARCHAR2(10 BYTE),

4 T2 NUMBER(10)

5 );

Table created.

SQL> CREATE TABLE T_POLICY

2 (

3 T1 VARCHAR2(10 BYTE),

4 T2 NUMBER(10)

5 );

CREATE TABLE T_POLICY

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL> insert into t_policy values('a',10);

insert into t_policy values('b',20);

insert into t_policy values('c',30);

1 row created.

SQL>

1 row created.

SQL>

1 row created.

SQL> commit;

Commit complete.

SQL> CREATE OR REPLACE function Fn_GetPolicy(P_Schema In Varchar2,P_Object In Varchar2) return varchar2 is

2 Result varchar2(1000);

3 begin

4 Result:='t2 not in (10)';

5 return(Result);

6 end Fn_GetPolicy;

7 /

Function created.

SQL> declare

2 Begin

3 Dbms_Rls.Add_Policy(

4 Object_Schema =>'SONG', --数据表(或视图)所在的Schema名称

5 Object_Name =>'T_Policy', --数据表(或视图)的名称

6 Policy_Name =>'T_TestPolicy', --POLICY的名称，主要用于将来对Policy的管理

7 Function_Schema =>'SONG', --返回Where子句的函数所在Schema名称

8 Policy_Function =>'Fn_GetPolicy', --返回Where子句的函数名称

9 Statement_Types =>'Select,Insert,Update,Delete', --要使用该Policy的DML类型，如'Select,Insert,Update,Delete'

10 Update_Check =>True, --仅适用于Statement_Type为'Insert,Update'，值为'True'或'False'

11 Enable =>True --是否启用，值为'True'或'False'

12 );

13 end;

14 /

PL/SQL procedure successfully completed.

SQL> select * from t_policy;

T1 T2

---------- ----------

b 20

c 30

SQL> SELECT SQL_TEXT FROM V$SQLAREA WHERE SQL_TEXT LIKE 'select * from t_policy%';

SQL_TEXT

--------------------------------------------------------------------------------

select * from t_policy ---从这里看，还是原来的sql,而没有加where 条件

ITPub博客

VPD

最新文章