On 10/11/05, Charles Jardine <[EMAIL PROTECTED]> wrote:
>
> Nardin, Simon wrote, on 11/10/2005 09:05:
> > I'm using perl to access Oracle database. Everything is going well, but
> > now I have a question about using CursorName. I was searching on the net
> > for examples without success how to use cursors with "FOR UPDATE" and
> > "CURRENT OF" construct. Is this possible with Oracle using CursorName?
> > Maybe do you have an example? Is there an example how to select record
> > and immediatelly after that updating it?
>
> FOR UPDATE part of the SQL language. You can use it from perl
> just as you would from SQL*Plus or PL/SQL.
>
> WHERE CURRENT OF is _not_ part of the SQL language. It is part
> of PL/SQL, and, I believe, the Oracle pre-compiler products.
Well, that might be how Oracle implements it, but it is actually part of
Standard SQL - all versions from 1986 to 2003.
SQL 2003 has the rules:
14.10 <update statement:
positioned><file:///Users/jleffler/home/apache/webserver/htdocs/SQL/sql-2003-2.bnf.html#update%20statement:%20positioned>
Update a row of a table.
<update statement: positioned>
<file:///Users/jleffler/home/apache/webserver/htdocs/SQL/sql-2003-2.bnf.html#xref-update%20statement:%20positioned>::=
UPDATE <target
table><file:///Users/jleffler/home/apache/webserver/htdocs/SQL/sql-2003-2.bnf.html#target%20table>SET
<set
clause
list><file:///Users/jleffler/home/apache/webserver/htdocs/SQL/sql-2003-2.bnf.html#set%20clause%20list>WHERE
CURRENT OF <cursor
name><file:///Users/jleffler/home/apache/webserver/htdocs/SQL/sql-2003-2.bnf.html#cursor%20name>
14.11 <update statement:
searched><file:///Users/jleffler/home/apache/webserver/htdocs/SQL/sql-2003-2.bnf.html#update%20statement:%20searched>
Update rows of a table.
<update statement: searched>
<file:///Users/jleffler/home/apache/webserver/htdocs/SQL/sql-2003-2.bnf.html#xref-update%20statement:%20searched>::=
UPDATE <target
table><file:///Users/jleffler/home/apache/webserver/htdocs/SQL/sql-2003-2.bnf.html#target%20table>SET
<set
clause
list><file:///Users/jleffler/home/apache/webserver/htdocs/SQL/sql-2003-2.bnf.html#set%20clause%20list>[
WHERE <search
condition><file:///Users/jleffler/home/apache/webserver/htdocs/SQL/sql-2003-2.bnf.html#search%20condition>]
You can't use WHERE CURRENT OF from perl.
You can with DBD::Informix. If you have problems, it is likely related to
the name of the cursor for which you specify 'WHERE CURRENT OF'.
There's a metadata method to get the cursor name - $sth->{CursorName} or
thereabouts. However, not all drivers can support it.
However, you can get exactly the same effect by using the
> pseudo-column 'rowid'. This column described in the SQL Reference
> Manual.
>
> There is a PL/SQL example of using rowid instead of WHERE CURRENT
> OF in the PL/SQL manual. Look for "Fetching Across Commits" in
> the index.
>
> --
> Charles Jardine - Computing Service, University of Cambridge
>
>
--
Jonathan Leffler <[EMAIL PROTECTED]> #include <disclaimer.h>
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
