MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle XE’ Category

Stable set of rows?

with 10 comments

My students are into the upload lab and they’ve encountered the wonderful ORA-30926 error from a MERGE statement. An example of the MERGE for an upload is in this earlier blog post.

This is the wonderful error message, which doesn’t seem to have meaning for many. The key is the non-deterministic where clauses phrase. That phrase means that the the query inside the USING clause returns a non-unique set of rows. The returned rows can’t be matched uniquely against the target table in the ON clause. The ON clause is where the MERGE statment matches the source query’s rows against the target table’s rows. The best join condition in a MERGE statement is one between a surrogate primary and foreign key column.

Error: ORA-30926
Text:  Unable TO GET a stable SET OF ROWS IN the SOURCE TABLES.
--------------------------------------------------------------------
Cause:  A stable SET OF ROWS could NOT be got because OF LARGE dml 
        activity OR a non-deterministic WHERE clause. 
 
Action: Remove any non-deterministic WHERE clauses AND reissue the dml.

The problem is very much like when you write what you think is a single-row subquery but find out it’s actually a multiple-row subquery when it return an ORA-01422 error in Oracle.

As a rule, I’ve found that using the DISTINCT operator in the source SELECT statement fixes it most of the time because the join isn’t returning a unique set of rows. Although, the better solution requires that you identify how to gain a unique result set. Alternatively, you need to re-examine the logic of your WHERE clause. It also happens when the SELECT clause returns date-time data types like SYSDATE instead of date data types. A date-time can be converted by using the TRUNCate function like this:

MERGE INTO TABLE_NAME target
USING
 (SELECT   ...
  ,        TRUNC(SYSDATE) AS creation_date
  FROM     ...) SOURCE
ON (target.primary_key_column = SOURCE.primary_key_column)
WHEN MATCHED THEN
UPDATE SET last_updated_by = SOURCE.last_updated_by
,          last_update_date = SOURCE.last_update_date
WHEN NOT MATCHED THEN
INSERT VALUES
( column_list_of_values );

Hope this helps my students and others … 😉

Written by maclochlainn

March 5th, 2010 at 1:43 am

Posted in Oracle,Oracle XE,sql

SQL Aggregation Tutorial

without comments

I’ve been working on a Basic Aggregation tutorial for my students. I think this might be close to what may benefit them. However, I thought it would be great to put it out there and solicit ideas. If you have some on improving this post, please let me know.

My first take at the post …

This is a lesson on basic aggregation in SQL. Aggregation in SQL means counting, adding, and grouping by results of counts or sums. Aggregation is a critical part of using the SQL language. At a basic level, aggregation includes the COUNT, SUM, AVERAGE, MAX, and MIN aggregation functions; and the ORDER BY, GROUP BY, and HAVING clauses.

You’ll find the setup scripts for these examples at the bottom of this blog page. The best way to use this page is to copy the setup code, run it in your database, and then test the examples as you work though them.

Data set ↓

This section discusses the data set. You unfold it by clicking on the bold text above.

The illustration of aggregation is a challenge because a trivial set of numbers doesn’t present much challenge or sometimes relevance but it shows the basic concept. A more applied sample set makes mental calculation and comprehension of functions more difficult. As a result of that trade off, this uses two copies of the ordinal numbers (ordinal numbers are the single digit integers from zero to nine).

After you run the setup script, you can display the data set with the following query.

-- Oracle only SQL*Plus formatting command to smooth output display.
SET PAGESIZE 99
 
-- Query ordered by the sets and then the integers in ascending order.
SELECT * FROM ordinal ORDER BY 2, 4;

You could have sorted them more easily by using the ORDINAL_ID surrogate key column but it wouldn’t demonstrate the ORDER BY clause. In some database, like Oracle and MySQL, the ORDER BY lets you use column names, column aliases, or the position order of the column in the SELECT clause. Unfortunately, this isn’t true across all databases.

The ORDER BY clause does let you see that you can set nested ordering, like the numbers inside the data sets that are qualified by the LIST_SET column. This is demonstrated in the output below.

ORDINAL_ID LIST_SET             LIST_NAME  LIST_VALUE
---------- -------------------- ---------- ----------
         1 Value Set A          Zero                0
         2 Value Set A          One                 1
         3 Value Set A          Two                 2
         4 Value Set A          Three               3
         5 Value Set A          Four                4
         6 Value Set A          Five                5
         7 Value Set A          Six                 6
         8 Value Set A          Seven               7
         9 Value Set A          Eight               8
        10 Value Set A          Nine                9
        11 Value Set A
        12 Value Set B          Zero                0
        13 Value Set B          One                 1
        14 Value Set B          Two                 2
        15 Value Set B          Three               3
        16 Value Set B          Four                4
        17 Value Set B          Five                5
        18 Value Set B          Six                 6
        19 Value Set B          Seven               7
        20 Value Set B          Eight               8
        21 Value Set B          Nine                9
        22 Value Set B

Single row aggregation function use ↓

This section discusses and demonstrates the five aggregation functions in the post. They return a single row of data. Like the other sections, you unfold this by clicking on the bold text above.

COUNT function

The COUNT function has two behaviors that may apply. One is when you count rows and the other is when you count values. Both return only a single row.

Counting by Reference: The following example demonstrates counting rows. In this scenario, the COUNT function takes a single call parameter, which is an asterisk (*). The asterisk actually counts the references to the rows in the table, and in Oracle is equivalent to COUNT(ROWID). The pseudo column ROWID points to the physical block address where the row is stored in the database. As such, the asterisk acts very much like a pointer in the C or C++ language (that is if you substitute a block address for a memory address).

SELECT COUNT(*) FROM ordinal;

It returns

  COUNT(*)
----------
        22

When you count rows, you count them whether they contain values or not. It is possible that you could insert a meaningless row with null values for all columns in a table that’s unconstrained. Moreover, the COUNT function counts all rows no matter whether contain any values.

Counting by Value: The following examples demonstrate approaches to counting values. It’s important to note that when you count values, you ignore null values. You can also count distinct things or all things when you count by value.

The LIST_SET column doesn’t contain any null values (as you can see by inspecting the data set early in this blog page). The following counts the number of values in the LIST_SET column. You could also substitute COUNT(ALL list_set) and it would return the same thing because ALL is the default and always provided unless you override it with a DISTINCT keyword.

SELECT COUNT(list_set) FROM ordinal;

It returns:

  COUNT(*)
