Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Monthly Archives: March 2012

Handling the sort event in Advanced Tables

Advanced Tables are the recommended approach for rendering tables in OA Framework since they offer both declarative support in addition to programmatic control. One of the features available in Advanced Tables is the ability to sort columns  by clicking on the column header. In this post, we will discuss how sort events are handled in Advanced Tables.

In general, the following table events are defined for an Advanced Table.
Navigation – Triggered when a user selects the Next or Previous link to navigate between different ranges of rows.
Sorting – Triggered when a user selects a beveled column heading to sort that column in ascending or descending order.
Insertion of a new row – Triggered when a user selects the Add Another Row button.
Recalculate column Total – Triggered when a user selects the Recalculate button to update the column total.
Detail Disclosure – Triggered when a user selects the Hide or Show link to collapse or expand the detail disclosure region.
Table Control – Triggered when a user selects the Action/Navigation button in the table Control bar.

These events are handled during processFormRequest and OA Framework automatically sets the following parameters based on which event was triggered:
SOURCE_PARAM – indicates the source of the event that is generating the current browser request.
EVENT_PARAM – indicates the event generated by the table.
VALUE_PARAM – indicates a value that is relevant to a particular event. For the sort event, it points to the column on which the sorting is done.
SIZE_PARAM – indicates the number of rows currently displayed in the table (relevant only to the navigation event).
STATE_PARAM – indicates the current sort state (ascending or descending) of the column on which sorting is invoked (relevant only for the sort event).
These parameters can be accessed programatically using pageContext.getParameter(<PARAMETER_NAME>). For example, pageContext.getParameter(SOURCE_PARAM) will fetch the name of the Advanced Table from which an event was triggered.

Below is the Advanced Table, xxAdvTab based on the View Object, xxAdvTabVO.

I have added the following code in the processFormRequest section of the controller in order to display the value of the event parameters during runtime.


if ("xxAdvTab";.equals(pageContext.getParameter(SOURCE_PARAM)))
{OAException mesg = new OAException("SOURCE_PARAM= " +pageContext.getParameter(SOURCE_PARAM)
+", EVENT_PARAM= " +pageContext.getParameter(EVENT_PARAM)
+", VALUE_PARAM= " +pageContext.getParameter(VALUE_PARAM)
+", SIZE_PARAM= " +pageContext.getParameter(SIZE_PARAM)
+", STATE_PARAM= " +pageContext.getParameter(STATE_PARAM)
,OAException.INFORMATION);
pageContext.putDialogMessage(mesg);}

1. Clicking on the Employee Name column sorts it in ascending order and sets the values of the parameters.

SOURCE_PARAM stores the name of the Advaced Table, xxAdvTab.
EVENT_PARAM stores the event, which is sort in this case.
VALUE_PARAM stores the column on which the sort was performed, in this case the second column. The value is 1 since the columns are numbered from 0 onwards.
SIZE_PARAM is null since it is used only in the Navigation event.
STATE_PARAM indicates the current sort state, ie, the sort state when the event was triggered. When the event was triggered, the Employee Name was in an unsorted state hence its value is null.

2. Clicking on the Employee Name column again sorts it in descending order and sets the values of the parameters.

All the parameters have the same values except for STATE_PARAM which is now ascending because when the event was triggered the Employee Name column was already sorted in the ascending order.

3. If the Employee Id column is now sorted, the parameters will have the following values.

SOURCE_PARAM stores the name of the Advaced Table, xxAdvTab.
EVENT_PARAM stores the event, which is sort in this case.
VALUE_PARAM shows 0 since the sort was performed on the first column.
SIZE_PARAM is null since it is used only in the Navigation event.
STATE_PARAM shows null since it shows the current sort state of the column on which the sort was invoked. Employee Id was in an unsorted state hence the value is null.

This brings us to a limitation of the STATE_PARAM parameter, it gives us the current sort state of the column which is being sorted. It does not provide the sort state of the Advanced Table as a whole. In the previous step, even though the Advanced Table was sorted based on the Employee Name in descending order, the STATE_PARAM parameter was set to null. One way of working around this is to get the sort status of the Advanced Table from the ORDER_BY clause of the underlying table. This can be done by changing the controller code as shown below:


//get a handle to the View Object
OAViewObject viewObject = (OAViewObject)oam.findViewObject("xxAdvTabVO1");
if ("xxAdvTab".equals(pageContext.getParameter(SOURCE_PARAM)))
{OAException mesg = new OAException("SOURCE_PARAM= " +pageContext.getParameter(SOURCE_PARAM)
+", EVENT_PARAM= " +pageContext.getParameter(EVENT_PARAM)
+", VALUE_PARAM= " +pageContext.getParameter(VALUE_PARAM)
+", SIZE_PARAM= " +pageContext.getParameter(SIZE_PARAM)
+", STATE_PARAM= " +pageContext.getParameter(STATE_PARAM)
//display the ORDER_BY clause of the VO
+", VO ORDER_BY clause= " +viewObject.getOrderByClause()
,OAException.INFORMATION);
pageContext.putDialogMessage(mesg);}

