About DML Locking

When automatic data manipulation language (DML) is used in Oracle Application Express to update or delete rows of a table, a transaction is initiated to first lock the row, verify if it has changed since it was displayed on the page, and then finally issue the actual UPDATE or DELETE statement for the row.

In some environments where locking of rows is prevalent, you may want to control the DML operation and determine if the DML operation:

waits indefinitely

fails immediately

waits for a specified period of time

About APEX_DML_LOCK_WAIT_TIME

You can set the value of an application substitution string, an application item, or a page item to APEX_DML_LOCK_WAIT_TIME to control the DML operation. The following values are supported:

If null (the default), results in the same behavior as previous versions of Oracle Application Express, that is, wait indefinitely.

If 0, fail immediately if the row is locked by another database session.

If > 0 and the row is locked, wait for the specified number of seconds.

When set in an application, the value for APEX_DML_LOCK_WAIT_TIME applies to all UPDATE and DELETE DML operations using Automatic DML in the entire application. To control a specific Automatic DML process, update the value of APEX_DML_LOCK_WAIT_TIME before the Automatic DML process and reset it after the Automatic DML process. Note that this does not affect updates and deletes using tabular forms.

About FSP_DML_LOCK_ROW

You can also set the value of an application substitution string, an application item, or a page item to FSP_DML_LOCK_ROW to control the DML operation. The following values are supported:

If the value is set to FALSE, then no SELECT FOR UPDATE will be issued.

If the value is anything other than FALSE, the default behavior of SELECT FOR UPDATE is performed when issuing an UPDATE or DELETE DML operation using Automatic DML.

Accessing Data with Database Links

Because the Workspace home page runs in an Oracle database, you have access to all distributed Oracle database capabilities. Typically, you perform distributed database operations using database links.

A database link is a schema object in one database that enables you to access objects on another database. Once you have created the database link you can access the remote objects by appending @dblink to the table or view name where dblink is the Database Link Name you specify in the Create Database Object Wizard.

Note:

By default, the CREATE DATABASE LINK system privilege is not granted to a provisioned workspace or database user. To use this feature, a DBA or administrator needs to grant this specific privilege to the database user in the user's workspace. See "Creating Database Links" in Oracle Database Administrator's Guide

To create a database link:

On the Workspace home page, click SQL Workshop and then Object Browser.

Object Browser appears.

Click Create.

Select Database Link and click Next.

Follow the on-screen instructions.

Note that Database Link names must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore.

To view an existing a database link:

On the Workspace home page, click SQL Workshop and then Object Browser.

About Configuring Oracle Application Express to Send Email

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

Log in to Oracle Application Express Administration Services and configure the email settings on the Instance Settings page. See in Oracle Application Express Administration Guide.

If you are running Oracle Application Express with Oracle Database 11g release 1 (11.1), you must 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".

Tip:

You can configure Oracle Application Express to automatically email users their login credentials when a new workspace request has been approved. To learn more, see "Specifying a Provisioning Mode"in Oracle Application Express Administration Guide.

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.

Use APEX_MAIL.SEND to manually send an outbound email mess age from your application

Use APEX_MAIL.ADD_ATTACHMENT to send an outbound email message from an application as an attachment. APEX_MAIL.ADD_ATTACHMENT

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.

Using Collections

Collections enable you to temporarily capture one or more nonscalar values. You can use collections to store rows and columns currently in session state so they can be accessed, manipulated, or processed during a user's specific session. You can think of a collection as a bucket in which you temporarily store and name rows of information.

The following are examples of when you might use collections:

When you are creating a data-entry wizard in which multiple rows of information first need to be collected within a logical transaction. You can use collections to temporarily store the contents of the multiple rows of information, before performing the final step in the wizard when both the physical and logical transactions are completed.

When your application includes an update page on which a user updates multiple detail rows on one page. The user can make many updates, apply these updates to a collection and then call a final process to apply the changes to the database.

When you are building a wizard where you are collecting an arbitrary number of attributes. At the end of the wizard, the user then performs a task that takes the information temporarily stored in the collection and applies it to the database.

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.

About Collection Naming

When you create a collection, you must give it a name that cannot exceed 255 characters. Note that collection names are not case-sensitive and will be converted to uppercase.

