Getting Started with DBMS_PARALLEL_EXECUTE

In this article I will explain what DBMS_PARALLEL_EXECUTE is and give an example of it’s use.

DBMS_PARALLEL_EXECUTE is an Oracle Supplied package which was introduced with Oracle Database 11g Release 2. It allows you to execute a SQL or PLSQL statement in parallel.

At a high level DBMS_PARALLEL_EXECUTE works by performing the following steps:

  1. Group sets of rows in the table into smaller sized chunks.
  2. Run a user specified statement on these chunks in parallel

The following example is based upon the one shown in the Oracle Documentation. One problem I found with that example was the calls to the DBMS_PARALLEL_EXECUTE sometimes had “mysterious” parameters that meant I needed to keep referring back to the documentation to find out what these parameters were.

The example that follows was built using the Oracle Developer Days Environment and used the following versions:

  • Oracle Linux running via Oracle Virtual Box
  • Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
  • Oracle SQL Developer 3.2.20.10
  • The user developing and running the code is logged in as the HR user.

Set up

DBMS_PARALLEL_EXECUTE makes use of the Oracle scheduler so the system priviledge “Create Job” needs to be granted to HR. After logging in as a suitable user run the following command:

    GRANT CREATE JOB TO HR
    /

The example is based on a table I have created called EMPLOYEES2. Using a method described by Jonathan Lewis it populates the table with 5000 rows of random data.

Once the table has been created, a session_id column is then added. This will be used to keep track of which session performed the update.

CREATE TABLE EMPLOYEES2
AS
WITH v1 AS (SELECT ROWNUM N FROM DUAL CONNECT BY LEVEL <= 10000)
SELECT ROWNUM AS EMPLOYEE_ID,
       DBMS_RANDOM.STRING ('a', 10) AS EMPLOYEE_NAME,
       TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2451911,2455928)),'J') AS HIRE_DATE,
       trunc(dbms_random.value(10000,20000),2) as salary
  FROM v1
WHERE rownum <= 5000  
/  

ALTER TABLE employees2 ADD session_id NUMBER 
/ 

The final part of the set up is to create the procedure that will be invoked by DBMS_PARALLEL_EXECUTE. This updates the salary and session_id columns of the EMPLOYEE2 table. Note the use of the rowids within the WHERE clause.

CREATE OR REPLACE PROCEDURE update_emps
(
   p_start_row_id IN ROWID,
   p_end_row_id   IN ROWID
)
IS
BEGIN

UPDATE employees2 e
SET e.salary     = salary * 0.10,
    e.session_id = sys_context( 'userenv', 'sessionid' )
WHERE e.rowid BETWEEN p_start_row_id AND p_end_row_id;

END update_emps;
/

Step 1 Create a task

The first step is to create a named task that will be referred to by later calls to DBMS_PARALLEL_EXECUTE.

BEGIN

   dbms_parallel_execute.create_task
   (
      task_name => 'MyTask',
   );

END;
/

Step 2 Dividing the table up

Next we create “chunks” of approx 1000 rows from EMPLOYEES2 table

BEGIN

   dbms_parallel_execute.create_chunks_by_rowid
   (
      task_name   => 'MyTask',
      table_owner => USER,
      table_name  => 'EMPLOYEES2',
      by_row      => TRUE,
      chunk_size  => 1000
   );

END;
/

Querying the view user_parallel_execute_chunks enables us to view the chunks created.

SELECT chunk_id,
       status,
       start_rowid,
       end_rowid
  FROM user_parallel_execute_chunks
 ORDER BY chunk_id

Capture

Step 3 Run Task

With the table “chunked” the task can now be run. In the call to dbms_parallel_execute.run_task, I have used the following values for the parameters

  • sqlstmt: This a call to the procedure created during the setup. Note the use of the :start_id and :end_id. The statement used as a value for this parameter must have these place holders which become the rowid range to process.
  • language_flag: This is set to native which according to the documentation “specifies normal behaviour for the database to which the program is connected”
  • parallel_level: Specifies the number of parallel jobs.
