MacLochlainns Weblog

I finally got around to writing that PHP Tutorial. It’s a bit large and takes about 10 seconds to load or longer depending on your connection and machine. It covers the basics from writing your first page to loops. It was too large for a blog post, so it’s a blog page. Click on the link if you’d like to check it out.

I plan others on functions, objects, and files. Then, I’ll get to tutorials against databases.

Naturally, suggestions are always welcome.

My son’s graduating with his B.S. in Computer Information Technology next month, moving on to his internship, and today released his first independent iPhone App – Chronos Stopwatch. This link takes you to his blog. He did an awesome job on writing the Bizarro iPhone App under contract, but I’m very happy he and Miles Ponson started writing their own software.

It was amazing seeing him sort through all the myriad issues in balancing analog clocks, the decaseconds refresh rates, and a lap counter. I’m sure a few swimming and track coaches will like this utility if they have an iPhone. Throughout his development cycle, the process has been fun to watch. He certainly loves Objective C.

Just as an aside, it has also been gratifying to know that he finally found major uses for the database, like set operators, inline views, and stored procedures. He once noted they weren’t too useful. 😉

Ruairi asked how you could disable automatic start of the Oracle Service for Oracle 11g on Windows 7 (a comment here). Ruairi also provided a nice Windows shell script that you can copy for starting and stopping the Oracle Service in his last comment.

The simplest way is to launch a command shell because I don’t want to provide all the navigation variations for different Windows versions.

Basically, you do that by clicking the Windows Start button and type cmd word in the run entry box. This launches a command session. Type the following from the prompt. It launches the Windows Services console in all relevant versions:

C:\> services.msc

Now you’ll see the Windows Services console. Navigate to the Oracle Service and right click on it. You choose Properties.

That will bring you to this screen. Click on the drop down for the Startup type and choose Manual. Click the OK button to complete the step. That’s it, the next time you start the machine the Oracle database won’t start automatically. You should do the same to the other Oracle Services.

If you don’t have a lot of memory and it’s a development machine, this makes a lot of sense.

I’ve been working with one of my lab tutors to have him take the 1Z0-047 Oracle Database SQL Expert test. He checked out the online practice exam, and found a couple interesting questions and new syntax. At least, it was new to me.

Naturally, I checked it out. I’ve also added it to my online tutorial for the class. Perhaps I’m a creature of habit but a range non-equijion is always a filtered cross product logically. Certainly, the explain plans indicate that this new syntax has zero performance change over the other forms.

I once used the comma-delimited tables (like everybody else), but now I try to always use the newer CROSS JOIN syntax. In both cases the range join is put in the WHERE clause. The new syntax uses an INNER JOIN and an ON clause to hold the range match. Examples of all are below.

Comma-delimited Filtered Cross Join

1
2
3
4
5

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c, transaction_join t
WHERE    t.transaction_date BETWEEN c.start_date AND c.end_date
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Filtered CROSS JOIN

1
2
3
4
5

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c CROSS JOIN transaction_join t
WHERE    t.transaction_date BETWEEN c.start_date AND c.end_date
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Range filtered INNER JOIN

1
2
3
4
5

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c INNER JOIN transaction_join t
ON      (t.transaction_date BETWEEN c.start_date AND c.end_date)
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Without an INDEX on the start and end date of the CALENDAR_JOIN table, the Oracle explain plan for all three queries is:

1
2
3
4
5
6
7
8
9
10

Query Plan
----------------------------------------------
SELECT STATEMENT   Cost = 9
  SORT ORDER BY
    MERGE JOIN
      SORT JOIN
        TABLE ACCESS FULL TRANSACTION_JOIN
      FILTER
        SORT JOIN
          TABLE ACCESS FULL CALENDAR_JOIN

Naturally, an INDEX on the START_DATE and END_DATE columns improves performance. The results again for all three are the same.

1
2
3
4
5
6
7
8

Query Plan
----------------------------------------------
SELECT STATEMENT   Cost = 6
  SORT ORDER BY
    TABLE ACCESS BY INDEX ROWID CALENDAR_JOIN
      NESTED LOOPS
        TABLE ACCESS FULL TRANSACTION_JOIN
        INDEX RANGE SCAN DATE_RANGE

