The second easiest way to get started with Oracle’s Application Express

Following on from my earlier post,  using VirtualBox  with the pre-built Database App Development Virtual Machine  is in my opinion, the second easiest method to getting up and running with Application Express. It is also my preferred method of prototyping, trying new features and providing answers to Stack Overflow Apex questions.

I particularly like the fact that once you have imported the pre-built Virtual Machine into Virtual box, you are good to go, there is no further configuration. It only takes three simple steps to start working with your Oracle environment.

Step 1 Start the Virtual Machine

Capture1

Step 2 From the the pre built VM desktop select the Apex Track

Capture1

Step 3 Start working with Apex

Untitled

It is worth mentioning that the Apex credentials at the time of writing were:

Workspace: obe
Username: obe
Password: oracle

At the time of this post, the installation of Virtual box can be found here and the pre-built Database App Development Virtual Machine here .

Writing PL/SQL in Oracle Application Express

There is an excellent article on Writing PL/SQL in Oracle Application Express in the March/April 2014 edition of the Oracle Magazine.

There is a lot of useful information about where your code should be located. My favourite part of the article is comparison between the parameterless function relying on the Apex V function and a function that passes the values explicitly via parameters.

It is an excellent article that will repay the short time you will invest in reading it.

 

 

Queuing for Oracle APEX (Part 2)

In Part 1 of this article I described the steps required to first create a queue and then how to enqueue messages using server side PL/SQL called from an Apex application.

In part 2 I will describe the steps required to dequeue the messages and how they may be displayed using Apex.

The first step is to write the procedure that will dequeue the messages from the queue. This procedure should be created in the same schema as the Queue Owner and the execute privilege should be given to Apex user.


CREATE OR REPLACE PROCEDURE feedback_dq
IS

   lkv_queue_name        CONSTANT VARCHAR2(60) := 'aquser.feedback_queue';
   lt_dq_opt             dbms_aq.dequeue_options_t;
   lt_mprop              dbms_aq.message_properties_t;
   lo_the_feedback       q_message;
   lt_deq_msgid          RAW(16);
   lb_further_feedback   BOOLEAN := TRUE;

   le_no_messages        EXCEPTION;
   PRAGMA EXCEPTION_INIT(le_no_messages, -25228);

BEGIN

   lt_dq_opt.wait := DBMS_AQ.NO_WAIT;

   WHILE lb_further_feedback
   LOOP
      BEGIN
         dbms_aq.dequeue
         (
            queue_name         => lkv_queue_name,
            dequeue_options    => lt_dq_opt,
            message_properties => lt_mprop,
            payload            => lo_the_feedback,
            msgid              => lt_deq_msgid
         );

         INSERT INTO feedback(id,
                              title,
                              details,
                              email_address,
                              date_time_created)
         VALUES(lo_the_feedback.ID,
                lo_the_feedback.title,
                lo_the_feedback.details,
                lo_the_feedback.email_address,
                SYSDATE);

         COMMIT;

      EXCEPTION

         WHEN le_no_messages THEN

            lb_further_feedback := FALSE;

      END;

   END LOOP;

END feedback_dq;

The procedure used to dequeue the messages is straight forward so I will only explain the salient points.

The wait parameter of the dequeue option is set to NO_WAIT (Line 16). This ensures that if there are no messages, control returns from dbms_aq.dequeue.

dbms_aq.dequeue is called (line 21) and if there is a message its contents are inserted into the feedback table.

When there are no more messages and because NO_WAIT has been specified dbms_aq.dequeue raises an ORA-25228. This is handled in the exception section which sets the boolean controlling the loop false and allows the procedure to complete.

With the dequeue procedure in place all that is left is to create an Apex page that will call the feedback_dq procedure and display the contents in a report on the same page.

Here is the page I created. The “Get Feedback button” simply calls the procedure feedback_dq and the report is based on the feedback table. So before any messages have been dequeued the page looks like this:

Untitled

and then after the “Get Feedback” button had been pressed, the dequeued messages are displayed in a report:

Untitled1

Summary

In this two part article I have shown how you get started with using Oracle Streams Advanced Queuing with Application Express.

Acknowledgements and further reading:

Oracle Developer.net introduction to advanced queuing

Oracle Streams Advanced Queuing User’s Guide

Queuing for Oracle APEX (Part 1)

In this first of a two part article I will demonstrate the use of Oracle Streams Advanced Queuing (AQ) with Application Express. The first part will cover the creating a new queue and adding a message to the queue. Whilst the second part will concentrate on how to dequeue messages.

In building the demonstration I have used:

Oracle Database 11gR2 (Enterprise Edition Release 11.2.0.1.0 – Production)
SQL Developer 3.2.10.09
SQL Plus 11.2.0.1
Application Express 4.2

For this example, the requirement is that we want to gather feedback from our users but we want to process that feedback at a time when the system is idle or has spare capacity. As with any requirement there are many different ways that this could be achieved but for this example I will be using AQ.

AQ is a big subject and can become complex very quickly. In order to keep the examples simple, the AQ objects created and manipulated within these article are done so with the minimum options used.