BEGIN

   dbms_parallel_execute.run_task
   (
      task_name      => 'MyTask',
      sql_stmt       => 'BEGIN update_emps(:start_id, :end_id ); END;',
      language_flag  => DBMS_SQL.NATIVE,
      parallel_level => 4
   );

END;
/

When executed, dbms_parallel.run_task creates four jobs and each job will run the update_emps procedure against the chunk(s) assigned to it.

Step 4 Confirmation

Once dbms_parallel_execute.run_task has finished, querying the user_parallel_execute_chunks view, shows the status of each chunk as PROCESSED.

If a chunk could not be processed or encountered an error, the status will be updated to PROCESSED_WITH_ERROR and the columns error_code and error_text from the same view will provide further information. Once the error(s) have been corrected you can run the task again to process the failed chunks.

SELECT chunk_id,
       status,
       start_rowid,
       end_rowid
  FROM user_parallel_execute_chunks
 ORDER BY chunk_id

Capture

Querying the EMPLOYEE2 table confirms that the job was completed using parallel 4 along with the number of rows updated by each session.

SELECT session_id,
       COUNT(*)
  FROM employees2
 GROUP BY session_id
 ORDER BY session_id

Capture

Summary

In this article I have given an overview of DBMS_PARALLEL_EXECUTE along with an example of its use.

Acknowledgements and Sources:

Tom Kyte’s superb book Expert Oracle Database Architecture 2nd Edition. The section on DBMS_PARALLEL_EXECUTE is available on the Ask Tom website.

The Oracle Database Documentation on DBMS_PARALLEL_EXECUTE.

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:

Untitled

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 => '[email protected]'
                  );
  
   feedback_nq
   (
      p_feedback => lt_feedback
   );
  
END;      

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

Untitled

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 <<schema your apex application can see>>
GRANT EXECUTE ON q_message to <<schema your apex application can see>>

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:

Untitled

When user presses Submit they see the following screen:

Untitled

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

Untitled

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

Selecting many rows when working with PL/SQL

In this article I will explain the method I use when I need to process an indeterminate number of rows using PL/SQL.

I became aware of this method after reading Bryn Llewellyn’s white paper Doing SQL From PL/SQL: Best and Worst Practises (My example is based on the code shown on page 31 of the white paper)

The code is shown below and I will explain how it works:

DECLARE

   CURSOR employees_cur IS
      SELECT e.employee_id,
             e.first_name,
             e.last_name,
             e.salary
      FROM employees e;

   TYPE emp_type IS TABLE OF employees_cur%ROWTYPE
      INDEX BY PLS_INTEGER;

   laa_emps emp_type;

   lkn_batchsize CONSTANT NUMBER(3) := 100;

BEGIN

   OPEN employees_cur;

   LOOP

      FETCH employees_cur BULK COLLECT INTO laa_emps LIMIT lkn_batchsize;

      FOR i IN 1 .. laa_emps.COUNT()
      LOOP

         -- do something interesting with each employee record
         null;

      END LOOP;

      EXIT WHEN laa_emps.COUNT() < lkn_batchsize;

   END LOOP;

   CLOSE employees_cur;

END;

First of all I define an explicit cursor to select the records that I want to work with. (lines 3 – 8). The employee table is a copy of the standard Oracle HR.EMPLOYEE table which contains 107 records.

I then create a PL/SQL Associative Array based on the explicit cursor defined at (lines 3 – 8) and declare a variable of this type. (lines 10 – 13)

The next line declares a constant that controls the batch size or the number of records that each fetch will return. (line 15)

The cursor is opened. (line 19)

A basic loop is entered. (line 21) I will refer to this loop as the outer loop

Using the bulk collect construct, the first batch of 100 records are fetched from the cursor into the associative array variable. (line 23).

The for loop is entered  (line 25) .  It is within this loop that the processing on each record is performed (line 28). This loop is executed 100 times.

Once the batch of records have been processed,  the next to be executed is line 33 where an evaluation is made whether to exit the outer loop. If the count of records that has just been fetched is less than the batch size then there are no further records to process. In this first iteration, the number of records retrieved was 100 and the batch size is 100 so this evaluates to false so processing returns to line 23.

This time the bulk collect fetches the remaining 7 records into the associative array variable. (line 23)