Unless I’m missing something, it looks like its only a matter of style. However, make sure you know that new one because it appears that it’s on the OCP exam. 😉

Comments are always welcome …

An interesting question came up today while discussing PL/SQL database triggers. Could you create a trigger on a MERGE statement, like this:

1
2
3
4
5
6
7
8

CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE MERGE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/

The answer is, no you can’t. It’ll raise an ORA-04073 error if you attempt it, like this:

BEFORE MERGE OF last_name ON contact
             *
ERROR at line 2:
ORA-04073: COLUMN list NOT valid FOR this TRIGGER TYPE

The only supported DML events are INSERT, UPDATE, and DELETE. The following DML trigger works against a MERGE statement. After all a MERGE statement is nothing more than an INSERT or UPDATE statement.

1
2
3
4
5
6
7
8

CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE INSERT OR UPDATE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/

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
56
57
58
59
60
61
62
63
64
65
66

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT NULL FROM user_tables WHERE TABLE_NAME = 'CONTACT_MERGE') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE contact_merge';
  END LOOP;
END;
/
 
-- Create the table.
CREATE TABLE contact_merge
( contact_id                  NUMBER
, member_id                   NUMBER       NOT NULL
, contact_type                NUMBER       NOT NULL
, first_name                  VARCHAR2(20) NOT NULL
, middle_name                 VARCHAR2(20)
, last_name                   VARCHAR2(20) NOT NULL
, created_by                  NUMBER       NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             NUMBER       NOT NULL
, last_update_date            DATE
, CONSTRAINT contact_merge_pk PRIMARY KEY(contact_id));
 
-- Create the trigger to enforce hyphenated last names..
CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE INSERT OR UPDATE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/
 
-- Merge statement that violates business rule.
MERGE INTO contact_merge target
USING
( SELECT 2001 AS contact_id
  ,      1001 AS member_id
  ,      1001 AS contact_type
  ,'Catherine' AS first_name
  ,'' AS middle_name
  ,'Zeta Jones' AS last_name
  , 2 AS created_by
  , SYSDATE AS creation_date
  , 2 AS last_updated_by
  , SYSDATE AS last_update_date
FROM dual) SOURCE
ON (target.contact_id = SOURCE.contact_id)
WHEN MATCHED THEN
UPDATE SET target.last_updated_by = 3
WHEN NOT MATCHED THEN
INSERT VALUES
( SOURCE.contact_id
, SOURCE.member_id
, SOURCE.contact_type
, SOURCE.first_name
, SOURCE.middle_name
, SOURCE.last_name
, SOURCE.created_by
, SOURCE.creation_date
, SOURCE.last_updated_by
, SOURCE.last_update_date );
 
-- Query results.
SELECT first_name||DECODE(middle_name,NULL,' ',' '||middle_name||' ')||last_name AS full_name
FROM   contact_merge
WHERE  first_name = 'Catherine';

I’ve been working with Oracle so long, sometimes it’s frustrating when I find a syntax feature isn’t in another database. I ran into another example tonight. There isn’t a multiple column look up operator in MySQL. For example, you can do this in Oracle:

1
2
3
4
5

DELETE FROM common_lookup
WHERE  (common_lookup_table,common_lookup_column) IN
         (('TRANSACTION','PAYMENT_METHOD_TYPE')
         ,('TRANSACTION','TRANSACTION_TYPE')
         ,('RENTAL_ITEM','RENTAL_ITEM_TYPE'));

When I transformed it to comply with MySQL, it seems just as clean. In fact, with strings it’s simpler.

1
2
3

DELETE FROM common_lookup
WHERE  common_lookup_table IN ('TRANSACTION','RENTAL_ITEM')
AND    common_lookup_column IN ('TRANSACTION_TYPE','PAYMENT_METHOD_TYPE','RENTAL_ITEM_TYPE');