Once the collection is named, you can access the values in the collection by running a SQL query against the view APEX_COLLECTIONS.

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

CREATE_COLLECTION_FROM_QUERY_B

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

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

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.

About the Parameter p_generate_md5

Use the p_generate_md5 flag to specify if the message digest of the data of the collection member should be computed. By default, this flag is set to NO. Use this parameter to check the MD5 of the collection member (that is, compare it with another member or see if a member has changed).

Deleting a Collection

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

Deleting All Collections for the Current Application

Use the DELETE_ALL_COLLECTIONS method to delete all collections defined in the current application, for example:

APEX_COLLECTION.DELETE_ALL_COLLECTIONS;

Deleting All Collections in the Current Session

Use the DELETE_ALL_COLLECTIONS_SESSION method to delete all collections defined in the current session., for example:

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.

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.

About the Parameters p_generate_md5 and p_clob001

Use the p_generate_md5 flag to specify if the message digest of the data of the collection member should be computed. By default, this flag is set to NO. Use this parameter to check the MD5 of the collection member (that is, compare it with another member or see if a member has changed).

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

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_MEMBERS 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 be deleted where the attribute (specified by p_attr_number) is null.

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:

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:

Array of first attribute values to be merged. Maximum length is 4,000 characters. If the maximum length is greater, it will be truncated to 4,000 characters.

The count of elements in the P_C001 PL/SQL table is used as the total number of items across all PL/SQL tables. For example, if P_C001.count = 2 and P_C002.count = 10, only 2 members will be merged. Note that if P_C001 is null, an application error will be raised.

p_c0xx

Attribute of XX attributes values to be merged. Maximum length is 4,000 characters. If the maximum length is greater, it will be truncated to 4,000 characters.

p_null_index

Use this argument to identify rows the merge function should ignore. This argument identifies an row as null. Null rows are automatically removed from the collection.

p_null_value

Use this argument with the p_null_index. Identifies the null value. If used this value cannot be null. A typical value for this argument is 0.

p_init_query

Use the query defined by this argument to create a collection if the collection does not exist.

Verifying Whether a Collection Exists

Adjusting a Member Sequence ID

You can adjust the sequence ID of a specific member within a collection by moving the ID up or down. When you adjust a sequence ID, the specified ID is exchanged with another ID. For example, if you were to move the ID 2 up, 2 becomes 3, and 3 would become 2.

Use MOVE_MEMBER_UP to adjust a member sequence ID up by one. Alternately, use MOVE_MEMBER_DOWN to adjust a member sequence ID down by one, for example:

Note that while using either of these methods an application error displays:

If the named collection does not exist for the current user in the current session

If the member specified by the p_seq sequence ID does not exist

However, an application error will not be returned if the specified member has the highest or lowest sequence ID in the collection (depending on if you are calling MOVE_MEMBER_UP or MOVE_MEMBER_DOWN).

Sorting Collection Members

Use the SORT_MEMBERS method to reorder members of a collection by the column number. This method sorts the collection by a particular column number and also reassigns the sequence IDs for each member to remove gaps, for example:

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 must clear the session state for a collection. You can remove session state of a collection by calling the TRUNCATE_COLLECTION method or by using f?p syntax.

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

Creating Custom Activity Reports Using APEX_ACTIVITY_LOG

The APEX_ACTIVITY_LOG view records all activity in a workspace, including developer activity and application run-time activity. You can use APEX_ACTIVITY_LOG to view to query all activity for the current workspace. For example, you can use this view to develop monitoring reports within a specific application to provide real-time performance statistics.

Keep in mind that logging of activity in an Oracle Application Express instance is rotated between two different log tables. Because of this, logging information is only as current as the oldest available entry in the logs. If you want to persist your application specific log information for all time, you must either copy the log information into your own application table or implement logging directly in your application.

See Also:

"Name" for information on enabling logging on the Edit Definition page

Running Background PL/SQL

You can use the APEX_PLSQL_JOB package to run PL/SQL code in the background of your application. This is an effective approach for managing long running operations that do not need to complete for a user to continue working with your application.

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:

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

Table 15-3 APEX_PLSQL_JOB Package: Available Functions

Function or Procedure