The for loop is again entered and the loop is executed 7 times. (line 28)

With the for loop complete the evaluation at (line 33) is again executed and this time evaluates to TRUE. The count of records is 7 which is less than the batch size of 100 which means all the records have been processed. The outer loop is then exited.

Finally the cursor is closed and the end of the program is reached. (line 37)

What has changed?

“It was working yesterday…”

Working in any form of shared development environment you will sooner or later hit a problem when something that used to work has either stopped working or is doing something it wasn’t doing before.

So how do you find out what have changed? With regards PL/SQL objects such as Packages, Triggers etc. the answer can be found by querying the Oracle Data Dictionary.

In the November/December 2012 issue of Oracle Magazine, Steven Feuerstein’s article on PL/SQL  101 describes the information that is available to developers via the various Data Dictionary views and included in that article is a SQL query that can be used to display objects belonging to the user that have changed today:

SELECT object_type,
       object_name,
       last_ddl_time
FROM   user_objects
WHERE  last_ddl_time >= TRUNC(SYSDATE)
ORDER BY object_type,
         object_name

Running this query in SQL Developer shows that the ADD_EMPLOYEE procedure has changed or has it?

What has changed

One limitation with this approach is that the query relies on the last_ddl_time column. According to the Oracle documenation this column…

“is the last modification of the object resulting from a DDL statement (including grants and revokes)”.

So this column is not set just by code changes. For the ADD_EMPLOYEE example I didn’t make any changes to the procedure code but I did recompile it, which changed the value for the last_ddl_time column and so it appeared when I ran the query against user_objects.

This limitation aside,  I have found this to be a useful query. Thanks Steven!

Formatting the output from dbms_xplan.display_cursor

I have in the past struggled with the myriad of formatting options available with dbms_xplan.display_cursor so I was pleased to learn about a quick and easy to remember way of formatting the results at this years UKOUG conference.

First execute the SQL statement of interest including the gather_plan_statistics hint:

SELECT /*+ gather_plan_statistics */ e.*
FROM   hr.employees e
WHERE  e.first_name = 'John'
AND    e.last_name  = 'Chen'
/

Then run dbms_xplan.display_cursor:

SELECT *
FROM   table(dbms_xplan.display_cursor(format => 'allstats last'))
/

The key point is that ‘allstats last’ is used to format the output. This is a short cut for the formatting options of ‘IOSTATS MEMSTATS’. The inclusion of last ensures that the details of the last SQL statement executed is shown.

The example shown above will work on 11g R1 onwards because of the use of a named parameter in SQL. If you are using 10g you need to remove the change the call dbms_xplan.display_cursor to dbms_xplan.display_cursor(null, null, ‘allstats last’))

Once run you will see the output of:

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  abd665w8zn3f8, child number 0                                                                                                                                                                                                                                            
-------------------------------------                                                                                                                                                                                                                                                              
SELECT /*+  gather_plan_statistics */ e.* FROM   hr.employees e WHERE                                                                                                                                                                                          
e.first_name = 'John' AND      e.last_name = 'Chen'                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                             
Plan hash value: 2077747057                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                             
-----------------------------------------------------------------------------------------------------                                                                                                                                                                              
| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                                                                                                           
-----------------------------------------------------------------------------------------------------                                                                                                                                                                              
|   0 | SELECT STATEMENT            |             |      1 |        |      1 |00:00:00.01 |       2 |                                                                                                                                                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |      1 |      1 |      1 |00:00:00.01 |       2 |                                                                                                                                                   
|*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |      1 |      1 |      1 |00:00:00.01 |       1 |                                                                                                                                                        
-----------------------------------------------------------------------------------------------------                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                             
Predicate Information (identified by operation id):                                                                                                                                                                                                                              
---------------------------------------------------                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                             
   2 - access("E"."LAST_NAME"='Chen' AND "E"."FIRST_NAME"='John')                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                             

 20 rows selected 

Different methods to perform XSLT from PL/SQL

In this article I will demonstrate several different methods you can use to perform XSLT from within PL/SQL.

The examples were built using Oracle Database 11.2.0.1.0 and SQL Developer 3.2