Then, I thought about it. Oracle would let me write a single subquery returning the two columns, whereas MySQL requires two subqueries in their syntax. Likewise, MySQL doesn’t support the WITH clause, which would let me reference a single query result in the scope of the master query (Oracle and SQL Server do support that).

I guess we can hope that Oracle will implement the feature in MySQL now that they own it. 🙂 Let me know if I’ve missed some fabulous syntax alternative.

As I continue down the warren hole of Persistent Stored Modules (SQL/PSM) in MySQL, I keep wondering about that mad hare, Johnny Depp. Alice isn’t a programming language to teach me anything in this dream. Moreover, TIm Burton’s tale this seems oddly familiar, like a child’s story gone mad.

A quick update on comparative SQL expression assignments between PL/SQL and MySQL. When you want to filter a value through SQL functions before assigning it to another variable in MySQL, it’s not like PL/SQL. Just like the new Alice in Wonderland movie isn’t like the book.

The programmatic differences lies in their origins. PL/SQL evolved from Pascal through Ada to become a recursive language where you can call SQL from PL/SQL and PL/SQL from SQL. MySQL implemented PSMs from the ANSI SQL:2003 specification, which didn’t see it the same way, apparently (a disclaimer since I’ve not read the details of the specification).

Personally, I think PL/SQL is easier to write but I’ve been using it for almost 20 years. Naturally, there may be a consistency thread on this that I’m missing and an opportunity that I may exploit. After all, it is dark in this warren hole.

Oracle PL/SQL Assignments from SQL Expressions

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

-- Enable output printing.
SET SERVEROUTPUT ON SIZE 1000000
 
-- Define an anonymous block.
DECLARE
 
  -- Declare a source variable.
  lv_right_operand VARCHAR2(10) := 'March';
 
  -- Define a target variable for the assignment.
  lv_left_operand  VARCHAR2(10);
 
BEGIN
 
  -- Return the expression from a nested call parameter of the source variable.
  lv_left_operand := UPPER(SUBSTR(lv_right_operand,1,3));
 
  -- Print it to console.
  dbms_output.put_line('Output ['||lv_left_operand||']');
 
END;
/

Oracle also supports this syntax, which isn’t frequently used because it’s much more verbose syntactically. It is also equivalent to the PSM syntax adopted by MySQL.

-- Define an anonymous block.
DECLARE
 
  -- Declare a source variable.
  lv_right_operand VARCHAR2(10) := 'March';
 
  -- Define a target variable for the assignment.
  lv_left_operand  VARCHAR2(10);
 
BEGIN
 
  -- Return the expression from a nested call parameter of the source variable.
  SELECT UPPER(SUBSTR(lv_right_operand,1,3)) INTO lv_left_operand FROM dual;
 
  -- Print it to console.
  dbms_output.put_line('Output ['||lv_left_operand||']');
 
END;
/

That means we can do it like the White Queen wants it or the Red Queen wants it in Oracle. Flexibility in PL/SQL is clearly broader because of the assignment options. Not so in MySQL, as you’ll see.

MySQL PSM Assignment from SQL Expressions

First, MySQL’s PSM approach doesn’t support anonymous blocks. The example must create a stored function or procedure, and then call it. A procedure seems like the best fit for the 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

-- Conditionally drop procedure.
SELECT 'DROP PROCEDURE IF EXISTS assignit' AS "Statement";
DROP PROCEDURE IF EXISTS assignit;
 
-- Create the proceudre
SELECT 'CREATE PROCEDURE assignit' AS "Statement";
 
DELIMITER $$
 
-- Define the procedure.
CREATE PROCEDURE assignit()
BEGIN
  /* Declare a source variable. */
  DECLARE lv_right_operand VARCHAR(10) DEFAULT 'March';
 
  /* Define a target variable for the assignment. */
  DECLARE lv_left_operand  VARCHAR(3);
 
  /* Assign the modified value through the SELECT-INTO model. */
  SELECT UCASE(SUBSTRING(lv_right_operand,1,3)) INTO lv_left_operand;
 
  /* Display assigned value. */
  SELECT lv_left_operand;
 
END;
$$
 