----------
        22

Since the LIST_SET column only contains one of two values, you can count that to make sure. You do it with the following syntax:

SELECT COUNT(DISTINCT list_set) FROM ordinal;

It returns:

  COUNT(*)
----------
         2

The LIST_NAME and LIST_VALUE both contain two null values. Let’s see what they return when we count all or distinct values. This example simplifies it a step more by performing both counts side-by-side. This is possible because both columns return a single row.

SELECT COUNT(ALL list_name) AS name_number
,      COUNT(DISTINCT list_value) AS value_number
FROM   ordinal;

It returns the twenty total string values found in the first column and the ten unique numeric values found in the second column. The COUNT function with the DISTINCT filter acts like a COUNT function with a GROUP BY clause on the LIST_VALUE column, as you’ll see later in the discussion.

NAME_NUMBER VALUE_NUMBER
----------- ------------
         20           10

SUM, AVERAGE, MAX, and MIN functions

Math Operations by Value: These math aggregations are done with the SUM, AVERAGE, MAX, and MIN aggregation functions. They’re restricted to columns that contain numeric values. Each of them support the default ALL or DISTINCT keywords and they behave much as the COUNT function discussed earlier.

There is a significant difference between their functionality. You can count unique strings but you can’t sum a column of numbers based on that other column without a GROUP BY clause. You see examples of the GROUP BY in the next part of this post.

Since these behave more or less the same, their examples are grouped together in sections by summing and averaging together, and taking the maximum and minimum values together. For reference, the ordinal numbers sum to 45, two sets of them are naturally 90; and the average of evenly dispersed numbers is half, or 45, and their unique values 4.5.

SELECT SUM(ALL list_value) AS sum_all
,      SUM(DISTINCT list_value) AS sum_distinct
,      AVG(ALL list_value) AS avg_all
,      AVG(DISTINCT list_value) AS avg_distinct
FROM   ordinal;

It returns:

   SUM_ALL SUM_DISTINCT    AVG_ALL AVG_DISTINCT
---------- ------------ ---------- ------------
        90           45        4.5          4.5

The next section makes the same comparison with the MAX, and MIN functions. The maximum or minimums are the same because they the uniqueness doesn’t change the minimum or maximum of the sets.

SELECT MIN(ALL list_value) AS min_all
,      MIN(DISTINCT list_value) AS min_distinct
,      MAX(ALL list_value) AS max_all
,      MAX(DISTINCT list_value) AS max_distinct
FROM   ordinal;

It returns:

   MIN_ALL MIN_DISTINCT    MAX_ALL MAX_DISTINCT
---------- ------------ ---------- ------------
         0            0          9            9

Multiple row aggregation function use ↓

This section discusses and demonstrates the five aggregation functions where they return more than a single row of data, which involves grouping by non-aggregated columns. This section covers the GROUP BY and HAVING clauses. Like the other sections, you unfold this by clicking on the bold text above.

COUNT function

Counting by Groups: The following example demonstrates how we can count rows or values by groups. The example counts rows by reference and value, but it does so by grouping the return values on the unique string values in the LIST_SET column.

1
2
3
4
5

SELECT   list_set AS grouping_by_column
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
GROUP BY list_set;

It returns the following values:

GROUPING_BY_COLUMN     COUNT(*) COUNT(LIST_VALUE)
-------------------- ---------- -----------------
VALUE SET A                  11                10
VALUE SET B                  11                10

The results show that there are eleven rows for each set, and that ten of the rows contain values while one holds a null value.

You can also group on columns that contain null values or sets of columns. The following example groups by two columns, where one contains a null value.

1
2
3
4
5
6
7
8

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
WHERE    list_set = 'Value Set A'
GROUP BY list_set
,        list_name;

It returns the following values:

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO   COUNT(*) COUNT(LIST_VALUE)
-------------------- ----------------- ---------- -----------------
VALUE SET A          Zero                       1                 1
VALUE SET A          Five                       1                 1
VALUE SET A          Three                      1                 1
VALUE SET A          Four                       1                 1
VALUE SET A          One                        1                 1
VALUE SET A          Two                        1                 1
VALUE SET A          Eight                      1                 1
VALUE SET A          Nine                       1                 1
VALUE SET A          Seven                      1                 1
VALUE SET A          Six                        1                 1
VALUE SET A                                     1                 0

The last row returned is the one where the grouping by column value and counted column value are null. You should note that the GROUP BY applies to a string column and the return set includes a row grouped by its null value. The count of null column values is always zero.

Another thing that you may have not noticed is that the rows are no longer in ascending numeric order by LIST_NAME or LIST_VALUE columns. There’s a multiple edge sword when you examine why this occurs. Basically with regard to the LIST_NAME column, Oracle and many other databases use advanced sorting algorithms which may leave non-ordered sets as outcomes. The LIST_VALUE column is not in the GROUP BY clause and therefore can’t be used as an ORDER BY key.

When you attempt to use a column outside of the GROUP BY clause to order the return set, like this:

1
2
3
4
5
6
7
8
9

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
WHERE    list_set = 'Value Set A'
GROUP BY list_set
,        list_name
ORDER BY list_value;

You would see the following error in an Oracle Database 10g instance:

ORDER BY list_value
         *
ERROR at line 9:
ORA-00979: NOT a GROUP BY expression

You can filter this particular set because the conditions are limited, but this wouldn’t be a good idea in many other solution sets. However, it certainly highlights some of the potential for extraordinary sorting of result sets.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
WHERE    list_set = 'Value Set A'
GROUP BY list_set
,        list_name
ORDER BY CASE
           WHEN list_name = 'Zero'  THEN 0
           WHEN list_name = 'One'   THEN 1
           WHEN list_name = 'Two'   THEN 2
           WHEN list_name = 'Three' THEN 3
           WHEN list_name = 'Four'  THEN 4
           WHEN list_name = 'Five'  THEN 5
           WHEN list_name = 'Six'   THEN 6
           WHEN list_name = 'Seven' THEN 7
           WHEN list_name = 'Eight' THEN 8
           WHEN list_name = 'Nine'  THEN 9
         END;