The examples are based on this XML document….

<?xml version="1.0"?>
<ROWSET>
 <ROW>
 <EMPNO>7566</EMPNO>
 <ENAME>JONES</ENAME>
 <JOB>MANAGER</JOB>
 <MGR>7839</MGR>
 <HIREDATE>02-APR-1981</HIREDATE>
 <SAL>2975</SAL>
 <DEPTNO>20</DEPTNO>
 <VALID>N</VALID>
 </ROW>
 <ROW>
 <EMPNO>7788</EMPNO>
 <ENAME>SCOTT</ENAME>
 <JOB>ANALYST</JOB>
 <MGR>7566</MGR>
 <HIREDATE>19-APR-1987</HIREDATE>
 <SAL>3000</SAL>
 <DEPTNO>20</DEPTNO>
 <VALID>N</VALID>
 </ROW>
</ROWSET>

and this XSLT document….

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/">
 <html>
 <body>
 <xsl:for-each select="ROWSET/ROW">
 <h2><xsl:value-of select="ENAME"/></h2>
 </xsl:for-each>
 </body>
 </html>
 </xsl:template>
</xsl:stylesheet>

XMLTRANSFORM

OK, so the first example is actually SQL rather than PL/SQL! XMTRANSFORM is a SQL Function that you can call from PL/SQL.  It accepts two arguments, both of which need to be XMLTYPE, one being the XML document that you want to transform and the other is the XSLT document.

Here is an example of it using the XML and XSLT files above:

DECLARE

l_xml XMLTYPE;
l_xsl XMLTYPE;
l_transformed XMLTYPE;

BEGIN

   l_xml := XMLTYPE.CREATEXML('<?xml version="1.0"?><ROWSET><ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-1981</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-1987</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-1987</HIREDATE><SAL>1100</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-1981</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW></ROWSET>');

   l_xsl := XMLTYPE.CREATEXML('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"> <html><body> <xsl:for-each select="ROWSET/ROW"> <h2><xsl:value-of select="ENAME"/></h2> </xsl:for-each> </body></html></xsl:template></xsl:stylesheet>');

   SELECT XMLTRANSFORM(l_xml, l_xsl)
   INTO l_transformed
   FROM dual;

   DBMS_OUTPUT.PUT_LINE(l_transformed.getstringval());

END;
/
  • lines 3 – 5 I declare three XMLTYPE variables.
  • line 9 using the CREATEXML method I create a valid XML Document containing the source XML document and assign it to the l_xml variable
  • line 11 again using CREATEXML I create a valid XML document containing the XSLT stylesheet and assign it to the variable l_xsl
  • lines 13 – 15 I perform the XSLT transformation using XMLTRANSFORM. The result of which is placed in the variable l_transformed.
  • line 17 I output the contents of l_transformed using the getstringval method.

When the anonymous block is run you will see that the XML document has been transformed into HTML and only includes the values from ENAME

<html>
 <body>
 <h2>JONES</h2>
 <h2>SCOTT</h2>
 <h2>ADAMS</h2>
 <h2>FORD</h2>
 </body>
</html>

Transform member function of the XMLTYPE

The Oracle XMLTYPE has a member function that you can use for transforming your XML documents. Here is an example of it being used:

DECLARE

l_xml XMLTYPE;
l_xsl XMLTYPE;
l_transformed XMLTYPE;

BEGIN

 l_xml := XMLTYPE.CREATEXML('<?xml version="1.0"?><ROWSET><ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-1981</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-1987</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-1987</HIREDATE><SAL>1100</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-1981</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW></ROWSET>');

 l_xsl := XMLTYPE.CREATEXML('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"> <html><body> <xsl:for-each select="ROWSET/ROW"> <h2><xsl:value-of select="ENAME"/></h2> </xsl:for-each> </body></html></xsl:template></xsl:stylesheet>');

 l_transformed := l_xml.transform(xsl => l_xsl);

 DBMS_OUTPUT.PUT_LINE(l_transformed.getstringval());

END;
/
  •  Lines 1 – 11, there is no difference from the example used to demostrate XMLTRANSFORM
  • At line 13 I call the TRANSFORM function of l_xml passing it the variable containing the XSLT document.