DELIMITER ;
 
-- Call the procedure.
CALL assignit();

The only question here in the warren is: Who’s the White Queen; and who’s the Red Queen. Which semantic should I choose? My I hope is that I wake up before it’s … oops, off with his head. Actually, 3D or not, I’ll probably not see it, that’s the new Alice in Wonderland film.

Likewise, when my students wake up and read this they’ll know I was just answering a question on how to perform assignments in MySQL stored procedures. By the way, I’ve updated this assignment process in my Debugging MySQL Procedures post.

As an aside, I’ve got a new MySQL debugger that I’m testing later in the week. When I complete the test cases, I’ll post a review.

Teaching SQL is interesting because folks try syntax that experience would tell you shouldn’t work. It was interesting when I discovered what should be broken from my perspective but was expected behavior in MySQL. It became clearer to me why it’s there as I did some experimenting with it enabled and disabled. While I’d still argue it’s broken, it’s the only way to get support for advanced aggregation concepts.

The reason that I find that it broken is a matter of perspective not standards. The fact that you can select a set of non-aggregated columns with an aggregated column, and exclude one, more than one, or all of the non-aggregated columns from the GROUP BY clause seemed like a broken behavior. The MySQL behavior is explained in Chapter 11.12.3 of the MySQL 5.1 Reference. What wasn’t clear from the documentation when I read it was why the behavior is allowed. Gary’s comment on the original post made me look more deeply into the behavior.

If you check the referenced documentation, the process is called GROUP BY hidden columns. That label didn’t immediately resonate with me. That’s probably of my background with IBM’s DB2 and Oracle. I’m so accustom to how they work, that sometimes I think they set the standards but they don’t. It turns out that the GROUP BY clause in the ANSI SQL standards doesn’t allow for expressions.

The default configuration of MySQL allows for the expressions (functions) in the GROUP BY clause. The documentation refers to hidden columns, which are in point of fact expressions in the GROUP BY clause. MySQL does support expressions in the GROUP BY by default, and requires, like Oracle, that when you use an expression in the SELECT clause that you mirror it in the GROUP BY clause. This means you group on the result of the expression, not a column in the table or view.

In the default configuration, you have three options. They are determinate results, indeterminate results, and hidden column results. The following cover these components.

Determinate Results

Determinate results are straight forward. They require that all non-aggregated columns in the SELECT clause are mirrored in the GROUP BY clause. This means that the non-aggregated column values are the key upon which results are aggregated.

An example of determinate results is:

1
2
3
4
5
6

SELECT   key_one
,        key_two
,        SUM(counter)
FROM     GROUPING
GROUP BY key_one
,        key_two;

Indeterminate Results

Indeterminate results isn’t straight forward but isn’t hard to grasp. An indeterminate result set is returned when one or more non-aggregated columns in a SELECT clause aren’t listed in the GROUP BY clause. The columns listed in the SELECT clause but excluded from the GROUP BY clause return meaningless values because they’re column values chosen indeterminately from all pre-aggregated rows.

The following query runs in a generically configured MySQL instance without an error. It returns a meaningless key_two column value from the pre-aggregated row set. In the example, the aggregation column counts the unique key_one column values. This behavior makes key_one a determinate value, and key_two an indeterminate value.

1
2
3
4
5

SELECT   key_one
,        key_two
,        SUM(counter)
FROM     GROUPING
GROUP BY key_one;

You can fix this mixed return set by adding the key_two column to the GROUP BY clause, which would return a determinate set. Alternatively, you can prevent the default behavior for the GROUP BY clause by adding the ONLY_FULL_GROUP_BY mode variable to your SQL_MODE system variable.

After setting the SQL_MODE> variable, a GROUP BY must contain all non-aggregated columns. When you make this change to the SQL_MODE system variable, you also disable any queries that use an expression in their GROUP BY clause. Those queries with expressions in the group by will now raise an ERROR 1055 exception, like this:

ERROR 1055 (42000): 'sampledb.grouping.key_two' isn't in GROUP BY

