Re: invalid cursor problem with Pro*C++

Hi Paul!

The thing is:
Oracle closes all cursor when performing a commit. They say, that should be done, 'cause of data integrity. But they have implemented an option where you can escape the ( I think ANSI ) standards.

EXEC ORACLE OPTION (HOLD_CURSOR=YES); I tried to use this option, 'cause oracle customer support to me to do so, but it didnt work with Pro*C 16. I dont know whether it will work with your config. Try it!

WieSi

Paul Wagner wrote:

> I'm having a problem with transactions and cursors. I'm using Pro*C/C++
> in a C++ program under Oracle 8.1.5.0.1 on Linux. I'm testing a program from
> Raj Sunderraman's book on Oracle 8 programming, which basically has a double
> nested while loop with fetches from two different tables at the two while loop
> levels, and then does an insert into another table at the inner level based
> on the row combinations from the two levels. I can get it to work if I
> allow the whole program to be one transaction, but if I try to make each
> insert be its own transaction, I get a -1001 - invalid cursor error. Any
> ideas on what the problem might be is much appreciated.
>
> I've put the major parts of the code below, and show which lines are
> causing the invalid cursor error.
>
> Thanks for any and all help,
>
> Paul
>
> ---
> (works with two commented code lines removed, generates invalid cursor -1001
> error with these lines uncommented)
> ...
> // --- Declare cursors
> EXEC SQL declare analyst_cur cursor for
> select aid
> from analyst;
>
> EXEC SQL declare security_cur cursor for
> select symbol
> from security;
>
> // --- Open and process cursors
> EXEC SQL open analyst_cur;
> EXEC SQL fetch analyst_cur
> into :analyst_id indicator :analyst_id_ind;
>
> while (sqlca.sqlcode == 0)
> {
> total_analysts++;
> EXEC SQL open security_cur;
> EXEC SQL fetch security_cur
> into :ssymbol indicator :ssymbol_ind;
>
> while (sqlca.sqlcode == 0)
> {
> total_securities++;
> // EXEC SQL set transaction read write;
> rating = rand() % 5 + 1; // generated rating is 1 to 5
> EXEC SQL insert into rating
> values (:analyst_id, :ssymbol, :rating);
> if (sqlca.sqlcode != 0)
> {
> cout << " Error while inserting rating for "
> << analyst_id.arr << " symbol " << ssymbol.arr << endl;
> EXEC SQL rollback release;
> }
> else // success
> {
> cout << " Analyst " << analyst_id.arr
> << " rated " << ssymbol.arr
> << " as " << rating << endl;
> total_ratings_ins++;
> // EXEC SQL commit work;
> }
> EXEC SQL fetch security_cur
> into :ssymbol indicator :ssymbol_ind;
> } // end - while - securities
> EXEC SQL close security_cur;
> EXEC SQL fetch analyst_cur
> into :analyst_id indicator :analyst_id_ind;
> }
> cout << "sqlca.sqlcode is: " << sqlca.sqlcode << endl;
>
> // --- Cursor cleanup
> EXEC SQL close analyst_cur;
>
> // --- Disconnect from Oracle
> EXEC SQL commit work release;
> ...
> --
> * * * * * * * * * * * * * * * *
> * Paul J. Wagner School - wagner_at_cs.umn.edu *
> * Computer Science Department Work - wagnerp_at_uwstout.edu *
> * University of Minnesota *
Received on Mon Apr 17 2000 - 20:04:01 CEST