It returns the following values:

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO   COUNT(*) COUNT(LIST_VALUE)
-------------------- ----------------- ---------- -----------------
VALUE SET A          Zero                       1                 1
VALUE SET A          One                        1                 1
VALUE SET A          Two                        1                 1
VALUE SET A          Three                      1                 1
VALUE SET A          Four                       1                 1
VALUE SET A          Five                       1                 1
VALUE SET A          Six                        1                 1
VALUE SET A          Seven                      1                 1
VALUE SET A          Eight                      1                 1
VALUE SET A          Nine                       1                 1
VALUE SET A                                     1                 0

A better way to sort this type of data would involve putting it into an inline view and then joining it against the base data set. This allows you to use a non-grouping column as the sort key in an ORDER BY clause. Here’s an example of that approach but note it does take system overhead and should only be done when SQL must perform the sort operation.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT   ilv.grouping_by_not_null
,        ilv.group_by_null_too
,        ilv.row_count
,        ilv.column_count
FROM    (SELECT   list_set AS grouping_by_not_null
         ,        list_name AS group_by_null_too
         ,        COUNT(*) AS ROW_COUNT
         ,        COUNT(list_value) AS column_count
         FROM     ordinal
         WHERE    list_set = 'Value Set A'
         GROUP BY list_set
         ,        list_name) ilv LEFT JOIN ordinal o
ON       ilv.grouping_by_not_null = o.list_set
AND      ilv.group_by_null_too = o.list_name
ORDER BY o.list_value;

That returns a perfectly ordered set because the LEFT JOIN lets us capture the null value in the inline view without matching the two columns in the join condition. It returns the following set:

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO  ROW_COUNT COLUMN_COUNT
-------------------- ----------------- ---------- ------------
Value Set A          Zero                       1            1
Value Set A          One                        1            1
Value Set A          Two                        1            1
Value Set A          Three                      1            1
Value Set A          Four                       1            1
Value Set A          Five                       1            1
Value Set A          Six                        1            1
Value Set A          Seven                      1            1
Value Set A          Eight                      1            1
Value Set A          Nine                       1            1
Value Set A                                     1            0

SUM, AVERAGE, MAX, and MIN functions

Math Operations by Groups: These math aggregations are done with the SUM, AVERAGE, MAX, and MIN aggregation functions. Like the previous single row return examples, they’re restricted to columns that contain numeric values. While they also support the default ALL or DISTINCT keywords, it didn’t seem necessary to demonstrate more of those behaviors here.

The following sums, averages, and gets both the minimum and maximum value by unique LIST_SET column values:

SELECT   list_set AS grouping_by_not_null
,        SUM(list_value) AS ordinal_sum
,        AVG(list_value) AS ordinal_avg
,        MIN(list_value) AS ordinal_min
,        MAX(list_value) AS ordinal_max
FROM     ordinal
GROUP BY list_set;

It returns the following two rows:

GROUPING_BY_NOT_NULL ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX
-------------------- ----------- ----------- ----------- -----------
VALUE SET A                   45         4.5           0           9
VALUE SET B                   45         4.5           0           9

Expanding the GROUP BY criteria to include the LIST_NAME column, you see what happens when the SUM and AVG functions work with only null values. They always return null when adding only column values with a null value. This is handy as you’ll see in the selective aggregation discussion in a subsequent tutorial.

As done in the prior example, this filters out the second value set, to focus on what’s happening with the rows grouped on two columns.

1
2
3
4
5
6
7
8
9
10

SELECT    list_set AS grouping_by_not_null
,         list_name AS group_by_null_too
,         SUM(list_value) AS ordinal_sum
,         AVG(list_value) AS ordinal_avg
,         MIN(list_value) AS ordinal_min
,         MAX(list_value) AS ordinal_max
FROM      ordinal
WHERE NOT list_set = 'Value Set B'
GROUP BY  list_set
,         list_name;

It returns the following set of records, which are clearly out of order based on the algorithms that optimize aggregation. It’s not hard to read because there aren’t too many rows returned.

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX
-------------------- ----------------- ----------- ----------- ----------- -----------
VALUE SET A          Zero                        0           0           0           0
VALUE SET A          Five                        5           5           5           5
VALUE SET A          Three                       3           3           3           3
VALUE SET A          Four                        4           4           4           4
VALUE SET A          One                         1           1           1           1
VALUE SET A          Two                         2           2           2           2
VALUE SET A          Eight                       8           8           8           8
VALUE SET A          Nine                        9           9           9           9
VALUE SET A          Seven                       7           7           7           7
VALUE SET A          Six                         6           6           6           6
VALUE SET A

If SQL must sort these into numeric order, it can do so like the prior example with the result from the COUNT function. You must instrument a solution for it through an inline view. The following shows you how to do that.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SELECT   ilv.grouping_by_not_null
,        ilv.group_by_null_too
,        ilv.ordinal_sum
,        ilv.ordinal_avg
,        ilv.ordinal_min
,        ilv.ordinal_max
FROM    (SELECT   list_set AS grouping_by_not_null
         ,        list_name AS group_by_null_too
         ,        SUM(list_value) AS ordinal_sum
         ,        AVG(list_value) AS ordinal_avg
         ,        MIN(list_value) AS ordinal_min
         ,        MAX(list_value) AS ordinal_max
         FROM     ordinal
         WHERE    list_set = 'Value Set B'
         GROUP BY list_set
         ,         list_name) ilv LEFT JOIN ordinal o
ON       ilv.grouping_by_not_null = o.list_set
AND      ilv.group_by_null_too = o.list_name
ORDER BY o.list_value;

Now you should see an ordered set like this:

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX
-------------------- ----------------- ----------- ----------- ----------- -----------
VALUE SET B          Zero                        0           0           0           0
VALUE SET B          One                         1           1           1           1
VALUE SET B          Two                         2           2           2           2
VALUE SET B          Three                       3           3           3           3
VALUE SET B          Four                        4           4           4           4
VALUE SET B          Five                        5           5           5           5
VALUE SET B          Six                         6           6           6           6
VALUE SET B          Seven                       7           7           7           7
VALUE SET B          Eight                       8           8           8           8
VALUE SET B          Nine                        9           9           9           9
VALUE SET B

Math Operations with the HAVING clause: The HAVING clause is very useful when you want to find rows that duplicate a column or set of column values. It’s also useful for sorting data sets.

The HAVING clause effects all aggregate rows and acts somewhat like a filter in the WHERE clause of a non-aggregated query. The difference is that the WHERE clause filters the base result set, while the HAVING clause filters the aggregated result set.