If you want to prevent indeterminate results and don’t use expressions in the GROUP BY clause, you can add the ONLY_FULL_GROUP_BY mode to your SQL_MODE system variable. You can do that during a session with the following syntax:

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));

Or, you can start the mysqld with the following option:

mysqld --sql_mode="sql_mode1,sql_mode2, ... ,sql_mode(n+1)"

A better alternative, is to add it to the my.cnf configuration file on Linux, or my.ini configuration file on Windows. You can add it to this line, which is done at the end of the line.

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

If you opt for changing it at the server configuration file, you must stop and restart the mysqld process. You can do that on Windows from the command line, like this on Windows provide the service name is mysql. If you’ve set the Windows service to mysql51, then you need to substitute mysql51 for mysql.

Hidden Column Results

A hidden column result, is a result generated by an expression in the GROUP BY clause. The following is a query that lets you sum transactions by the month name. This is supported by the default behavior of hidden columns. The following is an approach that you might find in Oracle because they don’t support aggregation by only part of the non-aggregated columns in a query.

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

SELECT   il.month AS "MON-YEAR"
,        il.base AS "BASE"
,        il.plus10 AS "10_PLUS"
,        il.plus20 AS "20_PLUS"
FROM    (SELECT   CONCAT(UPPER(SUBSTRING(MONTHNAME(t.transaction_date),1,3)),'-',EXTRACT(YEAR FROM t.transaction_date)) AS MONTH
         ,        MONTH(t.transaction_date) AS sortkey
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.0,2)),10,' ') AS base
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.1,2)),10,' ') AS plus10
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.2,2)),10,' ') AS plus20
         FROM     TRANSACTION t
         WHERE    EXTRACT(YEAR FROM t.transaction_date) = 2009
         GROUP BY CONCAT(UPPER(SUBSTRING(MONTHNAME(t.transaction_date),1,3)),'-',EXTRACT(YEAR FROM t.transaction_date))
         ,        MONTH(t.transaction_date)) il
ORDER BY il.sortkey;

The interesting thing about MySQL’s approach is that the Oracle equivalent of an inline view is unnecessary. You can simply switch the functions like the exmaple below.

1
2
3
4
5
6
7
8
9
10

SELECT   DATE_FORMAT(t.transaction_date,'%m-%Y') AS MONTH
,        MONTH(t.transaction_date) AS sortkey
,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.0,2)),10,' ') AS base
,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.1,2)),10,' ') AS plus10
,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.2,2)),10,' ') AS plus20
FROM     TRANSACTION t
WHERE    EXTRACT(YEAR FROM t.transaction_date) = 2009
GROUP BY DATE_FORMAT(t.transaction_date,'%m-%Y')
,        MONTH(t.transaction_date)
ORDER BY sortkey;

When you add the ONLY_FULL_GROUP_BY mode to your SQL_MODE system variable, you disallow this type of behavior and the query would no longer work. It would return the following error:

ERROR 1055 (42000): 'sampledb.t.transaction_date' isn't in GROUP BY

-- Conditionally drop sample table.
SELECT 'DROP TABLE IF EXISTS grouping' AS "Statement";
DROP TABLE IF EXISTS GROUPING;
 
-- Create sample table.
SELECT 'CREATE TABLE grouping' AS "Statement";
CREATE TABLE GROUPING
( key_one   CHAR(5)
, key_two   CHAR(5)
, counter   INT UNSIGNED );
 
-- Insert nine rows into the sample table.
SELECT 'INSERT INTO grouping' AS "Statement"
INSERT INTO GROUPING VALUES
('One','Uno-1',1),('Two','Due-1',2),('Three','Tre-1',3)
('One','Uno-2',1),('Two','Due-2',2),('Three','Tre-2',3)
('One','Uno-3',1),('Two','Due-3',2),('Three','Tre-3',3);
 
-- Query with hidden columns, no enforcement on non-aggregate columns.
SELECT 'SELECT non-aggregates FROM grouping with hidden columns' AS "Statement";
SELECT key_one, key_two, SUM(counter)
FROM   GROUPING
GROUP BY key_one;
 
