Re: Problem with a procedure

On Apr 8, 8:53 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Apr 8, 12:26 pm, mowi..._at_hotmail.com 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 with
> > VPD while the table USER_POLICIES has all the tables that have already
> > 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
> >    CURSOR kk
> >    IS
> >      SELECT object_name FROM  USER_POLICIES;
> >    ka              kk%ROWTYPE;
> > BEGIN
> >    OPEN kk;
> >    LOOP
> >       FETCH kk
> >        INTO ka;
> > for x in ( SELECT table_name FROM vpd_tabell_v ) loop
> >          IF x.table_name NOT IN (ka.object_name)
> >          THEN
> >             DBMS_OUTPUT.put_line ('Table name: '||x.table_name);
> >          END IF;
> >         End loop;
> >          EXIT WHEN kk%NOTFOUND;
> >      END LOOP;
> >      CLOSE 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
>   (  SELECT u.table_name from user_policies u );
>
>  It can be written as a MINUS  operation 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=u.table_name(+)
>   AND  u.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 Received on Wed Apr 09 2008 - 08:11:19 CDT