13 Advanced Programming Techniques

About Configuring Oracle Application Express to Send Email

Before you can send email from an Application Builder application, you must:

1. Log in to Oracle Application Express Administration Services and configure the email settings on the Instance Settings page. See "Configuring Email Settings".

2. If you are running Oracle Application Express with Oracle Database 11g release 1 (11.1), you need to enable outbound mail. In Oracle Database 11g release 1 (11.1), the ability to interact with network services is disabled by default. See "Enabling Network Services in Oracle Database 11g".

Sending Email from an Application

You can send an email from an Application Builder application by calling the PL/SQL package APEX_MAIL. This package is built on top of the Oracle supplied UTL_SMTP package. Because of this dependence, in order to use APEX_MAIL, the UTL_SMTP package must be installed and functioning.

APEX_MAIL contains two procedures for manually sending email:

• Use the APEX_MAIL.SEND procedure to manually send an outbound email message from your application

• Use APEX_MAIL.PUSH_QUEUE to deliver mail messages stored in APEX_MAIL_QUEUE

Oracle Application Express stores unsent email messages in a table named APEX_MAIL_QUEUE. You can deliver mail messages stored in this queue to the specified SMTP gateway by calling the procedure APEX_MAIL.PUSH_QUEUE.

Oracle Application Express logs successfully submitted messages in the table APEX_MAIL_LOG with the timestamp reflecting your server's local time.

The following UNIX/LINUX example demonstrates the use of the APEX_MAIL.PUSH_QUEUE procedure using a shell script.

SQLPLUS / <<EOF
APEX_MAIL.PUSH_QUEUE;
DISCONNECT
EXIT
EOF

About the APEX_COLLECTION API

Every collection contains a named list of data elements (or members) which can have up to 50 character attributes (VARCHAR2(4000)), and one large character attribute (CLOB). You insert, update, and delete collection information using the PL/SQL API APEX_COLLECTION.

Creating a Collection

Every collection contains a named list of data elements (or members) which can have up to 50 character attributes (VARCHAR2(4000)), and one large character attribute (CLOB). You use the following methods to create a collection:

• CREATE_COLLECTION

• CREATE_OR_TRUNCATE_COLLECTION

• CREATE_COLLECTION_FROM_QUERY

• APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B

The CREATE_COLLECTION method raises an exception if the named collection already exists, for example:

APEX_COLLECTION.CREATE_COLLECTION(
    p_collection_name => collection name );

The CREATE_OR_TRUNCATE_COLLECTION method creates a new collection if the named collection does not exist. If the named collection already exists, this method truncates it. Truncating a collection empties it, but leaves it in place, for example:

APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
    p_collection_name => collection name,
    p_generate_md5    => YES or NO );

The CREATE_COLLECTION_FROM_QUERY method creates a collection and then populates it with the results of a specified query, for example:

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
    p_collection_name => collection name,
    p_query           => your query );
    p_generate_md5    => YES or NO );

The CREATE_COLLECTION_FROM_QUERY_B method also creates a collection and then populates it with the results of a specified query, for example:

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
    p_collection_name => collection name,
    p_query           => your query );
   

The CREATE_COLLECTION_FROM_QUERY_B method offers significantly faster performance than the CREATE_COLLECTION_FROM_QUERY method by performing bulk SQL operations, but has the following limitations:

• No column value in the select list of the query can be more than 2,000 bytes. If a row is encountered that has a column value of more than 2,000 bytes, an error will be raised during execution.

• The MD5 checksum will not be computed for any members in the collection.

Truncating a Collection

If you truncate a collection, you remove all members from the specified collection, but the named collection remains in place, for example:

APEX_COLLECTION.TRUNCATE_COLLECTION(
    p_collection_name => collection name );

Accessing a Collection

You can access the members of a collection by querying the database view APEX_COLLECTIONS. The APEX_COLLECTIONS view has the following definition:

