This discussion is archived
6 Replies Latest reply: Mar 7, 2013 11:28 PM by user346369 RSS

issue with ROWID

Mark Malakanov (user11181920) Expert
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points