Forum Stats

  • 3,872,109 Users
  • 2,266,380 Discussions
  • 7,911,050 Comments

Discussions

yet another Apex 21.2 bug? - Validation on Tabular forms

massi_cic
massi_cic Member Posts: 7 Green Ribbon

Hi all,

we have an application written in apex 4.2 where we have a page containing a tabular form with a validation on data, type: Function body (returning error text)

Tabular form source query:

select ID_PROCESS,
TICKET_NUM_PROBLEM,
QUESTION,
dbms_lob.substr(ANSWER,4000,1) ANSWER
from PROCESS_QUESTIONNAIRE
where ticket_num_problem=replace(:P86_TICKET_NUM_PROBLEM,'=',':')

Validation source code:

DECLARE
sid_chk varchar2(40); sid_hidden varchar2(500); 
tmp boolean default true;  n_riga integer;

BEGIN
   FOR i IN 1 .. apex_application.g_f01.COUNT
   LOOP							
        sid_hidden:=apex_application.g_f01(i);
        if sid_hidden is null then 
        tmp := false;
        n_riga := i;
        end if;
   end loop;
    if tmp then 
       return null;
    else 
       return 'Please fill all fields';
    end if;
END;

The problem is that when importing the application in apex 20.2, everything is OK, but importing in apex 21.2.1 , when validation fires and shows the error 'Please fill all fields', the page tries to re-display the tabular forms giving the following error:

report error:
ORA-20001: Error fetching column value: ORA-01403: no data found

Looking into debug log , the code is the following:

Any help would be appreciated :)

Max

Answers

  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,990 Gold Crown

    besides the obvious fact that tabular forms are not really supported in the version of apex you are trying to do this in? I would suggest looking at converting your tabular form to a grid, it will be easier t o maintain the future..

    Thank you!

    Tony Miller

    White Rock, NM

  • massi_cic
    massi_cic Member Posts: 7 Green Ribbon

    Thank you Tony fro the quick response!

    Of course we are already converting tabular forms to interactive grids in some cases (we know they are no longer supported), but since we have a quite huge migration project of many applications from apex v4 to apex 21 we are struggling to make this upgrade as smooth and quick as possible, limiting any unnecessary modification to the source application...

    Thank you anyway :)

  • tirone
    tirone Member Posts: 185 Bronze Badge

    Hi Max,

    did you find a solution to your problem?

    I should face the same situation in my company (migration of many applications from Apex 4.2.4.00.08 to Apex 21.2) and I would not like to face the same problem.

    Thank you

    regards

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,391 Employee

    Hi,

    ... I hate to say it, but you're in a very gray area with this combination of a declarative tabular form and PL/SQL access to the APEX_APPLICATION.G_F01 array. Actually that very combination is not supported, and - although it worked - it never really was.

    Supported variants were and are:

    1 Declarative Tabular Form with Declarative Validations

    That means that the region is a "Tabular Form" (now Legacy Tabular Form) region type, and the query is a plain SQL Query. The column definitions determine the form item type to use when rendering the columns. Validations are done by setting the Tabular Form as the Editable Region and by referencing columns with their names - :COLUMN_NAME, as in the following example.

    2 Coded Tabular Form

    That means, that your region is a plain Classic Report, and the SQL Query explicitly uses the functions in the APEX_ITEM package to generate form input fields. In the calls to APEX_ITEM.TEXT or others, you explicitly specify an index number - this determines whether that column comes up as the APEX_APPLICATION.G_F01, G_F02 or other array.

    Your usage looks like a mix of the two. You have a declarative tabular form, and for those, there is no explicit mapping between a tab form column and the concrete APEX_APPLICATION.G_FXX array. Your code relies on that APEX provides the values in the G_F01 array, although that is nowhere stored.

    For that reason this approach (mix of declarative form and PL/SQL usage of APEX_APPLICATION.G_FXX) arrays is actually unsupported. I would indeed recommend to move towards Interactive Grid, as others already did.

    You might try out whether moving the validation to a declarative one resolves your issue, as I believe that this validation and how it executes is the cause of the issue. If you can stage a test case on apex.oracle.com, which reproduces the error, I can also have a look and see whether I can help.

    Best regards

    -Carsten