Re: Select for Update fails

amerar_at_iwc.net (Arthur) wrote in message news:<8b622eae.0404151011.1a438834_at_posting.google.com>...
> Daniel,
>
> Actually that was a typo on my part, but it does not help the problem
> at all. I'm still receiving the error. I'm leaning towards the fact
> that I'm dealing with an object type here....and maybe that is an
> issue.....
>
> Arthur
>
>

I did a test and I found the issue was caused by "SELECT * FROM TABLE(CAST(l_caps_codes AS myTableType)) " inside the cursor definition. I don't know if it's a bug or oracle just doesn't support it. But there is a workaround like this:

   CURSOR tax_portal_cursor IS

      SELECT * FROM web_payment_at_remotedb WHERE caps_code IN (
         select distinct(tax_cde) from iris_acct_charge_at_remotedb ) AND
         processed_datetime IS NULL
      FOR UPDATE OF processed_datetime;

table cast is not necessary here.

Regards,
DX

> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1082012119.771346_at_yasure>...
> > Michel Cadot wrote:
> >
> > > "Arthur" <amerar_at_iwc.net> a écrit dans le message de
> > > news:8b622eae.0404141101.6441e954_at_posting.google.com...
> > >
> > >>Hey Everyone,
> > >>
> > >>Is this an Oracle bug?
> > >>
> > >>Here is my cursor statement:
> > >>
> > >> CURSOR tax_portal_cursor IS
> > >> SELECT * FROM web_payment_at_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_at_remotedb) as myTableType)
> > >> INTO l_caps_codes
> > >> FROM dual;
> > >>
> > >>I then try to update the record with this statement:
> > >>
> > >> UPDATE web_payment_at_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
> > >
> > >
> > > As the message says, it is because it's a remote table.
> > >
> > > Regards
> > > Michel Cadot
> >
> > In addition to which one might ask ... why are you trying to lock the
> > entire table?
Received on Fri Apr 16 2004 - 10:06:55 CDT