-- Check current system mode variable assignment.
SELECT 'SELECT @@sql_mode' AS "Statement";
SELECT @@sql_mode;
 
-- Append the mode to the existing system mode variable.
SELECT 'SET SQL_MODE=(SELECT ...)' AS "Statement";
SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
 
-- Check modified system mode variable assignment.
SELECT 'SELECT @@sql_mode' AS "Statement";
SELECT @@sql_mode;

A Gauss posted a question on my from last year’s Utah Oracle User’s Group Training Days presentation. If I understood his question correctly, this should help him work with his legacy code. Honestly, as I wrote the example something Bryn Llewellyn said kept banging around in my head, “Just because we can, doesn’t mean we should.” He was speaking of writing poorly engineered code.

Sometimes, we don’t get the opportunity to re-factor existing code. That leaves us with writing wrappers that aren’t pretty or effective. A realization and preface to showing everyone how to accomplish these tasks, and perhaps a watch out warning if you choose this path. I suspect that there may be a better way but I don’t know their code tree.

Here’s the question, as I understand it. They’ve got a library function in PL/SQL that returns a system reference cursor and is principally consumed by an external Java program. This type of architecture is more or less an Adapter OOAD pattern that I wrote about here, over a year and a half ago. The question comes to how to you wrap this approach and make it work in PL/SQL natively too.

The answer depends on some earlier posts because I don’t have a great deal of time to write new examples. It uses a COMMON_LOOKUP table, which is more or less a bunch of small tables grouped into a big table for use in user interaction forms. That way the values don’t get lost in a large code base and are always consistently maintained. These types of tables exist in all major ERP and CRM applications.

The base code for the example is found here, where I discussed how you can effectively use object tables – collections of user-defined object types (Oracle 9iR2 forward if I remember correctly). You can grab the full code at the bottom of the page by clicking the Code Script widget to unfold the code. That code also depends on the Oracle Database 11g PL/SQL Programming downloadable code, which you can download by clicking the link to the zip file location.

Here are the steps to wrap a function that returns a PL/SQL reference cursor so that it can also return a PL/SQL associative array.

1. Create a package specification to hold all the components that are required to manage the process. Assuming that they may have anchored the system reference cursor to something other than a table like a shared cursor, which is a cumbersome implementation design. (I actually chose to exclude this from the book because it’s a stretch as a good coding practice. At least, it is from my perspective. Also, I couldn’t find an example in the Oracle documentation, which led me to believe they didn’t think it’s a great idea either or I could have glossed over it.) You should note that the PL/SQL RECORD, Associative Array (collection), and the REF CURSOR are defined in this package specification.

-- Create a package to hold the PL/SQL record structure.
CREATE OR REPLACE PACKAGE example IS
 
  -- Force cursors to be read as if empty every time.
  PRAGMA SERIALLY_REUSABLE;
 
  -- Package-level record structure that mimics SQL object type.
  TYPE common_lookup_record IS RECORD
  ( common_lookup_id      NUMBER
  , common_lookup_type    VARCHAR2(30)
  , common_lookup_meaning VARCHAR2(255));
 
  -- Package-level collection that mimics SQL object table.
  TYPE common_lookup_record_table IS TABLE OF common_lookup_record
  INDEX BY PLS_INTEGER;
 
  -- Cursor structure to support a strongly-typed reference cursor.
  CURSOR c IS
    SELECT   common_lookup_id
    ,        common_lookup_type
    ,        common_lookup_meaning
    FROM     common_lookup;
 
  -- Package-level strongly-typed system reference cursor.
  TYPE cursor_lookup IS REF CURSOR RETURN c%ROWTYPE;
 
END;
/

2. Write a function to return a strongly typed system reference cursor that’s anchored to a cursor defined in the package. This is fairly straightforward when the package specification is done right. You should notice right away that anchoring the original cursor in the package was a horrible practice because you must repeat it all again in the function. In my opinion, you shouldn’t anchor any system reference cursor explicitly to anything other than a table. The cursor could have used the generic weak cursor data type – SYS_REFCURSOR. Doing so, saves all the extra lines required by a potential shared cursor.

