Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: cursors, views, and fga.
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
13 dbms_fga.drop_policy( object_schema=>schemes(i), 14 object_name=>objects(i), 15 policy_name=>policies(i));16 end loop;
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.comReceived on Wed Jun 07 2006 - 00:41:53 CDT