The output is shown below:

<html>
 <body>
 <h2>JONES</h2>
 <h2>SCOTT</h2>
 <h2>ADAMS</h2>
 <h2>FORD</h2>
 </body>
</html>

DBMS_XSLPROCESSOR

Given the relative ease of use of the previous two methods, using DBMS_XSLPROCESSOR requires a lot more code to transform an XML document. One advantage (the only?) is that this method doesn’t require the use of XMLTYPE.

The example below is taken from the Oracle documentation and modified to use the XML and XSLT documents that have been used throughout this post.

DECLARE

l_xml                     VARCHAR2(4000);
l_xsl                     VARCHAR2(4000);
l_parser                  dbms_xmlparser.parser;
l_xml_dom_document        dbms_xmldom.domdocument;
l_xslt_dom_document       dbms_xmldom.domdocument;
l_xslprocessor_ss_type    dbms_xslprocessor.stylesheet;
l_dom_doc_fragment        dbms_xmldom.domdocumentfragment;
l_dom_node                dbms_xmldom.domnode;
l_xsl_processor           dbms_xslprocessor.processor;
l_buffer                  VARCHAR2(2000);

BEGIN

 l_xml :='<?xml version="1.0"?><ROWSET><ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-1981</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-1987</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-1987</HIREDATE><SAL>1100</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-1981</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW></ROWSET>';

 l_xsl := '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"> <html><body> <xsl:for-each select="ROWSET/ROW"> <h2><xsl:value-of select="ENAME"/></h2> </xsl:for-each> </body></html></xsl:template></xsl:stylesheet>';

 l_parser := dbms_xmlparser.newparser;

 dbms_xmlparser.parsebuffer(l_parser, l_xml);

 l_xml_dom_document := dbms_xmlparser.getdocument(l_parser);

 dbms_xmlparser.parsebuffer(l_parser, l_xsl);

 l_xslt_dom_document := dbms_xmlparser.getDocument(l_parser);

 l_xslprocessor_ss_type := dbms_xslprocessor.newStyleSheet(l_xslt_dom_document, '');

 l_xsl_processor := dbms_xslprocessor.newprocessor;

 l_dom_doc_fragment := dbms_xslprocessor.processXSL(l_xsl_processor, l_xslprocessor_ss_type, l_xml_dom_document);

 l_dom_node := dbms_xmldom.makeNode(l_dom_doc_fragment);

 dbms_xmldom.writeToBuffer(l_dom_node, l_buffer);

 dbms_output.put_line(l_buffer);

 dbms_xmldom.freedocument(l_xml_dom_document);
 dbms_xmldom.freedocument(l_xslt_dom_document);
 dbms_xmldom.freedocfrag(l_dom_doc_fragment);
 dbms_xmlparser.freeparser(l_parser);
 dbms_xslprocessor.freeprocessor(l_xsl_processor);

END;
/

If you run this code you will see the expected output of:

<html>
 <body>
 <h2>JONES</h2>
 <h2>SCOTT</h2>
 <h2>ADAMS</h2>
 <h2>FORD</h2>
 </body>
</html>

Summary

This post has shown several different methods that you can use to transform your XML documents using XSLT from within PL/SQL.

What are my favourite Oracle books? and why?

This post was inspired by a comment on Martin Windlake’s blog.

I am an Oracle Developer who actually likes reading technical books, not only about Oracle technologies but also the wider Software Development world. (Does that put me in a minority?)

As a self taught programmer I have read many Oracle books since I started working with the Database. Whilst most of the books I have read have been at best, perfunctory,  the books that have made it on to this list, which are in no particular order, all have had a very positive impact on I how work with the Oracle Database and it’s related technologies.

Effective Oracle By Design by Tom Kyte

This book is the Code Complete for Oracle developers.

It was the book that first made me aware, when working with PL/SQL less is definitely more and to start thinking in sets. It highlighted the importance of instrumentation within your code, to be wary of universal best practises and also included the only road map I have yet to see for the Oracle Documentation.

PL/SQL From SQL a chapter by Adrian Billington from the book Expert PL/SQL Practises

