Management of Oracle Cursors

Where ever possible, oracle cursors should be declared  at the start of the program.  It  is also good practice to tell the system the  maximum number  of cursors you want, at the start  of  the program.  This can be done in DB by opening  the cursors you require, in Pro*c set MAXCURSORS.

There  are two 'expensive' operations  during the execution of a query, apart from returning the rows.  These are associated with the  'parse'  and  'bind' phase of query execution.  

What is meant by the parse phase. This is the time  when  the sql text that the  programmer  has written    is   translated   into  an    internal representation, that the  Oracle kernel can understand. The following activities happen during  the parse phase.

a)  Data dictionary look up and security checking.  This activity causes the oracle  kernel to produce internal sql queries, which are  called recursive queries, to check such things as whether this user is allowed access to this column etc.

b)  Query optimisation. Part of the query optimisation which deals with views and sub queries is performed.

Performing all of the above can take a considerable time. 

Therefore they should be  done as infrequently as possible.         

The binding phase takes place after the parse phase. It is at this stage that the user variables are decided. For instance in the query select  'x' from  person where name = :1 the host  variable  1 would have its value 'bound' into the query during  binding  by  having the address of  :1  associated with the host variable.

Under normal circumstances, wms programs should perform the following steps, for a query that is the be executed more than once.

Step 1:

During program start-up set up and parse  the  queries with calls to sql_set-up.

Step 2:

Execute the query with a call to  sql_execute supplying the necessary bind variables.

Step 3:

Fetch and process the returned rows by  using sql_fetch.

To execute the query again, return to step  2 and   supply  new bind  variables.  

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of or the content authors.