In this tutorial you build a simple Oracle Forms 11g application with an event object and integrate it with a database queue that you create. You then use a more complex chat application in Oracle Forms 11g that demonstrates the asynchronous events feature in an Oracle database.

Oracle Advanced Queuing (AQ) mechanisms enable messages to be exchanged between different programs. AQ functionality is implemented by using the following interfaces, which are PL/SQL packages: DBMS_AQ, DBMS_AQADM, and DBMS_AQELM. See the Resources section of this tutorial for more information about Oracle Advanced Queuing.

In the simple application that you build, you create an Event object in Forms Builder, subscribe to a database queue that you set up, and perform some action when the event occurs. The more complex application that you demonstrate in this tutorial is a chat application that also integrates Advanced Queuing. Both applications show how Oracle Forms 11g can handle external events, such as asynchronous events, by using the database queue.

In this section of the tutorial, you set up the environment and configure the Forms Servlet to run the chat application, incorporating the WebUtil utility for client interaction. You also set up the Forms Servlet to run the simple application that you build in this tutorial.

In theformsweb.cfg file create a named configuration, [chat], for the chat application .

Add the form, userid , and separateframe parameters to the configuration. The form to run is chat_mainnew, to be run in a separate window, and the user id and password are chat/chat. To complete the connection (userid) parameter, add your database information. The parameters should look similar to the following: [chat]
form=chat_mainnew
userid=chat/CHAT@<dbstring>separateframe =true

.

The Forms Server is notified of asynchronous events without polling, and it responds to the event by firing a WHEN-EVENT-RAISED trigger. However, because of the request/response paradigm of the HTTP protocol, it does not fire this trigger until it receives a request from the Forms Client.

There is a new parameter, MaxEventWait, that governs how many milliseconds the application should wait before checking for an event.

Set MaxEventWait to 2000 milliseconds.

.

Create another named configuration, [title_test], for running the simple form that you build in this tutorial. The new named configuration should be a duplicate of what you have defined so far for the [chat] configuration, except that the form name is title:[title_test]
form=title
userid=chat/chat@<dbstring>separateframe =true

Add the following WebUtil parameters to the [chat] named configuration in the formsweb.cfg file, and then save and close the file:WebUtilArchive=frmwebutil.jar,jacob.jar
WebUtilLogging=off
WebUtilLoggingDetail=normal
WebUtilErrorMode=Alert
WebUtilDispatchMonitorInterval=5
WebUtilTrustInternal=true
WebUtilMaxTransferSize=16384
baseHTML=webutilbase.htm
baseHTMLjpi=webutiljpi.htm

.

The jacob.jar file must be signed, but first you must edit the batch file for signing JAR files. This batch file (sign_webutil.bat or sign_webutil.sh ) is located in the <Oracle_Instance>\bin directory. Edit the batch file to specify:

Distinguished Names you want to use for your organization's digital signature; for example, the default values are as follows: SET DN_CN=Project Management
SET DN_OU=Development Tools
SET DN_O=Oracle
SET DN_C=US

Key and passwords; for example: SET KEYSTORE_PASSWORD=webutilpasswd (if the keystore already exists, you must use its password; otherwise, this becomes the password for a new keystore)
SET JAR_KEY=webutil2
SET JAR_KEY_PASSWORD=webutil2

Keystore directory; for example: SET KEYSTORE="%HOMEDRIVE%%HOMEPATH%/.keystore"

Number of days signature is valid; for example: SET VALIDDAYS=360

Save the modified file.

.

Sign the jacob.jar file.

Open a command prompt window and navigate to the <Oracle_Instance>\bin directory.

Set the CLASSPATH to include <ORACLE_Home>\jdk\bin; for example:set CLASSPATH=C:\Oracle\Middleware\as_1\jdk\bin

Set PATH to include ORACLE_HOME\forms\java\ and < ORACLE_Home >\forms\webutil; for example: set PATH=C:\Oracle\Middleware\as_1\forms\java;C:\Oracle\Middleware\as_1\forms\webutil

