Plug & play tabular form handling

I still remember the time when I was an APEX newbie and I really got confused with tabular forms (updateable reports). They behaved very different from what I knew about “normal” page items. So what where the issues which confused me?

  • It isn’t possible to create any validations on the columns of the report, like I can do it for page items.
  • You have to use the APEX_Application.g_fxx arrays instead of using bind variables as for page items.
  • To find the correct array you have to look into the generated HTML code for the correct mapping.
  • If the order of the columns gets changed, your code gets screwed up, because the columns now maps to a different g_fxx array.
  • If a column has a condition or authorization the g_fxx array changes again, in the case the column doesn’t get rendered.
  • The code isn’t really readable if there are just references to g_f01, g_f02, …
  • The format mask has to be duplicated if the g_fxx is accessed and you want to get the correct data type (eg NUMBER, DATE)
  • When you want to get the number of array entries you have to take care that you don’t use the array for a checkbox column, because that just contains the checked values!
  • You have no function to find out if the row has changed.
  • When the validation of a format mask or required column fails, the error message is very internal. The user doesn’t have a clue which column and which row was effected. And it stops after the first error.

That’s a long list and that’s why I did some programming on the weekend to get a solution for the above short comings, so that newbies and experienced APEX developers don’t have to care anymore.

To make a long story short, I have created a package which you just call once in your application and which takes care of the above problems. It allows you to

  • reference your columns by name without having to hassling around to which array your column is mapped.
  • To get the correct number of rows to process
  • Identify which row has changed
  • Get your data with the correct data type (NUMBER, DATE) without having to take care about format masks.
  • Add a required column check by declaration, instead of coding it.
  • Get more meaningfull error message than APEX provides.

But first try it by yourself, I have set up a tabular form with the default APEX handling for tabular forms. Follow the instructions and play around with it.

Standard Tabular Form example

After you have done that, check out my

Enhanced Tabular Form example.

How to integrate it into your application?

Follow the steps in my How to integrate posting.

In the case if you don’t want that one of your report regions is processed by the library, add $APEXLIB_IGNORE_VALIDATION$ in the region comment to skip the processing.

That’s the basic integration which does the required column and format mask check. If you want to access your column in your own validation code, follow the example to see how to do that.

Example

  1. Create a new page
    • Page Type: Form/Tabular Form
    • Allowed Operations: Update, Insert and Delete
    • Table / View Name: EMPLOYEES
    • Select Columns(s): Everything except of PHONE_NUMBER, COMMISSION_PCT, MANAGER_ID and DEPARTMENT_ID
    • Primary Key Column1: EMPLOYEE_ID
    • Source Type: Existing trigger
    • Updateable Columns: ALL
    • Confirm everything on the following pages
  2. Open the Report Attributes of the created region.
    • For the columns LAST_NAME, EMAIL, HIRE_DATE, JOB_ID go to the Column Link section and enter $APEXLIB_REQUIRED$ as Link Attributes
    • For the column HIRE_DATE set the following properties
      • Display As: “Date Picker”
      • Date Picker Format Mask: “DD-MM-YYYY” => we use a different format mask than the database has, so that auto date convertion doesn’t work!
      • Element Width: 10
    • For the column JOB_ID set the following properties
      • Display As: Select List (query based LOV)
      • LOV Query:
        SELECT JOB_TITLE AS D, JOB_ID AS R FROM JOBS ORDER BY JOB_TITLE
    • For the column SALARY set the following properties
      • Number / Date Format: FML999G990D00
  3. Let’s create some extra validation, so that you see how easy it is to access your columns. Create a validation for the created page
    • Identify the validation level: Page level validation
    • Select a validation method: PL/SQL
    • Select the type of PL/SQL validation you wish to create: Function Returning Error Text
    • Sequence: 10
    • Validation Name: Validate Hire Date and Salary
    • Error Display Location: On Error Page
    • Validation:
      DECLARE
          vMinSalary NUMBER;
          vMaxSalary NUMBER;
      BEGIN
          FOR ii IN 1 .. ApexLib_TabForm.getRowCount
          LOOP
             IF ApexLib_TabForm.hasRowChanged(ii)
             THEN
                 IF ApexLib_TabForm.DV('HIRE_DATE', ii) > SYSDATE
                 THEN
                     ApexLib_Error.addError
                       ( pError      => 'Hire Date is in the future'
                       , pColumnName => 'HIRE_DATE'
                       , pRow        => ii
                       );
                 END IF;
                 --
                 SELECT MIN_SALARY
                      , MAX_SALARY
                   INTO vMinSalary
                      , vMaxSalary
                   FROM JOBS
                  WHERE JOB_ID = ApexLib_TabForm.V('JOB_ID', ii)
                 ;
                 IF ApexLib_TabForm.NV('SALARY', ii) NOT BETWEEN vMinSalary AND vMaxSalary
                 THEN
                     ApexLib_Error.addError
                       ( pError      => 'Salary has to be between %0 and %1'
                       , pColumnName => 'SALARY'
                       , pRow        => ii
                       , p0          => vMinSalary
                       , p1          => vMaxSalary
                       );
                 END IF;
             END IF;
          END LOOP;
          --
          RETURN ApexLib_Error.getErrorStack;
      END;
    • Error Message: Error occurred.

