Fine Grained Acces Control & cursor

[robert]

Mr. Kyte's article doesn't use cursors, while Mr. Feuerstein's
book examples do. my recollection of conventional wisdom is
to avoid using cursors.

is this difference merely a question of style, or is one
approach right and the other wrong?


oracle 8.1.7

thanks,
robert

[Thomas Kyte]

Re: Fine Grained Acces Control & cursor

gnuoytr@rcn.com (robert) wrote in message news:<da3c2186. 0406041150.67f1 09a9@posting.go ogle.com>...

Mr. Kyte's article doesn't use cursors, while Mr. Feuerstein's
book examples do. my recollection of conventional wisdom is
to avoid using cursors.
>
is this difference merely a question of style, or is one
approach right and the other wrong?
>
>
oracle 8.1.7
>
thanks,
robert

well, we both use cursors -- it is *impossible* to get data otherwise!

The difference is "implicit" cursors vs "explicit" cursors.

There are two basic types of cursors in PL/SQL:

o Implicit cursor With this type of cursor, PL/SQL does most of the
work for you. You don't have to open close, declare, or fetch from an
implicit cursor.

o Explicit cursor With this type of cursor, you do all of the work.
You must open, close, fetch, and control an explicit cursor
completely.

There is a myth that explicit cursors are superior in performance and
usability to implicit cursors. However, the truth is that equivalent
implicit cursors are faster and much easier to code. Does that mean
you'll never use an explicit cursor? No, there are cases where
explicit cursors are used

o When you are performing large bulk operations. (bulk collect).
Note that in 10g, there is an automagical 100 row bulk collect in
place for implicit cursors so even this case is going the way of
mythology in the future...

o Explicit cursors are also necessary when you are using dynamic SQL
and fetching more than a single row. Here, you do not have a choice.
There is no way to dynamically process an implicit cursor. So, when
processing dynamic SQL using ref cursors, you will be using explicit
cursors


there are two cases to be looked at really:

o single row selects
o result sets (0, 1 or more rows)


for single row selects -- select .... INTO .... from .... is the *only
way to go*.

The select into does many things for us -- some people will say to
code this:


cursor c is ....
begin
open c;
fetch c;
close c;


but really, to get the functionality of the select into, you MUST
code:

cursor c is ...
begin
open c;
fetch c;
if c%notfound then raise an error;
fetch c;
if c%found then raise an error;
close c;


because a select into says "you shall get AT LEAST one and AT MOST one
row" -- it has all of that error checking nicely bundled into a single
call (if you want "at least one" but don't care about the "at most"
part -- just add "and rownum = 1" to your query and you are done). I
wrote about this in Effective Oracle By Design - the plsql chapter and
said:


Which should we use (discussingimpl ict vs explicit for select into
logic)? *Without exception*, we should use SELECT INTO, for the
following reasons:

o It is less code (less chance for bugs) and easier to read.
o It runs faster (more efficient).
o It makes our code safer (more bug-free).



Similar arguments are in place for the explicit vs implicit cursor for
loop construct -- when you are processing row by row.

I prefer to always code:


for x in ( select .... )
loop


(and if the select is heinously big -- well, we do have views! or we
could "cursor it" and

for x in CURSOR_NAME
loop

which is sort of an implicitly explicit cursor...)


This sets up a data structure for me (easy).
This reduces the code I have to write (easy).
This removes the bug I see in way too much code. If I had a penny for
everytime I've seen:


...
open c;
loop
fetch c into ...;
process record;
exit when c%notfound;
end loop;
close c;


the exit when is in the wrong place. I see this time and time and
time again. A mistake not possible with implicit cursors where the
code would just be:


for x in ( select ... )
loop
process record;
end loop;

[Tony]

Re: Fine Grained Acces Control &amp; cursor

thomas.kyte@ora cle.com (Thomas Kyte) wrote in message news:<7b0834a8. 0406060725.86a2 686@posting.goo gle.com>...

for single row selects -- select .... INTO .... from .... is the *only
way to go*.