The following example captures the sum and average for only the odd LIST_VALUE column values. It does that by using modular (or modulo) mathematics through the MOD function in Oracle’s SQL implementation. Modulo math works by calculating the remainder of integer division. When you divide any number by two and the result is one, then you have an odd number.

Here’s the sample program that illustrates the HAVING clause in a CASE statement. The CASE statement only adds the number when it is an odd number. Any even number is zeroed out. The comparison operator ensures that only rows are returned when the SUM is greater than zero, which only happens for odd numbers.

1
2
3
4
5
6
7
8
9
10
11

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        SUM(list_value) AS ordinal_sum
,        AVG(list_value) AS ordinal_avg
FROM     ordinal
WHERE    list_set = 'Value Set A'
HAVING   SUM(CASE
               WHEN MOD(list_value,2) = 1 THEN list_value ELSE 0
             END) > 0
GROUP BY list_set
,        list_name;

The data set, as you’ve seen with others isn’t sorted in ascending order because of how the algorithms work. Since there are only five rows returned, it is easy to see that the HAVING clause let us narrow the return set to odd numbers. What we might overlook is that the HAVING is always a filtering statement. This means we need to have a comparative operator tied to the HAVING return value.

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG
-------------------- ----------------- ----------- -----------
Value Set A          Five                        5           5
Value Set A          Three                       3           3
Value Set A          One                         1           1
Value Set A          Nine                        9           9
Value Set A          Seven                       7           7

As you’ve seen in early examples, you can also put this inside an inline view and sort the aggregated results. Here’s the code to accomplish that.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT   ilv.grouping_by_not_null
,        ilv.group_by_null_too
,        ilv.ordinal_sum
,        ilv.ordinal_avg
FROM    (SELECT   list_set AS grouping_by_not_null
         ,        list_name AS group_by_null_too
         ,        SUM(list_value) AS ordinal_sum
         ,        AVG(list_value) AS ordinal_avg
         FROM     ordinal
         WHERE    list_set = 'Value Set A'
         HAVING   SUM(CASE
                        WHEN MOD(list_value,2) = 1 THEN list_value ELSE 0
                      END) > 0
         GROUP BY list_set
         ,        list_name) ilv LEFT JOIN ordinal o
ON       ilv.grouping_by_not_null = o.list_set
AND      ilv.group_by_null_too = o.list_name
ORDER BY o.list_value;

It naturally returns the ordered set of aggregated results from only odd numbers in the base set of values.

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG
-------------------- ----------------- ----------- -----------
Value Set A          One                         1           1
Value Set A          Three                       3           3
Value Set A          Five                        5           5
Value Set A          Seven                       7           7
Value Set A          Nine                        9           9

Setup Script ↓

The drop down items, unfold with source code to seed the examples.

Oracle SQL Join Supporting Script ↓

This has the setup script for the example tables.

-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME = 'ORDINAL') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name;
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name = 'ORDINAL_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create the aggregation sample table and sequence.
CREATE TABLE ordinal
( ordinal_id  NUMBER
, list_set    VARCHAR2(20)
, list_name   VARCHAR2(5)
, list_value  NUMBER);
 
CREATE SEQUENCE ordinal_s1;
 
-- Seeding values in the aggregation sample table.
DECLARE
  -- Define local types.
  TYPE listg IS TABLE OF VARCHAR2(20);
  TYPE listn IS TABLE OF NUMBER;
  TYPE lists IS TABLE OF VARCHAR2(5);
 
  -- Declare local variables.
  groups LISTG := listg('Value Set A','Value Set B');
  labels LISTS := lists('Zero','One','Two','Three','Four','Five','Six','Seven','Eight','Nine');
  valuen LISTN := listn(0,1,2,3,4,5,6,7,8,9);
 
BEGIN
  -- Outer loop sets the group level.
  FOR i IN 1..groups.COUNT LOOP
    -- Inner loop sets the row level.
    FOR j IN 1..labels.COUNT LOOP
      INSERT INTO ordinal
      VALUES (ordinal_s1.NEXTVAL,groups(i),labels(j),valuen(j));
    END LOOP;
 
    -- Insert the null values for each group.
    INSERT INTO ordinal
    VALUES (ordinal_s1.NEXTVAL,groups(i),NULL,NULL);
  END LOOP;
  COMMIT;
END;
/
 
-- Commit the inserts.
COMMIT;

MySQL SQL Join Supporting Script ↓

This has the setup script for the example tables.

-- Conditionally drop the table.
SELECT 'DROP TABLE ordinal' AS Statement;
DROP TABLE IF EXISTS ordinal;
 
-- Create the table.
SELECT 'CREATE TABLE ordinal' AS Statement;
CREATE TABLE ordinal
( ordinal_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, list_set    VARCHAR(20)
, list_name   VARCHAR(5)
, list_value  INT UNSIGNED);
 
-- Seed the row values.
SELECT 'INSERT INTO ordinal' AS Statement;
INSERT INTO ordinal
VALUES
 (NULL,'Value Set A','Zero','0')
,(NULL,'Value Set A','One','1')
,(NULL,'Value Set A','Two','2')
,(NULL,'Value Set A','Three','3')
,(NULL,'Value Set A','Four','4')
,(NULL,'Value Set A','Five','5')
,(NULL,'Value Set A','Six','6')
,(NULL,'Value Set A','Seven','7')
,(NULL,'Value Set A','Eight','8')
,(NULL,'Value Set A','Nine','9')
,(NULL,'Value Set A',NULL,NULL)
,(NULL,'Value Set B','Zero','0')
,(NULL,'Value Set B','One','1')
,(NULL,'Value Set B','Two','2')
,(NULL,'Value Set B','Three','3')
,(NULL,'Value Set B','Four','4')
,(NULL,'Value Set B','Five','5')
,(NULL,'Value Set B','Six','6')
,(NULL,'Value Set B','Seven','7')
,(NULL,'Value Set B','Eight','8')
,(NULL,'Value Set B','Nine','9')
,(NULL,'Value Set B',NULL,NULL);
 
-- Commit the inserts.
COMMIT;

Written by maclochlainn

February 26th, 2010 at 1:29 am

Posted in MySQL,Oracle,Oracle XE,sql,SQL Server 2008

Merge Statement for ETL

with 2 comments

