6 Replies Latest reply: Mar 8, 2013 1:28 AM by user346369 RSS

    issue with ROWID

    Mark Malakanov (user11181920)
      There is an quite old app that runs in Forms 3.0.
      Currently it runs on Oracle 7. but it is decided to migrate DB to Oracle 10.2. I know it is not supported combination.
      We have migrated data, retargeted forms and tested.
      Most of forms work well.
      However some forms issue ORA-01410 Wrong ROWID.
      We traced them in Oracle and found that some forms issue "select for update" queries:
      "SELECT A,B,C FROM TBL WHERE ROWID=:0001 FOR UPDATE A,B,C NOWAIT"
      The issue is that the bind variable :0001 is VARCHAR type, not ROWID type.
      In this case BLOCK.ROWID is converted to string value of old format 'XXXX.XXX.XXXXX' that Oracle does not like in WHERE clause. Other SQLs that use binds of ROWID type work well.
      It is interesting that some forms issue such queries, some do not.
      I tried to find the query in .INP files - it is not there.
      The erro happens when Forms issues COMMIT_FORM, so I suppose that it is Forms itself, not the App, generates these SQLs and bind vars.
      Is my assumption correct?
      Is there any way to make Forms to use ROWID type for such binds? May be by using some keys, or by defining invisible field ROWID of type rowid?
      Or is it possible to customize locking SQL statement in ON-LOCK trigger?
      Or may be it will be more safe to migrate forms to v.4.5?
      Any ideas are welcome.
      thanks.
        • 1. Re: issue with ROWID
          HamidHelal
          Mark Malakanov (user11181920) wrote:
          There is an quite old app that runs in Forms 3.0.
          Currently it runs on Oracle 7. but it is decided to migrate DB to Oracle 10.2. I know it is not supported combination.
          We have migrated data, retargeted forms and tested.
          Most of forms work well.
          However some forms issue ORA-01410 Wrong ROWID.
          We traced them in Oracle and found that some forms issue "select for update" queries:
          "SELECT A,B,C FROM TBL WHERE ROWID=:0001 FOR UPDATE A,B,C NOWAIT"
          The issue is that the bind variable :0001 is VARCHAR type, not ROWID type.
          In this case BLOCK.ROWID is converted to string value of old format 'XXXX.XXX.XXXXX' that Oracle does not like in WHERE clause. Other SQLs that use binds of ROWID type work well.
          It is interesting that some forms issue such queries, some do not.
          I tried to find the query in .INP files - it is not there.
          The erro happens when Forms issues COMMIT_FORM, so I suppose that it is Forms itself, not the App, generates these SQLs and bind vars.
          Is my assumption correct?
          Is there any way to make Forms to use ROWID type for such binds? May be by using some keys, or by defining invisible field ROWID of type rowid?
          Or is it possible to customize locking SQL statement in ON-LOCK trigger?
          Or may be it will be more safe to migrate forms to v.4.5?
          Any ideas are welcome.
          thanks.
          try to upgrade to latest supported version. I think there is no other issue you have.

          hope this helps
          Hamid
          • 2. Re: issue with ROWID
            Mark Malakanov (user11181920)
            try to upgrade to latest supported version. I think there is no other issue you have.
            Hi Hamid,
            Will Forms 11g convert Forms V3 without issues?
            There are also user exits in ProC. Will be Forms11g compatible with them? Or do we have to make adjustments in ProC code?
            • 3. Re: issue with ROWID
              tony.g
              Hi
              Will Forms 11g convert Forms V3 without issues?
              No there are many issues, though much of the code will convert relatively easily unless you are using V2 style triggers
              There are also user exits in ProC. Will be Forms11g compatible with them? Or do we have to make adjustments in ProC code?
              Forms11g works with user exits in just the same way, often some minor Pro*C recoding might be required, but again the bulk of the Pro*C code is relatively unchanged.

              rgds
              Tony

              www.formsmigration.com
              • 4. Re: issue with ROWID
                Andreas Weiden
                Additionally, you cannot migrate directly from Forms 3 to Forms 11G. You have to make some intermediate steps, though i don't know the correct versions.
                • 5. Re: issue with ROWID
                  Mark Malakanov (user11181920)
                  As a workaround can we use ON-LOCK trigger to substitute an SQL, that is generated by Forms, by our SQL?

                  for example
                  Forms generates following SQL:
                  "SELECT A,B,C FROM TBL WHERE ROWID=:0001 FOR UPDATE A,B,C NOWAIT"

                  And we add trigger

                  DEFINE BLOCK My_Block
                  DEFINE TRIGGER ON-LOCK
                  SELECT A,B,C FROM TBL WHERE ROWID=My_Block.rowid FOR UPDATE A,B,C NOWAIT;
                  END DEFINE TRIGGER;
                  ...
                  • 6. Re: issue with ROWID  (Forms 3)
                    user346369
                    Amazingly old app you have running! I think it was nearly 15 years ago, I converted a number of Forms 3 forms to Forms 4.5. I believe there was quite a bit of manual work involved, but it has been sooo long ago, I don't recall everything.

                    That rowid issue during commit (and at other times) cropped up because Oracle changed the rowid format somewhere along the way.... Oracle 8, I believe. All our forms needed to be recompiled within a new version to get everything working again.

                    I think you need to upgrade your forms before you can run in Oracle 10. Not sure if you can go from Forms 3 to Forms 6 -- you might need to migrate to Forms 4.5 first. The 4.5 forms open and compile in Forms 6 Builder, and Forms 6 will then open/compile in Forms 10. Only you can determine which of those steps you can jump over.

                    I sure hope you can find a working version of Forms 4.5 and 6i!