?
Solved

Disadvantages

Posted on 2004-08-07
5
Medium Priority
?
4,081 Views
Last Modified: 2010-08-05
What are the disadvantages of cursors ?
What are the disadvantages of packages?
0
Comment
Question by:hiswapna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
morphman earned 150 total points
ID: 11747582
Disadvantages of Cursors:
1. Row by row processing, so bulk get operations are slow.
2. Inherent risk of memory mismanagement if cursors are not managed correctly. ie. in our compnay we usually forfeit the performance benefits of cursor fetch loops for cursor for loops, so that they will automatically be implicitly closed rather than having the possibility of rather large open cursors floating round the system on an abnormal exit.
3. Pre 10g, cursors are handled by the plsql engine which is not quite as efficient/bug free as the sql engine. I think 10g has combined the 2 engines now.
4. Building a large cursor can have quite an overhead, which is more of a problem especially in an XA environment, when a transactional commit means that cursors must be re-built to continue from the last processed record. (ie checkpointing etc). In an oracle only system this is not a problem, but multi platform/multi database transactional system, we have found that we need to strike the right balance for our batch checkpoint frequency, otherwise the time taken to build a cursor can outweigh the actual work taking place, and the batch slows right down.

I think the advantages far outweigh the disadvantages though.

Disadvantages of Packages.
1. They are stored code, and any malicious/accidental overwriting can result in a disaster. This can be aleviated by putting certain privs on the packages for users, but that is a maintenance overhead.
2. The DESC function could be more descriptive in my opinion. In a package you can desc it to se what procedures and function, and their inputs/outputs, but it would be nice to also include a description. (can you do this by the way?)

Cant think of any more as packages are pretty much wonderful as long as someone keeps an eye on the stored code to make sure some developers testing hasnt been included onto live :-)

0
 
LVL 8

Expert Comment

by:annamalai77
ID: 11750383
hi

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors.
 
 
0
 
LVL 48

Expert Comment

by:schwertner
ID: 11760201
Cursors are the only way to handle multirow select in PL/SQL. They embed the SELECT statement to the logic of 3GL languges like PL/SQL.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 11760234
The main disadvantage is ORA-01000 error.
"Too many open cursors".
This is when in PL/SQL one has no EXCEPTION section which closes the all opened cursors.

But this is also a hidden error in the Java application world.
The drivers (JDBC - thin and fat) implicitely opened many cursors and very often (canceled application)
forget to close them. The result is very unpleasant - ORA-01000. So programmers have to catch the exception
and to close the resulsets by every normal or abnormal end of the programs.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question