COLLECTION_NAME   NOT NULL VARCHAR2(255)
SEQ_ID            NOT NULL NUMBER 
C001                       VARCHAR2(4000)
C002                       VARCHAR2(4000)
C003                       VARCHAR2(4000)   
C004                       VARCHAR2(4000)   
C005                       VARCHAR2(4000)  
...
C050                       VARCHAR2(4000)
CLOB001                    CLOB   
MD5_ORIGINAL               VARCHAR2(4000)  

Use the APEX_COLLECTIONS view in an application just as you would use any other table or view in an application, for example:

SELECT c001, c002, c003
   FROM APEX_collections
 WHERE collection_name = 'FIREARMS'

Deleting a Collection

If you delete a collection, you delete the collection and all of its members, for example:

APEX_COLLECTION.DELETE_COLLECTION (
    p_collection_name => collection name );

Be aware that if you do not delete a collection, it will eventually be deleted when the session is purged. For example:

APEX_COLLECTION.DELETE_ALL_COLLECTIONS;

APEX_COLLECTION.DELETE_ALL_COLLECTIONS_SESSION;

Adding Members to a Collection

When data elements (or members) are added to a collection, they are assigned a unique sequence ID. As you add members to a collection, the sequence ID will change in increments of 1, with the newest members having the largest ID.

You add new members to a collection using the ADD_MEMBER function. Calling this function returns the sequence ID of the newly added member. The following example demonstrates how to use the ADD_MEMBER function.

APEX_COLLECTION.ADD_MEMBER(
    p_collection_name => collection name,
    p_c001          => [member attribute 1],
    p_c002          => [member attribute 2],
    p_c003          => [member attribute 3],
    p_c004          => [member attribute 4],
    p_c005          => [member attribute 5],
    p_c006          => [member attribute 6],
    p_c007          => [member attribute 7],
  ...
    p_c050          => [member attribute 50]);
    p_clob001       => [CLOB member attribute 1],
    p_generate_md5  => YES or NO);    

You can also add new members (or an array of members) to a collection using the ADD_MEMBERS method, for example:

APEX_COLLECTION.ADD_MEMBERS(
    p_collection_name => collection name,
    p_c001          => member attribute array 1,
    p_c002          => member attribute array 2,
    p_c003          => member attribute array 3,
    p_c004          => member attribute array 4,
    p_c005          => member attribute array 5,
    p_c006          => member attribute array 6,
    p_c007          => member attribute array 7,
    ...
    p_c050          => member attribute array 50);
    p_generate_md5  => YES or NO);

This method raises an error if the specified collection does not exist with the specified name of the current user and in the same session. Also any attribute exceeding 4,000 characters will be truncated to 4,000 characters. The number of members added is based on the number of elements in the first array.

Updating Collection Members

You can update collection members by calling the UPDATE_MEMBER procedure and referencing the desired collection member by its sequence ID, for example:

APEX_COLLECTION.UPDATE_MEMBER (
    p_collection_name => collection name,
    p_seq             => member sequence number,
    p_c001            => member attribute 1,
    p_c002            => member attribute 2,
    p_c003            => member attribute 3,
    p_c004            => member attribute 4,
    p_c005            => member attribute 5,
    p_c006            => member attribute 6,
    p_c007            => member attribute 7,
    ...
    p_c050            => member attribute 50);
    p_clob001         => [CLOB member attribute 1],  

The UPDATE_MEMBER procedure replaces an entire collection member, not individual member attributes. This procedure causes an error if the named collection does not exist. For example:

Use the p_clob001 parameter for collection member attributes which exceed 4,000 characters.

If you want to update a single attribute of a collection member, use the UPDATE_MEMBER_ATTRIBUTE procedure, for example:

APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(
    p_collection_name       => collection_name,
    p_seq                   => member sequence number,
    p_attr_number           => member attribute number,
    p_attr_value            => member attribute value )

APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(
    p_collection_name        => collection_name,
    p_seq                    => member sequence number,
    p_clob_number            => CLOB member attribute number,
    p_clob_value             => CLOB member attribute value );

Calling the UPDATE_MEMBER_ATTRIBUTE procedure causes an error if the named collection does not exist.

Note that the only valid value for the p_clob_number parameter is 1.

Deleting Collection Members

You can delete a collection member by calling the DELETE_MEMBER procedure and referencing the desired collection member by its sequence ID, for example:

APEX_COLLECTION.DELETE_MEMBER(
    p_collection_name => collection name,
    p_seq             => member sequence number);

Note that this procedure leaves a gap in the sequence IDs in the specified collection. In addition, calling this procedure causes an error if the named collection does not exist.

You can also delete all members from a collection by when an attribute matches a specific value, for example:

APEX_COLLECTION.DELETE_MEMBERS(
    p_collection_name => collection name,
    p_attr_number     => number of attribute used to match for the specified
                         attribute value for deletion, 
    p_attr_value      => attribute value of the member attribute used to 
                         match for deletion);

Note that the DELETE_MEMBER procedure also leaves a gap in the sequence IDs in the specified collection. This procedure causes an error if:

• The named collection does not exist.

• The specified attribute number is outside the range of 1 to 50, or not valid.

If the supplied attribute value is null, then all members of the named collection will deleted.

Determining Collection Status

The p_generate_md5 parameter determines if the MD5 message digests are computed for each member of a collection. The collection status flag is set to FALSE immediately after you create a collection. If any operations are performed on the collection (such as add, update, truncate, and so on), this flag is set to TRUE.

You can reset this flag manually by calling RESET_COLLECTION_CHANGED, for example:

APEX_COLLECTION.RESET_COLLECTION_CHANGED (
    p_collection_name => collection name)

Once this flag has been reset, you can determine if a collection has changed by calling COLLECTION_HAS_CHANGED, for example:

l_changed := APEX_COLLECTION.COLLECTION_HAS_CHANGED(
  p_collection_name => collection_name);

When you add a new member to a collection, an MD5 message digest is computed against all 50 attributes and the CLOB attribute if the p_generated_md5 parameter is set to YES. You can access this value from the MD5_ORIGINAL column of the view APEX_COLLECTION. You can access the MD5 message digest for the current value of a specified collection member by using the function GET_MEMBER_MD5. For example:

APEX_COLLECTION.GET_MEMBER_MD5 (
    p_collection_name => collection name,
    p_seq             => member sequence number );
    RETURN VARCHAR2;

Merging Collections

You can merge members of a collection with values passed in a set of arrays. By using the p_init_query argument, you can create a collection from the supplied query, for example:

APEX_COLLECTION.MERGE_MEMBERS
p_collection_name => collection_name

Note that if the collection exists, the following occurs:

• Rows in the collection not in the arrays will be deleted.

• Rows in the collection and in the arrays will be updated.

• Rows in the array and not in the collection will be inserted.

Any attribute value exceeding 4,000 characters will be truncated to 4,000 characters. Table 13-1 describes the available arguments you can use when merging collections.

Managing Collections

You can use the following utilities to manage collections.

Topics in this section include:

• Obtaining a Member Count

• Resequencing a Collection

• Verifying Whether a Collection Exists

• Adjusting a Member Sequence ID

• Sorting Collection Members

l_count := APEX_COLLECTION.COLLECTION_MEMBER_COUNT (
  p_collection_name => collection name );

APEX_COLLECTION.RESEQUENCE_COLLECTION (
   p_collection_name => collection name )
   

l_exists := APEX_COLLECTION.COLLECTION_EXISTS  (
  p_collection_name => collection name );

APEX_COLLECTION.MOVE_MEMBER_DOWN(
    p_collection_name => collection name,
    p_seq             => member sequence number);

APEX_COLLECTION.SORT_MEMBERS(
    p_collection_name       => collection name,
    p_sort_on_column_number => column number to sort by);

