Re: Problem with a procedure

On Apr 8, 7:06 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> On Apr 8, 11:26 am, 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;
>
> > SQL> exec p_add_vpd_34
> > Table name: Person
> > Table name: Dept
> > Table name: Section
> > Table name: Location
> > Table name: Hr
> > Table name: Deployment
>
> > ...
> > ...
>
> > Table name: Salaries
>
> > BEGIN p_add_vpd_34; END;
>
> > *
> > Error on line 1 1:
> > ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
> > ORA-06512: at "SYS.DBMS_OUTPUT", line 32
> > ORA-06512: at "SYS.DBMS_OUTPUT", line 97
> > ORA-06512: at "SYS.DBMS_OUTPUT", line 112
> > ORA-06512: at "P_ADD_VPD_34", line 16
> > ORA-06512: at line 1
>
> By default the output buffer for dbms_output is 2000 bytes; it can be
> set as high as 1000000 bytes:
>
> set serveroutput on size 1000000
>
> Execute the above command then run  your procedure; unless you have
> 1000000 bytes of data loaded before your procedure completes its
> processing you won't see the error you've posted.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Thanks David for the tip on extending the size of the output buffer. However, I this example that was not the main problem (a derived problem may be) because the bigger table has only 51 records in it.

--
Mark