By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,502 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,502 IT Pros & Developers. It's quick & easy.

Select for Update fails

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a


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

P: n/a
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

P: n/a
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.