As you can see in the code, you don’t have to care about date convertion, you are only processing the changed records. The code is still understandable when you look at the code 5 days later, aren’t names more meaningful than just references
to APEX_Application.g_f01? ;-)

You have different getter functions to access your columns. ApexLib_TabForm.V, -NV and -DV have the same purpose as the existing APEX functions which you can use for items. Check out the package specification of the ApexLib_TabForm package, all the functions are documented.

BTW, if you are not satisfied with the error message the validateColumns procedure is raising for required and format mask check. You can define your own error text by creating a message (Shared Components/Text Messages). The name has to be APEXLIB_TABFORM_COLUMN_REQUIRED (Format: Column “%0″ is required.) or APEXLIB_TABFORM_INVALID_FORMAT (Format: Column value of “%0″ doesn’t match to the format “%1″.) The row prefix can be define with APEXLIB_TABFORM_ROW_PREFIX (Format: Row %0: ).

Impact on your existing application

Nothing really changes except that you have better error messages in your tabular form, so it’s no problem to use it also for your existing application.

Update

Known restrictions

  • If you use APEX_Item or HTMLDB_Item to build your own form elements. I can’t map the columns, because I don’t know which index you have used.
  • If a column has a condition or authorization. See below.

Problems?

Enable debugging, it will show you what the library is doing and which columns are mapped to which array. Look for ApexLib_TabForm in the output.

What’s next?

I have now a question. Where do you install the packages? Are you allowed to install them into the FLOWS schema? Because that would make the condition checking a breeze. If there is a big demand to just install the packages into the application schema, I have to re-code the condition/authorization checking, because it’s not accessable from the application schema.

Do you have other ideas and troubles with tabular form handling which you want to automate? Feedback very welcome!