Run the batch file once for each JAR file to be signed; for example:
sign_webutil C:\Oracle\Middleware\as_1\forms\java\jacob.jar

In order to be able to run the applications, you need to start WebLogic Server if it is not already running. You do not need the Admin server just to run a Forms application, but only the managed WebLogic Server for Forms. To start it, perform the following steps:

.

Start the WLS_FORMS managed server on Windows by executing the appropriate batch file, startManagedWebLogic.cmd, located in the user_projects\domains\ClassicDomain\bin subdirectory of the Fusion Middleware home directory. You must pass SERVER_NAME and ADMIN_URL command-line arguments to these scripts; for example: startManagedWebLogic.cmd WLS_FORMS t3://myhost1.com:7001Note: The batch file on Linux systems ends with the .sh extension rather than.cmd.

The Create_Required_tables.sql script that you ran earlier created some objects for the user CHAT. Now you examine some of these objects by performing the following steps:

.

Open Forms Builder and connect to the database as the user chat. Then in the Object Navigator, expand the Database Objects Node. Expand the CHAT user and the Types node. Double-click OBJTITLE_TYP2 (Object Type) to see the definition of the data type.

You need an abstract data type to manage the message payload. In AQ you can use either a RAW data type or an abstract data type (ADT) for the message payload.

The RAW data type is suitable for streams and multimedia and not so suitable for payloads. Because AQ has no support for simple data types, such as VARCHAR2 and NUMBER, the Create_Required_tables.sql script created the objtitle_typ2 ADT that encapsulates the data type you need for the payload, which is VARCHAR2.

.

The script also created a queue table based on that ADT. The code in the script that created the table is: begin
DBMS_AQADM.CREATE_QUEUE_TABLE(
QUEUE_TABLE =>'chat.obj_title_table',
QUEUE_PAYLOAD_TYPE =>'chat.objtitle_typ2',
MULTIPLE_CONSUMERS =>TRUE,
COMPATIBLE => '8.1.3');
end;
/

To view the table in the Object Navigator, under the CHAT user expand Tables> OBJ_TITLE_TABLE > Columns > USER_DATA (OBJTITLE_TYP2).

You can see that the USER_DATA column is based on the objtitle_typ2 ADT:

.

The script created a procedure that accepts values for title, F1, and F2, adds these values to the message payload, and places a message on the queue. To view the code, under the CHAT user expand PL/SQL Stored Program Units and double-click NEW_OBJ_ENQUEUE2(Procedure).

The script also created a database trigger that automatically calls the procedure when an INSERT is done on the Title table.

To view the code, under the CHAT user expand Tables > Title > Triggers, and then double-click TITLE_INSERT_TRIGGER (After Each Row Insert).

The code that created the table is:create or replace trigger TITLE_insert_trigger
AFTER INSERT on TITLE
FOR EACH ROW
begin
new_obj_enqueue2( 'chat.objqueue_titleinsert' , :new. TITLE ,
:new.F1, :new.F2);
end;
/

There are additional queue related objects shown in the Object Navigator of Forms Builder that you may examine if you like.

.

In addition to what you can see in the Object Navigator, the Create_Required_tables.sql script performed some additional actions:

A queue administrator can specify the list of subscribers who can retrieve messages from a queue. The Create_Required_tables.sql script issued the following SQL to attach a subscriber to the queue that was just started:DECLARE
subscriber sys.aq$_agent;
begin
subscriber := sys.aq$_agent( 'admin' , null, null);
dbms_aqadm.add_subscriber(
queue_name => 'CHAT.objqueue_titleinsert' ,
subscriber => subscriber);
end;
/

So far you have set up and started the database queue, added a subscriber, and created a procedure that enqueues an event whenever a record is added to the Title table. The payload of the event contains the new values that were inserted into the Title table.

Now you create a form that retrieves the payload from the queued message and displays its values. In Forms 11g there is new EVENT object that you can create in the Object navigator. A new WHEN-EVENT-RAISED trigger is introduced to handle the event for the EVENT object. You can use the new GET_EVENT_OBJECT_PROPERTY() built-in to retrieve the PAYLOAD.

