Category: SQL

Using INSERT ALL with related tables

In this article I will demonstrate a method where the INSERTALL statement can be used to insert rows into two tables that are related by a foreign key constraint.

The following example was developed and tested using the Pre-Built Oracle Database App Development VM which at the time of this post used Oracle Database 12.1.0.2.0  & SQL Developer 4.0.3.16.  The example was created within the standard HR schema.

First I create two tables:


CREATE TABLE parent_tbl (the_pk    NUMBER PRIMARY KEY,
                         object_id NUMBER);

CREATE TABLE child_tbl (the_pk      NUMBER PRIMARY KEY,
                        the_fk      NUMBER,
                        object_name VARCHAR2(30));

Next I add a foreign to define the relationship between these tables and two sequences used to populate the primary keys:


ALTER TABLE child_tbl
ADD CONSTRAINT child_tbl_fk1 FOREIGN KEY (the_fk)
   REFERENCES parent_tbl (the_pk);

CREATE SEQUENCE parent_tbl_seq;

CREATE SEQUENCE child_tbl_seq START WITH 500;

Next I ensure that the foreign key relationship is working as expected by trying to insert a record into the child table with value for the_fk column that doesn’t exist in parent_tbl:


INSERT INTO child_tbl
(the_pk,
the_fk,
object_name)
VALUES
(child_tbl_seq.nextval,
999,
'SomeObject');

Attempting to run this statement results in the expected error message:

SQL Error: ORA-02291: integrity constraint (HR.CHILD_TBL_FK1) violated - parent key not found

With the tables and relationship between them in place I can now demostrate how to use INSERTALL to insert information from user_objects into the parent and child tables.


INSERT ALL
INTO parent_tbl(the_pk, object_id) 
VALUES (parent_tbl_seq.nextval, 
        object_id)
INTO child_tbl(the_pk, the_fk, object_name) 
VALUES (child_tbl_seq.nextval,
        parent_tbl_seq.currval,
        object_name)
SELECT uo.object_id,
       uo.object_name
FROM user_objects uo
/

Lines 2 – 4 insert into the parent table, note the use of the sequence to populate the primary key.

Lines 5 – 8 insert into the child table, the important part (and the focus of this article) is to understand how the foreign key column, the_fk is populated. It uses currval of the sequence that was used to populate the parent table.

Thanks go to Tom Kyte for coming up with this elegant solution.

Summary

In this article I have demonstrated how to use INSERTALL to insert into two tables that are related by a foreign key.

Acknowledgements

The idea for this article came from Tom Kyte’s answer to this Ask Tom question.

No surprises with Oracle 12c Identity Column performance

Oracle 12c introduced the Identity Column. You can find out more about this feature in the new features guide and within the CREATE TABLE documentation.

In this article I will use Tom Kyte’s run stats utility to compare the performance of the IDENTITY column with the explicit use of a sequence object. The script below will be used to insert 10,0000, 100,000 and finally a million rows.

The tests were performed using Virtual Box running the pre built Database App Development VM. The database version at this time was 12.1.0.2.0 and all the examples were developed using SQL Developer 4.0.3.16

To support the tests,  the following objects are required. Firstly, a table using the IDENTITY column is created. Note the syntax for creating an IDENTITY column. In addition as I know there will be some large inserts into this table I have adjusted cache size of the sequence accordingly.


CREATE TABLE t_identity(id      NUMBER         GENERATED AS IDENTITY CACHE 1000
                                               CONSTRAINT t_identity_pk PRIMARY KEY,
                        details VARCHAR2(32))
/

Next a table and a sequence which will be used to hold the results of the inserts via a regular Oracle sequence is created. Again the sequence cache size has been increased from the default.

CREATE TABLE t_seq(id      NUMBER CONSTRAINT t_seq_pk PRIMARY KEY,
                   details VARCHAR2(32))
/

CREATE SEQUENCE s1 CACHE 1000
/

Below is the test script. As a brief overview, it initialises the call to the runstats package, it then inserts the required number of records into the table with the IDENTITY column.

The runstats package is called again to show that the first part of the processing has finished and the second part is about to start. The second insert is identical to the first one with the exception of the explicit call to the sequence object.

Thanks to Oracle Base for the tip about using TIMESTAMP as a seed to dbms_random. I am not advocating using row by row processing to insert volumes of data of this size in the real world!


BEGIN

   runstats_pkg.rs_start();

END;
/

DECLARE

   l_data VARCHAR2(32);
   l_seed VARCHAR2(32);

BEGIN

   l_seed := TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF');

   dbms_random.seed (val => l_seed);

   FOR i IN 1 .. 1000000
   LOOP

      l_data := dbms_random.string(opt => 'a', len => 32);

      INSERT INTO t_identity(details)
      VALUES(l_data);

   END LOOP;

   COMMIT;

END;
/

BEGIN

   runstats_pkg.rs_middle();

END;
/

DECLARE

   l_data VARCHAR2(32);
   l_seed VARCHAR2(32);

BEGIN

   l_seed := TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF');

   dbms_random.seed (val => l_seed);

   FOR i IN 1 .. 1000000
   LOOP

      l_data := dbms_random.string(opt => 'a', len => 32);

      INSERT INTO t_seq(id,
                        details)
      VALUES(s1.nextval,
             l_data);

   END LOOP;

   COMMIT;

END;
/

BEGIN

   runstats_pkg.rs_stop(1000);

END;
/

Here is the runstats output for each of the runs

10,000 rows inserted


Run1 ran in 106 cpu hsecs
Run2 ran in 105 cpu hsecs
run 1 ran in 100.95% of the time
...
Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff  Pct
171,097   171,432   335   99.80%

100,000 rows inserted


Run1 ran in 1216 cpu hsecs
Run2 ran in 1156 cpu hsecs
run 1 ran in 105.19% of the time
...
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff      Pct
1,719,582   2,061,024   341,442   83.43%

1,000,000 rows inserted

Run1 ran in 12308 cpu hsecs
Run2 ran in 11835 cpu hsecs
run 1 ran in 104% of the time
...
Run1 latches total versus runs -- difference and pct
Run1         Run2         Diff      Pct
18,480,661   18,761,711   281,050   98.50%

and the difference between the two methods is negligible.

Summary

In this article, using Tom Kyte’s runstats utility, I have invested the performance of the Oracle 12c new feature; IDENTITY column

Acknowledgements

Tom Kyte for his seminal package runstats

 

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:

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 (2)

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

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
/

201308_c

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:

sortednestedtable

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 CASTMultiset explanation is based upon Tony Andrews superb answer to the following question.