Clearing Collection Session State

Clearing the session state of a collection removes the collection members. A shopping cart is a good example of when you might need to clear collection session state. When a user requests to empty the shopping cart and start again, you need to clear the session state for a collection. You can remove session state of a collection by calling the CREATE_OR_TRUNCATE_COLLECTION method or by using f?p syntax.

Calling the CREATE_OR_TRUNCATE_COLLECTION method deletes the existing collection and then recreates it, for example:

APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
    p_collection_name       => collection name,

You can also use the sixth f?p syntax argument to clear session state, for example:

f?p=App:Page:Session::NO:1,2,3,collection name

Understanding the APEX_PLSQL_JOB Package

APEX_PLSQL_JOB is a wrapper package around DBMS_JOB functionality offered in the Oracle database. Note that the APEX_PLSQL_JOB package only exposes that functionality which is necessary to run PL/SQL in the background. The following is a description of the APEX_PLSQL_JOB package:

SQL> DESC APEX_PLSQL_JOB
FUNCTION JOBS_ARE_ENABLED RETURNS BOOLEAN
PROCEDURE PURGE_PROCESS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_JOB                          NUMBER                  IN
FUNCTION SUBMIT_PROCESS RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SQL                          VARCHAR2                IN
 P_WHEN                         VARCHAR2                IN     DEFAULT
 P_STATUS                       VARCHAR2                IN     DEFAULT
FUNCTION TIME_ELAPSED RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_JOB                          NUMBER                  IN
PROCEDURE UPDATE_JOB_STATUS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_JOB                          NUMBER                  IN
 P_STATUS                       VARCHAR2                IN
 P_DESC                         

Table 13-1 describes the functions available in the APEX_PLSQL_JOB package.

You can view all jobs submitted to the APEX_PLSQL_JOB package using the APEX_PLSQL_JOBS view. The following is the description of APEX_PLSQL_JOBS view:

SQL> DESCRIBE APEX_PLSQL_JOBS
 Name                              Null?    Type
 --------------------------------- -------- ----------------------------
 ID                                         NUMBER
 JOB                                        NUMBER
 FLOW_ID                                    NUMBER
 OWNER                                      VARCHAR2(30)
 ENDUSER                                    VARCHAR2(30)
 CREATED                                    DATE
 MODIFIED                                   DATE
 STATUS                                     VARCHAR2(100)
 SYSTEM_STATUS                              VARCHAR2(4000)
 SYSTEM_MODIFIED                            DATE
 SECURITY_GROUP_ID                          NUMBER

Table 13-4 describes the columns available in APEX_PLSQL_JOBS view.

About System Status Updates

Submitted jobs can contain any of the following system status settings:

• SUBMITTED indicates the job has been submitted, but has not yet started. The DBMS_JOB does not guarantee immediate starting of jobs.

• IN PROGRESS indicates that the DBMS_JOB has started the process.

• COMPLETED indicates the job has finished.

• BROKEN (sqlcode) sqlerrm indicates there was a problem in your job that resulted in an error. The SQL code and SQL error message for the error should be included in the system status. Review this information to determine what went wrong.

Using a Process to Implement Background PL/SQL

The following example runs a PL/SQL job in the background for testing and explanation:

001  BEGIN
002    FOR i IN 1 .. 100 LOOP
003      INSERT INTO emp(a,b) VALUES (:APP_JOB,i);
004      IF MOD(i,10) = 0 THEN
005        APEX_PLSQL_JOB.UPDATE_JOB_STATUS(
006          P_JOB     => :APP_JOB,
007          P_STATUS  => i || 'rows inserted');
008      END IF;
009      APEX_UTIL.PAUSE(2);
010    END LOOP;
011  END;

In this example, note that:

• Lines 002 to 010 run a loop that inserts 100 records into the emp table.

