On Apr 9, 3:49=A0pm, Ed Prochak <edproc...@[EMAIL PROTECTED]
> wrote:
> On Apr 9, 9:11 am, mowi...@[EMAIL PROTECTED]
wrote:
>
>
>
>
>
> > On Apr 8, 8:53 pm, Ed Prochak <edproc...@[EMAIL PROTECTED]
> wrote:
>
> > > On Apr 8, 12:26 pm, mowi...@[EMAIL PROTECTED]
wrote:
>
> > > > Hi all,
>
> > > > I have two tables; vpd_tabell_v and USER_POLICIES.
>
> > > > vpd_tabell_v contains all the table names that we plan to secure
wit=
h
> > > > VPD while the table USER_POLICIES has all the tables that have
alrea=
dy
> > > > been secured with VPD. What I am trying to do is find out in
> > > > vpd_tabell_v which tables have not been secured yet then print
them
> > > > out.
>
> > > > I've written the procedure below to assist in achieving this. It
> > > > compiles fine but when I run it, it does not supply the desired
> > > > result. Any help is greatly appreciated.
>
> > > > --
> > > > Mark
>
> > > > CREATE OR REPLACE PROCEDURE p_add_vpd_34
> > > > IS
> > > > =A0 =A0CURSOR kk
> > > > =A0 =A0IS
> > > > =A0 =A0 =A0SELECT object_name FROM =A0USER_POLICIES;
> > > > =A0 =A0ka =A0 =A0 =A0 =A0 =A0 =A0 =A0kk%ROWTYPE;
> > > > BEGIN
> > > > =A0 =A0OPEN kk;
> > > > =A0 =A0LOOP
> > > > =A0 =A0 =A0 FETCH kk
> > > > =A0 =A0 =A0 =A0INTO ka;
> > > > for x in ( SELECT table_name FROM vpd_tabell_v ) loop
> > > > =A0 =A0 =A0 =A0 =A0IF x.table_name NOT IN (ka.object_name)
> > > > =A0 =A0 =A0 =A0 =A0THEN
> > > > =A0 =A0 =A0 =A0 =A0 =A0 DBMS_OUTPUT.put_line ('Table name:
'||x.tabl=
e_name);
> > > > =A0 =A0 =A0 =A0 =A0END IF;
> > > > =A0 =A0 =A0 =A0 End loop;
> > > > =A0 =A0 =A0 =A0 =A0EXIT WHEN kk%NOTFOUND;
> > > > =A0 =A0 =A0END LOOP;
> > > > =A0 =A0 =A0CLOSE kk;
> > > > END;
>
> > > Why are you doing in PL/SQL what can be done in straight SQL?
> > > this is a simple not in query, like this:
>
> > > SELECT v.table_name
> > > FROM vpd_tabell_v v
> > > WHERE v.table_name NOT IN
> > > =A0 ( =A0SELECT u.table_name from user_policies u );
>
> > > =A0It can be written as a MINUS =A0operation and as an outer join
also=
..
> > > Here's the outer join:
>
> > > SELECT v.table_name
> > > FROM vpd_tabell_v v, user_policies u
> > > WHERE v.table_name=3Du.table_name(+)
> > > =A0 AND =A0u.table_name is null ;
>
> > > Then you don't have to worry about the server buffer for PL/SQL
> > > output. Just an example of picking the right tool for the job.
>
> > > --
> > > Magic Interface, Ltd.www.magicinterface.com
> > > 440-498-3700
> > > Hardware/Software Alchemy- Hide quoted text -
>
> > > - Show quoted text -
>
> > Ed,
>
> > Your solution helped fix my problem but had to use it in a procedure
> > to print using the DBMS_OUTPUT.PUT_LINE function.
>
> > Thanks a lot :-)
> > Mark
>
> Was this a school assignment? Why else would you NEED PUT_LINE()??
>
> =A0 Ed- Hide quoted text -
>
> - Show quoted text -
No, it is not a school assignment. The put_line() is meant to trap any
bugs before any security policies are added to the tables in the next
section of the IF statement.
--
Mark
|
