Integrating Advanced Queuing in Oracle Forms 11g

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

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.

Time to Complete

Approximately 2 hours

Overview

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.

Here are some screenshots of the chat application:

After the user logs in, the application looks like this:


After the user opens a chat window, it looks like this:

 

Prerequisites

Before starting this tutorial, you should:

.

Have access to or have installed Oracle Forms version 11.1.1.

.

Have access to or have installed a supported Oracle database; see the certification matrix accessible here. Please note that you must use a database that supports Advanced Queuing and XML.

.

Have access to the SYS user account.

Setting Up the Application User and Objects

Both of the applications in this tutorial are accessed by a user named chat. To set up the chat user and objects, perform the following steps:

.

Extract the zip file to a temporary location; by default, unzipping it creates a subdirectory called \chat.

 

.

Set up the CHAT user:

  • Invoke SQL*Plus from the \Scripts subdirectory of your \chat directory.
  • Connect to the SYS user in the database as the system DBA.
  • Run the SQL script chat_sys.sql.

Do not disconnect from SQL*Plus.


.

Create the CHAT user's tables, procedures, and queues, and start the created queues.

  • Connect as the user CHAT with password CHAT. Note that in some databases, the password may be case sensitive.
  • Run the SQL script Create_Required_Tables.sql

Setting Up the Applications


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.

Set Up the Application Environment


.

From the \Forms subdirectory of the \chat directory where you extracted the zip file, copy the .fmb and .mmb files to a directory of your choice.

 

.

Set the FORMS_PATH variable in the default.env file to include the directory containing the .fmb and .mmb files, and then save and close the file.


.

Create a directory named oracle/forms/demos under $ORACLE_HOME/forms/java/ and copy the RoundedButton.class file there .

 

Set Up the Application Configuration


.

In the formsweb.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

Configure WebUtil for the Application


.

From the \WebUtil subdirectory of the \chat directory where you extracted the zip file, copy the jacob.jar file to $ORACLE_HOME/forms/java.

 

.

Copy jacob.dll to the $ORACLE_HOME\forms\webutil directory.


.

Make the following changes in webutil.cfg to enable WebUtil functions, and then save and close the file:

transfer.database.enabled=TRUE
transfer.appsrv.enabled=TRUE
transfer.appsrv.workAreaRoot=
transfer.appsrv.accessControl=TRUE
#List transfer.appsrv.read.<n> directories
transfer.appsrv.read.1=c:\ temp
#List transfer.appsrv.write.<n> directories
transfer.appsrv.write.1=c:\ temp

 

.

Set up the WebUtil database objects:

  1. Connect to SQL*Plus as the user SYSTEM.
  2. Create a user named webutil.
  3. Grant resource and connect privileges to the webutil user.
  4. Connect as the webutil user.
  5. Run the SQL script $ORACLE_HOME\forms\create_webutil_db.sql.

 

.

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

Starting WebLogic Server

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:7001
Note: The batch file on Linux systems ends with the .sh extension rather than .cmd.

Alternatively, you can call this batch file from the Windows Start menu:
All Programs > Oracle Classic Instance > Forms Services > Start WebLogic Server WLS_FORMS.
These menu items already include the command-line arguments.

 

.

You will need to input the user name and password for starting the Oracle WebLogic Server, which were specified at the time of installation.You can minimize, but do not close, the command window.

The characters of the password are not visible as you type them.

 

.

You can minimize, but do not close, the command window.

Examining the AQ Objects

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 code is:
procedure new_obj_enqueue2(
    queue_name  in varchar2,
    TITLE in varchar2,
    F1 in varchar2,
    F2 in varchar2)
as
    enq_ct     dbms_aq.enqueue_options_t;
    msg_prop   dbms_aq.message_properties_t;
    enq_msgid  raw( 16 );
    userdata   chat.objtitle_typ2;
begin
    userdata := objtitle_typ2( TITLE ,F1,F2);
    DBMS_AQ.ENQUEUE(
        QUEUE_NAME         => queue_name,
        ENQUEUE_OPTIONS    => enq_ct,
        MESSAGE_PROPERTIES => msg_prop,
        PAYLOAD            => userdata,
        MSGID              => enq_msgid);
end;
/

The script granted the EXECUTE privilege on this procedure to PUBLIC.

.

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:

  • The script issued the following commands to create and start the queue:
    begin
        DBMS_AQADM.CREATE_QUEUE(
            QUEUE_NAME => 'CHAT.objqueue_titleinsert' ,
            QUEUE_TABLE => 'CHAT.obj_title_table' );
    end;
    /
    begin
        DBMS_AQADM.START_QUEUE( 'CHAT.objqueue_titleinsert' );
    end;
    /
  • 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;
    /

Building the Forms Application to Interact with AQ

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.

Enter the following trigger code:
declare
    new_msg_str varchar2(4000);
begin
    new_msg_str:=GET_EVENT_OBJECT_PROPERTY('EVENT2',event_payload);
    message('Payload is'||new_msg_str);
    message(' ');
end;

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:
  1. 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.
  2. Because the form is subscribed to the queue, the event that you defined in the form is raised.
  3. 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.

Exit the form and the browser.

 

Running the Demonstration Application

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 Send IM 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.

Summary

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.

Resources