11 Replies Latest reply: Mar 4, 2013 3:37 PM by Mark Malakanov (user11181920) RSS

    How to change transparently a restricted ROWID to extended one?

    Mark Malakanov (user11181920)
      For example Client 7 application sends query to Oracle 10g:
      SELECT A,B,C FROM SCM.TBL WHERE ROWID=:R;
      and it puts old restricted rowid into :R.

      How to transparently change such query to
      SELECT A,B,C FROM SCM.TBL WHERE ROWID=DBMS_ROWID.ROWID_TO_EXTENDED(:R,'SCM,'TBL',0);
      ???

      I tried to create view, tried Advanced Query Rewrite, nothing works.

      Please do not advise me to upgrade client and DB to 11g. The Application uses Client 7 (and its short rowids), it cannot use another one, unfortunately. And of course we cannot modify the query in the App.
      thanks,
        • 1. Re: How to change transparently a restricted ROWID to extended one?
          rp0428
          C'mon - you've posted more than enough to know you need to provide your full 4 digit Oracle version not just a major number.
          >
          For example Client 7 application sends query to Oracle 10g:
          SELECT A,B,C FROM SCM.TBL WHERE ROWID=:R;
          and it puts old restricted rowid into :R.

          How to transparently change such query to
          SELECT A,B,C FROM SCM.TBL WHERE ROWID=DBMS_ROWID.ROWID_TO_EXTENDED(:R,'SCM,'TBL',0);
          ???

          I tried to create view, tried Advanced Query Rewrite, nothing works.

          Please do not advise me to upgrade client and DB to 11g. The Application uses Client 7 (and its short rowids), it cannot use another one, unfortunately. And of course we cannot modify the query in the App.
          >
          What is a 'Client 7 application' is and why does it store ROWIDs. ROWIDs are a a pseudo-feature of the DB; clients don't have them unless they get them from a DB.

          And explain why, at this late date, you suddenly have a need to access extended ROWID values.
          • 2. Re: How to change transparently a restricted ROWID to extended one?
            Mark Malakanov (user11181920)
            full 4 digit Oracle version not just a major number.
            10.2.0.5
            What is a 'Client 7 application' is and why does it store ROWIDs. ROWIDs are a a pseudo-feature of the DB; clients don't have them unless they get them from a DB.
            well, it is Forms 3.0. In some cases it issues
            SELECT A,B,C FROM SCM.TBL WHERE ROWID=:R;
            and it puts a rowid into :R bind of VARCHAR2 type(!) and it is formatted in old restricted format 'xxxx.xxx.xxxxxxx'.
            Sure it took ROWID previously into a block field.

            it is definitely a bug. but I doubt that a patch is available.
            And explain why, at this late date, you suddenly have a need to access extended ROWID values.
            Because they are all extended starting from Oracle 8.
            If a restricted value is provided, the statement fails with ORA-01410.
            • 3. Re: How to change transparently a restricted ROWID to extended one?
              JohnWatson
              Can yo use conversions such as this:
              orcl> ed
              Wrote file afiedt.buf
              
                1  select ename,
                2  rowid,
                3  dbms_rowid.ROWID_TO_RESTRICTED(rowid,0),
                4  dbms_rowid.ROWID_TO_EXTENDED(dbms_rowid.ROWID_TO_RESTRICTED(rowid,1),'SCOTT','EMP',0)
                5* from emp where rownum=1
              orcl> /
              
              ENAME      ROWID              DBMS_ROWID.ROWID_T DBMS_ROWID.ROWID_T
              ---------- ------------------ ------------------ ------------------
              ADAMS      AAAUgqAAFAAAA2UAAK 00000D94.000A.0005 AAAUgqAAFAAAA2UAAK
              
              orcl>
              --
              John Watson
              Oracle Certified Master DBA
              http://skillbuilders.com
              • 4. Re: How to change transparently a restricted ROWID to extended one?
                Jonathan Lewis
                Mark Malakanov (user11181920) wrote:
                full 4 digit Oracle version not just a major number.
                10.2.0.5


                it is definitely a bug. but I doubt that a patch is available.
                You can't call it a bug when the combination has never been on the support matrix.
                As far as I recall, the last version where Forms 3.0 was supported was 7.3.4 - though I think there were some workarounds for 8i.
                I don't think it was even possible to get it working at all on 9i.

                Regards
                Jonathan Lewis
                • 5. Re: How to change transparently a restricted ROWID to extended one?
                  Mark Malakanov (user11181920)
                  You can't call it a bug when the combination has never been on the support matrix.
                  I agree, officially, yes it is not a bug, because it does not impact a supported DB.
                  But, looking from programming culture perspective, why first "select" statement gets rowid value into a block field of type rowid, and later following "select for update where rowid=:0001" sets :0001 bind var of varchar2 type?
                  Huh?
                  As far as I recall, the last version where Forms 3.0 was supported was 7.3.4 - though I think there were some workarounds for 8i. I don't think it was even possible to get it working at all on 9i.
                  Is there a difference in ROWIDs in 9i and 8i?

                  It almost works in 10g. all other SQLs that use ROWID with bind vars of type ROWID work perfectly well.
                  Except of that one, that uses varchar2 type bind.

                  I'd happy to find these workarounds for 8i.
                  But currently we are thinking about migration to Forms4.5.
                  • 6. Re: How to change transparently a restricted ROWID to extended one?
                    Mark Malakanov (user11181920)
                    Can yo use conversions such as this:
                    I can and I would like to. But where? I cannot modify runform30 binary code.
                    • 7. Re: How to change transparently a restricted ROWID to extended one?
                      Jonathan Lewis
                      Mark Malakanov (user11181920) wrote:
                      As far as I recall, the last version where Forms 3.0 was supported was 7.3.4 - though I think there were some workarounds for 8i. I don't think it was even possible to get it working at all on 9i.
                      Is there a difference in ROWIDs in 9i and 8i?
                      I'm workng from memory from a long time ago, but I think the problem that really wrecked things for 9i was something to do with the SQL*net connection dialogue, not with the rowids.

                      Regards
                      Jonathan Lewis
                      • 8. Re: How to change transparently a restricted ROWID to extended one?
                        rp0428
                        >
                        And explain why, at this late date, you suddenly have a need to access extended ROWID values.
                        Because they are all extended starting from Oracle 8.
                        >
                        So does that mean you just upgraded the DB from Oracle 7 to Oracle 8 and that is why the problem just started?
                        >
                        And of course we cannot modify the query in the App.
                        >
                        Well then you have NO solution available except changing the client app.

                        The query includes ROWID in the filter predicate. If you can't change that to use a DBMS_ROWID function then the DB (8 or later) will interpret that to mean the extended ID and the only thing that can possibly match is an extended ID on the other side of the predicate. And you can't create a column named ROWID in either the table or a view to try to fool things.

                        As you have already shown the only way to get an extended Id onthe other side of the predicate is the DBMS_ROWID.ROWID_TO_EXTENDED function but that would have to be done by the client also.
                        • 9. Re: How to change transparently a restricted ROWID to extended one?
                          Mark Malakanov (user11181920)
                          So does that mean you just upgraded the DB from Oracle 7 to Oracle 8 and that is why the problem just started?
                          Yes. We migrated data from O7 to O10g. The application (Forms 3.0) is still on Oracle Client 7.2. Most of queries that use ROWID there work perfectly fine. Because they use bind of type ROWID. But some SQLs, that are generated by runform executable setting ROWID=:0001 bind of type VARCHAR2 and provide there value in old 'XXXXX.XXX.XXXXX' format. These ones Oracle does not like.
                          Well then you have NO solution available except changing the client app.
                          it is Oracle Forms 3.0 (runform30 executable itself). We cannot change it.
                          The query includes ROWID in the filter predicate. If you can't change that to use a DBMS_ROWID function then the DB (8 or later) will interpret that to mean the extended ID and the only thing that can possibly match is an extended ID on the other side of the predicate.
                          Oracle does transparently convert bind vars of type ROWID.
                          But it does not convert bind vars of type VARCHAR2 that have same value (visually same value). It issues ORA-01410.

                          I am thinking why Oracle does not like this? With binds typed as ROWID values are looks very same, they still have 'XXXXX.XXX.XXXXX' format in trace file, but value type is not VARCHAR. May be it is just how trace shows them, and internally they have ObjectID?
                          Again, what prevents Oracle to translate old rowids, that were passed as varchar, to new rowids internally and transparently? It already knows to which table the restricted rowid should belong to. Why oracle cannot convert it transparently without issuing ORA-1410 error?
                          And you can't create a column named ROWID in either the table or a view to try to fool things.
                          I can. I tried, i can create view with column named "ROWID". But Oracle ignores it and passes real table ROWID, not a value I've programmed in the view.
                          As you have already shown the only way to get an extended Id onthe other side of the predicate is the DBMS_ROWID.ROWID_TO_EXTENDED function but that would have to be done by the client also.
                          My initial question was - Is there something that we can use to substitute SQL A to SQL B, both return identical result set, and take same bind parameters.
                          Advanced Rewrite does not work, it does not like bind parameters and ROWID.
                          • 10. Re: How to change transparently a restricted ROWID to extended one?
                            rp0428
                            >
                            My initial question was - Is there something that we can use to substitute SQL A to SQL B, both return identical result set, and take same bind parameters.
                            Advanced Rewrite does not work, it does not like bind parameters and ROWID.
                            >
                            Well I'm about out of ideas (which is pretty unusual for me).

                            Unless you can create a rowid translation lookup table (containing the extended rowid and the corresponding restricted rowid) and get it in the middle using a view you may not be able to do this in 10g.

                            In vanilla 11.2.0.1.0 the CREATE_ROWID shows two incompatible formats for the extended vs restricted formats
                            select dbms_rowid.ROWID_TO_RESTRICTED(rowid,0)from  dept where deptno = 20
                            
                            00000087.0001.0004
                            
                            select dbms_rowid.rowid_create(1, 73199, 4, 135 ,1) from dept where deptno = 20
                            
                            00000087.0001.0004
                            
                            select dbms_rowid.rowid_create(0, 73199, 4, 135 ,1) from dept where deptno = 20
                            
                            AAAR3vAAEAAAACHAAB
                            So you can't use the restricted in a query like this
                            select * from dept where rowid in (
                            select dbms_rowid.rowid_create(0, 73199, 4, 135 ,1) from dept where deptno = 20)
                            
                            ORA-01410: invalid ROWID
                            There might be a remote possibility of use a SQL Profile to do what you need to do but don't know.

                            If you were using 11g you might be able to use SQL Plan Baselines to do it. I know you aren't using 11g but you mind find something useful in these articles by Maria Colgan, a member of Oracle's Optimizer development team. You can search for other articles by her.

                            She covers a lot of the internals of the optimizer; how it works and how to get it to do things.

                            What is the difference between SQL Profiles and SQL Plan Baselines?
                            https://blogs.oracle.com/optimizer/entry/what_is_the_different_between

                            SQL Plan Management (Part 1 of 4) Creating SQL plan baselines
                            https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines
                            • 11. Re: How to change transparently a restricted ROWID to extended one?
                              Mark Malakanov (user11181920)
                              There might be a remote possibility of use a SQL Profile to do what you need to do but don't know.
                              SQL Profile stores bunch of hints for a given SQL. I do not see how it can be helpful in our situation.