4. Performing Steps#1, 2 and 3 once again provides the following values of the parameters.

The standard parameters still show the same values as in Step#3, but the VO ORDER_BY clause now correctly shows that the Advanced Table was already sorted based on the Employee Name in the descending order when the sort event was triggered.

Refer to the chapter on Advanced Tables in the Oracle Application Framework Developers Guide for additional details.

List of JDR_UTILS APIs

JDR_UTILS is a PL/SQL package which can be used to query and inspect OA Framework metadata in the MDS repository. The complete list of JDR_UTILS APIs are given below.

Sample usage:


SQL> exec jdr_utils.listcustomizations('/oracle/apps/icx/icatalog/shopping/webui/NonCatalogRequestPG');

/oracle/apps/icx/icatalog/shopping/webui/customizations/site/0/NonCatalogRequestPG

PL/SQL procedure successfully completed

SQL>

PROCEDURE deleteEmptyCustomizations;
Deletes all empty customization documents from the repository. An empty customization document is a customization document that does not specify any modifications to the base metadata.

PROCEDURE deleteDocument(p_document VARCHAR2);
Deletes the document from the repository.
p_document – the fully qualified document name, which can represent either a document or package file.

PROCEDURE deletePackage(p_package VARCHAR2);
Deletes the package from the repository if the package is empty. If the package is not empty (i.e. it contains either documents or packages), then an error will be issued indicated that non-empty packages can not be deleted.
p_package – the fully qualified package name

FUNCTION exportDocument(
p_document VARCHAR2,
p_exportFinished OUT BOOLEAN,
p_formatted BOOLEAN DEFAULT TRUE) RETURN VARCHAR2;
Export the XML for a document and pass it back in 32k chunks. This function will return XML chunks, with a maximum size of 32k. Returns the exported XML in 32k chunks
p_document – the fully qualified name of the document.
p_exportFinished – OUT parameter which indicates whether or not the export is finished
p_formatted – TRUE indicates that the XML is formatted nicely

FUNCTION getComponentName(
p_docid jdr_paths.path_docid%TYPE,
p_compid jdr_components.comp_id%TYPE) RETURN VARCHAR2;
Gets the fully qualified name of the component.
p_docid – the ID of the document which contains the component
p_compid – the ID of the component (from comp_id in the jdr_components table)

FUNCTION getDocumentName(
p_docid jdr_paths.path_docid%TYPE) RETURN VARCHAR2;
Gets the fully qualified name of the document.
p_docid – the ID of the document

FUNCTION getTranslations(
p_document VARCHAR2) RETURN translationList;
Gets all of the translations of the specified document.
p_document – the fully qualified document name

PROCEDURE listContents(p_path VARCHAR2, p_recursive BOOLEAN DEFAULT FALSE);
Prints the contents of a package. For the non-recursive case, this will list the documents, package files and package directories. For the recursive case, this will list the document, package files and empty package directories (i.e. packages which contain no documents or child packages). In order to diferentiate documents from package directories, package directories will end with a ‘/’.
p_path – The path in which to list the documents. To specify the root directory, use ‘/’.
p_recursive – If TRUE, recursively lists the contents of sub-directories. Defaults to FALSE.

PROCEDURE listCustomizations(p_document VARCHAR2);
List the customizations for the specified document.
p_document – the fully qualified document name, which can represent either a document or package file.

PROCEDURE listDocuments(p_path VARCHAR2, p_recursive BOOLEAN DEFAULT FALSE);
Internally calls listContents.

PROCEDURE listLanguages(p_document VARCHAR2);
Lists the supported languages for the specified document.
p_document – the fully qualified document name, which can represent either a document or package file.

PROCEDURE printDocument(p_document VARCHAR2,
p_maxLineSize NUMBER DEFAULT MAX_LINE_SIZE);
Prints the contents of a JRAD document to the console.
p_document – the fully qualified document name, which can represent either a document or package file.
p_maxLineSize – the maximum size of line. This defaults to 255 which is the maximim allowable size of a line (the 255 limit is a limitation of the DBMS_OUPUT package).

