Using SQL with Associative Arrays of records in Oracle 12c

The ability of using SQL to operate on Associative Arrays or PL/SQL tables as they were known when I started working as a Database Developer is one of my favourite new features of 12c. I wasn’t aware of it until I read the post by Connor Mcdonald.

It’s announcement within the new features guide is easy to overlook:

“The table operator can now be used in a PL/SQL program on a collection whose data type is declared in PL/SQL. This also allows the data type to be a PL/SQL associative array. (In prior releases, the collection’s data type had to be declared at the schema level.)”

The following example was developed using the the pre-built Oracle Virtual Machine Database Database App Development image with the versions:

SQL Developer: 4.0.0.13

Oracle Database: 12.1.0.1.0

The first step is to create a package spec which will contain the record type and Associative Array.

CREATE OR REPLACE PACKAGE cake
IS

   TYPE cake_record IS RECORD (cake_name   VARCHAR2(100),
                               cake_price  NUMBER(4));

   TYPE cake_aa IS TABLE OF cake_record
      INDEX BY PLS_INTEGER;

END cake;

There should be nothing surprising with this code, a record type is defined (Lines 4 & 5) and then an Associative Array is defined (Lines 7 & 8) based on the record.

To demonstrate the new functionality I create the following anonymous block:

DECLARE

laa_cakes   cake.cake_aa;
ln_price    NUMBER(4);

BEGIN

   laa_cakes(1).cake_name := 'Eccles';
   laa_cakes(1).cake_price := 10;

   laa_cakes(2).cake_name := 'Cinnamon Bun';
   laa_cakes(2).cake_price := 40;

  SELECT c.cake_price
    INTO ln_price
    FROM table(laa_cakes) c
   WHERE c.cake_name = 'Cinnamon Bun';

   DBMS_OUTPUT.PUT_LINE('The price of the Cinnamon Bun is: '|| TO_CHAR(ln_price));                     

END;
/

Lines 3 – 12, I create a Associative Array of the type defined in the package and populate it with a couple of cakes and their prices. I then use SQL via the TABLE  pseudofunction to query the Associative Array to tell me the price of one of the cakes.

And once run, I see the following output:

Capture

Before 12c I would have had to start looping through the Associative Array to look for the item of interest.

It is important to note that in order to use the TABLE pseudofunction the Associative Array has to reside in a package spec.

Summary

In this post I have demonstrated how to use SQL with Associative Array of Records.

Acknowledgements

Connor Mcdonald

A simple example of using the Oracle ROLLUP extension

Recently I needed to find out how many rows in a table had a certain status and display a grand total of the number of objects.

In this post, rather than create a demo table with the various status I am going to base my example on the user_objects data dictionary view.

Using the following query I could see the types of objects as well as how many of each object I had.

SELECT object_type, 
       COUNT(*)
  FROM user_objects
 GROUP BY object_type
/

Capture

All that was missing was the total (which should be 37). After various attempts to roll my own query to produce a total which soon started to get unwieldy or over complicated, I knew there must be a better way. A quick search and I found this excellent answer on Stack Overflow from Rob van Wijk which used the Oracle Group By Extension, ROLLUP

Here is the query amended to use ROLLUP, along with the output. Note the total.

SELECT object_type, 
       COUNT(*)
  FROM user_objects
 GROUP BY ROLLUP(object_type)
/

Capture2

Amending my query to use ROLLUP was trival, (I only had to change line 4) and I now have the total I required produced by a straightforward SQL query.

Acknowledgements

Rob van Wijk

 

In Oracle SQL, why doesn’t ROWNUM = 2 work?

The inspiration for this post came after recently overhearing a developer asking why his query was not returning a row. It had been working fine, the only change was instead of ROWNUM = 1, he had changed it to ROWNUM = 2.

The query was could have been doing something similar to the following:


SELECT x.*
  FROM (SELECT e.employee_id,
               e.last_name,
               e.salary
          FROM employees e
         ORDER BY salary DESC) x
 WHERE ROWNUM = 1 
/

which returns information about the top earning employee.

However when the last line of the query is changed to ROWNUM = 2 to find the second highest earning employee no rows are returned and this is what was causing confusion.

I have read enough Tom Kyte to understand superficially why the change stopped the query from working but not in enough detail to stand up in front of my peers or even my cat for that matter and confidently explain why. This post is my attempt to change that.

What is ROWNUM? In Oracle terms, ROWNUM is a pseudocolumn which returns a number for each row returned by a query. The ROWNUM returned is not permanently assigned to a row. A ROWNUM value is assigned after it passes the predicate part of the query but before any sorting or aggregation. It is important to know that the ROWNUM is only incremented after it has been assigned.

Stepping through our example, where ROWNUM = 2. The first row fetched is assigned a ROWNUM of 1 and makes this condition false (So at this point ROWNUM = 1, we are looking for ROWNUM = 2 or to put it another way 1 = 2 which of course is false), the second row to be fetched is now the first row and ROWNUM is still 1 (It hasn’t incremented because it wasn’t assigned) so again the condition is false and this repeats for all rows so none are returned.

So how do you find the second highest earning employee? That will be the subject of a future post.

Acknowledgements

On ROWNUM and Limiting Results By Tom Kyte

Does using a sequence in a PL/SQL expression improve performance?

In versions of the Oracle Database prior to 11g Release 1, to populate a variable with the next value from a sequence you would have call the NEXTVAL function from within a SQL statement. From Oracle Database 11g Release 1 you can now use the NEXTVAL function in a PL/SQL expression.

So other than less typing, does using a sequence in this way improve performance?

The following examples are all based on the following sequence

CREATE SEQUENCE emp_seq
/

 
and were developed 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.

To recap, the pre 11g method was:

...
SELECT emp_seq.nextval
  INTO some_variable
  FROM dual;
...

and from 11g Release one you can use the following:

...
some_variable := emp_seq.nextval;
...

Better Performance?

Using the method described by Adrian Billington you will be able see if the 11g method improves performance.

ALTER SESSION SET SQL_TRACE = TRUE
/

DECLARE

   emp_id PLS_INTEGER;

BEGIN
   
   FOR i IN 1 .. 10000 
   LOOP
   
      emp_id := emp_seq.NEXTVAL;   
   
   END LOOP;

   FOR i IN 1 .. 10000 
   LOOP
  
      SELECT emp_seq.NEXTVAL AS nval 
        INTO emp_id 
        FROM dual;
   
   END LOOP;

END; 
/

SQL Trace is enabled and the new 11g method of using NEXTVAL via an expression is called 10000 times after which the pre 11g method of using a SQL statement is also called 10000 times. Note the alias of nval is used with the SQL statement method this will help us identify the SQL statement method.

Here is the output from the trace file. The first entry is for the new 11g method and the second entry is for pre 11g method (as proved by the use of the alias nval)

20140502_TraceFile

The trace file shows very little differences when using the new 11g method. Which is not surprising as the trace file also shows that “under the covers” Oracle has not changed the implementation for the new 11g method and NEXTVAL is still obtained via a SQL statement.

Summary

In this article I have shown the two methods that are available to populate a variable with the next value from a sequence and that there is no performance gains to be had by choosing one method over another.

Acknowledgements

Adrian Billington (oracle-developer.net)

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.