Re: updating current row of cursor resultset in OCI

joeyee1_at_msn.com (Joe) wrote in message news:<a9aed213.0309080659.cdbe71c_at_posting.google.com>...
> I'm using Oracle9i and converting some existing applications from
> Sybase, which allows for update of current row in the result set with
> its ct_cursor function call. I've looked through the Oracle
> documentation, the news groups in Google and asktom.oracle.com and
> haven't found an example in OCI of how to do something like this:
>
> Some sample C code that uses OCI to create a cursors using the "OPEN
> C1 FOR SELECT ... FOR UPDATE", and later uses the cursor (C1) to
> update the row via the "UPDATE ... SET .... WHERE CURRENT OF C1"
> statement.
>
> I saw that this question was posed before on many occasions, and there
> were basically 2 types of response (1) No response (2) Use rowid as
> part of the "SELECT ... FOR UPDATE". This second option, although
> seems to work, also appears to be a hack. It looks like a hack because
> (a) I saw a bunch of postings and in asktom.oracle.com that the rowid
> may change depending on the type of data changes (b) updating current
> row of cursor result set is supposed to be in the SQL standard and I
> can do it in PL/SQL, why do I need to play around with rowid, which is
> an internal feature of the Oracle database.
>
> Any definitive answer to this question would be of great help as I'm a
> newbie at this Oracle programming stuff. I also created a "TAR" in
> metalink.oracle.com about a week ago, but haven't got any response
> yet. I just need some guidance on the proper way to do this, if there
> is one. Thanks.

WHERE CURRENT OF works fine. ROWID wont change on the fly like that. You are safe. The reasons to 'prefer' SQL over pl/sql are as follows:

1. By using PL/SQL you increase the complexity of the code. Unless this is absolutely necessary use SQL. Its simpler.
2. SQL by itself is always faster than sql nested inside pl/sql. Ive seen the opposite stated in some books, they are wrong. Run it and test it. Do it on any platform in a variety of scenarios. Its never faster. Not even close. The reason its slower is that Oracle has a SQL parser and a PL/SQL parser. Your asking oracle to use the PL/SQL parser on the cursor and switch to the SQL parser for your DML and keep going back and forth. Much slower. Can take twice as long.

Basically use SQL if its possible to do your work with just SQL. Only use PL/SQL if you need the extra functionality.

Its not a hack. It works. Received on Mon Sep 08 2003 - 11:30:33 CDT