While working through examples for my students on uploading data, I thought it would be interesting to demonstrate how to create a re-runnable upload. Especially when chatting with a friend who was unaware that you could use joins inside the source element of a MERGE statement. Naturally, the MERGE statement seemed like the best approach in an Oracle database because with my criteria:

  • The source file would not include any surrogate key values.
  • The source file would have denormalized record sets with data that should belong to parent and child tables, technically unnormalized form (UNF).
  • Primary and foreign key values would be determined on load to the tables.
  • There could be a one-to-many relationship between the parent and child tables in the original source.
  • Subsequent data sets may replicate data already seeded or not in the tables.
  • Avoid any complex PL/SQL structures.

Step #1 : Create a Virtual Directory

You can create a virtual directory without a physical directory but it won’t work when you try to access it. Therefore, you should create the physical directory first. Assuming you’ve created a C:\Data\Download file directory on the Windows platform, you can then create a virtual directory and grant permissions to the student user as the SYS privileged user. The syntax for these steps is:

CREATE DIRECTORY download AS 'C:\Data\Download';
GRANT READ, WRITE ON DIRECTORY download TO student;

If you want more detail on these steps, check this older post on the blog.

Step #2 : Create the Target and External Tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

-- Conditionally drop tables and sequences.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN ('KINGDOM','KNIGHT','KINGDOM_KNIGHT_IMPORT')) LOOP 
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('KINGDOM_S1','KNIGHT_S1')) LOOP 
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create normalized kingdom table.
CREATE TABLE kingdom
( kingdom_id    NUMBER
, kingdom_name  VARCHAR2(20)
, population    NUMBER);
 
-- Create a sequence for the kingdom table.
CREATE SEQUENCE kingdom_s1;
 
-- Create normalized knight table.
CREATE TABLE knight
( knight_id             NUMBER
, knight_name           VARCHAR2(24)
, kingdom_allegiance_id NUMBER
, allegiance_start_date DATE
, allegiance_end_date   DATE);
 
-- Create a sequence for the knight table.
CREATE SEQUENCE knight_s1;
 
-- Create external import table.
CREATE TABLE kingdom_knight_import
( kingdom_name          VARCHAR2(20)
, population            NUMBER
, knight_name           VARCHAR2(24)
, allegiance_start_date DATE
, allegiance_end_date   DATE)
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY download
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'DOWNLOAD':'kingdom_import.bad'
      DISCARDFILE 'DOWNLOAD':'kingdom_import.dis'
      LOGFILE     'DOWNLOAD':'kingdom_import.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('kingdom_import.csv'))
REJECT LIMIT UNLIMITED;

Step #3 : Create a Procedure to ensure an all or nothing transaction

The procedure ensures that an all or nothing transaction occurs to both tables. Inside the procedure you have two MERGE statements.

The first MERGE statement uses a LEFT JOIN to ensure that any new kingdom_name will be added to the kingdom table. The kingdom_name and population columns are the natural key in this model. The second MERGE statement uses an INNER JOIN to ensure that knight rows are only inserted when they belong to an existing kingdom_name. Naturally, the primary key capture occurs in this statement and it maps the primary key to the foreign key column in the knight table.

The complete procedure code follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

-- Create a procedure to wrap the transaction.
CREATE OR REPLACE PROCEDURE upload_kingdom IS 
BEGIN
  -- Set save point for an all or nothing transaction.
  SAVEPOINT starting_point;
 
  -- Insert or update the table, which makes this rerunnable when the file hasn't been updated.  
  MERGE INTO kingdom target
  USING (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population) SOURCE
  ON (target.kingdom_id = SOURCE.kingdom_id)
  WHEN MATCHED THEN
  UPDATE SET kingdom_name = SOURCE.kingdom_name
  WHEN NOT MATCHED THEN
  INSERT VALUES
  ( kingdom_s1.nextval
  , SOURCE.kingdom_name
  , SOURCE.population);
 
  -- Insert or update the table, which makes this rerunnable when the file hasn't been updated.  
  MERGE INTO knight target
  USING (SELECT   k.kingdom_id
         ,        kki.knight_name
         ,        kki.allegiance_start_date AS start_date
         ,        kki.allegiance_end_date AS end_date
         FROM     kingdom_knight_import kki INNER JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population) SOURCE
  ON (target.kingdom_allegiance_id = SOURCE.kingdom_id)
  WHEN MATCHED THEN
  UPDATE SET allegiance_start_date = SOURCE.start_date
  ,          allegiance_end_date = SOURCE.end_date
  WHEN NOT MATCHED THEN
  INSERT VALUES
  ( knight_s1.nextval
  , SOURCE.knight_name
  , SOURCE.kingdom_id
  , SOURCE.start_date
  , SOURCE.end_date);
 
  -- Save the changes.
  COMMIT;
 
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO starting_point;
    RETURN;
END;
/

Step #4 : Test the Process

You can test it by calling the procedure. Rerunning it will demonstrate that it doesn’t violate any of the rules.

EXECUTE upload_kingdom;

As always, I hope this is useful to somebody besides me.

Written by maclochlainn

February 23rd, 2010 at 2:28 am

Posted in Oracle,Oracle XE,pl/sql,sql,Stored Procedures

PL/SQL Workbook Code

with 3 comments

I got a request Saturday for me to post code for the Oracle Database 11g PL/SQL Programming Workbook. You can download the book code here. It should also be on the McGraw-Hill web site tomorrow.

The irony for me is the timing of the request. I didn’t get it until late Saturday night when I had to make an early plane to Dallas, Texas on Sunday morning. It teaches me once again, that I should keep my book updates in one place and backup in a convenient carry-anywhere location.

I also found out that the Bulletin Board I’d set up wasn’t accessible. At least, accessible to anybody but bots. I uninstalled and re-installed it, and configured it. Now I’ll start maintaining it.

Written by maclochlainn

February 10th, 2010 at 5:07 pm

Posted in Oracle,Oracle XE,pl/sql

Sample PL/SQL Cursor Loops

with 2 comments

A few of my students wanted me to post sample cursor loops, so here are examples of simple, FOR, and WHILE loops. There are a couple variations on approaches that demonstrate %TYPE and %ROWTYPE anchoring to data dictionary table definitions and local cursors.

Part of the idea behind these examples is to show the basic structure while mimicking the \G option of MySQL. The \G (Go) displays results as a list of column names and values by row. Ever since I discovered that in MySQL, I’ve hoped Oracle would incorporate something similar in their product. While discussing my wish list, I’d also like Oracle to make the FROM dual optional (like MySQL does) when selecting a string or numeric literal. You can find an implementation here, that leverages an example from Tom Kyte.

