469,636 Members | 1,976 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,636 developers. It's quick & easy.

Select for Update fails

Hey Everyone,

Is this an Oracle bug?

Here is my cursor statement:

CURSOR tax_portal_cursor IS
SELECT * FROM web_payment@remotedb WHERE caps_code IN (
SELECT * FROM TABLE(CAST(l_caps_codes AS myTableType)) ) AND
processed_datetime IS NULL
FOR UPDATE OF processed_datetime;

I later fill the object using this statement:

SELECT CAST(MULTISET(SELECT DISTINCT(tax_cde) FROM
iris_acct_charge@remotedb) as myTableType)
INTO l_caps_codes
FROM dual;

I then try to update the record with this statement:

UPDATE web_payment@remotedb
SET processed_datetime = SYSDATE
WHERE CURRENT OF tax_portal_cursor;

I receive the following error:

ERROR at line 1:
ORA-02015: cannot select FOR UPDATE from remote table
Any ideas here? Select for Update is a pretty common function. Is it
because of the object type???

Thanks,

Arthur
Jul 19 '05 #1
3 9336


Arthur wrote:
Hey Everyone,

Is this an Oracle bug?

Here is my cursor statement:

CURSOR tax_portal_cursor IS
SELECT * FROM web_payment@remotedb WHERE caps_code IN (
SELECT * FROM TABLE(CAST(l_caps_codes AS myTableType)) ) AND
processed_datetime IS NULL
FOR UPDATE OF processed_datetime;

I later fill the object using this statement:

SELECT CAST(MULTISET(SELECT DISTINCT(tax_cde) FROM
iris_acct_charge@remotedb) as myTableType)
INTO l_caps_codes
FROM dual;

I then try to update the record with this statement:

UPDATE web_payment@remotedb
SET processed_datetime = SYSDATE
WHERE CURRENT OF tax_portal_cursor;

I receive the following error:

ERROR at line 1:
ORA-02015: cannot select FOR UPDATE from remote table
Any ideas here? Select for Update is a pretty common function. Is it
because of the object type???


'FOR UPDATE' leads to a row level lock being taken out - you probably
don't need such a thing. The error message is saying that you aren't
allowed to take out row level locks on objects at the other end of a
database link. Try removing the line "FOR UPDATE OF processed_datetime".

-------------------------------------------------------------------------------
Remove FRUITBAT for a valid Email address..

Orinda Software make "OrindaBuild", which generates Java JDBC access
code for calling PL/SQL procedures. www.orindasoft.com

Jul 19 '05 #2
Well, if you take out the 'for update' clause, then how do you know
which row to update? That is where the 'current of' clause comes into
play and is useful.....

Arthur

D Rolfe <dw*************@orindasoft.com> wrote in message news:<40**************@orindasoft.com>...
Arthur wrote:
Hey Everyone,

Is this an Oracle bug?

Here is my cursor statement:

CURSOR tax_portal_cursor IS
SELECT * FROM web_payment@remotedb WHERE caps_code IN (
SELECT * FROM TABLE(CAST(l_caps_codes AS myTableType)) ) AND
processed_datetime IS NULL
FOR UPDATE OF processed_datetime;

I later fill the object using this statement:

SELECT CAST(MULTISET(SELECT DISTINCT(tax_cde) FROM
iris_acct_charge@remotedb) as myTableType)
INTO l_caps_codes
FROM dual;

I then try to update the record with this statement:

UPDATE web_payment@remotedb
SET processed_datetime = SYSDATE
WHERE CURRENT OF tax_portal_cursor;

I receive the following error:

ERROR at line 1:
ORA-02015: cannot select FOR UPDATE from remote table
Any ideas here? Select for Update is a pretty common function. Is it
because of the object type???


'FOR UPDATE' leads to a row level lock being taken out - you probably
don't need such a thing. The error message is saying that you aren't
allowed to take out row level locks on objects at the other end of a
database link. Try removing the line "FOR UPDATE OF processed_datetime".

-------------------------------------------------------------------------------
Remove FRUITBAT for a valid Email address..

Orinda Software make "OrindaBuild", which generates Java JDBC access
code for calling PL/SQL procedures. www.orindasoft.com

Jul 19 '05 #3
am****@iwc.net (Arthur) wrote in message news:<8b**************************@posting.google. com>...
Well, if you take out the 'for update' clause, then how do you know
which row to update? That is where the 'current of' clause comes into
play and is useful.....

Arthur

no, the 'current of' clause refers to the CURSOR.
It is unrealted to the FOR UPDATE.

ed
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by kalamos | last post: by
5 posts views Thread by PAUL | last post: by
2 posts views Thread by gimme_this_gimme_that | last post: by
3 posts views Thread by Arthur | last post: by
5 posts views Thread by Chris Cowles | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.