• APP_JOB is referenced as a bind variable inside the VALUES clause of the INSERT, and specified as the P_JOB parameter value in the call to UPDATE_JOB_STATUS.

• APP_JOB represents the job number which will be assigned to this process as it is submitted to APEX_PLSQL_JOB. By specifying this reserved item inside your process code, it will be replaced for you at execution time with the actual job number.

• Note that this example calls to UPDATE_JOB_STATUS every ten records, INSIDE the block of code. Normally, Oracle transaction rules dictate updates made inside code blocks will not be seen until the entire transaction is committed. The APEX_PLSQL_JOB.UPDATE_JOB_STATUS procedure, however, has been implemented in such a way that the update will happen regardless of whether or not the job succeeds or fails. This last point is important for two reasons:

  1. Even if your status shows "100 rows inserted," it does not mean the entire operation was successful. If an error occurred at the time the block of code tried to commit, the user_status column of APEX_PLSQL_JOBS would not be affected because status updates are committed separately.

  2. Updates are performed autonomously. You can view the job status before the job has completed. This gives you the ability to display status text about ongoing operations in the background as they are happening.

Understanding Web Service References

To utilize Web services in Oracle Application Express, you create a Web service reference using a wizard. Web service references can be based either on a Web Services Description Language (WSDL) document or created manually by supplying information about the service.

When you create a Web service reference based on a WSDL, the wizard analyzes the WSDL and collects all the necessary information to create a valid SOAP message, including:

• The URL used to post the SOAP request over HTTP(S)

• A Universal Resource Identifier (URI) identifying the SOAP HTTP request

• Operations of the Web Service

• Input parameters for each operation

• Output parameters for each operation

When you create a Web service reference manually, you supply the necessary information to create a valid SOAP request, including:

• The URL used to post the SOAP request over HTTP(S)

• A Universal Resource Identifier (URI) identifying the SOAP HTTP request

• The SOAP envelope for the request, including any item substitutions

• Optionally the name of a collection to store the response from the Web service

Working with SSL Enabled Web Services

Secure Sockets Layer (SSL) is an industry standard protocol that uses RSA public key cryptography in conjunction with symmetric key cryptography to provide authentication, encryption, and data integrity.

If the Web service that you need to interact with is SSL-enabled (that is, https displays in the URL to the Web service), you must create a wallet. A wallet is a password-protected container that stores authentication and signing credentials (including private keys, certificates, and trusted certificates) needed by SSL.

Creating a Web Service Reference Based on a WSDL

When you create a Web service reference based on a WSDL, you need to decide how to locate the WSDL. You can locate a WSDL in two ways:

• By searching a Universal Description, Discovery and Integration (UDDI) registry

• by entering the URL to the WSDL document

A UDDI registry is a directory where businesses register their Web services.

Topics in this section include:

• Creating a Web Service Reference by Searching a UDDI Registry

• Creating a Web Service Reference by Specifying a WSDL Document

• Creating a Web Service Manually

Using the Web Service Reference Repository

Web service references are stored in the Web Service Reference Repository.

To access the Web Service References Repository:

1. On the Workspace home page, click the Application Builder icon.

2. Select an application.

   Application Builder appears.

3. Click Shared Components.

   The Shared Components page appears.

4. Under Logic, click Web Service References.

   The Web Service Reference page appears.

   Use the Navigation bar at the top of the page to search for Web service references or change the page display. Available options include:

   • Web Service Reference - Enter a case insensitive query for the reference name and click Go. To view all Web service references, leave the field blank and click Go.

   • View - Select a display mode and click Go. Available options include:

     • Icons (the default) displays each Web service reference as a large icon. To edit a Web service reference, click the appropriate icon.

     • Details displays each Web service reference as a line in a report.

5. Select Details from the View list and click Go.

6. In Details view you can:

   • Edit a reference by clicking the reference name.

   • Test a reference by clicking the Run icon.

   • View details about a reference by clicking the View icon. Note that this option is not available for manually created Web service references.