I want to keep all my AQ related objects separate from the schema where my application is run from so the first step is to create a new user and then give that user certain roles and privileges.


CREATE USER aquser IDENTIFIED BY aquser
/
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE TO aquser
/
GRANT EXECUTE ON dbms_aq TO aquser
/
GRANT EXECUTE ON dbms_aqadm to aquser
/

I now log in to the database as aquser and create a schema level object type. This object will be referenced when the queue is created and will be used to store the feedback as a message on the queue.

CREATE TYPE q_message AS OBJECT
(
    id              NUMBER(10),
    title           VARCHAR2(100),
    details         VARCHAR2(4000),
    email_address   VARCHAR2(1000)
)
/

With the object type created, the next step is to create the queue table. The queue table is used to store the messages until they are dequeued. It is created via the api dbms_aqadm.create_queue_table. The parameters show the name of the queue table and the reference to the object that was created in the previous step.

BEGIN
   dbms_aqadm.create_queue_table
   (
      queue_table        => 'feedback_queue_table',
      queue_payload_type => 'q_message',
      comment            => 'Creating feedback queue table'
   );
END;
/

With the queue table in place, the queue can now be created. This is achieved by running:

BEGIN

    dbms_aqadm.create_queue
    (
       queue_name  => 'feedback_queue',
       queue_table => 'feedback_queue_table',
       comment     => 'feedback queue'
    );    

END;
/

Once created, the new feedback queue can be started by running:


BEGIN

    dbms_aqadm.start_queue
    (
       queue_name => 'feedback_queue'
    );    

END;
/

Now that the Feedback queue has been created and started, it is ready for messages to be enqueued. To do that I have created a feedback_nq procedure which in essence is just a wrapper for the dbms_aq.enqueue procedure. Feedback_nq accepts one parameter, which is of the same type used in creating the Feedback queue:


CREATE OR REPLACE PROCEDURE feedback_nq
(
    p_feedback IN q_message
)
IS

    lkv_queue_name    CONSTANT VARCHAR2(60) := 'aquser.feedback_queue';
    lt_eopt           dbms_aq.enqueue_options_t;
    lt_mprop          dbms_aq.message_properties_t;
    lo_the_feedback   q_message;
    lt_enq_msgid      RAW(16);        

BEGIN

    dbms_aq.enqueue
    (
        queue_name         => lkv_queue_name,
        enqueue_options    => lt_eopt,
        message_properties => lt_mprop,
        payload            => p_feedback,
        msgid              => lt_enq_msgid
    );

    COMMIT;

END feedback_nq;
/

I can now run a pl/sql anonymous block to test this procedure. Before I run the test I check the the queue table is empty:


select t.user_data
from   aq$feedback_queue_table t

This query completes successfully but returns zero rows because there are now messages on the queue. So lets add a message:


DECLARE

   lt_feedback   aquser.q_message;

BEGIN

   lt_feedback := aquser.q_message
                  (
                     id            => 1,
                     title         => 'Compliment',
                     details       => 'Sample details',
                     email_address => 'oracle@oracle.com'
                  );

   feedback_nq
   (
      p_feedback => lt_feedback
   );

END;      

Once run, a message will be enqueued. This can be confirmed by querying the feedback queue table

qae1_

Because the Application that will ultimately call the procedure is in another schema, grants are required on the feedback_nq procedure and the object used to contain the message.


GRANT EXECUTE ON feedback_nq to 
GRANT EXECUTE ON q_message to 

All the objects required by the new user AQuser have now been created so we can now move on to creating a couple of supporting objects for our Apex interface.

Start a new database session, this time logging into the application schema create the following objects.


CREATE SEQUENCE feedback_seq
/

CREATE OR REPLACE PROCEDURE add_feedback
(
    pv_title         IN VARCHAR2,
    pv_details       IN VARCHAR2,
    pv_email_address IN VARCHAR2
)
IS

   lo_feedback_message   aquser.q_message;

BEGIN

  lo_feedback_message := aquser.q_message
                         (
                            id            => feedback_seq.nextval,
                            title         => pv_title,
                            details       => pv_details,
                            email_address => pv_email_address
                         );

   aquser.feedback_nq
   (
      p_feedback => lo_feedback_message
   );

END add_feedback;

The procedure assigns the incoming parameters to the queue object type and once done it calls the feedback_nq procedure.

The final step is to call this procedure from our Apex application.

I created a new Apex database application, added two HTML pages. On page one I added several form text items and a button. Under page processing I created a new process called add feedback which contains a call to the procedure and passes it the values of the page items.


add_feedback
(
    pv_title           => P1_HEADING,
    pv_details         => P1_FEEDBACK,
    pv_email_address   => P1_EMAIL
);

The screen looks like this:

Untitled1

When user presses Submit they see the following screen:

Untitled2

If we run our query against the feedback queue table you can see that the message from Apex is has been enqueued:

Untitled3

In Part 2 I will show how to dequeue these messages and display them in an Apex report.