Re: Problem with a procedure
Date: Tue, 8 Apr 2008 10:06:43 -0700 (PDT)
Message-ID: <7fe26c01-f7f2-4792-8e88-71c4fb9f4c33@24g2000hsh.googlegroups.com>
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 Received on Tue Apr 08 2008 - 12:06:43 CDT