Description

SUBMIT_PROCESS

Use this procedure to submit background PL/SQL. This procedure returns a unique job number. Because you can use this job number as a reference point for other procedures and functions in this package, it may be useful to store it in your own schema.

UPDATE_JOB_STATUS

Call this procedure to update the status of the currently running job. This procedure is most effective when called from the submitted PL/SQL.

TIME_ELAPSED

Use this function to determine how much time has elapsed since the job was submitted.

JOBS_ARE_ENABLED

Call this function to determine whether the database is currently in a mode that supports submitting jobs to the APEX_PLSQL_JOB package.

PURGE_PROCESS

Call this procedure to clean up submitted jobs. Submitted jobs stay in the APEX_PLSQL_JOBS view until either Oracle Application Express cleans out those records, or you call PURGE_PROCESS to manually remove them.

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:

The job number assigned to each submitted PL/SQL job. The APEX_PLSQL_JOB.SUBMIT_PROCESS function returns this value. This is also the value you pass into other procedures and functions in the APEX_PLSQL_JOB package.

FLOW_ID

The application from which this job was submitted.

OWNER

The database schema that owns the application. This identifies what schema will parse this code when DBMS_JOB runs it.

ENDUSER

The end user (that is, who logged into the application) that caused this process to be submitted.

CREATED

The date when the job was submitted.

MODIFIED

The date when the status was modified.

STATUS

The user-defined status for this job. Calling APEX_PLSQL_JOB.UPDATE_JOB_STATUS updates this column.

SYSTEM_STATUS

The system defined status for this job.

SYSTEM_MODIFIED

The date when the system status was modified.

SECURITY_GROUP_ID

The unique ID assigned to your workspace. Developers can only see jobs submitted from their own workspace.

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:

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 the job succeeds or fails. This last point is important for two reasons:

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.

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.

Implementing Web Services

Web services enable applications to interact with one another over the Web in a platform-neutral, language independent environment. In a typical Web services scenario, a business application sends a request to a service at a given URL by using the protocol over HTTP. The service receives the request, processes it, and returns a response. You can incorporate calls with external Web services in applications developed in Application Builder.

Web services are based on Simple Object Access Protocol (SOAP). SOAP is a World Wide Web Consortium (W3C) standard protocol for sending and receiving requests and responses across the Internet. SOAP messages can be sent back and forth between a service provider and a service user in SOAP envelopes.

SOAP offers two primary advantages:

SOAP is based on XML, and therefore easy to use.

SOAP messages are not blocked by firewalls because this protocol uses simple transport protocols, such as HTTP.

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:

Working with SSL Enabled Web Services

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 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 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 and Report After Creating a Reference

After the Web service reference has been added, select Create Form and Report on Web Service.

For Choose Service and Operation:

Web Service Reference - Select the Web service reference.

Operation - Select the method to be executed.

For Page and Region Attributes, review the displayed attributes. If the page you specify does not exist, the wizard creates the page for you.

For Input Items:

Identify which items to add to the form. To include an item, select Yes in the Create column. Otherwise, select No.

If necessary, edit the item label.

If applicable, specify the Item Names and Item Labels for basic authentication. Note that this step only appears if basic authentication was specified for this Web service reference when it was created.

For Window Service Results:

Temporary Result Set Name - Enter a name for the collection that stores the Web service result.

Result Tree to Report On - Select the portion of the resulting XML document that contains the information you want to include in the report.

For Result Parameters to Display, select the parameters to be included in the report.

Click Finish.

Creating a Form and Report by Adding a New Page

If you have an existing Web service reference, you can create an input form and report by adding a new page.

For Page and Region Attributes, review the page and region attributes. If the page you specify does not exist, the wizard creates the page for you.

For Input Items:

Identify which items to add to the form. To include an item, select Yes in the Create column. Otherwise, select No.

If necessary, edit the item label.

If applicable, specify the Item Names and Item Labels for basic authentication. Note that this step only appears if basic authentication was specified for this Web service reference when it was created.

Follow the on-screen instructions.

Click Finish.

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.

For the report implementation, select Report on collection containing Web service result.On Identify Region Attributes, enter a region title and optionally edit the region attributes.

