Re: ProC Question!

Dorian Winterfeld (dorian_at_wam.umd.edu) wrote:
: Hello ProC gurus,
: I am a novice ProC programmer and I have a question about
: cursors. Is it possible to open multiple cursors in the same program?
: Either at the same time, by nesting one cursor inside another, or
: consecutively, one after the other. From the manual, the only way
: to 'break out' of a loop is 'WHEN NOT FOUND GOTO..'. This seems very
: limiting as it applies to all SELECT statements. I know that in
: PL/SQL you can refer to a specific cursor, as in 'WHEN cursor%NOTFOND
: GOTO...'. This is much more flexable. Is there a similar trick
: in ProC? Please send replies to:
 

: dorian_at_eng.umd.edu

My mail doesnt seem to work, so I'll post this:

I try to avoid using the "WHEN" syntax whenever possible, as there are more structured alternatives to use. Oracle provides a data structure called the SQLCA which is updated during execution with various error codes, counts and other useful info. These values can be tested and used to control program flow. For instance, in the example you describe concerning multiple cursors (yes, it is possible): following a FETCH statement, you could check the value of sqlca.sqlcode - a value of 1403 represents the NOT FOUND condition (be careful here, if MODE=ANSI13 then this changes to 100) and you would write you code accordingly to handle that condition. You can also use the SQLCA to avoid using WHENEVER SQLERROR (this statement can be a real pain if you have multiple routines in your code). Instead, test the value of sqlca.sqlcode after each EXEC statement - a value less than zero indicates a serious error that is usually fatal (i.e. any transactions should be rolled back). Negative values correspond to the error codes listed in the Error Messages and Codes Manual.

To make the SQLCA available to your program include the following line somewhere near the beginning of your code (outside your main block):

   EXEC SQL INCLUDE sqlca;

If you can get your hands on "Oracle Precompilers - Programmer's Guide", check out the section on "Handling Runtime Errors" which explains quite well the condition handling process. Also, the "Oracle Precompilers - Pro*C Supplement" talks about C specific processing and describes the structure of the SQLCA.

Hope this helps.
---Kevin Lukes

Kevin J. Lukes  Programmer/Analyst   |  klukes_at_novatel.cuc.ab.ca
NovAtel Communications Ltd.          |  (403)295-4573
Calgary, Alberta CANADA              |  "Opinions expressed are my own"