473,405 Members | 2,210 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 software developers and data experts.

Fine Grained Acces Control & cursor

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
Jul 19 '05 #1
3 5107
gn*****@rcn.com (robert) wrote in message news:<da**************************@posting.google. 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 (discussingimplict 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;
Jul 19 '05 #2
th*********@oracle.com (Thomas Kyte) wrote in message news:<7b*************************@posting.google.c om>...
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!
Jul 19 '05 #3
th*********@oracle.com (Thomas Kyte) wrote in message news:<7b*************************@posting.google.c om>...
gn*****@rcn.com (robert) wrote in message news:<da**************************@posting.google. 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 (discussingimplict 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;

Jul 19 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Vic | last post by:
Dear All, I have been developing a small access database, but I am new to security concepts with access. This is a multiuser database, I have a table which will be written by various users...
4
by: BBFrost | last post by:
We have a data entry application written within Framework 1.0 that contains 20+ custom user controls. The controls overlay each other so that only one custom control is visible at a time. What...
2
by: Craig | last post by:
I'm trying to do this within a control I've created: Cursor.Current = Cursors.WaitCursor; During the initialization of the parent form, my control gets added to the form, and while the control...
11
by: trinitypete | last post by:
Hi all, I have a user control that uses control literal to build a heading with a link, and a div containing links below. As the link heading is hit, I want to change the style of the div,...
2
by: dw | last post by:
Hello. I've got my LDAP authentication working, and have authorization working to a great extent as well: I'm limiting who can access the site by using <allow users="smithj, harrisb, ...."> in the...
4
by: | last post by:
ASP.NET is a kind of "engine" that takes declarative markup and code and renders these instructions into presentational forms. And every page is a class. With that in mind I'm wondering, a)...
6
by: dimitris.papastamos | last post by:
Hello everyone, I've been working on a simple editor myself and I seem to be having some problems with ncurses or so. I have been debugging this program quite a lot trying to detect where the bug...
3
by: robert | last post by:
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,...
11
by: giddy | last post by:
hi, (I want someone to check if my idea is utterly stupid or ok??) I have a mid complex app for a hotel that needs a fine grained user security system. In other words an admin should be able...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.