Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: cursors, views, and fga.
Mladen Gogala wrote:
> 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
One of these days, I'll get good enough at Oracle and PL/SQL to do this on the fly like you did. :) Thanks. Received on Wed Jun 07 2006 - 09:31:55 CDT