Problem with a procedure

#31

On Apr 9, 3:49*pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

On Apr 9, 9:11 am, mowi... (AT) hotmail (DOT) com wrote:

On Apr 8, 8:53 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

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

Was this a school assignment? Why else would you NEED PUT_LINE()??

* Ed- Hide quoted text -

- Show quoted text -

It's not a school assignment. PUT_LINE() is to be used to trap any
bugs before any security policies are added to the tables in the next
section of the IF statement.

--
Mark

#32

On Apr 9, 3:49*pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

On Apr 9, 9:11 am, mowi... (AT) hotmail (DOT) com wrote:

On Apr 8, 8:53 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

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

Was this a school assignment? Why else would you NEED PUT_LINE()??

* Ed- Hide quoted text -

- Show quoted text -

It's not a school assignment. PUT_LINE() is to be used to trap any
bugs before any security policies are added to the tables in the next
section of the IF statement.

--
Mark

#33

On Apr 9, 3:49*pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

On Apr 9, 9:11 am, mowi... (AT) hotmail (DOT) com wrote:

On Apr 8, 8:53 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

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

Was this a school assignment? Why else would you NEED PUT_LINE()??

* Ed- Hide quoted text -

- Show quoted text -

It's not a school assignment. PUT_LINE() is to be used to trap any
bugs before any security policies are added to the tables in the next
section of the IF statement.

--
Mark

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Problem with a procedure

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Problem with a procedure - 04-09-2008 , 09:15 AM

Re: Problem with a procedure - 04-09-2008 , 09:15 AM

Re: Problem with a procedure - 04-09-2008 , 09:15 AM