SQL Forms 3 and Oracle 8 - SUMMARY, and Request for experiences

From: AMcC <compobs_use_compobs_at_ihug.co.nz>
Date: Wed, 23 Jun 1999 13:10:42 +1200
Message-ID: <37703412.EC4B7277@ihug.co.nz>

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)

DETAILS

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 :

some of these issues addressed in 8.05 - upgrade (we are using 8.0.4.3.0)
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 Tue Jun 22 1999 - 20:10:42 CDT