Solved

Disadvantages

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

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 47

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

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.

Question has a verified solution.

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

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

773 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