Re: commit statement

If the commit is closing your cursor that means that your Pro*C compiler is set to mode=ANSI (check the Pro*C manuals for a full explanation on this). When the mode is set to ANSI, all cursors are closed on commit. To fix this you have three options:
(1) Change the precomiler to be MODE=ORACLE (this will let you commit without having to close cursors.
(2) Change the cursor loop and delete to PL/SQL. This anonymous PL/SQL block will bypass the mode=Oracle. When you precompile your program, the you'll have to have SQLCHECK=SEMANTICS
(3) Create a PL/SQL procedure and store it on the database that will perform the delete and return any messages to the C Program. The procedure can be accessed through either an annonymous PL/SQL block in the Pro*C, or if you can't change to SQLCHECK=SEMANTICS, through a query, such as: EXEC SQL

     select delete_emp_table()
     from dual;

Anyway, depending on how your environment is set up and administerered, the options above may be limited. In performace terms, however, the options above are likely going from worst (#1) to best (#3) IMHO.

Steve

Most likely th
"Edward Horky" <ejjh4527_at_home.com> wrote in message news:3AB2B31C.E0D68E51_at_home.com...
> Hi, I have a "C" program with some embedded SQL in it. I am compiling
> the program with ORACLES Pro*C Compiler. The program is designed to
> delete a large amount of rows from a table within the ORACLE database.
> To do this, I declare a cursor (outside of the loop) and fetch each row
> from the database then I delete it. This all happens within a for loop.
> The problem is this, I have a counter set up that when it reaches 20 I
> will issue a commit statement which is also within the loop. The problem
> is every time I issue the commit the cursor is closed.
> I can't stick the commit statement outside of the for loop because I
> don't want to commit a large amount of work from the delete to ORACLE
> all at once. By having the commit inside the for loop I can delete and
> commit say 20 at a time. The question is, how do I issue a commit
> without it closing the cursor. I must delete(commit) only 20 at a time.
>
> Regards,
>
> Edward
Received on Fri Mar 16 2001 - 20:07:42 CST