Solved

Disadvantages

Posted on 2004-08-07
5
3,969 Views
Last Modified: 2010-08-05
What are the disadvantages of cursors ?
What are the disadvantages of packages?
0
Comment
Question by:hiswapna
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
morphman earned 50 total points
Comment Utility
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
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now