Re: Problem with a procedure

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