Choose whether the Web reference was created manually or from a WSDL.

If the Web service reference was created from a WSDL:

For Web Service Reference and Operation, select a Web service reference and an operation (that is, the method to be executed).

For Result Tree to Report On, select the portion of the resulting XML document that contains the information you want to include in the report.

For Result Parameters:

In Temporary Result Set Name, enter a name for the collection that stores the Web service result.

Select and deselect the appropriate parameters.

If the Web service reference was created manually:

Select the Web service reference.

Choose the SOAP style.

Choose the message format.

Enter the XPath expression to the node to report on.

Enter the namespace for the SOAP response envelope and click Next.

Enter the name of the collection where the response message is stored.

Enter the names of the parameters that you want to be included in the report.

Click Create SQL Report.

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.

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

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:

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

Select an application.

Application Builder appears.

Click Shared Components.

The Shared Components page appears.

Under Logic, click Web Service References.

Click History.

Note:

The History button only appears on the Web Service Reference page after you have created a Web service reference.

About BLOB Support in Forms and Reports

Starting with release 3.1, Oracle Application Express includes declarative BLOB support to enable developers to declaratively uploaded files in forms, and downloaded or display files in reports. BLOB display and download can also be authored procedurally using PL/SQL. This section describes how to upload, download and display files, and how to manage additional file attributes such as MIME type and file name that are important for proper management of files stored in BLOB columns. Using this functionality you can easily extend your Oracle Application Express applications to manage files including images, documents, videos, and so on.

About BLOB in Forms

If you create a Form (either using the Create Application Wizard, create page of type Form - or Report and Form, or create region of type Form) or add an item to an existing form, any items whose source is a database column of type BLOB will result in an item of type File Browse. When the form is called for INSERT, the file selected by the user will be loaded into the BLOB column. When the form is called for update, a download link is displayed to the right of the Browse button. Users can use this link to download the file.

Populating the BLOB and Providing Download

The defaulted BLOB support does not give you all the information a typical application needs to effectively manage a BLOB. In addition to knowing that the column is a BLOB, more information about the file will provide a better experience for the end-user. To facilitate managing this additional information, the 'Source' attribute of an item has been extended to have additional components (in addition to DB_COLUMN):

Position

Attribute

Required

Description

1

Column containing BLOB

Yes

Case sensitive name of column of type BLOB.

2

MIME type Column

No

Case sensitive column name used to store the MIME type.

3

Filename Column

No

Case sensitive column name used to store the filename of the BLOB. If null, the column name is used as the default when user downloads the file.

4

Last Update Column

No

Case sensitive column name used to store the last update date of the BLOB. If used, the HTTP header of the file download will indicate the date of last modification and browsers will be able to cache the BLOB. If not specified, the browser may not be able to cache files.

5

Character Set Column

No

Case sensitive column name used to store the character set of the BLOB. Most relevant for Asian languages which may need to maintain the character set encoding.

6

Content Disposition

No

Specify inline or attachment. All other values ignored. attachment is the default, inline can only be respected if a MIME type is specified.

7

Download Text

No

String used for the download link. If nothing is provided, Download will be used. Note this will support substitutions (useful for translated applications).

If you have an item of type File Browse, whose source is Database Column that contains at least one colon (for example, RESUME:), you will see a link below the Source BLOB Download Format Mask. This popup assists in entering all the parameters necessary for the BLOB format.

At a minimum, you must specify the database BLOB column with at least one trailing colon (for example, RESUME:). This preserves compatibility with Oracle Application Express release 3.0.1.

To provide this additional information, Oracle recommends you add additional columns to your base table to store and track the MIME type and file name attributes. You can accomplish this simply by extending your table, for example:

If you manually create a form, you can still take advantage of this feature. You simply use the format described within an item of type File (File Browse) on a page with a DML Process of type DML_PROCESS_ROW. This process will determine the table name and primary key columns.

Displaying the BLOB

Removing the Image Reference

Because there is no set to NULL when using File Browse, if you need to provide a mechanism to remove an image reference, you must include a special Remove Image button to nullify the necessary columns. Consider the following example:

About BLOB Support in Reports

Oracle Application Express includes BLOB support for both classic and interactive reports. If you use a wizard to create a report and include a column of type BLOB, basic support will be included. Additional information should be added after generation to make the download capability more user friendly.