You can click any of the titles to view the code, which isn’t needed when you don’t have JavaScript enabled or the RSS expands them for you.

Simple loop with local variables ↓

This simple loop example uses a static cursor and local variables that map to each column returned by the cursor. It uses the %TYPE to anchor local variables to the data dictionary.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare local variables that are anchored to column data types.
  lv_title     item.item_title%TYPE;
  lv_subtitle  item.item_subtitle%TYPE;
  lv_rating    item.item_rating%TYPE;
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the three local variables.
    FETCH c
    INTO  lv_title
    ,     lv_subtitle
    ,     lv_rating;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variables on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||lv_title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||lv_subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||lv_rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

Simple loop with a local record structure variable ↓

This simple loop example uses a static cursor, a local record structure data type, and a local variable of the local record structure data type. The local record structure maps to the columns returned by the cursor. It uses explicit data types that match those of the table. You could also use the %TYPE to anchor the elements of the structure in the local data type, like the prior example. With explicit data types, you must modify the program when the definition of the table changes otherwise your program may fail at runtime.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a local record data type, with explicit data types (you could use %TYPE here too).
  TYPE title_type IS RECORD
  ( title     VARCHAR2(60)
  , subtitle  VARCHAR2(60)
  , rating    VARCHAR2(8));
 
  -- Declare a local variable of the local record structure data type.
  item_record TITLE_TYPE;
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the local record structure variable.
    FETCH c
    INTO  item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

Simple loop with a local cursor structure variable ↓

This simple loop example uses a static cursor, a local variable that inherits its record structure from the local cursor. It does so by using the %ROWTYPE against the cursor. Often the %ROWTYPE is only applied when you return a structure that maps to the complete table definition. Sometimes I think using cursor_name%ROWTYPE is the only real purpose for shared cursors but I know that’s not really true.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the local record structure variable.
    FETCH c
    INTO  item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

For loop with an implicit record structure variable ↓

This FOR loop example uses a static cursor. When a FOR loop uses a cursor it becomes a cursor FOR loop, and the iterator i becomes an implicit cursor record structure. You should note that this is a very compact program because a cursor FOR loop manages opening and closing the cursor, and handling the loop exit implicitly. Many programmers default to this approach whenever it fits because it is simple and easy to implement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start a cursor FOR loop block.
  FOR i IN c LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||i.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||i.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
END;
/

For loop without a declaration block ↓

This FOR loop example uses a static cursor. Like the prior example, this FOR loop uses a cursor but it is defined inside the actual FOR loop structure. That approach eliminates the need for the declaration block. It’s a nice feature that some may call a trick. As a rule, you should really avoid this style because formally defining your cursor is a good practice and improves code maintainability. Naturally, this is probably the most compact program because everything is managed implicitly including the cursor assignment to the loop structure.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SET SERVEROUTPUT ON SIZE 1000000
 
BEGIN
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start a cursor FOR loop block with the static cursor in the definition.
  FOR i IN (SELECT   i.item_title AS title
	        ,        i.item_subtitle AS subtitle
	        ,        i.item_rating AS rating
	        FROM     item i) LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||i.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||i.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
END;
/

WHILE loop with a cursor guard on entry statement ↓

This WHILE loop example uses a static cursor, like the prior examples. Unlike the prior example, the WHILE loop is a guard on entry loop. The previous loops were guard on exit loops. This has much the same structure as the simple loop with a cursor record structure variable but differs on two key points.

Point one is that you must have two FETCH statements because the guard on entry condition checks whether any records are found in the cursor. The first FETCH statement checks whether at least a one row is returned. When true or false, it initializes the cursor attributes, like %FOUND. The second FETCH statement handles the second row returned to last row returned from the cursor.

Point two is that you don’t have an EXIT WHEN cursor_name%NOTFOUND inside the loop because the guard condition stops the loop when it fails to find at least one record.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Fetch the first record to put into context the cursor attributes, like %FOUND.
  FETCH c INTO item_record;
 
  -- Start the simple loop block with a guard on entry condition.
  WHILE (c%FOUND) LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
    dbms_output.put_line('----------------------------------------------------------------------');
 
    -- Fetch the second and subsequent rows of the cursor and assign it to a local variables.
    FETCH c
    INTO  item_record;
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

The WHILE loop as presented is complex because of the pre-loop FETCH statement, and the internal FETCH statement. It’s may appear better to convert it to a pseudo-infinite loop. You do that by setting the condition in a WHILE loop to a TRUE constant, like this example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block with a guard on entry condition.
  WHILE (TRUE) LOOP
 
    -- Fetch the record set into a user-defined variable.
    FETCH c INTO item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
    dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

The preceding example behaves much like a simple loop, and you have to ask what is the benefit of WHILE (TRUE) LOOP over LOOP. Generally, it appears that the WHILE loop syntax is slighly longer to type.

I’m sure this will help my students and hope it helps somebody else.

Written by maclochlainn

January 21st, 2010 at 10:53 pm

Posted in OPAL,Oracle,Oracle XE,pl/sql,sql

Oracle 11g XDB Shake & Bake

with 33 comments

It’s a bit awkward when a post generates a new question, but here’s a quick explanation and example of using XDB (XML Database Server) outside of the realm of APEX. More or less, XDB is an Apache Server equivalent configured inside the database. It’s really a protocol server tied into the Shared Server Oracle*Net Architecture (a correction provided by Marco Gralike). As a note, testing was done by using a NAT static IP addressing for the virtual Windows XP, Vista, and 7 environments.

This blog post will show you how to experiment with the PL/SQL Web Toolkit and build both password protected and unprotected database content. It assumes you have access to the SYS privileged account.

Setting Up a Secure DAD

There’s secure and then there’s secure. This falls in the less than secure category but it does provide a password and uses basic HTTP authentication. The USER is the schema name, and the PASSWORD is the same as that for the SQL*Plus access to the schema.

  1. Connect as the privileged SYS user and run the following script. It creates a generic STUDENT user and grants minimalist privileges, then it creates a DAD (Data Access Descriptor), and authorizes the DAD. Don’t run the command if you’re actively using Oracle APEX on the default configuration of port 8080. It’s there for those folks you are running Tomcat on 8080.
-- This resets the default port so that it doesn't conflict with other environment.
EXECUTE dbms_xdb.SETHTTPPORT(8181);
 