I found this book to be very hit and miss but I believe Adrian’s single chapter “PL/SQL from SQL” is by itself worth the price of the book.

For many years every relevant Oracle tome I have read had the dire warning “beware of context switching” Oracle Developers know it is has to affect performance when you switch from SQL to PL/SQL within the same statement but exactly how bad it actually is was rarely, if ever disclosed. Adrian’s chapter is the first I know which shows the true cost of context switching. The first part of the chapter explains the term “Context Switching” and goes on to show’s the cost with easy to follow “Then and Now”  SQL.  The second part of the chapter then moves on to explaining how you can start reducing the cost of PL/SQL functions when called from SQL along with some non – PL/SQL alternatives.

Troubleshooting Oracle Performance by Christian Antognini

The book on Oracle performance.

It covers the whole spectrum of Oracle Performance tuning. From identifying and the prioritisation of problems from a business perspective to in depth discussion of the DBMS_XPLAN package.

Oracle PL/SQL Programming by Steven Feuerstein

The seminal book on working with the PL/SQL language. I hesitate to recommend it for learning the language simply because I struggled to learn PL/SQL using the 2nd Edition but once you are up and running there is no better resource for PL/SQL.

Expert Application Express by John Scott, et al

This is a book that should be within reach if you are working with Oracle Application Express. With thirteen chapters written by many of today’s Application Express luminaries it covers topics from the myriad of choices you have for selecting the webserver to how to develop tabular forms effectively and working the Apex 4 features such as Dynamic Actions. My favourite is Doug Gault’s chapter on Debugging.

Where are the Jonathan Lewis books? Whilst I never miss a Jonathan Lewis presentation at the UKOUG events, I have struggled with his books and so at the moment they do not appear on my list. However this list is very much live so one may appear as I periodically review this post.

Oracle File Watcher on a Windows PC

Introduction

Introduced in Oracle 11g Release 2, the File Watcher enables jobs to be triggered when a file arrives in an Operating System Folder.

In this article I am going to set up a new file watcher on my Windows PC. The example inserts the contents of the newly arrived file into a database table.  The information shown here is distilled from the Oracle documentation

Before getting into the detail, here is a quick run down of the key components and their versions that was used to create the example.

  1. Microsoft Windows XP with Service Pack 3 running via Oracle Virtual Box
  2. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
  3. Oracle SQL Developer 3.2.10.09
  4. The user running all the code is logged in with the DBA role

Step 1 Alter the File Watcher Interval (Optional)

File watchers check for the arrival of files every ten minutes by default. If you want to change the interval, connect as sys user and run the set_attribute procedure of dbms_scheduler.

The following example changes the interval to one minute.

BEGIN

   dbms_scheduler.set_attribute
     ('file_watcher_schedule',
      'repeat_interval',
      'freq=minutely; interval=1'
   );

END;
/

Step 2 Create a credential

In order for the File Watcher to be able to access the file(s) on Windows, a  Scheduler credential object is required. The following code creates a credential called “watch_credential”

BEGIN

   dbms_scheduler.create_credential
   (
      credential_name => 'watch_credential',
      username        => 'your operating system username',
      password        => 'your operating system password',
   );

END;
/

Step 3 File Location Details

The call to create_file_watcher (unsurprisingly) creates the file watcher object and tells it where to “watch” for incoming files along with the name of the file that you are interested in.

In the following example I want the File Watcher to watch for files that appear in the Operating System directory C:etl_dir and as the names of the files could be all different but will have the .txt suffix I have set the file name parameter accordingly.

BEGIN

   dbms_scheduler.create_file_watcher
   (
      file_watcher_name => 'the_file_watcher',
      directory_path    => 'C:etl_dir',
      file_name         => '*.txt',
      credential_name   => 'watch_credential',
      destination       => NULL,
      enabled           => FALSE
   );

END;
/

Step 4 Specify the program unit that will be executed when the file watcher runs

In this step I have specified that the stored procedure that will be executed by the File Watcher, when the file arrives. The stored procedure, sp_load_customer_files, doesn’t yet exist and will be created in Step 6.