PROCEDURE printTranslations(p_document VARCHAR2,
p_language VARCHAR2,
p_maxLineSize NUMBER DEFAULT MAX_LINE_SIZE);
Prints the translations for the document in XLIFF format.
p_document – the fully qualified document name, which can represent either a document or package file.
p_language – the language to use for the translations
p_maxLineSize – the maximum size of line. This defaults to 255 which is the maximim allowable size of a line (the 255 limit is a limitation of the DBMS_OUPUT package).

PROCEDURE saveTranslations(
p_document VARCHAR2,
p_translations translationList,
p_commit BOOLEAN := TRUE);
Saves the specified translations for the specified document.This procedure will do the following:
1. Lock the document so as to prevent multiple users attempting to modify translations at the same time
2. Delete all of the translations for the specified document
3. Insert the new translations
4. Commit the data unless p_commit set to FALSE
p_document – the fully qualified document name
p_translations – the list of translations to insert
p_commit – if TRUE, the data is committed. Default is TRUE

Upgrade APEX 3.2.1 to 4.1.1 on Oracle Database 11gR2

My previous post listed the steps I had followed while configuring APEX 3.2.1 in Oracle Database 11gR2. Later, I checked the Oracle APEX website and found that the current version was 4.1.1 and it had quite a few interesting new features. The features seemed exting enough to warrant an upgrade. So, here are the steps for upgrading APEX 3.2.1 to 4.1.1 on Oracle Database 11gR2. The installation guide provides the steps in details.

1. Download APEX and unzip it in your server, preserving the directory names. Here, I have kept the zip file under the base directory /home/oracle.


[oracle@db11gr2 ~]$ pwd
/home/oracle
[oracle@db11gr2 ~]$ ls
apex_4.1.1_en.zip bin data
[oracle@db11gr2 ~]$ unzip apex_4.1.1_en.zip

When you unzip it, a directory called apex will be created under the base directory.


[oracle@db11gr2 ~]$ ls
apex apex_4.1.1_en.zip bin data
[oracle@db11gr2 ~]$

2. Change your working directory to apex. For installing a full development environment, connect as sysdba and run apexins.sql passing the following four arguments:

@apexins tablespace_apex tablespace_files tablespace_temp images

tablespace_apex is the name of the tablespace for the Oracle Application Express application user
tablespace_files is the name of the tablespace for the Oracle Application Express files user
tablespace_temp is the name of the temporary tablespace
images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/

I ran the script using the following parameters:


@apexins SYSAUX SYSAUX TEMP /i/

It took around 20 minutes for the script to complete execution in my environment and it ended with the following messages.


...Begin key object existence check 01:13:31
...Completed key object existence check 01:13:31
...Setting DBMS Registry 01:13:31
...Setting DBMS Registry Complete 01:13:31
...Exiting validate 01:13:31
timing for: Validate Installation
Elapsed: 00:02:04.78
timing for: Development Installation
Elapsed: 00:14:06.07
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db11gr2 apex]$

3. Connect as sysdba and run the apxldimg.sql script to update the images directory. Include, as a parameter, the path to the base directory where the APEX installation file was unzipped in step#1.


SQL&gt; connect sys as sysdba
Enter password:
Connected.
SQL&gt; @apxldimg.sql /home/oracle

PL/SQL procedure successfully completed.

old 1: create directory APEX_IMAGES as '&amp;1/apex/images'
new 1: create directory APEX_IMAGES as '/home/oracle/apex/images'

Directory created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.

timing for: Load Images
Elapsed: 00:01:21.55

Directory dropped.

SQL&gt;

4. Enable network services for APEX. This is not a mandatory step but is required in case you want to access network services from APEX. Please check the installation document for additional details. To allow access to network services, grant connect privileges to any host for the APEX_040100 database user. This can be done by using the following script. Please check the installation guide on how to allow more fine grained access.


DECLARE
 ACL_PATH VARCHAR2(4000);
 ACL_ID RAW(16);
BEGIN
 -- Look for the ACL currently assigned to '*' and give APEX_040100
 -- the &quot;connect&quot; privilege if APEX_040100 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
 WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, ensure that the ACL is valid
 -- (for example, does not contain stale references to dropped users).
 -- If it does, the following exception will be raised:
 --
 -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040100'
 -- ORA-06512: at &quot;XDB.DBMS_XDBZ&quot;, line ...
 --
 SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
 FROM XDB.XDB$ACL A, PATH_VIEW P
 WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
 EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);
 IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040100',
 'connect') IS NULL THEN
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
 'APEX_040100', TRUE, 'connect');
 END IF;

EXCEPTION
 -- When no ACL has been assigned to '*'.
 WHEN NO_DATA_FOUND THEN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
 'ACL that lets power users to connect to everywhere',
 'APEX_040100', TRUE, 'connect');
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

This completes the upgrade leaving you with a new, shiny and feature-filled Oracle APEX 4.1.1 installation.