Re: cursors, views, and fga.

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Wed, 07 Jun 2006 05:41:53 GMT
Message-Id: <pan.2006.06.07.05.41.52.425707@sbcglobal.net>

On Tue, 06 Jun 2006 14:49:43 -0700, DaLoverhino wrote:

> declare
> cursor policy_cursor
> is select object_schema, object_name, policy_name
> where policy_name like 'PATTERN...'
>
> prec policy_cursor%ROWTYPE;
> begin
> open policy_cursor
> loop
> fetch policy_cursor into prec;
> exit when brec%NOTFOUND;
> dbms_fga.drop_policy( object_schema=>prec.object_schema,
> object_name=>prec.object_name,
> policy_name=>prec.policy_name);
> end loop;
> close policy_cursor;
> end;
> /

  1 declare
  2 type silly_names is table of varchar2(64) index by binary_integer;   3 schemes silly_names;
  4 objects silly_names;
  5 policies silly_names;
  6 begin
  7 select object_schema, object_name, policy_name

  8           bulk collect into schemes,objects,policies
  9      from dba_audit_policies
 10     where policy_name like 'PATTERN';

 11 for i in 1..objects.count
 12 loop

 13      dbms_fga.drop_policy( object_schema=>schemes(i),
 14                            object_name=>objects(i),
 15                            policy_name=>policies(i));

 16 end loop;
 17* end;
SQL> / PL/SQL procedure successfully completed.

SQL> Of course, I don't have any policies with names like 'PATTERN' so no harm is done to my database. This way, you will not have to contend with "fetch accross commit". Of course, naming of the collection types is prescribed by the Oracle Corporation and must be followed strictly.

-- 
http://www.mgogala.com

Received on Wed Jun 07 2006 - 00:41:53 CDT