Thursday, June 10, 2010

Forms fail to generate after 10g RDBMS upgrade [cannot pass cursor variables to a procedure that is called through a database link]

After 10g RDBMS upgrade some forms fail to generate. An example is shown in the image, where you can see the generation is stuck compiling a trigger.
The relevant Metalink note is 300990.1:
Compiling Form Fails or FRM-10760 When Using TYPE Declarations In Procedure Called Via DB Link.

Although the note refers to Forms 9.0 to 10.1, the problem also occurs in Forms 6i, since it is a RDBMS functionality change:

In Oracle Server 10g and higher restrictions have been placed on cursor variables. To quote from the Oracle Server 10g documentation, Chapter 6 Performing SQL Operations from PL/SQL:
Restrictions on Cursor Variables
You cannot pass cursor variables to a procedure that is called through a database link.
The restriction therefore applies to TYPE declarations, %TYPE and %ROWTYPE used in a remote database procedure.

In the trigger, in which the compilation hungs, there is a call to a procedure:

XXE_F36_LLU_WCRM.prc_Get_LocationInfo

In this package there are declarations, like:

prec_cli IN w_llu_ll_order@datallu%ROWTYPE
p_wcrm_order_id IN w_llu_ll_order.order_id@datallu%TYPE

w_llu_ll_order is a table in a remote database, accessed via a DB link.
These declarations have to be replaced.
I tried 2 workarounds, which both work.
The first is to create local views that point to the remote table, like:

create view apps.v_w_llu_ll_order as select * from w_llu_ll_order@datallu;

and then replace the above declarations:

prec_cli IN v_w_llu_ll_order%ROWTYPE
p_wcrm_order_id IN v_w_llu_ll_order.order_id%TYPE

The second is to check the column types in the remote table and explicitly set the variable types to match them:

p_wcrm_order_id IN number(10)

Now, the form generation will not hung.