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

On Apr 8, 11:26*am, mowi... (AT) hotmail (DOT) com wrote:
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

On Apr 8, 11:26*am, mowi... (AT) hotmail (DOT) com wrote:
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

On Apr 8, 11:26*am, mowi... (AT) hotmail (DOT) com wrote:
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

On Apr 8, 11:26*am, mowi... (AT) hotmail (DOT) com wrote:
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

On Apr 8, 12:26 pm, mowi... (AT) hotmail (DOT) com wrote:
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

On Apr 8, 12:26 pm, mowi... (AT) hotmail (DOT) com wrote:
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

On Apr 8, 12:26 pm, mowi... (AT) hotmail (DOT) com wrote:
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

On Apr 8, 12:26 pm, mowi... (AT) hotmail (DOT) com wrote:
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

On Apr 8, 7:06*pm, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote:
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