33 thoughts on “Plug & play tabular form handling

  1. Hello Patrick,

    first of all THANK YOU for your work. I always hated it to look at the HTML-Code to find out the right g_fxx number! Let’s say it’s a dark past now! But I cannot get the whole stuff to work. I tried your complete example in

    http://inside-apex.blogspot.com/2006/12/plug-play-tabular-form-handling.html

    but when detecting an input error with the page validation process APEX shows me this usual ugly error page with the red stop sign but with the error message I created in the page process. (Rather than above the report area) I followed your example by creating the application process and by inserting $APEXLIB_REQUIRED$ as link attributes.
    I tried it on APEX Express 2.1.

    Some hint for me ?
    Thanks
    Andree

  2. Hello Patrick,

    nice enhancement !
    One minor thing:
    You forgot to add the grant and synonym for APEX_LIB_ERROR in the HowtoInstall.txt.

    Thanks again!
    Andree

  3. … and once again hello !

    Now I have some more issues to ask for…
    (1)
    I have two columns to check. According to your example I coded both checks with separate calls to ApexLib_Error.addError and at the end of the validation i called RETURN ApexLib_Error.getErrorStack.
    When I start the page I have in one row for any reason an (old) error condition in the first of the two columns. This error is not detected when submited AND THAT IS OK, because the row contents did not change. BUT when I change the contents of the second column of the same row (to a correct or wrong value) the (unchanged) error of the first column is detected. Seems not logical.
    (2)
    You force “Row xx:” in front of the error message. How can I change or suppress it ? Seems not reasonable for a german (or other language than english) application.
    (3)
    You force a “f0x_000y” at the end of the message. Is it a forgotten debug output or did I something wrong ?

    Thank you again ! It’s a really great help to use your tools !
    Keep going on !
    Andree

  4. Hi Andree,

    thanks a lot for letting me know! I have already fixed it and uploaded release V0.12.

    So you are installing it in the FLOWS schema? No problems with your DBA? :-)

    Patrick

  5. Ups, another comment :-)

    1) I can’t really detect if a single column value has changed, because APEX is just generating a checksum for the hole record. But anyway, when you write an IF and read the value of a column, how should the library know that you don’t want to check this column, because it hasn’t changed? :-) Or do you mean the automatic required and format mask check?

    But as I said, single column change detection will not be possible as long as I’m not reading the record from the database… Costs performance…

    2) All the messages the ApexLib library is raising can be translated, add under Shared Components\Text Message entries for APEXLIB_TABFORM_ROW_PREFIX, APEXLIB_TABFORM_COLUMN_REQUIRED and APEXLIB_TABFORM_INVALID_FORMAT. Details about the format and placeholders can be found in the posting.

    3) You see that f0x_000y in your error message? Or only when you are running in debug mode? Or do you also see it in the inplace message? The user normally should see it as long as he is running in debug mode.

    Great that you are really using it! That’s the way to find bugs the fastest way.

    Patrick

  6. Hello Patrick,

    Yes, I installed it, because for the Express Edition on my own machine I AM THE DBA (Hugh!).

    1) I guessed that!
    2) Works !
    3) Until now I did not install the “error page hack” and live with the usual page with the red sign. And there the messages are concatinated with “f0x_000y” (x and y depending on column and row) No debug mode. (APEX Task bar shows “Debug” NOT “No Debug”, so it is switched off)
    If you give me an email address, I send you a screenshot.

    Regards
    Andree

  7. Ok, in the case if you are not using the APEX error page hack yet, it’s clear for me why you see it.

    But if you are not using it, your columns cell will never get highlighted! If you don’t like to use the highlighting, you can call the addError with pColumnName => NULL, pRow => NULL. So that the column information isn’t added to the error message.

    But I will add something into the next version so that this information is always hidden.

    You can find my e-mail address in the profile of the blog.

    Patrick

  8. Hi Patrick,

    (Beginner level)

    I was using the Tabular Form and I found a problem. What can I do to validate this?: if I erase a value from a field and then click on the Submit button. After this an error appears “Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-01407: cannot update (“SUPPORT”.”GK_ACCESS_TYPES”.”ACCESS_TYPE_DESC”) to NULL, update “SUPPORT”.”GK_ACCESS_TYPES” set “ACCESS_TYPE_ID” = :b1, “ACCESS_TYPE_DESC” = :b2 where “ACCESS_TYPE_ID” = :p_pk_col
    “.

    How can I fix this exception?

  9. btw, in my previous comment, that error is displayed when the submit button tries to do an update into a table, but it tries to update a field with a null value… and I want to display a normal error message not that exception page

    thank you

  10. Hi Fury,

    if you are using my ApexLib Framework, just use the $APEXLIB_REQUIRED$ tag to tell the framework that the column is required and it will do all necessary checks that the tabular form column contains a value.

    See above for further details.

    Patrick

  11. Hi Patrick,

    You have done a good job on creating this APEXLIB framework to make our life easier for APEX development. But I have encountered a problem that the APEXLIB_TABFORM functions (like getRowCount, hasRowChanged) will not return proper value if the report region has a condition there. Once I remove the condition for the region, the APEXLIB_TABFORM will be able to return the correct value. Is there any solution for this?

    Thanks.

    Best Regards,
    Terence

  12. Hi Terence,

    that’s a restriction of the current implementation of the ApexLib_Tabform package which is probably removed in a future version – it’s not so easy :-)

    Patrick

  13. Hi Patrick,

    I put a validation on the two select list in tabular form by using g_f01,but this is not working until and unless i will not check the corresponding check box,then I saw your enhancement here and tried with your suggested way i.e by using APEXLIB_TABFORM,but got error :
    ‘identifier ‘APEXLIB_TABFORM.GETROWCOUNT’ must be declared’
    Am I doing something wrong?
    Please suggest.

  14. Patrick, thanks for your help.

    I have a problem workink with tabular forms, can I have a tabular form based on a view, in fact a complex view, and update 2 fields of the view?

    I’m going crazy, because I try to do this and only get the following error:

    Error in mru internal routine: ORA-20001: no data found in tabular form
    Error Unable to process update.
    OK

  15. Hi Miguel,

    are you using apex_item or htmldb_item in your SQL statement of the report?

    Can you put an example on apex.oracle.com?

    Patrick

  16. Patrick,

    I want substitute the g_fxx with a usefull name, but i can’t find an example. The error handling works perfect with e.g. “ApexLib_TabForm.NV(‘SALARY’, ii)”.
    But this is a IF statement.

    How can i change
    —————–
    FOR ii IN 1 .. APEX_Application.g_f04.COUNT
    LOOP
    APEX_Application.g_f04(ii) := ‘N';
    END LOOP;
    —————-
    where g_f04 is SALARY.

    Thanks for answering.

    Bert

  17. Hi,

    I did above example. Everything worked fine.(Apex 3.2)

    But Error messages don’t come above the tabular form.

    I debuged error messages come to error page not tabular form page.

    I tried to understand the code. If I am not wrong these error messages are transfered to tabular form page by cookies. My browser blocks such cookies.

    Is there any solution for this?

    Thanks for everthing

  18. Hi Patrick,
    Can your tabular form solution allow me the ability to access a
    column value in the row, to use in the LOV query of another column in the row? I need a dynamic LOV in my Tabular Form.

    Thanks.

    Bonnie
    (New to Apex)

  19. Hi Patrick,

    It’s a really great enhancement, but I have a big question:
    “When will it be possible to have a condition on a region/item and use your Framework?”
    Or how it is possible to hide a column of a table without using a condition?

    Best Regards,
    Knut

  20. Hi Knut,

    i’m planning to add some basic condition support for the next release of Apexlib (2.1).

    In the meantime you can hide columns of a table using javascript (preferably jQuery).

    Peter

  21. I am new to apex. just learning. I have proble with tabular form. I have On Load-Befor header following pl/sql
    begin
    if APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => ‘CALL_AROUND’ ) then
    APEX_COLLECTION.DELETE_COLLECTION (p_collection_name => ‘CALL_AROUND’ );
    end if;
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
    p_collection_name => ‘CALL_AROUND’,
    p_query => ‘select HOTEL_ID,NAME,PHONE_NUMBER FROM HOTELS order by NAME’ );
    commit;
    end;
    & I have following query in tablur form region source
    select c001, c002, c003 , nvl(c004,0) RATE, nvl(c005,0) PERT_OCCUP
    from apex_collections
    where collection_name=’CALL_AROUND’
    ORDER BY SEQ_ID
    and I have following code at Before Submit -Valication and computation

    declare
    c number default 0;
    begin
    for c1 in (
    select seq_id from apex_collections
    where collection_name = ‘CALL_AROUND’
    order by seq_id) loop
    c := c+1;
    apex_collection.update_member_attribute (p_collection_name=> ‘CALL_AROUND’,
    p_seq=> c1.seq_id,p_attr_number =>4,p_attr_value=> apex_application.g_f04(c) );
    apex_collection.update_member_attribute (p_collection_name=> ‘CALL_AROUND’,
    p_seq=> c1.seq_id,p_attr_number=>5, p_attr_value=>apex_application.g_f05(c) );

    COMMIT;
    end loop;

    FOR C1 IN (
    select to_number(c001) HOTEL_ID,
    to_number(c004) rate,
    to_number(c005) occp
    from apex_collections where collection_name = ‘CALL_AROUND’) loop
    insert INTO CALL_ARROUND (CALL_AROUND_ID,HOTEL_ID,RATE,PER_OCCUPANCY,SHIFT,BUS_DATE)
    VALUES (call_around_seq.nextval,c1.HOTEL_ID,c1.RATE,c1.OCCP,2,SYSDATE);
    end loop;
    commit;
    end;

    When I press submit it says no data found. Also If I hardcode value of p_attr_value with any number then it insert that value in CALL_ARROUND table , but it does not update in Collection.

    Please help on this I spent lots of time , but could not figher it out.
    Thank you.

  22. Hi Patrick,

    Is there anyway to create custom number format mask in report for a column?

    i.e. We had inbuilt Number Date Format like,

    $5,234.10
    -5,234.10
    5,234
    5,234.10

    but I want to display a standard report column as,

    56%
    Rs. 5,234.10

    and can we have an option to make an editable cell as read only on a specific condition like we had in normal text box?

    Thanks,

    Sohil Bhavsar

  23. Hi Patrick,

    I saw your post, I have a query can one create a Tabular form with a search option, means a dynamic tabular form?
    or is there any option to hack interactive report such that I can insert textbox in the columns to see the values of each columns in those textboxes.

    Thanks,
    Tauceef

  24. Hello Patrick,

    Your article answered all my questions. I followed your instruction and get error:
    ORA-06550: line 5, column 20: PLS-00201: identifier ‘APEXLIB_TABFORM.GETROWCOUNT’ must be declared ORA-06550: line 5, column 5: PL/SQL: Statement ignored

    I don’t know why.
    Can you help?

    Thanks
    sherry

  25. Dear Patric

    I have a situation when I use ApexLib 2.1.4 and make data validation in a tabular form exactly as described in this post from you. This everything works perfect in Firefox and Google Chrome, but not in Internet Explorer (8 nor 6) when some of the cell is populated with data from PopUp LOV.
    This is the situation in IE8:
    My table consists of 5 columns, in one of them I use PopUP LOV. When I click to add new row, the row is added to the table. I populate the cells with data, I use the PopUp LOV to select appropriate value and when I click to save the data then the validation process is executed successfully showing that that data is not valid and the error message is displayed properly on the Error Page, but the newly added row disappears from the table and there is yellow mark in the left down corner of the IE8 window showing “Done, but with errors on page”.
    This is the error:
    Webpage error details

    User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)
    Timestamp: Wed, 1 Dec 2010 16:44:21 UTC
    Message: Exception thrown and not caught
    Line: 19
    Char: 44681
    Code: 0
    URI: http://www.unilogsrv.dyndns.org:8080/apex/wwv_flow_file_mgr.get_file?p_security_group_id=1038406578662398&p_fname=ApexLib_Full-min.js

    But,when I populate the cell manually, instead of using the PopUp LOV then everything is all right. I assume that the PopUp LOV is making something on the cell that can’t “get over” IE8.

    I will be very grateful if you help me, or just give me directions how to solve this.

    Another issue is, again in IE8, my validation process checks if all cells in the column 3, have same value which I choose from PopUp LOV. When I change one cell value, lets say from A to B, using my PopUP LOV and all other cells have value A, then the validation process works perfectly, it displays the message in the error page saying that value A in row x and value B in row y don’t match. The cells are colored as expected, BUT the values in the colored cells are both A, although I have selected one to have value B. This is strange.

    Best regards,
    Vane Spasov

  26. Hi Patrick

    Your solution for validation on tabular forms is exactly what I need, however it does not work if there is a condition on the reports region or conditions on any of the form columns. I understand this is a known issue, do you have a timeframe when you expect this to be fixed.

    Thanks,
    Wayne

  27. Hi Wayne,

    TabForms currently can handle only very simple conditions like equal, null, …

    Currently there is no timeframe for supporting all other condition types.

    If this is a blocking point for you just send me an email to apexlib@oracleapex.info and we’ll figure something out.

  28. Hi Peter

    Thanks for your quick response. I have come up with an alternative solution which is acceptable to our users.

    Regards
    Wayne

  29. I’m still getting my feet wet with APEX, but I’m getting in deeper. :)

    We have several tabular forms, each with several date fields. My users tend to fat-finger on at least one of the fields and end up with an extra character placed somewhere (usually the end, but not always) in the date. I would like to find some way to remedy this, either by simply checking the length of the entry, or by fully validating the date.

    I looked at Patrick’s example above, but don’t think it would do what I’m looking for (I could be wrong). We don’t have the ApexLib framework installed…our DBA is still getting his feet wet as a DBA and is a little leary of it. So, what is the best plan of action for my problem?

    Thanks.

Comments are closed.