Perform the following steps to create the form that is integrated with the queue that you have set up:

.

In Forms Builder, select the Events node and click Create (or select Edit > Create) to create a new Event object. Name this event Event2.

.

The form must subscribe to a queue in order to get event messages and payloads.

To subscribe to a queue:

Open the Property Palette for the event.

Set the Subscription Name property to OBJQUEUE_TITLEINSERT (click the ellipsis and select the value from the list.)

.

For the Event object, create a WHEN-EVENT-RAISED trigger to code the action that should be initiated when a message is received in the subscribed queue. In this case, to simply demonstrate that the payload was received, you can use the MESSAGE() built-in to display the payload values.

Note the use of the new GET_EVENT_OBJECT_PROPERTY() built-in to retrieve the payload from the event that was raised when a message was placed on the queue to which the form is subscribed.

.

Inserting a record in the Title table is the action that initiates the event. To make it possible to insert a record, create a data block in the form that is based on the Title table, using all columns from the table.

.

Save the form with the name title.fmb, and then generate the form. Be sure to save it to the directory that you earlier added to FORMS_PATH.

.

Run the form by entering a URL in the browser similar to the following, using your own host name:http://localhost:9001/forms/frmservlet?config=title_test

.

The form opens in a separate window.

Enter and commit a record.

.

Inserting a record initiates the following sequence of actions:

The database AFTER INSERT trigger that you defined earlier places a message on the queue with a payload that contains the new values that you inserted in the table.

Because the form is subscribed to the queue, the event that you defined in the form is raised.

The WHEN-EVENT-RAISED trigger in the form fires, retrieves the payload from the event, and displays the payload in a message.

You can that the message shows the XML payload containing the values that you entered into the form.

To run the application and demonstrate its functionality, perform the following steps:

.

Open the demonstration application .fmb and .mmb files in Forms Builder; generate the forms and compile the menu modules.

Note: If you get compilation errors, ensure that you are using a supported database with Advanced Queuing and XML support, as stated in the Prerequisites.

.

Run the application in your browser by issuing the following URL:

http://localhost:9001/forms/frmservlet?config=chat

When prompted, log in as the CHAT user.

Note: If the runtime session crashes with FRM-93652, do the following:

Detach webutil.pll library from the CHAT_MAINNEW form.

Open webutil.pll library in Forms Builder and resave it.

Reattach the webutil library to the CHAT_MAINNEW form.

Resave and recompile the CHAT_MAINNEW form.

.

Click Get new user ID to create a user id...

---and enter information to register yourself as a new user (Userid should be 8 characters):

Click Continue, and then click OK to acknowledge the confirmation message.

.

Log in with the user id that you created.

Once you are logged in, a buddy list of other users who are logged in to the chat Application is displayed in a tree. Initially there are no other users, so the buddy list is blank.

.

Leaving the first buddy list open, open a second browser window. Following steps 2-4 above, run the application to create a second user and log in. The buddy list for the second user shows that the first user is already logged in:

Now return to the first user's buddy window – you should see that the second user has been added to the buddy list.

.

To send a message to another logged-in user, select the user from the list, and then right-click and select SendIM from the context menu.

.

A new chat window opens. Type in a message and click Send.

.

Return to the second user's window. You can see the message sent by the first user, and you can send a message back.

The first user's window shows the return message.

Note: There is an unresolved bug in the application. Occasionally a message, especially the first message sent, does not appear in one of the message windows (sometimes the sender's and other times the receiver's message window.) If this happens, you can send the message again and it should appear in both windows.

.

You can upload Images of particular user. In the first user's chat window, click Browse to select the image file, and then click Upload to save it in the database. (This is functionality of WebUtil, not the asynchronous event integration with Advanced Queuing.)

.

The next time another user opens a chat window to chat with the first user, the picture appears.

In this tutorial, you built a simple form that retrieves the payload from an AQ queue that you set up. You then ran the chat application to demonstrate how it integrates with Advanced Queuing to send and receive messages.