Solved

Disadvantages

Posted on 2004-08-07
5
4,047 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 50 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

732 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