-- This creates the STUDENT Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DAD','/sampledb/*');
 
-- This authorizes the STUDENT_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DAD','STUDENT');
  1. Connect as the STUDENT user and run the following script to create a PL/SQL Web Toolkit procedure.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE OR REPLACE PROCEDURE HelloWorld AS
BEGIN
  -- Set an HTML meta tag and render page.
  owa_util.mime_header('text/html');  -- <META Content-type:text/html>
  htp.htmlopen;                       -- <HTML>
  htp.headopen;                       -- <HEAD>
  htp.htitle('Hello World!');         -- <TITLE>HelloWorld!</TITLE>
  htp.headclose;                      -- </HEAD>
  htp.bodyopen;                       -- <BODY>
  htp.line;                           -- <HR>
  htp.print('Hello ['||USER||']!');   -- Hello [dynamic user_name]!
  htp.line;                           -- <HR>
  htp.bodyclose;                      -- </BODY>
  htp.htmlclose;                      -- </HTML>
END HelloWorld;
/
  1. Open a browser of your choice, and enter the following URL.
http://localhost:8181/sampledb/helloworld

You then see (or should see) the following Basic HTTP Authentication dialog box. Enter the STUDENT user as the User Name and the Password for the database account. Then, click the OK button.

XDB_BasicHTTPAuthentication

Provided you enter the User Name and Password correctly, you should see the following inside the browser’s display panel. The USER name is a system session scope variable, which will always return the owner of the package because its created as a Definers Rights procedure.

XDB_ProcedureDisplay

You have now successfully configured your Basic HTTP Authentication XDB, which may offer you some possibilities outside of using Oracle APEX.

Setting Up an Unsecured DAD

The trick here is building on what you did by eliminating the authentication. You do this by using the ANONYMOUS account, like Oracle’s APEX does. Well, not quite like it does because APEX provides a very good user authentication model. It allows you to connect to the ANONYMOUS user where you present and validate your credentials.

Since you have to do all the prior steps, these steps are numbered after those above. You start with step #4.

  1. Generally, the XML configuration is missing one key node that allows repository anonymous access. The missing node disallows anonymous login. You can run the code below as the SYS privileged user to open that up if its missing. You can check whether or not it’s missing by running this as the SYS user:
SQL> @?/rdbms/admin/epgstat.sql

If it returns the following as the last element of the output, you’ll need to run the PL/SQL block below.

+-------------------------------------------------------------------+
| ANONYMOUS access to XDB repository:                               |
|  To allow public access to XDB repository without authentication, |
|  ANONYMOUS access to the repository must be allowed.              |
+-------------------------------------------------------------------+
 
Allow repository anonymous access?
----------------------------------
false
 
1 row selected.

When you run this script, make sure you’re the privileged SYS user. Then, rerun the epgstat.sql script to verify that you’ve enabled anonymous access to the repository. You may also need to refresh your browser cache before retesting it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

SET SERVEROUTPUT ON
DECLARE
  lv_configxml XMLTYPE;
  lv_value     VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
  lv_configxml := DBMS_XDB.cfg_get();
 
  -- Check for the element.
  IF lv_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
    -- Add missing element.
    SELECT insertChildXML
           ( lv_configxml
           , '/xdbconfig/sysconfig/protocolconfig/httpconfig'
           , 'allow-repository-anonymous-access'
           , XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">'
       	   || lv_value
       	   || '</allow-repository-anonymous-access>')
       	   , 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    INTO   lv_configxml
    FROM   dual;
 
    DBMS_OUTPUT.put_line('Element inserted.');
  ELSE
    -- Update existing element.
    SELECT updateXML
           ( DBMS_XDB.cfg_get()
           , '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()'
           , lv_value
           , 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    INTO   lv_configxml
    FROM   dual;
 
    DBMS_OUTPUT.put_line('Element updated.');
  END IF;
 
  -- Configure the element.
  DBMS_XDB.cfg_update(lv_configxml);
  DBMS_XDB.cfg_refresh;
END;
/
  1. These tasks also require the privileged SYSTEM user account, and you should sign on to that account to run these commands. The first thing you may need to do is unlock the ANONYMOUS account. It is locked by default. After you unlock it, you’ll need to verify no default password was assigned by unassigning a password. The following two commands accomplish those tasks.
-- Unlock the user account.
ALTER USER anonymous ACCOUNT UNLOCK;
-- Ensure a password is assigned to the account so you can create a synonym later.
ALTER USER anonymous IDENTIFIED BY ANONYMOUS;
  1. These tasks require the privileged SYS user account because you’re going to create and authorize another DAD.
-- This creates the STUDENT_DB_DAD Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DB_DAD','/db/*');
 
-- This authorizes the STUDENT_DB_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DB_DAD','ANONYMOUS');
 
-- Open the anonymous account by setting the database-username parameter and value.
EXECUTE dbms_epg.set_dad_attribute('STUDENT_DB_DAD','database-username','ANONYMOUS');
  1. Connect as the STUDENT user and grant EXECUTE permissions on the HELLOWORLD procedure to the ANONYMOUS user account. The GRANT allows you to give unrestricted access to the ANONYMOUS account, which in turn provides it to your web audience.
SQL> GRANT EXECUTE ON helloworld TO anonymous;
  1. Connect as the ANONYMOUS user and create a local synonym that point to the STUDENT.HELLOWORLD procedure. The SYNONYM provides a program name for the URL statement. It’s hides the ownership of the actual procedure by supressing the schema name. (You may need to grant CREATE ANY SYNONYM as the SYSTEM user to the ANONYMOUS user.)
SQL> CREATE SYNONYM helloworld FOR student.helloworld;
After you’ve created the synonym, you want to remove the password from the ANONYMOUS account. The following syntax lets you do that as the privileged SYSTEM user.

SQL> ALTER USER anonymous IDENTIFIED BY NULL;
  1. Open a browser of your choice, and enter the following URL, which won’t require a User Name or Password.
http://localhost:8181/db/helloworld

You should see the same browser panel information as that shown by step #3 above, except one thing. The difference is the user name, which should now be ANONYMOUS. The execution occurs with the permissions of the invoker. This means you’ll see the data you’re allowed to see by the owning schema.

Written by maclochlainn

December 2nd, 2009 at 3:54 am

Posted in Oracle,Oracle XE,pl/sql,sql,XDB Server,xml

SQL Query in Excel 2007

with 43 comments

Over a year ago, I put out a blog page on how to query the contents of a table from an Oracle database through Excel. I meant to get back to provide much more about this. Specifically, I want to cover the XML mechanism used to accomplish the task. However, a blog reader augmented my page with a solution comment. I’m thrilled he did because it tells me to get a basic thing out rather than hang folks up halfway. My thanks go to Vernon.

Here’s a quick addendum to the original post with screen shots because folks tell me they’re very useful. It demonstrates how you write queries in Microsoft Excel against the Oracle database. While I plan a full tutorial on a different URL, this should help everybody in the meantime. This content is dependent on the generic mechanism, which I covered in this older post.

It starts with an alternative to Step #9 in the older blog page, and therefore, I’ve started the numbering as 9 there.

  1. The Import Data dialog asks you how and where you want to import it. When you don’t want the contents of a simple table, click the Properties button.

OracleQueryFromExcel01

  1. Clicking the prior dialog’s Property button brings you to the Connection Properties dialog. The Usage tab is first for a reason but the Definition tab is where you need to go to enter a free form query. Click the Definition tab highlighted in yellow below.

OracleQueryFromExcel02

  1. In this dialog, the most important boxes are the Command type (as you may guess, Table is the default value) and Command text boxes. The first thing to do, is click on the Command type multiple select widget and choose SQL.

OracleQueryFromExcel03

Now, you can write any query that you’d like against the selected Oracle schema. You may reference any tables or views for which your user (schema) has SELECT permissions. You don’t need to limit yourself to a single table because ANSI SQL:89 (comma delimited tables), and ANSI SQL:92 (INNER JOIN, et cetera) are both supported. Don’t forget that Oracle uses a different concatenation method than Microsoft Access or SQL Server, you can find that here. After you’ve entered your query, click the OK button to proceed.

OracleQueryFromExcel04

  1. When you clicked OK, you’ve instructed Microsoft Excel 2007 to change the connection, it raises this dialog box (click on it if you want to read it). You click Yes to continue and save your query.

OracleQueryFromExcel05

  1. You’re back to where you began, more or less, this it the Import Data dialog. This is Step #9 but this time it’ll run your query not return a simple table content. Click OK to run the query. Don’t be surprised if you get a message saying no password was provided. It just means you’ll be prompted to enter one. You should never store that password because it’s stored in plain text inside an XML file.

OracleQueryFromExcel06

You can find those data source files in this directory:

C:\Document and Settings\<some_user>\My Documents\My Data Sources

Written by maclochlainn

November 30th, 2009 at 11:52 pm

Posted in Excel 2007,Oracle,Oracle XE

No Java in Oracle XE

with 3 comments

While helping in the forum, I noticed that folks don’t know that Java isn’t deployed with the Oracle 10g XE. However, it strikes me that you might be able to fudge it but I’m not sure that’s allowed in the EULA. If you want Java inside the database, why wouldn’t you install the licensed product?

There was an OTN article that listed three limitations but this wasn’t one in the article. Maybe I’ll run across the marketing note sometime in the furture or somebody will post the URL as a comment, which is always appreciated.

Anyway, the presence or lack of Java inside the database is pretty easy to test. You only need to do this:

SQL*Plus: Release 10.2.0.1.0 - Production ON Thu Nov 26 21:19:42 2009
Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.
 
Connected TO:
Oracle DATABASE 10g Express Edition Release 10.2.0.1.0 - Production
 
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS
  2  public class HelloWorldSQL {
  3    public static String hello() {
  4      RETURN "Hello World."; }
  5  }
  6  /
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS
*
ERROR at line 1:
ORA-29538: Java NOT installed

This is also true for Oracle Database 11g XE, as shown:

SQL*Plus: Release 11.2.0.2.0 Production ON Fri DEC 9 02:34:20 2011
Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.
 
Connected TO:
Oracle DATABASE 11g Express Edition Release 11.2.0.2.0 - 64bit Production
 
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS
  2  public class HelloWorldSQL {
  3    public static String hello() {
  4    RETURN "Hello World!"; }
  5  }
  6  /
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS
*
ERROR at line 1:
ORA-29538: Java NOT installed

It also explains the lack of loadjava or dropjava from the $ORACLE_HOME/bin directory.

Written by maclochlainn

November 26th, 2009 at 10:34 pm

Posted in Java,Oracle,Oracle XE

Cleaning up wasted LOB space

with 10 comments

After replacing the contents of a BLOB or CLOB column, you will see the size grow because old indexes and segments aren’t deleted or removed from the index. The only way to get rid of the old information is to perform some simple table maintenance. The following provides an example of dumping redundant or obsolete space and indexes.

You should first check space, by using the query provided earlier in my blog to compare LOB indexes and segments. Such extremes as the regular expression in that query aren’t needed when DBAs ensure that LOBs are created with named segments. In this test case, this is the starting point:

TABLE COLUMN                              Segment      Segment
Name  Name      Segment Name              TYPE           Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00007$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00006$$  LOBINDEX      393216
ITEM  ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416

You create a temporary CLOB column in the target table. Then, you update the temporary column with the value from your real column.

ALTER TABLE item ADD (item_temp CLOB);
UPDATE item SET item_temp = item_desc;

When you requery the table’s indexes and segments, you’d find something like the following. You should note the size of the index and segments are three times larger in the real column than the temporary columns.

TABLE COLUMN                              Segment      Segment
Name  Name      Segment Name              TYPE           Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00007$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00006$$  LOBINDEX      393216
ITEM  ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416
ITEM  ITEM_TEMP SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

You drop the real column and add it back, or simply rename the new table to the old column once you’ve dropped it. Then, you update the real column with the values from the temporary column.

ALTER TABLE item DROP COLUMN item_desc;
ALTER TABLE item ADD (item_desc CLOB);
UPDATE item SET item_desc = item_temp;

You can now requery the table and find that you’ve eliminated extraneous space.

TABLE COLUMN                              Segment     Segment
Name  Name      Segment Name              TYPE          Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00006$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712
ITEM  ITEM_TEMP SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

You drop the temporary column after making the change.

ALTER TABLE item DROP COLUMN item_temp;

You can now requery the table and find that you’ve eliminated extraneous space.

TABLE COLUMN                              Segment     Segment
Name  Name      Segment Name              TYPE          Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00006$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

Written by maclochlainn

April 18th, 2008 at 6:18 pm

Posted in Oracle,Oracle XE,sql,xml

Tagged with , , , , , , , , , , ,

Newer Entries »