BEGIN

   dbms_scheduler.create_program
   (
      program_name        => 'file_watcher_prog',
      program_type        => 'stored_procedure',
      program_action      => 'sp_load_customer_files',
      number_of_arguments => 1,
      enabled             => FALSE
   );
END;
/

Step 5 Defining metadata

In order for the new stored procedure, sp_load_customer_files, to access attributes of event that started the File Watcher, a call to dbms_scheduler.define_metadata_argument is required.

For more information about this program unit please refer to the documentation.

BEGIN

   dbms_scheduler.define_metadata_argument
   (
      program_name       => 'file_watcher_prog',
      metadata_attribute => 'event_message',
      argument_position  => 1
   );

END;
/

Step 6 Creating the supporting objects

This step creates a table where the contents of the files will be inserted into, along with the file name. To keep the example concise, no primary keys, indexes etc have been defined.

CREATE TABLE files_from_customers(file_name     VARCHAR2(100),
                                  file_contents CLOB);

The stored procedure that was first referenced in step 4 is now created. This procedure uses some attributes from the filewatcher object to obtain the file name. It then uses the dbms_lob packages to load the data from the file into the table.

CREATE OR REPLACE PROCEDURE sp_load_customer_files
(pt_payload IN sys.scheduler_filewatcher_result)
IS

 lc_clob           CLOB;
 lt_bfile          BFILE;
 li_warning        INTEGER;
 li_dest_offset    INTEGER := 1;
 li_src_offset     INTEGER := 1;
 li_lang_context   INTEGER := 0;

BEGIN
   INSERT INTO files_from_customers (file_name,
                                     file_contents)
   VALUES(
   pt_payload.directory_path || '' || pt_payload.actual_file_name,
   empty_clob())
   RETURNING file_contents INTO lc_clob;
   lt_bfile := BFILENAME(directory => 'ETL_DIR',
                         filename  => pt_payload.actual_file_name);

   dbms_lob.fileopen
   (
      file_loc => lt_bfile
   );

   dbms_lob.loadclobfromfile
   (
      dest_lob     => lc_clob,
      src_bfile    => lt_bfile,
      amount       => dbms_lob.getlength(file_loc =&gt; lt_bfile),
      dest_offset  => li_dest_offset,
      src_offset   => li_src_offset,
      bfile_csid   => NLS_CHARSET_ID('UTF8'),
      lang_context => li_lang_context,
      warning      =>; li_warning
   );

  dbms_lob.fileclose
  (
     file_loc => lt_bfile
  );

END sp_load_customer_files
/

Step 7: Creating a job

Create an Event-Based Job That References the File Watcher.

BEGIN

   dbms_scheduler.create_job
   (
      job_name        => 'file_watcher_job',
      program_name    => 'file_watcher_prog',
      event_condition => NULL,
      queue_spec      => 'the_file_watcher',
      auto_drop       => FALSE,
      enabled         => FALSE
    );

END;
/

Step 8: Enable All the objects

Enable all the objects that you have created by running:

BEGIN

   dbms_scheduler.enable
   (
      'the_file_watcher, file_watcher_prog, file_watcher_job'
   );

END;
/

Step 9: Seeing the results

Before a file arrives, query the table to show it is empty:

A file arrives into the directory that the File Watcher is monitoring.

When the File Watcher runs (as specified in Step 1) the contents of the new file are inserted into the table:

Step 10 Nothing has happened! (Optional)

So you have followed all the steps shown, double checked the code and nothing has happened. The file hasn’t loaded and your table is still empty.  You have my sympathy! As with anything with many moving parts something is bound not to work.

One tool I found invaluable in debugging these issues is looking at the run log for the job (This is the job created in step 7 and in is called “FILE_WATCHER_JOB” )

SQL Developer performs all the heavy listing when it comes to getting to this information.

From the SQL Developer Object Navigator select the Scheduler folder

Expand the folder and then the Jobs folder and you should see the job you created for the File Watcher, in this case it is called “FILE_WATCHER_JOB” (The job is created in Step 7)

Selecting the appropriate job will then bring up a list of tabs. Select the Run Log

From here you can see lots of useful information that should assist in your debugging.