Custom Code for Tabular Forms (Part 2)

The previous article covered how to create a Tabular Form with manual code rather than automatic row processing. This article will demonstrate how to change the Tabular Form to modify data from multiple tables using the same Tabular Form.

Modify Tabular Form

Using the example from the previous article, modify the Tabular Form and change the query to:

select
  e.empno,
  e.empno empno_display,
  e.ename,
  e.sal,
  d.dname
from emp e, dept d
where 1=1
  and e.deptno = d.deptno

Edit DNAME

Edit the newly added DNAME column with the following changes:

Modify Page process

Change the page process to use the code snippet below. Note: This isn't the best example, as the below code will update the department name for each modified employee record. It does highlight is that you can reference and modify data from multiple tables.

if :empno is null then
  -- code to insert emp
  null;
else
  update emp
  set
    ename = :ename,
    sal = :sal
  where empno = :empno;

  -- Update dept name
  update dept d
  set dname = :dname
  where 1=1
    and d.deptno = (
      select e.deptno
      from emp e
      where e.empno = :empno)
end if;

If you've ever developed a true manual tabular form using collections, the above approach covered in this article may be a better alternative to manage and maintain.

Custom Code for Tabular Forms (Part 1)

Over a year ago I wrote an article covering how to create a tabular form and then use custom PL/SQL to process the data rather than the automatic Apply MRU and Apply MRD processes. The demo showed how to do this in APEX 4.2. This article will re-introduce the topic but use APEX 5.0 instead.

Create new Tabular Form

• Create page > Page type: Form > Tabular Form
• Select the following options: 

• Note: For simplicity/demo purposes, limiting to just the SAL and ENAME columns.
• Primary Key: Select Primary Key Column(s) > Primary Key Column 1 > EMPNO
• Run through the rest of the wizard.

Remove Automatic DML

Since custom code will be used to process the page, delete the automatic row processing process as shown below.

Create Process

Create a new process with the following settings (the Source is included below the image).

if :empno is null then
  -- code to insert emp
  null;
else
  update emp
  set
    ename = :ename,
    sal = :sal
  where empno = :empno;
end if;

Using the above technique you can now use a tabular form to call custom PL/SQL code rather than the automatic row processing.

The next article will cover how to modify data from multiple tables in the same Tabular Form.

APEX_JSON.PARSE Issue with CLOBs and 11g

Update: this issue is documented on the APEX 5 Known Issues page (bug 20974582) and has a patch for users with access to MOS (bug 20931298).

APEX 5.0 comes with some new APIs including APEX_JSON which manages JSON files.

When using APEX_JSON to consume a JSON file, the process is fairly straight forward:
- Parse: This parses the JSON and stores it in a record type.
- Read values in the JSON object by providing the path to each name/value pair.

The APEX_JSON documentation has some great examples which covers the above process in more details.

There is one "bug" that I found with APEX_JSON.PARSE when passing in a CLOB that has more than 8191 characters.  The reason is APEX_JSON.PARSE uses DBMS_LOB.SUBSTR behind the scenes which has a known bug in 11g R1, 11g R2, and 11g XE. In 12c onwards, this is not an issue. An example of the error that is raised is:

ORA-20987: Error at line 626, col19: Expected ":", seeing "<varchar2>"

ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 292
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 560
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 756
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 774
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 624
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 719
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 615
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 758
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 774
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 811
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 887
ORA-06512: at line 24

The APEX_JSON.PARSE procedure currently has two supported overloaded methods. One which takes in a varchar2, and the other which takes in a CLOB. Thankfully there's a third (undocumented) method which takes in a table of varchar2. Note: in future versions of APEX, this may be a supported procedure.

Here's a sample snippet on how to convert your CLOB to a varchar2 table and then call APEX_JSON.PARSE:

declare
  l_clob clob;
  l_json apex_json.t_values;

  l_temp varchar2(1000);

  l_clob_tab apex_application_global.vc_arr2;

begin

  l_clob := 'load json data here';

  -- Convert clobtotable
  declare
    c_max_vc2_size pls_integer := 8100; -- Bug with dbms_lob.substr 8191

    l_offset pls_integer := 1;
    l_clob_length pls_integer;
  begin
    l_clob_length := dbms_lob.getlength(l_clob);

    while l_offset <= l_clob_length loop
      l_clob_tab(l_clob_tab.count + 1) :=
        dbms_lob.substr (
         lob_loc => l_clob,
         amount => least(c_max_vc2_size, l_clob_length - l_offset +1 ),
         offset => l_offset);

      l_offset := l_offset + c_max_vc2_size;
    end loop;
  end;


  -- Parse clob as JSON
  apex_json.parse(
    p_values => l_json,
    p_source => l_clob_tab,
    p_strict => true);


  -- You can now use the JSON object for l_json
end;

Note: the code to convert the CLOB to the table will be available soon as part of the new OraOpenSource OOS_UTILS project.

Thanks to Christian Neumüller for helping resolve this issue.

APEX 5: Creating Sub-Menus

APEX 5 has gotten rid of the old APEX tabs concept and instead replaced it with the APEX Navigation Menu. This is a very positive change and, tied in with the new Universal Theme it, certainly makes life easier.

One thing that I really like is the simplicity to create sub-menus. They're various ways to create sub-menus. This post will cover creating sub-menus from both the page creation wizard and via the Shared Components > Navigation Menu options.  There will be three pages in this example: parent, child, and grand child.

To start, I'll assume that you have one page and one Navigation Menu item called Parent. Next, create a second page called Child using the wizard. In the wizard, configure the Navigation Menu options as shown below:

Finish the page creation wizard. When you run your app you'll now see the Parent/Child menu and sub-menu.

Create a third page, called Grand Child. This time when you get the the Navigation Menu wizard option you can't select the Child page as it's parent. It is shown in the list, but greyed out. To get around this issue, just select "No Parent Selected" and finish the wizard. 

To make the Grand Child page a sub-menu of Child:

• Go to Shared Components > Navigation Menu. 
• Edit the Grant Child list entry.
• Select Child for the Parent List Entry and click Apply Changes.

If you rerun the application you should now see the updated sub-menu structure.

The above example show the default Navigation Menu with the new Universal Theme, which is a side menu. To make it into a top navigation menu with a drop down sub-menus:

• Go to Shared Components > User Interface Attributes.
• Edit the Desktop theme.
• Click on the Navigation Menu tab, and make the following changes:

If you refresh the page the Navigation Menu will now be at the top of the page and has the drop down menu/sub-menu.