Providing a Download Link

To facilitate the inclusion of a download link in a report, the report includes the selection of the length of the BLOB (for example, dbms_lob.getlength(RESUME)). If the length is 0, the BLOB is NULL and no download link is displayed. In the same way you specify a format mask for a date or number you can format a download link. The DOWNLOAD format is more complex however then other format masks in that you are required to specify at least three parameters, for example:

DOWNLOAD:EMP:RESUME:EMPNO

The parameters of the DOWNLOAD format are described in the following table:

Position

Attribute

Required

Description

1

DOWNLOAD

Yes

Identifies the DOWNLOAD report format mask.

2

Table Name

Yes

Case sensitive name of table containing target column of type BLOB.

3

Column containing BLOB

Yes

Case sensitive name of column of type BLOB.

4

Primary Key Column 1

Yes

Case sensitive name of primary key column 1.

5

Primary Key Column 2

No

Case sensitive name of primary key column 2.

6

MIME type Column

No

Case sensitive column name used to store the MIME type.

7

Filename Column

No

Case sensitive column name used to store the filename of the BLOB. If NULL, the column name is used as the default when a user downloads the file.

8

Last Update Column

No

Case sensitive column name used to store the last update date of the BLOB. If used, the HTTP header of the file download indicates the date of last modification and Web browsers will be able to cache the BLOB. If not specified, the browser may not be able to cache files.

9

Character Set Column

No

Case sensitive column name used to store the character set of the BLOB. Most relevant for Asian languages which may need to maintain the character set encoding.

10

Content Disposition

No

Specify inline or attachment. All other values ignored. If a MIME type is provided and the file is a type that can be displayed, the file will be displayed. If MIME type is not provided, or the file cannot be displayed inline, the user will be prompted to download.

11

Download Text

No

String used for the download link. If nothing provided, Download is used. Note that this will support substitutions (useful for translated applications).

If you have a report column with a format mask that begins with DOWNLOAD:, you will see a link below the format 'BLOB Download Format Mask'. This popup assists in entering all the parameters necessary for the DOWNLOAD format.

Displaying the BLOB

If the BLOB you are working with is an image, you can display it in the report as well. To do this, you use the new report format mask of 'IMAGE'. Regardless of the MIME type, the report will always attempt to display the BLOB. If the BLOB cannot be rendered, a broken image will be displayed.

The parameters of the IMAGE format mask are described in the following table:

Position

Attribute

Required

Description

1

IMAGE

Yes

Identifies the IMAGE report format mask.

2

Table Name

Yes

Case sensitive name of table containing target column of type BLOB.

3

Column containing BLOB

Yes

Case sensitive name of column of type BLOB.

4

Primary Key Column 1

Yes

Case sensitive name of primary key column 1.

5

Primary Key Column 2

No

Case sensitive name of primary key column 2.

6

MIME type Column

No

Case sensitive column name used to store the MIME type.

7

Filename Column

No

Not used for IMAGE format but left in so that the format can easily be changed between IMAGE and DOWNLOAD.

8

Last Update Column

No

Case sensitive column name used to store the last update date of the BLOB. If used, the HTTP header of the file download indicates the date of last modification and Web browsers will be able to cache the BLOB. If not specified, the browser may not be able to cache files.

9

Character Set Column

No

Not used for IMAGE format but left in so that the format can easily be changed between IMAGE and DOWNLOAD.

10

Content Disposition

No

Not used for IMAGE format but left in so that the format can easily be changed between IMAGE and DOWNLOAD.

If you have a report column with a format mask that begins with 'IMAGE:', you will see a link below the format 'BLOB Download Format Mask'. This popup assists in entering all the parameters necessary for the IMAGE format.

Working With BLOBs Procedurally

As an alternative to using the built-in methods of providing a download link, you can use the APEX_UTIL.GET_BLOB_FILE_SRC function. One advantage of this approach, is the ability to more specifically format the display of the image (with height and width tags). Please note that this approach is only valid if called from a valid Oracle Application Express session. Also, this method requires that the parameters that describe the BLOB to be listed as the format of a valid item within the application. That item is then referenced by the function.