Testing a Web Service Reference Created from a WSDL

After you have created a Web service reference, you can test it on the Test Web Service Reference page.

To test a Web service reference:

1. Navigate to the Web Service References page. See "Accessing the Web Service References Page".

2. From View, select Details.

3. Click the Run icon adjacent to the Web Service reference name.

   The Test Web Service Reference page appears. The Web service name and URL endpoint display at the top of the page.

4. From Operation, select an operation (that is, the method to be executed).

5. Under Input Parameters, enter the appropriate value.

6. Click Test.

   The message request and response appear at the bottom of the page.

Testing a Web Service Reference Created Manually

After you have created a Web service reference, you can test it on the Test Web Service Reference page.

To test a Web service reference:

1. Navigate to the Web Service References page. See "Accessing the Web Service References Page".

2. From View, select Details.

3. Click the Run icon adjacent to the Web Service reference name.

   The Test Web Service Reference page appears. The Web service name and URL endpoint display at the top of the page.

4. If required, enter the username and password under Basic Authentication.

5. In SOAP Envelope text area, optionally edit the SOAP request envelope.

6. Click Test.

   The message request and response appear at the bottom of the page.

Creating an Input Form and Report on a Web Service

The Create Form and Report on Web Service Wizard creates an input form, a submit button, and a report for displaying results. You can execute this wizard directly after creating the Web service reference from a WSDL, or by adding a new page.

Use this wizard when you expect a nonscalar result from the Web service. The Amazon Web service is a good example. This Web service returns many results based on the search criteria entered in an input form.

Creating a Form on a Web Service

The Create Form on Web Service Wizard creates a form and a submit button. You can execute this wizard after creating the Web service reference from a WSDL, or from the Page Definition.

Use this wizard when you expect a scalar result from the Web service. A Web service that looks up a stock price is a good example because the input is a stock symbol and the output is the scalar value price.

Invoking a Web Service as a Process

You can also implement a Web service as a process on the page. Running the process submits the request to the service provider. You can then display the request results in report.

To invoke a Web service as a process:

1. Create a new page. See "Managing Pages in an Application".

   In the Create Page Wizard:

   1. Select Blank Page.

   2. When prompted to use tabs, select No.

2. Navigate to the Page Definition. See "Accessing a Page Definition".

3. Under Page Rendering, Processes, click the Create icon.

   The Create Page Processes Wizard appears.

4. From the process category, select Web Services.

5. Specify a process name, sequence, and processing point.

6. Select the Web service reference.

   If the Web reference was created from a WSDL, perform the following additional steps.

7. Select the Web service reference and operation (that is, the method to be executed).

8. Define the process. You can store the results in a collection or in items on the page by selecting options under Web Service Output Parameters.

   1. To store the results in a collection:

      • For Store Result in, select Collection.

      • Enter a name for the collection in the value field.

   2. To store the results in items on the page:

      • For Store Result in, select Items.

      • Enter the appropriate items value in the fields provided.

9. Click Create Process.

Editing a Web Service Process

After you create a process of type Web service on a Web service reference created from a WSDL, you can map input parameters to a static value (for example to pass a key) by editing the Web service process.

To edit a Web service process:

1. Create a Web service process. See "Invoking a Web Service as a Process".

2. Navigate to the Page Definition containing the Web service process.

3. Select the process name.

   The Edit Page Process page appears.

4. To map an input parameter to a static value:

   1. Scroll down to Web Service Input Parameters.

   2. Enter a value in the Value field, adjacent to the appropriate parameter name.

5. Click Apply Changes.

Viewing a Web Service Reference History

The Web Services History displays changes to Web service references for the current application by application ID, Web service references name, developer, and date.

To view a history of Web service reference changes:

1. On the Workspace home page, click the Application Builder icon.

2. Select an application.

   Application Builder appears.

3. Click Shared Components.

   The Shared Components page appears.

4. Under Logic, click Web Service References.

5. Click History.