I agree. But I do find that this gets me into trouble with the
"PL/SQL police" on my project, who point me to the project PL/SQL
standards that say thou shalt always use explicit cursors, supported
by the usual myths plus some additional ones (quote: "use explicit
cursors if you want to re-use a cursor. This will improve performance
because there is a high chance that the cursor will execute pre-parsed
SQL in the SGA").

Recently, I used a SELECT INTO in a situation where exactly one record
should be found. However, during system testing TOO_MANY_ROWS was
raised due to corrupt data caused by poor validation elsewhere in the
system. Of course, rather than being thanked for finding a problem
that needs solving, I was mildly scolded for using a SELECT INTO
instead of opening a cursor and doing a single fetch like everyone
else!

[robert]

Re: Fine Grained Acces Control &amp; cursor

thomas.kyte@ora cle.com (Thomas Kyte) wrote in message news:<7b0834a8. 0406060725.86a2 686@posting.goo gle.com>...

gnuoytr@rcn.com (robert) wrote in message news:<da3c2186. 0406041150.67f1 09a9@posting.go ogle.com>...

Mr. Kyte's article doesn't use cursors, while Mr. Feuerstein's
book examples do. my recollection of conventional wisdom is
to avoid using cursors.

is this difference merely a question of style, or is one
approach right and the other wrong?


oracle 8.1.7

thanks,
robert

lots of useful information. thanks. (if only you were published
by o'reilly; since they're again binding with Rep-Kover my prejudice
has returned.)

robert

>
well, we both use cursors -- it is *impossible* to get data otherwise!
>
The difference is "implicit" cursors vs "explicit" cursors.
>
There are two basic types of cursors in PL/SQL:
>
o Implicit cursor With this type of cursor, PL/SQL does most of the
work for you. You don't have to open close, declare, or fetch from an
implicit cursor.
>
o Explicit cursor With this type of cursor, you do all of the work.
You must open, close, fetch, and control an explicit cursor
completely.
>
There is a myth that explicit cursors are superior in performance and
usability to implicit cursors. However, the truth is that equivalent
implicit cursors are faster and much easier to code. Does that mean
you'll never use an explicit cursor? No, there are cases where
explicit cursors are used
>
o When you are performing large bulk operations. (bulk collect).
Note that in 10g, there is an automagical 100 row bulk collect in
place for implicit cursors so even this case is going the way of
mythology in the future...
>
o Explicit cursors are also necessary when you are using dynamic SQL
and fetching more than a single row. Here, you do not have a choice.
There is no way to dynamically process an implicit cursor. So, when
processing dynamic SQL using ref cursors, you will be using explicit
cursors
>
>
there are two cases to be looked at really:
>
o single row selects
o result sets (0, 1 or more rows)
>
>
for single row selects -- select .... INTO .... from .... is the *only
way to go*.
>
The select into does many things for us -- some people will say to
code this:
>
>
cursor c is ....
begin
open c;
fetch c;
close c;
>
>
but really, to get the functionality of the select into, you MUST
code:
>
cursor c is ...
begin
open c;
fetch c;
if c%notfound then raise an error;
fetch c;
if c%found then raise an error;
close c;
>
>
because a select into says "you shall get AT LEAST one and AT MOST one
row" -- it has all of that error checking nicely bundled into a single
call (if you want "at least one" but don't care about the "at most"
part -- just add "and rownum = 1" to your query and you are done). I
wrote about this in Effective Oracle By Design - the plsql chapter and
said:
>
>
Which should we use (discussingimpl ict vs explicit for select into
logic)? *Without exception*, we should use SELECT INTO, for the
following reasons:
>
o It is less code (less chance for bugs) and easier to read.
o It runs faster (more efficient).
o It makes our code safer (more bug-free).
>
>
>
Similar arguments are in place for the explicit vs implicit cursor for
loop construct -- when you are processing row by row.
>
I prefer to always code:
>
>
for x in ( select .... )
loop
>
>
(and if the select is heinously big -- well, we do have views! or we
could "cursor it" and
>
for x in CURSOR_NAME
loop
>
which is sort of an implicitly explicit cursor...)
>
>
This sets up a data structure for me (easy).
This reduces the code I have to write (easy).
This removes the bug I see in way too much code. If I had a penny for
everytime I've seen:
>
>
...
open c;
loop
fetch c into ...;
process record;
exit when c%notfound;
end loop;
close c;
>
>
the exit when is in the wrong place. I see this time and time and
time again. A mistake not possible with implicit cursors where the
code would just be:
>
>
for x in ( select ... )
loop
process record;
end loop;