SQL Forms 3 and Oracle 8 - SUMMARY

From: AMcC <compobs_use_compobs_at_ihug.co.nz>
Date: Tue, 18 Jan 2000 12:25:18 +1300
Message-ID: <3883A4DE.96EFE011@ihug.co.nz>

(originally posted 6/1999, reposted with additional info)

BRIEFLY

---

We worked around some of the problems, but some still remain. A summary of our approach and the outstanding problems below - hopefully for the mutual benefit of ourselves, if anybody reading this has a working solution, and of anybody who is starting down the road we have taken.

(If I receive any useful tips I will update my summary and
repost. Note anti-spam header to email address)

• updates : -------------------------------

18/1/2000 : I received some information from Isaac Chocron re point number 5) , indicating he found a solution - he writes :

"Recently we emigrated from Oracle 7.1.5 on Open VMS 6 to Oracle 8.0.5 on Open VMS 7.2
 in client/server configuration (Our Forms 3 are in another Open VMS 6 computer ).
 Your Summary help us very much.
 The currently open problem is the unpredictably ORA-03113.  You indicate that the problem was ORA-07445. (We see ORA-00602 on our trace files)
 There is a patch to solve this problem."

(In our case we ended up converting these forms to 4.5, mostly
because of Y2K signoff requirements from some of our "powers-that-be".

This went reasonably well, the only sticking point being a Pro*C User Exit that we can no longer compile on the new SUN iron because it no longer has a C compiler,and we are not willing to fork out for the Sun's C development environment (and don't have the guts to get GNU going). )

• end updates --------------------------------

DETAILS

---

1. use SQL*Net to connect to Oracle 8 - e.g.

   runmenu50 scott/tiger_at_8_tns_name my_menu    runform30 scott/tiger_at_8_tns_name my_form    

   (or I think the equivalent TWO_TASK environment variable setting...     something like

    $TWO_TASK=8_tns_name ; export TWO_TASK # Unix Bourne shell example

   allows you to run the normal command without change

   runmenu50 my_menu
   runform30 my_form )

2) the SQL generated internally by SQL*Forms uses the "rowid"   pseudo-column. The format of this has changed in Oracle 8.   You can't use rowid in SQL*Forms 3.0, and you have to stop   SQL*Forms from generating SQL code that uses rowid in a   "where" clause.

  This means modifying locking, updating and deleting behaviour.   (If you only query and insert, you are OK)

  A workaround is to replace the default locking, updating and deleting   behaviour, using ON-LOCK, ON-UPDATE and ON-DELETE triggers (assuming   that you have a practical primary key for your tables , that you can   use to replace rowid).

  Technically we have found this works, and can be done reasonably   tidily using special PL/SQL procedures, called from the   triggers. The code - e.g. for the on-update trigger in particular

• can readily be generated from the data dictionary SQL*Forms tables, assuming the form is stored in the database. (Would be easier if PL/SQL supported dynamic SQL, however because not, essentially each table needs its own dedicated procedure)

  (There is the question of whether this is economic to do - for   us it would have been given the number of forms and the   time-to-live of the application, however it may not   be economic in many cases)

  (I understand that there is an Oracle patch for the rowid problem   for Forms 4.x and higher ?)

3) We found we got run-time error with statements like

   select sysdate into :forms_field from dual;

• a workaround seems to be to declare a PL/SQL variable and use like this :

   today date;

   select sysdate into today from dual;    :forms_field := today;

   (Previously, we found the built-in SQL*Forms date variable stopped    working when we moved from v6 to 7.2)    

4) We found the SQL*Forms development tool core-dumped if connected

   to 8. We have not tested the command line compiler.    So even if run-time to 8 works, application maintenance may not be    possible unless there is a an Oracle instance available that can    support the forms compiler.

5) At this point we thought we had it licked. On testing everything

   worked well most of the time, however we would get    ORA-03113 errors unpredicatbly. It turns out this error is    just a derivative error, caused by the process on the Oracle 8    back-end crashing, and disconnecting the client SQL*Forms 3.0

   We checked the trace files on Oracle 8 generated by the crash and the    actual error that causes the back-end crash was ORA-07445

6) We have been advised by Oracle support that the problem is

   because of incompatabilities between the 7.2 libraries used by    SQL*Forms 3, and Oracle 8. (Don't quite understand as I thought only    SQL hit the back-end. Maybe the client does remote procedure calls as well and

   the method signatures have changed ? Out of my depth here, as to why    libraries on the client affect the backend server). Possible solutions

   suggested were :

1. some of these issues addressed in 8.05 - upgrade (we are using 8.0.4.3.0)
2. set it up like this :

     SQL*Forms 3.0 -----------> Database 7.3 ------------> Oracle 8
                     SQL*Net                    DBLink

• we are currently working on b) (We only have a 7.2 instance) 7) Another approach we tried was running SQL*Forms 3.0 against our 7.2 database, and setting up DBLinks from 7.2 to 8.

   Pretty early on, we got

   ORA-01002: fetch out of sequence

   This is not an uncommon error (having seen it quite a bit in these    newgroups), and its possible there is a simple workaround, however we    abandoned this approach at this point as it seems unlikely 7.2 DBLinks to

   8 will be very hassle free, given the large gap. Received on Mon Jan 17 2000 - 17:25:18 CST