Forms 6 problems when calling DB stored program units.

From: Matt B. <gtimatt_at_home.com>
Date: Fri, 03 Aug 2001 01:55:00 GMT
Message-ID: <Uvna7.32619$MC1.9175705_at_news1.elcjn1.sdca.home.com>

We have been running into a lot of problems with our forms at runtime in relation to when they call stored program units in the database and I was wondering if anyone else had had this problem and if there's a reasonable solution. Checked Metalink and also googled this and didn't find a solution that seemed to deal with this specific issue.

What happens is that if a stored procedure or function (standalone or in a package) uses ROWTYPE in the parameter and/or return value declaration and the row's structure changes from one database to another, the form will error at the call to the function or procedure if the form isn't recompiled on the other database (or even if it's the same database and the row's structure has changed).

For example...

Create a small table with a character and a numeric column:

create table test_table(TEST_FIELD_VARCHAR varchar2(30), TEST_FIELD_NUMBER number(30));

Insert a row and commit it:

insert into test_table values('test data', 300); commit;

Create a stored function that selects the row and returns it to the caller using ROWTYPE:

create or replace function get_test_table_rec return test_table%ROWTYPE IS
  v_test_table_rec test_table%ROWTYPE;
begin
  select *
  into v_test_table_rec
  from test_table;
  return v_test_table_rec;
end;
/

Create a simple form that calls this function. I made it have two text items (one character and one numeric, corresponding to the database items in the table) and on WHEN-NEW-FORM-INSTANCE call the above function:

DECLARE
  v_test_rec TEST_TABLE%ROWTYPE;
BEGIN
  synchronize;
  v_test_rec := get_test_table_rec;
  :block1.test_field_varchar := v_test_rec.test_field_varchar;   :block1.test_field_number := v_test_rec.test_field_number; END; Run the form and you'll see it runs fine - the data is returned from the database and put into the text items.

However, here's where the problem comes in. If the table's columns are swapped around, the form errors (and does so with inconsistent errors).

First I dropped the table:

drop table test_table;

I created it again but with the columns in reverse order:

create table test_table(TEST_FIELD_NUMBER number(30), TEST_FIELD_VARCHAR varchar2(30));

Insert data again and commit:

insert into test_table values(300, 'test data'); commit;

Run the form again, but do *not* regenerate it. Use the one you already have generated.

The first error I get when the WHEN-NEW-FORM-INSTANCE fires is an ORA-04020:

04020, 00000, "deadlock detected while trying to lock object %s%s%s%s%s"
// *Cause: While trying to lock a library object, a deadlock is detected.
// *Action: Retry the operation later.

I get this error even though I'm running the form with a new database connection/session.

So I then bounce the database and I then get an ORA-04062 when running the form:

04062, 00000, "%s of %s has been changed"
// *Cause: Attempt to execute a stored procedure to serve
// an RPC stub which specifies a timestamp or signature that is
// different from the current timestamp/signature of the procedure.
// *Action: Recompile the caller in order to pick up the new timestamp.

I'm semi-confused at the different error messages (why the ORA-04020?), but even more so, the one about the signature doesn't make a lot of sense to me (well, it makes sense, but I'm not sure I agree with it).

Our database is set with REMOTE_DEPENDENCIES_MODE set to SIGNATURE.

What we'd like to happen is we'd like the form to not "care" about the order of columns in the table. What it seems to be doing is at compile time it sees that a rowtype is being returned and it somehow "memorizes" that the varchar2 column came first and the number one came second. So when the columns are swapped, the signature apparently changes. What we'd like to happen is that we'd like the form (and the database function it calls) to only "know" that we're dealing with a TEST_TABLE%ROWTYPE and nothing more and have the underlying columns and their order and datatype be resolved at runtime. However it seems that they are being resolved at compile time. Is there any sort of database setting or Developer/Forms setting or environment variable that can indicate to the form at compile time and/or runtime to do this? I can sort of see where the signature did change in terms of the TEST_TABLE%ROWTYPE having columns in a different order, but at the same time I can also see where it didn't change (at least in my mind) because both before and after, the stored function and the form that made the call were just dealing with TEST_TABLE%ROWTYPE, but unfortunately each seems to be going one step further at compile time and actually looking at the columns themselves.

The reason for this happening (the swapped columns) is that we develop on a development database and deploy the form on a production database. Tables can get their columns switched around between the databases, for example, when developer A adds a column to a table, developer B adds another column to the same table, but the scripts they wrote might get run in opposite order on the production database. In addition, our DBAs usually drop and recreate tables on production databases when doing index re-orgs, and they sometimes get the columns in a different order when they do that.

I guess basically what we're looking for is some kind of runtime binding of types rather than compile-time binding of types. Can forms be set to do this, such as via a compile/generation argument or some kind of environment variable? Granted even if it can, it's probably a performance thing (it'd need to check at runtime at each call), but at this time we prefer the idea of using a rowtype without having to worry about the order of the underlying columns, so we're OK with a performance drain in this case.

We have run into this problem both on DG/UX UNIX in character mode and on WinNT in GUI mode (the above example I gave is in GUI on WinNT).

About the only thing that isn't consistent is the errors we get. In the case above, I got the ORA-4020 and ORA-4062. We've also got other errors such as ORA-06502 (numeric or value error, probably where a character was put into a number type when a numeric and character column were switched around), and even ORA-00600 at times too. Not sure why the errors tend to differ. One guess is that maybe if I used a stored program unit where ROWTYPE was a parameter instead of a return value, maybe that'd give me one of these other errors?

Lastly, here's the version info of Form Builder and the database:

Form Bulder:
Forms [32 Bit] Version 6.0.5.33.0 (Production) Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production

     With the Partitioning option
     JServer Release 8.1.6.1.0 - Production

Oracle Toolkit Version 6.0.5.33.3 (Production) PL/SQL Version 8.0.5.1.0 (Production)
Oracle Procedure Builder V6.0.7.2.0 Build #135 - Production PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production) Oracle Query Builder 6.0.5.6.0 - Production Oracle Virtual Graphics System Version 6.0.5.32.0 (Production) Oracle Tools GUI Utilities Version 6.0.5.32.0 (Production)

Oracle Multimedia Version 6.0.5.33.0 (Production)
Oracle Tools Integration Version 6.0.5.32.0 (Production)
Oracle Tools Common Area Version 6.0.5.32.1

Oracle CORE Version 4.0.5.0.0 - Production

Database:

Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option
JServer Release 8.1.6.1.0 - Production

Thanks in advance for any help!

-Matt Received on Fri Aug 03 2001 - 03:55:00 CEST