CREATE OR REPLACE FUNCTION get_common_lookup_cursor
( TABLE_NAME VARCHAR2, column_name VARCHAR2)
RETURN example.cursor_lookup IS
 
  -- Define a local variable of a strongly-typed reference cursor.
  lv_cursor EXAMPLE.CURSOR_LOOKUP;
 
BEGIN
 
  -- Open the cursor from a static cursor
  OPEN lv_cursor FOR
    SELECT common_lookup_id
    ,      common_lookup_type
    ,      common_lookup_meaning
    FROM   common_lookup
    WHERE  common_lookup_table = TABLE_NAME
    AND    common_lookup_column = column_name;
 
  -- Return the cursor handle.
  RETURN lv_cursor;
 
END;
/

3. Write a wrapper function that takes the reference cursor as a formal parameter and returns an Associative Array. You should note that this can’t be called from a SQL context. You must only use it in a PL/SQL context because system reference cursors are PL/SQL only data types.

CREATE OR REPLACE FUNCTION convert_common_lookup_cursor
( pv_cursor EXAMPLE.CURSOR_LOOKUP) 
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Local PL/SQL-only variable.
  out_record  EXAMPLE.COMMON_LOOKUP_RECORD;
  out_table   EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  LOOP
    FETCH pv_cursor INTO out_record;
    EXIT WHEN pv_cursor%NOTFOUND;
 
    -- Assign it one row at a time to an associative array.
    out_table(counter) := out_record;
 
    -- Increment the counter.
    counter := counter + 1;
 
  END LOOP;
 
  -- Return the record collection.
  RETURN out_table;
 
END;
/

4. You can test the program in an anonymous block, like the one below. It defines a local Associative Array variable and then assigns the system reference cursor through the wrapper.

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Define a local associative array.
  process_table  EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
  -- Print title block.
  dbms_output.put_line('Converting a SYS_REFCURSOR to TABLE');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Run the dynamic variables through the cursor generating function and then convert it.
  process_table := convert_common_lookup_cursor(get_common_lookup_cursor('ITEM','ITEM_TYPE'));
 
  -- Read the content of the Associative array.  
  FOR i IN 1..process_table.COUNT LOOP
    dbms_output.put('['||process_table(i).common_lookup_id||']');
    dbms_output.put('['||process_table(i).common_lookup_type||']');
    dbms_output.put_line('['||process_table(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

I hope this answers Gauss’s question. While writing it, I could envision another question that might pop-up. How do you convert an object table type to a PL/SQL context. It was an omission not to include it in that original post on object table types. Here’s how you wrap an object table type into a PL/SQL scope collection.

You might have guessed. It’s done with another wrapper function. At least this is the easiest way to convert the SQL data type to a PL/SQL data type that I see. If you’ve another approach, a better way, let us know.

CREATE OR REPLACE FUNCTION get_common_lookup_record_table
( TABLE_NAME  VARCHAR2
, column_name VARCHAR2 )
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Define a dynamic cursor that takes two formal parameters.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT   *
    FROM     TABLE(get_common_lookup_object_table(UPPER(table_name_in),UPPER(table_column_name_in)));
 
  -- A local PL/SQL-only collection variable.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  FOR i IN c(TABLE_NAME, column_name) LOOP
    list(counter) := i;
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_record_table;
/

You can then test this in an anonymous block, like so:

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Declare a local PL/SQL-only collection and assign the value from the function call.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
  BEGIN
 
  -- Print title block.
  dbms_output.put_line('Converting a SQL Collection to a PL/SQL Collection');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Assign wrapped SQL collection to a PL/SQL-only collection.
  list := get_common_lookup_record_table('ITEM','ITEM_TYPE');
 
  -- Call the record wrapper function.
  FOR i IN 1..list.COUNT LOOP
    dbms_output.put('['||list(i).common_lookup_id||']');
    dbms_output.put('['||list(i).common_lookup_type||']');
    dbms_output.put_line('['||list(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

As always, I hope this helps somebody without paying a fee for content. 😉

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 … 😉