Select for Update fails

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Arthur
    #1

    Select for Update fails

    Hey Everyone,

    Is this an Oracle bug?

    Here is my cursor statement:

    CURSOR tax_portal_curs or IS
    SELECT * FROM web_payment@rem otedb WHERE caps_code IN (
    SELECT * FROM TABLE(CAST(l_ca ps_codes AS myTableType)) ) AND
    processed_datet ime IS NULL
    FOR UPDATE OF processed_datet ime;

    I later fill the object using this statement:

    SELECT CAST(MULTISET(S ELECT DISTINCT(tax_cd e) FROM
    iris_acct_charg e@remotedb) as myTableType)
    INTO l_caps_codes
    FROM dual;

    I then try to update the record with this statement:

    UPDATE web_payment@rem otedb
    SET processed_datet ime = SYSDATE
    WHERE CURRENT OF tax_portal_curs or;

    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
    Tags: None
    • D Rolfe
      #2
      Re: Select for Update fails



      Arthur wrote:
      Hey Everyone,
      >
      Is this an Oracle bug?
      >
      Here is my cursor statement:
      >
      CURSOR tax_portal_curs or IS
      SELECT * FROM web_payment@rem otedb WHERE caps_code IN (
      SELECT * FROM TABLE(CAST(l_ca ps_codes AS myTableType)) ) AND
      processed_datet ime IS NULL
      FOR UPDATE OF processed_datet ime;
      >
      I later fill the object using this statement:
      >
      SELECT CAST(MULTISET(S ELECT DISTINCT(tax_cd e) FROM
      iris_acct_charg e@remotedb) as myTableType)
      INTO l_caps_codes
      FROM dual;
      >
      I then try to update the record with this statement:
      >
      UPDATE web_payment@rem otedb
      SET processed_datet ime = SYSDATE
      WHERE CURRENT OF tax_portal_curs or;
      >
      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_datet ime".

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

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



        Comment

        • Arthur
          #3
          Re: Select for Update fails

          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 <dwrolfeFRUITBA T@orindasoft.co mwrote in message news:<407DA715. 5040808@orindas oft.com>...
          Arthur wrote:
          >
          Hey Everyone,

          Is this an Oracle bug?

          Here is my cursor statement:

          CURSOR tax_portal_curs or IS
          SELECT * FROM web_payment@rem otedb WHERE caps_code IN (
          SELECT * FROM TABLE(CAST(l_ca ps_codes AS myTableType)) ) AND
          processed_datet ime IS NULL
          FOR UPDATE OF processed_datet ime;

          I later fill the object using this statement:

          SELECT CAST(MULTISET(S ELECT DISTINCT(tax_cd e) FROM
          iris_acct_charg e@remotedb) as myTableType)
          INTO l_caps_codes
          FROM dual;

          I then try to update the record with this statement:

          UPDATE web_payment@rem otedb
          SET processed_datet ime = SYSDATE
          WHERE CURRENT OF tax_portal_curs or;

          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_datet ime".
          >
          -------------------------------------------------------------------------------
          Remove FRUITBAT for a valid Email address..
          >
          Orinda Software make "OrindaBuil d", which generates Java JDBC access
          code for calling PL/SQL procedures. www.orindasoft.com

            Comment

            • Ed prochak
              #4
              Re: Select for Update fails

              amerar@iwc.net (Arthur) wrote in message news:<8b622eae. 0404151013.22d5 a3aa@posting.go ogle.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

                Comment

                Previous template Next
                Working...