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.

 

 

There is all the more reason to use database PL/SQL…

Oracle have published a white paper entitled: “With Oracle Database 12c there is all the more reason to use database PL/SQL” it’s author is Bryn Llewellyn who is the Product Manager for PL/SQL.

In this paper Bryn lists the reasons he hears from customers on why they are not using PL/SQL in the database and then methodically sets about demolishing each of these objections.

It is an accessible, interesting paper and well worth reading.

As Oracle have a habit of periodically changing (breaking!) their links you can download the paper from here.

Listing files from a Linux directory from within Oracle SQL or PL/SQL in 11g

In this article I will describe a method which enables you to list the contents of a Linux directory from within Oracle using the 11gR2 Preprocessor feature of Oracle External Tables.

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

After logging into a database session as a SYSDBA user, run the following commands:


CREATE DIRECTORY PREPROCESSOR_DIRECTORY AS '/home/oracle/preprocessor_scripts'
/
GRANT EXECUTE ON DIRECTORY PREPROCESSOR_DIRECTORY TO HR
/

The first command creates a new directory object, called PREPROCESSOR_DIRECTORY which points to a location on the file system.

The next command grants the execute privilege on the directory to the HR user. The execute privilege allows the HR to run scripts in this folder.

After switching to the file system and to the location specified by the directory object, create the following bash script and save it as list_files.sh (The file owner in my example is oracle)


#!/bin/bash
cd /home/oracle
/bin/ls -l

As you can see the script changes directory to the home directory and then lists the files.

The next step is to start a new database session as the HR user and create the following External Table:


CREATE TABLE home_directory
(
   fpermissions   VARCHAR2(10),
   flink          VARCHAR2(2),
   fowner         VARCHAR2(6),
   fgroup         VARCHAR2(6),
   fsize          VARCHAR2(100),
   fdate          VARCHAR2(100),
   fname          VARCHAR2(255)
)   
   ORGANIZATION EXTERNAL
   (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY PREPROCESSOR_DIRECTORY
      ACCESS PARAMETERS
      (                
       records delimited by newline
       preprocessor PREPROCESSOR_DIRECTORY:'list_files.sh'              
       fields terminated by whitespace               
       (
          fpermissions   position(1:10),
          flink          position(12:14),
          fowner         position(15:21),
          fgroup         position(22:28),
          fsize          position(28:37),
          fdate          position(37:49),
          fname          position(50:305)
       )
      )
   LOCATION ('list_files.sh')   
   )
REJECT LIMIT UNLIMITED    
/

The majority of the code shown above is the standard for creating an Oracle External Table. I will highlight the key points:

Line 18 shows the preprocessor command that calls the bash script, list_files.sh created in the preceding step. In order for this command to run, the user requires the execute privilege on the PREPROCESSOR_DIRECTORY object (which was granted here).

Lines 21 – 27 slices up the output from the bash script into the columns of the External Table.

Line 30 The location clause; Although not used in this example, this clause is mandatory, so I have pointed to the same bash script.

With the External Table in place, you can query to check what the output look like.


SELECT *
  FROM home_directory
/

Running this SQL on my environment produces the following output:

x

Whilst this proves you can now query the Linux directory from SQL, the output is relatively “raw”. The first line returned shows the total number of blocks used by files included in the directory listing and there are directories and files mixed in together.

You could go back and amend the External Table definition to exclude information that is not required, I prefer to clean up the output by creating a view of the data that I want to see. I like this method because when the requirements change and I need to see different information from the home directory I can simply create another view that returns that information.

Here is a view that just returns the files from the home directory:


CREATE OR REPLACE VIEW files_view
AS
   SELECT hd.fpermissions file_permissions,             
          hd.fowner       file_owner,  
          hd.fgroup       file_group,  
          hd.fsize        file_size, 
          hd.fdate        file_date,  
          hd.fname        file_name  
     FROM home_directory hd
    WHERE hd.fpermissions NOT LIKE 'total%'
      AND hd.fpermissions NOT LIKE 'd%'
/

Line 10 Excludes the total number of blocks row

Line 11 Excludes directories

With the view created I can now get query it to see just the files in the home directory:


SELECT fv.* 
  FROM files_view fv
/
 

Capture

Summary

In this article I have shown a method available that allows you to obtain a listing of a Linux directory.

Acknowledgements

This post was inspired by Adrian Billington’s comprehensive post on Listing Files with the External table preprocessor 

Sorting an Oracle Nested Table

This article demonstrates a method used to sort an Oracle nested table collection from within PL/SQL .

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.

The first step is to create a couple of schema level objects.


CREATE TYPE fruit_object AS OBJECT
(
   ID         NUMBER(10),
   FRUIT_NAME VARCHAR2(30)
);
/

CREATE TYPE fruit_table AS TABLE OF fruit_object;
/

The first type to be created is an object which contains the necessary attributes, which in this example is an identifier and a fruit name. Next a nested table is created based upon this object.

The objects need to be created at the database level and not within the PL/SQL code so that the SQL engine can “see” them. If anyone has a method which allows these objects to reside within PL/SQL please let me know via the comments.

With these structures in place, the code to populate and sort the collection is shown below. In order to keep the amount of code to a minimum, I have used a PL/SQL anonymous block.

DECLARE

 lnt_not_sorted  fruit_table := fruit_table();
 lnt_sorted      fruit_table := fruit_table();

BEGIN

 lnt_not_sorted.extend(3);

 lnt_not_sorted(1) := fruit_object(2, 'Banana');

 lnt_not_sorted(2) := fruit_object(99, 'Pineapple');

 lnt_not_sorted(3) := fruit_object(1, 'Apple');

 dbms_output.put_line('Unsorted...');

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

  dbms_output.put_line(lnt_not_sorted(i).id || ' ' || lnt_not_sorted(i).fruit_name );

 END LOOP;

 SELECT CAST(MULTISET(SELECT *
                        FROM TABLE(lnt_not_sorted)
                       ORDER BY 1 ) AS fruit_table )
 INTO lnt_sorted
 FROM dual;

 dbms_output.put_line('Sorted...');

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

  dbms_output.put_line(lnt_sorted(i).id || ' ' || lnt_sorted(i).fruit_name );

 END LOOP;

END;

In the declaration section (lines 3 & 4), I declare two variables of the nested table type that was created earlier. As the names reveal, one will be used for the initial unsorted records and another will hold the sorted records. Nested table collections need to be initialised before use and this has done here in the declaration section.

Moving to the executable part of the block, lines 8 – 14 makes space within the collection and then populates it with three rows. Each row of the collection, containing an id and fruit name is populated using the default constructor for the object. The id value will determine where each row should appear once the collection has been sorted.

Lines 18 – 23 output the collection showing its current unsorted order.

Lines 25 – 29, show the most relevant part of the code because this is where the collection is sorted. The SQL used may appear strange the first time you encounter it.

Starting from the inner select statement, The TABLE() operator converts the nested table collection into a row source that allows it to be used in a select statement, note the order by is on the first (id) column. Next the MULTISET operator is used, which tells Oracle to put the multiple rows returned by a select into a single collection object. Finally using the CAST function, Oracle is explicitly told what datatype to convert this to.

With the sorted collection now populated, the contents are displayed to prove they have been sorted.

When run the output of this anonymous block is:

Capture

Summary

In this article I have demonstrated one method that allows the sorting of a Nested Table collection.

Acknowledgements:

oracle-developer.net For the explanation of the Table Operator

The CAST\Multiset explanation is based upon Tony Andrews superb answer to the following question.

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 => '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:

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!