MacLochlainns Weblog

A tale of Oracle SQL object types, their constructors, and how you use them. This demonstrates what you can and can’t do and gives brief explanations about why.

The following creates a base SAMPLE_OBJECT data type and a sample_table
collection of the base SAMPLE_OBJECT data type.

CREATE OR REPLACE TYPE sample_object IS OBJECT
(id       NUMBER
,name     VARCHAR2(30));
/
 
CREATE OR REPLACE TYPE sample_table IS TABLE OF sample_object;
/

If the base SAMPLE_OBJECT data type were a Java object, the default constructor of an empty call parameter list would allow you to construct an instance variable. This doesn’t work for an Oracle object type because the default constructor is a formal parameter list of the object attributes in the positional order of their appearance in the declaration statement.

The test case on this concept is:

1
2
3
4
5
6

DECLARE
  lv_object_struct SAMPLE_OBJECT := sample_object();
BEGIN
  NULL;
END;
/

Running the program raises the following exception, which points to the object instance constructor from line 2 above:

  lv_object_struct SAMPLE_OBJECT := sample_object();
                                    *
ERROR at line 2:
ORA-06550: line 2, column 37:
PLS-00306: wrong number or types of arguments in call to 'SAMPLE_OBJECT'
ORA-06550: line 2, column 20:
PL/SQL: Item ignored

Changing the instantiation call to the Oracle design default, two null values let you create
an instance of the SAMPLE_OBJECT type. The following shows that concept, which works when the base object type allows null values.

1
2
3
4
5
6

DECLARE
  lv_object_struct SAMPLE_OBJECT := sample_object(NULL, NULL);
BEGIN
  NULL;
END;
/

If you want to have a null parameter constructor for an object type, you must implement a type and type body with an overloaded no argument constructor, like this:

1
2
3
4
5

CREATE OR REPLACE TYPE sample_object IS OBJECT
( id       NUMBER
, name     VARCHAR2(30)
, CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT);
/

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE TYPE BODY sample_object IS
  CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(NULL,NULL);
  BEGIN
    SELF := sample_obj;
    RETURN;
  END sample_object;
END;
/

Unlike Java, the addition of an overloaded constructor doesn’t drop the default constructor. You can also create a single parameter constructor that leverages the sequence like this:

1
2
3
4
5
6

CREATE OR REPLACE TYPE sample_object IS OBJECT
( id       NUMBER
, name     VARCHAR2(30)
, CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION sample_object (pv_name VARCHAR2) RETURN SELF AS RESULT);
/

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

CREATE OR REPLACE TYPE BODY sample_object IS
  CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(sample_object_id.NEXTVAL,NULL);
  BEGIN
    SELF := sample_obj;
  END sample_object;
  CONSTRUCTOR FUNCTION sample_object (pv_name VARCHAR2) RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(sample_object_id.NEXTVAL,pv_name);
  BEGIN
    SELF := sample_obj;
    RETURN;
  END sample_object;
END;
/

You can test the final object type and body with this anonymous block of code:

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

SET SERVEROUTPUT ON SIZE UNLIMITED
 
DECLARE
  lv_object_struct1 SAMPLE_OBJECT := sample_object();
  lv_object_struct2 SAMPLE_OBJECT := sample_object('User Name');
  lv_object_struct3 SAMPLE_OBJECT := sample_object(1001,'User Name');
BEGIN
  dbms_output.put_line('lv_object_struct1.id   ['||lv_object_struct1.id||']');
  dbms_output.put_line('lv_object_struct1.name ['||lv_object_struct1.name||']');
  dbms_output.put_line('lv_object_struct2.id   ['||lv_object_struct2.id||']');
  dbms_output.put_line('lv_object_struct2.name ['||lv_object_struct2.name||']');
  lv_object_struct2.name := 'Changed Name';
  dbms_output.put_line('lv_object_struct2.id   ['||lv_object_struct2.id||']');
  dbms_output.put_line('lv_object_struct2.name ['||lv_object_struct2.name||']');
  dbms_output.put_line('lv_object_struct3.id   ['||lv_object_struct3.id||']');
  dbms_output.put_line('lv_object_struct3.name ['||lv_object_struct3.name||']');
END;
/

It prints to console:

lv_object_struct1.id   [1]
lv_object_struct1.name []
lv_object_struct2.id   [2]
lv_object_struct2.name [User Name]
lv_object_struct2.id   [2]
lv_object_struct2.name [Changed Name]
lv_object_struct3.id   [1001]
lv_object_struct3.name [User Name]

Hope this helps those looking for a quick syntax example and explanation.

Somebody asked for a simple comparison between a PL/SQL pass-by-value function and pass-by-reference procedure, where the procedure uses only an OUT mode parameter to return the result. This provides examples of both, but please note that a pass-by-value function can be used in SQL or PL/SQL context while a pass-by-reference procedure can only be used in another anonymous of named block PL/SQL program.

The function and procedure let you calculate the value of a number raised to a power of an exponent. The third parameter lets you convert the exponent value to an inverse value, like 2 to ¹/₂.

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

CREATE OR REPLACE FUNCTION find_root_function
( pv_number   BINARY_DOUBLE
, pv_power    BINARY_DOUBLE
, pv_inverse  BINARY_INTEGER DEFAULT 0 ) RETURN BINARY_DOUBLE IS
 
  -- Declare local variable for return value.
  lv_result   BINARY_DOUBLE;
 
BEGIN
 
  -- If the inverse value is anything but zero calculate the inverse of the power.
  IF pv_inverse = 0 THEN
    lv_result := POWER(pv_number,pv_power);
  ELSE
    lv_result := POWER(pv_number,(1 / pv_power));
  END IF;
 
  RETURN lv_result;
END find_root_function;
/

You can test it with these to queries against the dual table:

SELECT TO_CHAR(find_root_function(4,3),'99,999.90') FROM dual;
SELECT TO_CHAR(find_root_function(125,3,1),'99,999.90') FROM dual;

The procedure does the same thing as the function. The difference is that the fourth parameter to the procedure returns the value rather than a formal return type like a function.

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

CREATE OR REPLACE PROCEDURE find_root_procedure
( pv_number   IN     BINARY_DOUBLE
, pv_power    IN     BINARY_DOUBLE
, pv_inverse  IN     BINARY_INTEGER DEFAULT 0
, pv_return      OUT BINARY_DOUBLE ) IS
 
BEGIN
 
  -- If the inverse value is anything but zero calculate the inverse of the power.
  IF pv_inverse = 0 THEN
    pv_return := POWER(pv_number,pv_power);
  ELSE
    dbms_output.put_line('here');
    pv_return := POWER(pv_number,(1 / pv_power));
  END IF;
 
END find_root_procedure;
/

You can test it inside an anonymous block PL/SQL program, like this:

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

DECLARE
 
  -- Declare input variables.
  lv_input   BINARY_DOUBLE;
  lv_power   BINARY_DOUBLE;
  lv_inverse BINARY_INTEGER;
  lv_output  BINARY_DOUBLE;
 
BEGIN
 
  -- Assign input values to variables.
  lv_input := '&1';
  lv_power := '&2';
  lv_inverse := '&3';
 
  -- Test raising to a power.
  find_root_procedure(lv_input, lv_power, lv_inverse, lv_output);
  dbms_output.put_line(TO_CHAR(lv_output,'99,999.90'));
 
  -- Test raising to an inverse power.
  find_root_procedure(lv_input, lv_power, lv_inverse, lv_output);
  dbms_output.put_line(TO_CHAR(lv_output,'99,999.90'));
 
END;
/

You can test it inside an anonymous block PL/SQL program, like the following example. For reference, the difference between PL/SQL and the SQL*Plus environment is large. The EXECUTE call is correct in SQL*Plus but would be incorrect inside a PL/SQL block for a Native Dynamic SQL (NDS) call. Inside a PL/SQL block you would use EXECUTE IMMEDIATE because it dispatches a call from the current running scope to a nested scope operation (see comment below).

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

-- SQL*Plus Test.
VARIABLE sv_input BINARY_DOUBLE
VARIABLE sv_power BINARY_DOUBLE
VARIABLE sv_inverse BINARY_DOUBLE
VARIABLE sv_output  BINARY_DOUBLE
 
-- Verify the null value of the session variable.
SELECT :sv_output AS ":sv_output" FROM dual;
 
BEGIN
 
  -- Prompt for local assignments and initialize output variable.
  :sv_input   := '&1';
  :sv_power   := '&2';
  :sv_inverse := '&3';
  :sv_output  := 0;
 
END;
/
 
-- Run the procedure in the SQL*Plus scope.
EXECUTE find_root_procedure(:sv_input, :sv_power, :sv_inverse, :sv_output);
 
-- Query the new value of the session variable.
SELECT TO_CHAR(:sv_output,'99,999.90') AS ":output" FROM dual;

As usual, I hope this helps folks beyond the one who asked. Comments are always welcome.

Answering a reader’s question: How can you sort data inside an Oracle table view column? This blog post shows you how to perform the trick, but for the record I’m not a fan of nested tables. A table view column is an Oracle specific user-defined type (UDT), and is nested table or varray of a scalar data type.

Oracle’s assigned a formal name to this type of UDT. It’s now labeled an Attribute Data Type (ADT). The ADT doesn’t allow you to update nested elements outside of PL/SQL program units.

This blog post reviews table view columns, and extends concepts from Oracle Database 11g & MySQL 5.6 Developer Handbook (by the way virtually everything in the book is relevant from MySQL 5.1 forward). It demonstrates how you can use PL/SQL user-defined functions (UDFs) to supplement the SQL semantics for updating nested tables, and then it shows how you can reshuffle (sort) data store the sorted data in table view columns.

Before you implement table view columns, you should answer two design questions and one relational modeling principal. You should also understand that this direction isn’t portable across database implementations. It currently supported fully by the Oracle database and mostly by PostgreSQL database. You can find how to join nested tables helpful in understanding the UPDATE statements used in this posting, and this earlier post on UPDATE and DELETE statements.

Design Questions:

• Should you implement full object types with access methods in PL/SQL? The object type solution says there is no value in the nested data outside of the complete object. While choosing the table view column solution says that there is value to just implementing a nested list without element handling methods.
• Should you embed the elements in an XML_TYPE? An XML solution supports hierarchical node structures more naturally, like when you only access child nodes through the parent node. While choosing the table view column solution says that you want to avoid the XML Software Development Kit and that the data set is small and more manageable in a table view column.

Design Principle:

• Should you implement an ID-dependent relational modeling concept? An ID-dependent model replaces the primary and foreign keys with the relative position of parent and child elements. This is the design adopted when you choose a table view column, and it is more complex than single subject relational tables.

You should note that table view columns are inherently static at creation. You must also update the entire nested table view column when using Oracle SQL. Oracle SQL does let you modified attributes of object types in nested tables, as qualified in my new book (page 252).

Any attempt to modify a table view column element in SQL raises an ORA-25015 error. The error message states that (you) cannot perform DML on this nested TABLE VIEW COLUMN.

You can update the table view column value by replacing it with a new collection, and that’s done with a PL/SQL function. This type of function preserves the ordered list in the table view column by finding and replacing an element in the collection.

Unfortunately, developers who use nested tables typically design table view columns with an internal ordering scheme. That means the collection is ordered during insert or update. This type of design relies on the fact that you can’t change the order without re-writing the stored structure.

While common for those you use these, it is a bad practice to rely on the ordering of elements in a collection. At least, it’s a bad practice when we’re trying to work within the relational model. All that aside, here’s how you ensure element updates while preserving element position:

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

CREATE OR REPLACE FUNCTION update_collection
( old_element_collection  STREET_LIST
, old_element_value   VARCHAR2
, new_element_value   VARCHAR2 ) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
  FOR i IN 1..old_element_collection.COUNT LOOP
    IF NOT old_element_collection(i) = old_element_value THEN
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := old_element_collection(i);
    ELSE
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := new_element_value;
    END IF;
    lv_counter := lv_counter + 1;
  END LOOP;
 
  RETURN lv_element_collection;
END update_collection;
/

Then, you can use the user-defined function (UDF) inside a SQL UPDATE statement, like this:

1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM    employee e
              WHERE   e.employee_id = 1) e
SET   e.street_address = update_collection(e.street_address, 'Suite 525','Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;

The UPDATE_COLLECTION function replaces Suite 525 with Suite 522, and preserves the sequence of elements in a new nested table. The UPDATE statement assigns the modified nested table to the table view column. You can find the code to create the employee table in Chapter 6 (pages 148-149), and the code to insert the default data in Chapter 8 (page 229) of Oracle Database 11g & MySQL 5.6.

The lv_counter variable could be replaced with a reference to the for loop’s iterator (i) because the counts of both collections are the same. I opted for the local variable to make the code easier to read.

While common for those you use these, it is a bad practice to rely on the ordering of elements in a collection. At least, it’s a bad practice when we’re trying to work within the relational model. Along the same line of thought, you also have the ability of removing elements from a table view column with a similar PL/SQL function. You could write the function like this:

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

CREATE OR REPLACE FUNCTION delete_from_collection
( old_element_collection  STREET_LIST
, old_element_value   VARCHAR2 ) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
 
  FOR i IN 1..old_element_collection.COUNT LOOP
    IF NOT old_element_collection(i) = old_element_value THEN
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := old_element_collection(i);
      lv_counter := lv_counter + 1;
    END IF;
  END LOOP;
 
  RETURN lv_element_collection;
END delete_from_collection;
/

Then, you can use the user-defined function (UDF) to delete an element from the collection inside a SQL UPDATE statement, like this:

1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM    employee1 e
              WHERE   e.employee_id = 1) e
SET   e.street_address = delete_from_collection(e.street_address,'Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;

After understanding all that, let’s examine how you sort data in a nested table or varray of a scalar data type (the basis of a table view column). The easiest way is a BULK COLLECT INTO statement nested inside a function, like this:

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

CREATE OR REPLACE FUNCTION sort_collection
( old_element_collection  STREET_LIST) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
 
  -- Sort a collection alphabetically based on case sensitivity.
  SELECT   column_value BULK COLLECT INTO lv_element_collection
  FROM     TABLE(old_element_collection)
  ORDER BY column_value;
 
  RETURN lv_element_collection;
END sort_collection;
/

You could test it with this:

1
2

SELECT   column_value
FROM     TABLE(sort_collection(street_list('Adams', 'Lewis', 'Clark', 'Fallon')));

Then, you can use the user-defined function (UDF) to update a table view column like this:

1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM    employee1 e
              WHERE   e.employee_id = 1) e
SET   e.street_address = sort_collection(e.street_address)
,     e.city = 'Oakland'
WHERE e.address_id = 1;

The funny thing about database solutions these days is that some Java developers don’t appreciate the simplicity of SQL and PL/SQL and would solve the problem with Java. Especially, if it was an case insensitive sort operation. That’s the hard way (easy way at the bottom), but I figured it should be thrown in because some folks think everything is generic if written in Java. Though, I thought making it proprietary would increase the irony and wrote it as a Java library for Oracle.

Here’s the Java library, which you can run from the SQL*Plus command line, SQL Developer, or that pricey Toad:

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

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortOracleList" AS
 
  // Import required classes.
  import java.io.*;
  import java.security.AccessControlException;
  import java.sql.*;
  import java.util.Arrays;
  import oracle.sql.driver.*;
  import oracle.sql.ArrayDescriptor;
  import oracle.sql.ARRAY;
 
  // Define class.
  public class DemoSort {
    public static ARRAY getList(oracle.sql.ARRAY list) throws SQLException, AccessControlException {
 
      // Convert Oracle data type to Java data type.
      String[] unsorted = (String[])list.getArray();
 
      // Sort elements.
      Arrays.sort(unsorted, String.CASE_INSENSITIVE_ORDER);
 
      // Define a connection (this is for Oracle 11g).
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
      // Declare a mapping to the schema-level SQL collection type.
      ArrayDescriptor arrayDescriptor = new ArrayDescriptor("STRINGLIST",conn);
 
      // Translate the Java String{} to the Oracle SQL collection type.
      ARRAY sorted = new ARRAY(arrayDescriptor,conn,((Object[])unsorted));
    return sorted; }}
/

Then, you write the PL/SQL wrapper like this:

1
2
3
4

CREATE OR REPLACE FUNCTION sortTable(list STRINGLIST) RETURN STRINGLIST IS
LANGUAGE JAVA
NAME 'DemoSort.getList(oracle.sql.ARRAY) return oracle.sql.ARRAY';
/

You could test the case insensitive sort with this:

1
2

SELECT   column_value
FROM     TABLE(sort_collection(street_list('Adams', 'adams', 'Lewis', 'Clark', 'Fallon')));

Naturally, it ignores the fact you could do it like this without Java by using the UPPER function in the purely PL/SQL SORT_COLLECTION function shown earlier in this post:

12
13
14
15

    -- Sort a collection alphabetically based on case insensitive comparison.
    SELECT   column_value BULK COLLECT INTO lv_element_collection
    FROM     TABLE(old_element_collection)
    ORDER BY UPPER(column_value);

Anyway, it’s a bunch of thoughts about writing solutions for table view columns. Hope it helps those interested in nested tables.

My new Oracle Database 11g & MySQL 5.6 Developer Handbook will be available at Oracle Open World 2011 (OOW2011). It’s a great book to compare and contrast approaches in Oracle and MySQL. It covers Oracle SQL & PL/SQL and MySQL SQL & SQL/PSM (Persistent Stored Modules – functions and procedures). Unfortunately, it seems like the book won’t be available on amazon.com until much later in the month, and not available from amazon.de until November. You can read about it at McGraw Hill Professional’s web site. They’ve made a special effort to get copies to OOW2011. Here’s the source code for the book because I don’t know when it’ll be on the publisher’s web site.

I’ll also be at OOW2011. They’ve scheduled me in the bookstore (probably 2^nd floor of Moscone North, as usual) from 10 to 10:30 A.M. on Monday and Wednesday for a book signing. If you’re at OOW2011 and you like to stop by and say hello, I look forward to meeting you. Many folks leave comments on the posts but only a few suggest what they’d like me to write on when I’ve got a chance, and you can do that if you stop by to chat.

It’s also interesting to know how many folks use both Oracle and MySQL (any updates on that are always appreciated). Last year at the Bioinformatics and Computation Biology (ACM-BCB 2010) Conference in Niagara Falls, I found it interesting to discover how many pharmaceutical companies and national labs were using both Oracle and MySQL. They appeared consistent about using Oracle for their systems governed by legal compliance rules and MySQL for actual research.

The pharmaceutical companies also had clear barriers between the researchers and professional IT staff, specifically the DBAs. It seems that the DBAs don’t want to cede any control over installed Oracle instances, and they place barriers to research by denying additional Oracle instances when their site licenses would allow them to do so at no incremental cost. On the other hand, the DBAs are fine with letting researchers host and pilot with the MySQL Community Edition databases. This book supports those trying to figure out how to write portable SQL and how to port solutions from MySQL to Oracle and vice versa.

Hope to meet a few new folks at OOW2011. The Kindle version of the book became available 11/25/2011.

As an addendum to this original post, some folks asked for the summary of content for the new book, and the location of the errata (the errors of omission and commission in the book). Below is a summary of the book from page XVIII of the Introduction, and the errata is in the second comment to this post:

Part I: Development Components

• Chapter 1, “Architectures,” explains the Oracle 11g and MySQL 5.6 development architectures and highlights the comparative aspects of both client and server environments.
• Chapter 2, “Client Interfaces,” explains and demonstrates the basics of how you use SQL*Plus and MySQL Monitor client software.
• Chapter 3, “Security,” explains the security barriers for database servers and Data Control Language (DCL) commands that let you manage user and account privileges in the database servers.
• Chapter 4, “Transactions,” explains the nature of ACID-compliant transactions and the Two-phase Commit (2PC) process demonstrated by INSERT, UPDATE, and DELETE statements.
• Chapter 5, “Constraints,” explains the five primary database-level constraints and covers the check, not null, unique, primary key, and foreign key constraints.

Part II: SQL Development

• Chapter 6, “Creating Users and Structures,” explains how you can create users, databases, tables, sequences, and indexes.
• Chapter 7, “Modifying Users and Structures,” explains how you modify users, databases, tables, sequences, and indexes.
• Chapter 8, “Inserting Data,” explains how you insert data into tables.
• Chapter 9, “Updating Data,” explains how you update data in tables.
• Chapter 10, “Deleting Data,” explains how you delete data from tables.
• Chapter 11, “Querying Data,” explains how you query data from a single table, from a join of two or more tables, and from a join of two or more queries through set operators.
• Chapter 12, “Merging Data,” explains how you import denormalized data from external tables or source files and insert or update records in normalized tables.

Part III: Stored Program Development

• Chapter 13, “PL/SQL Basics,” explains the basics of using PL/SQL to write transactional blocks of code.
• Chapter 14, “SQL/PSM Basics,” explains the basics of using SQL/PSM to write transactional blocks of code.
• Chapter 15, “Triggers,” explains how to write database triggers in Oracle and MySQL databases.

Part IV: Appendix

• Appendix, Covers the answers to the mastery questions at the end of the chapters.

Somebody raised the question about writing stored functions while we were discussing scalar subqueries against COMMON_LOOKUP tables. Common look up tables store collections of possible <OPTION> elements. They’re basically generalized tables that contain a set of smaller tables, where the row sets make up a list of unique values.

When you write a function that must return one and only one row, the SELECT-INTO syntax is ideal in Oracle because it automatically raises an exception when the query returns no row or two or more rows. MySQL doesn’t raise an automatic exception when a SELECT-INTO fails to return a row but it does raise an ERROR 1722 when two or more rows are found.

Here are sample implementations written in Oracle’s PL/SQL and MySQL’s SQL/PSM languages. If you’re an Oracle developer and new to MySQL, the biggest oddity may be the delimiter, or it may be the sizing of formal parameters. Hopefully, its not the lack of formal declaration and exception blocks in SQL/PSM. For those coming from MySQL, the OR REPLACE command or dynamic sizing of formal parameter list values are the big changes.

Oracle PL/SQL Function

The Oracle PL/SQL function is very straightforward and helped by pre-defined exceptions for both the no data found and too many rows found errors. Formal parameters inherit their physical size at run time from the calling scope program.

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

-- Create or replace existing function with new implementation.
CREATE OR REPLACE FUNCTION get_common_lookup_id
( pv_table   VARCHAR2
, pv_column  VARCHAR2
, pv_type    VARCHAR2 )
RETURN NUMBER IS
 
  -- Declare a return variable.
  lv_return  NUMBER;
 
BEGIN
 
  -- Query data and assign it to a local variable.
  SELECT   common_lookup_id
  INTO     lv_return
  FROM     common_lookup
  WHERE    common_lookup_table = pv_table
  AND      common_lookup_column = pv_column
  AND      common_lookup_type = pv_type;
 
  -- Return the value found.
  RETURN lv_return;
 
EXCEPTION
 
  -- Handle errors.
  WHEN NO_DATA_FOUND THEN
    RAISE;
  WHEN TOO_MANY_ROWS THEN
    RAISE;
 
END;
/

MySQL SQL/PSM Function

The lack of an implicitly raised exception when a SELECT-INTO structure fails to return a row adds several steps to a SQL/PSM function. You must define a custom exception and an if-block to raise the exception, which is trigger by returning a null value into the lv_return local variable.

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

/* Conditionally drop the function before trying to create it. */
DROP FUNCTION IF EXISTS get_common_lookup_id;
 
/* Change the delimiter to write a stored procedure into the database. */
DELIMITER $$
 
/* Create a function that replaces a scalar subquery. */
CREATE FUNCTION get_common_lookup_id
( pv_table   VARCHAR(30)
, pv_column  VARCHAR(30)
, pv_type    VARCHAR(30))
RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a return variable. */
  DECLARE lv_return  INT UNSIGNED;
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE no_data_found CONDITION FOR SQLSTATE '99001';
 
  /* Query data and assign it to a local variable. */
  SELECT   common_lookup_id
  INTO     lv_return
  FROM     common_lookup
  WHERE    common_lookup_table = pv_table
  AND      common_lookup_column = pv_column
  AND      common_lookup_type = pv_type;
 
  /* The local variable is only null when no rows are returned by the implicit cursor. */
  IF lv_return IS NULL THEN
    SIGNAL no_data_found SET MESSAGE_TEXT = 'Result was no rows found.';
  END IF;  
 
  /* Return the value found. */
  RETURN lv_return;
 
END;
$$
 
/* Reset the delimiter to the default to run other programs. */
DELIMITER ;

Hope this help solve your problems.

Somebody wanted another example of how to leverage a true/false condition from a PL/SQL stored function in PHP. The first key is that you write the function as if you were using it in SQL not PL/SQL. That means you return a NUMBER data type not a PL/SQL-only BOOLEAN data type.

Here’s the schema-level PL/SQL function:

CREATE OR REPLACE FUNCTION like_boolean
( a NUMBER, b NUMBER ) RETURN NUMBER IS
 
  /* Declare default false return value. */
  lv_return_value NUMBER := 0;
 
BEGIN
 
  /* Compare numbers and return true for a match. */
  IF a = b THEN
    lv_return_value := 1;
  END IF;
 
  /* Return value. */
  RETURN lv_return_value;
 
END;
/

Here’s the PHP that leverages the PL/SQL in an if-statement on line #24:

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

<?php
  // Capture local variables when provided.
  $thingOne = (isset($_GET['thingOne'])) ? $_GET['thingOne'] : 1;
  $thingTwo = (isset($_GET['thingTwo'])) ? $_GET['thingTwo'] : 1;
 
  // Open a connection.
  if(!$c = oci_connect("student","student","localhost/xe"))
  {
    die;
  }
  else
  {
 
    // Parse a statement.  
    $s = oci_parse($c,"BEGIN
                         :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo);
                       END;");
 
    // Bind input and output values to the statement.
    oci_bind_by_name($s,":returnValue",$returnValue);
    oci_bind_by_name($s,":thingOne",$thingOne);
    oci_bind_by_name($s,":thingTwo",$thingTwo);
 
    // Execute the statement.
    if (@oci_execute($s))
    {
      // Print lead in string.
      print "[".$thingOne."] and [".$thingTwo."] ";  
      if ($returnValue)
        print "are equal.<br />";
      else
        print "aren't equal.<br />";
    }
 
    // Clean up resources.
    oci_close($c);
  }
?>

If you run into a parsing error, which is infrequent now. You can wrap the multiple row PL/SQL anonymous block call with this function. It strips tabs and line returns. Alternatively, you can put all the lines of PL/SQL on a single line.

  // Strip special characters, like carriage or line returns and tabs.
  function strip_special_characters($str)
  {
    $out = "";
    for ($i = 0;$i < strlen($str);$i++)
      if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
          (ord($str[$i]) != 13))
        $out .= $str[$i];
 
    // Return pre-parsed SQL statement.
    return $out;
  }

If you run into a parsing problem on Oracle XE 10g, you can wrap the PL/SQL call like the following. Alternatively, you can place the entire anonymous PL/SQL block on a single line without embedded tabs or return keys..

10
11
12
13

  $s = oci_parse($c,strip_special_characters(
                    "BEGIN
                       :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo);
                     END;"));

Hope that answers the question and helps some folks.

We had a discussion today about how you can manage legacy code that you can’t change. For example, how do you access a PL/SQL function in PHP that returns a PL/SQL table of record structures? PL/SQL tables, index-by tables, or associate arrays are one in the same dependent on the release documentation. They’ve been available since Oracle 7.3 (roughly 15+ years).

You’ve a handful of solutions but I think the best is to wrap it in a Pipelined Table function (more on that in this older post). Here’s an example of such a package, wrapper function, and PHP program calling the wrapper function (command-line only PHP sample code).

Let’s say you have the following type of legacy package specification and body:

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

-- Create the package specification.
CREATE OR REPLACE PACKAGE lib IS
  /* Define a record structure. */
  TYPE movie_title_record IS RECORD
  ( title    VARCHAR2(60)
  , subtitle VARCHAR2(60));
 
  /* Define an associative array of a package record structure. */
  TYPE movie_title_table IS TABLE OF movie_title_record
  INDEX BY BINARY_INTEGER;
 
  /* Define a prototype of a package function. */
  FUNCTION get_movie
  ( pv_title VARCHAR2 ) RETURN lib.movie_title_table;
 
END lib;
/
 
-- Create the package body.
CREATE OR REPLACE PACKAGE BODY lib IS
 
  /* Implement the package function. */
  FUNCTION get_movie
  ( pv_title VARCHAR2 ) RETURN lib.movie_title_table IS
 
    /* Declare a counter variable. */
    lv_counter INTEGER := 1;
 
    /* Declare an instance of the package nested table and initialize it. */
    lv_table   LIB.MOVIE_TITLE_TABLE := lib.movie_title_table();
 
    /* Define a parameterized cursor to read values from the ITEM table. */  
    CURSOR c ( cv_partial_title VARCHAR2 ) IS
      SELECT   i.item_title
      ,        i.item_subtitle
      FROM     item i
      WHERE    i.item_title LIKE '%'||cv_partial_title||'%';
 
  BEGIN
 
    /* Read the contents of the parameterized cursor. */
    FOR i IN c (pv_title) LOOP
 
      /* Extend space, assign values from the cursor to the record structure
         of the nested table, and increment counter. */
      lv_table.EXTEND;
      lv_table(lv_counter) := i;
      lv_counter := lv_counter + 1;
 
    END LOOP;
 
    /* Return PL/SQL-scope nested table. */
    RETURN lv_table;
 
  END get_movie;
 
END lib;
/

You can wrap the lib package’s get_movie function with a schema-level function provided you convert the older associative array to a PL/SQL-scope nested table. You can do that in two steps. The first requires that you create a wrapper package specification, like the following example. The second step requires you to write a conversion wrapper function, shown later.

The table is dependent on the named record structure from the lib, and as such the packages are now tightly coupled. This is not uncommon when you can’t fix a vendors legacy code set.

1
2
3
4
5
6
7

CREATE OR REPLACE PACKAGE wlib IS
 
  /* Define a nested table of a package record structure. */
  TYPE movie_title_table IS TABLE OF lib.movie_title_record;
 
END wlib;
/

The wrapper function also converts the Oracle Database 7.3 forward data type to an Oracle Database 8.0.3 data type, and then pipes it into a SQL aggregate table. SQL aggregate tables are valid call parameters in the SQL-context. The TABLE function converts the collection of record structures into an inline view or derived table, as you’ll see a little farther along.

You should note that the return type of this function differs from the original package-level get_movie function. The former uses an associative array defined in the lib, while the latter uses a nested table defined in the wlib package.

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

CREATE OR REPLACE FUNCTION get_movie
( pv_title VARCHAR2 ) RETURN wlib.movie_title_table
PIPELINED IS
 
  /* Define a PL/SQL-scope associative array (Available Oracle 7.3). */
  lv_table_source LIB.MOVIE_TITLE_TABLE;
 
  /* Define a PL/SQL-scope nested table (Available Oracle 8.0.3). */
  lv_table_target WLIB.MOVIE_TITLE_TABLE := wlib.movie_title_table();
 
BEGIN
 
  /* Assign the results of a PL/SQL-scope call to the package function. */
  lv_table_source := lib.get_movie(dbms_assert.simple_sql_name(pv_title));
 
  /* Read the contents of the PL/SQL-scope nested table into a PIPE ROW. */    
  FOR i IN 1..lv_table_source.COUNT LOOP
    lv_table_target.EXTEND;
    lv_table_target(i) := lv_table_source(i);
    PIPE ROW(lv_table_target(i));
  END LOOP;
 
END;
/

You can test this exclusively in SQL*Plus with the following formatting and query. The TABLE function translates the returned array into an inline view or derived table for processing.

-- Format columns for display with SQL*Plus.
COLUMN title    FORMAT A20 HEADING "Movie Title"
COLUMN subtitle FORMAT A20 HEADING "Movie Subtilte"
 
-- Select the contents of the schema-level function in a SQL-context.
SELECT *
FROM   TABLE(get_movie('Star'));

If you’re using my sample code from the Oracle Database 11g PL/SQL Programming book, you should see:

Movie Title          Movie Subtilte
-------------------- --------------------
Star Wars I          Phantom Menace
Star Wars II         Attack of the Clones
Star Wars II         Attack of the Clones
Star Wars III        Revenge of the Sith

The following is a simple command-line PHP program that calls the wrapper function. It calls the wrapper function, which calls the lib.get_movie() function, and it converts the PL/SQL data type from an associative array (Oracle 7.3+ data type) to a nested table (Oracle 8.0.3+ data type). The nested table is defined in the wlib library, which supplements rather than replaces the original lib library.

The last thing that the wrapper function does is transform the associative array result into a nested table before placing it in the pipe (this process is known as a Pipelined Table function). Only nested table and varray data types may be piped into a SQL aggregate table. Then, the external programming language can manage the output as if it were a query.

Here’s the PHP program:

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

<?php
  // Connect to the database.
  if ($c = oci_connect("student","student","localhost/orcl"))
  {
    // Parsing a statement into a digestable SQL statement.
    $s = oci_parse($c,"SELECT * FROM TABLE(get_movie('Star'))");
 
  // Execute the parsed statement.
  oci_execute($s,OCI_DEFAULT);
 
  // Store control variable for the number of columns returned by the statement.
  $columns = oci_num_fields($s);
 
  // Find the number of columns, loop through them, and write their column name metadata.
  for ($i = 1; $i <= $columns; $i++) {
 
    // Print the column names, also known as field names.
    print oci_field_name($s,$i);
 
    // Define a variable.
    $line;
 
    /* Check whether a variable is declared and pad it.
     *   The numeric literal is for my convenience because the maximum size
     *   of possible returns is known. In a real situation, you'd use the 
     *   following str_pad() call:
     *
     *   str_pad($temp,oci_field_size($s,$i),"-") */
    if (!isset($line)) 
      $line .= str_pad($temp,15,"-");
    else
      $line .= " ".str_pad($temp,20,"-");
 
    /* One of the values requires a double tab to line up, otherwise this
       practice is unwise. */    
    if ($i < $columns)
      print "\t\t"; }
 
  // Print line return for the header and a line to mimic Oracle SQL*Plus output.
  print "\n";
  print $line .= "\n";
 
  // Process row-by-row data returned when data is returned.
  while (oci_fetch($s))
  {
    // Process column-by-column data returned for each row.
    for ($i = 1; $i <= $columns; $i++) {
      print oci_result($s,$i);
 
    if ($i < $columns) print "\t"; }
 
      // Print line return for the row of data returned.
    print "\n";
  }
 
  // Release resources.
  oci_close($c);
 
  // Explicitly free any resources.
  oci_free_statement($s);
  oci_free_cursor($c);
  }
?>

Assuming you call this callOracle.php, you can call it from the command-line with this syntax:

php callOracle.php

It prints, like it would in SQL*Plus:

TITLE           SUBTITLE
--------------- --------------------
Star Wars I     Phantom Menace
Star Wars II    Attack of the Clones
Star Wars II    Attack of the Clones
Star Wars III   Revenge of the Sith

Hope that helps those working with legacy Oracle code.

Sometimes I’m amazed at things that come up. A student wondered why the sequences were incrementing by two when they’re defined to increment by one. It turns out that they were using Oracle APEX to create SQL statements to build a table, constraints, and a auto-numbering sequence trigger. Before executing the code, they’d copy it into their re-runnable script that created their schema.

Here’s an example of code that was generated by APEX for a table:

-- Create the table.
CREATE TABLE onesy
( onesy_id NUMBER
, onesy_text VARCHAR2(20));
 
-- Add the primary key constraint.
ALTER TABLE onesy ADD CONSTRAINT onesy_seq PRIMARY KEY (onesy_id);
 
-- Add a database trigger.
CREATE OR REPLACE TRIGGER onesy_trg 
BEFORE INSERT ON onesy
FOR EACH ROW
BEGIN
  :NEW.onesy_id := onesy_s1.NEXTVAL;
END;
/

This works in APEX because it doesn’t create forms that call onesy_seq.NEXTVAL but they did create that logic in their forms. The INSERT statement would look like:

INSERT INTO onesy VALUES (onesy_seq.NEXTVAL, 'One');

Therefore, the INSERT statement incremented the trigger by one and the database trigger incremented it by one. The result is that sequences two-step, which isn’t effective or the desired behavior.

After I explained the two-step problem, they asked if they could only call the trigger when the primary key value was null. While they could do that like this:

1
2
3
4
5
6
7
8
9

-- Add a database trigger.
CREATE OR REPLACE TRIGGER onesy_trg 
BEFORE INSERT ON onesy
FOR EACH ROW
WHEN (NEW.one_id IS NULL)
BEGIN
  :NEW.onesy_id := onesy_s1.NEXTVAL;
END;
/

The problem is that this type of trigger doesn’t stop other possible problems. While it prevents two-stepping the sequence, it doesn’t prevent two other errors.

One possible error that isn’t managed in this scenario is the use of numeric literals beyond the next value of the sequence. It writes the row but eventually the sequence catches up to the higher value and a production insert would fail. It would raise the following exception.

INSERT INTO onesy (onesy_text) VALUES ('Eight')
*
ERROR at line 1:
ORA-00001: UNIQUE CONSTRAINT (STUDENT.PK_ONE) violated

Another possible error can occur when you use a bulk insert operation. Assuming you’re inserting 500 rows at a go, you query the maximum value of the onesy_id column and then create an array of 500 numbers. Then, you perform the bulk INSERT statement. The next call to the trigger would raise another ORA-00001 unique constraint error.

Yes, you could lock the table before you perform the bulk operation. After the bulk operation you would drop and recreate the sequence with a new value equal to the maximum value in the column, and unlock the table. This limits concurrency of operation. You could treat these bulk operations as off-line transactions (batch processing) and it would work nicely.

You could also implement a policy that no bulk operations provide generated column values that link to a sequence. Beyond it’s impracticality to manage, that type of restriction does limit the benefit of bulk operations.

The students wanted a solution. So, here’s my take on a trigger that prevents collision with values above the next sequence value. It assumes that bulk operations will be performed as batch processing where you can disable this trigger.

This trigger disallows numeric literals, logs any attempts to use them, and stops processing when an INSERT statement tries to use anything other than the .NEXTVAL of the sequence. It will only work in an Oracle Database 11g database because the context of using a sequence_name.CURRVAL in a comparison isn’t supported in prior releases. The onesy table is renamed the one table in 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
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

-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE msg_s1;
 
CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
DECLARE
 
  /* Define an autonomous transaction scope to the trigger. */
  PRAGMA AUTONOMOUS_TRANSACTION;
 
  /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence
     is called before a .NEXTVAL for the same sequence in the same session. */
  no_sequence_in_scope EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002);
 
BEGIN
 
  /* Check if surrogate key is provided and the sequence not out of transaction scope. */
  IF :NEW.one_id IS NOT NULL AND NOT :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Write message when sequence value is a numeric literal not a sequence
       generated value but a one_s1.NEXTVAL was previously called in the session.
       Commit after write or information is lost because it throws an user-defined
       exception. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing exception. */
    RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:NEW.one_id||']['||:NEW.one_text||'].');
 
  ELSIF :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */
    NULL;
 
  ELSE
 
    /* Increment sequence and assign a value when one isn't provided, like a NULL value. */
    :NEW.one_id := one_s1.NEXTVAL;
 
  END IF;
 
EXCEPTION
 
  /* Handle a no sequence in scope error. */
  WHEN no_sequence_in_scope THEN
 
    /* Write and commit log message for error. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing an exception. */
    RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:NEW.one_id||'].');
 
END;
/

Since anonymous transaction triggers are tricky, it’s important to note that the message writing requires two commits. One before raising the exception when the .CURRVAL is in session scope and another in the exception handler before raising the error. If you forget those COMMIT statements, this is a sample of the error stack:

INSERT INTO one VALUES (one_s1.nextval,'Six')
                        *
ERROR at line 1:
ORA-06519: active autonomous TRANSACTION detected AND rolled back
ORA-06512: at "STUDENT.ONE_T1", line 31
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'

The trigger raises the following type of exceptions for an offending INSERT statement. The first occurs when the sequence is valid in the session scope, like:

DECLARE
*
ERROR at line 1:
ORA-20001: NOT a SEQUENCE generated VALUE [1].
ORA-06512: at "STUDENT.ONE_T1", line 48
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'
ORA-06512: at line 15

The second occurs when the sequence isn’t valid in the session scope.

INSERT INTO one VALUES (401,'Nine')
            *
ERROR at line 1:
ORA-20002: ID provided BY calling scope IS NOT NEXT SEQUENCE VALUE [401][Nine].
ORA-06512: at "STUDENT.ONE_T1", line 24
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'

A value that’s below the current high-watermark of the sequence raises a unique constraint, like this:

INSERT INTO one VALUES (1,'Eight')
*
ERROR at line 1:
ORA-00001: UNIQUE CONSTRAINT (STUDENT.PK_ONE) violated

The following is a script with all the necessary code components to test 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
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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140

-- Conditionally drop tables and sequences.
BEGIN
  FOR i IN (SELECT object_name, object_type FROM user_objects WHERE object_name IN ('ONE','ONE_S1','MSG','MSG_S1')) LOOP
    IF i.object_name = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';  
    ELSE
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;  
    END IF;
  END LOOP;
END;
/
 
-- Create a table ONE.
CREATE TABLE one (one_id NUMBER CONSTRAINT pk_one PRIMARY KEY, one_text VARCHAR2(20));
 
-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE one_s1;
 
-- Create a table MSG.
CREATE TABLE msg (msg_id NUMBER, msg_text VARCHAR2(80));
 
-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE msg_s1;
 
-- Create or replace trigger.
CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
DECLARE
 
  /* Define an autonomous transaction scope to the trigger. */
  PRAGMA AUTONOMOUS_TRANSACTION;
 
  /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence
     is called before a .NEXTVAL for the same sequence in the same session. */
  no_sequence_in_scope EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002);
 
BEGIN
 
  /* Check if surrogate key is provided and the sequence not out of transaction scope. */
  IF :NEW.one_id IS NOT NULL AND NOT :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Write message when sequence value is a numeric literal not a sequence
       generated value but a one_s1.NEXTVAL was previously called in the session.
       Commit after write or information is lost because it throws an user-defined
       exception. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing exception. */
    RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:NEW.one_id||']['||:NEW.one_text||'].');
 
  ELSIF :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */
    NULL;
 
  ELSE
 
    /* Increment sequence and assign a value when one isn't provided, like a NULL value. */
    :NEW.one_id := one_s1.NEXTVAL;
 
  END IF;
 
EXCEPTION
 
  /* Handle a no sequence in scope error. */
  WHEN no_sequence_in_scope THEN
 
    /* Write and commit log message for error. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing an exception. */
    RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:NEW.one_id||'].');
 
END;
/
 
-- Define a schema collection of strings.
CREATE OR REPLACE TYPE one_index AS TABLE OF VARCHAR2(20);
/
 
-- Define a schema collection of strings.
CREATE OR REPLACE TYPE one_list AS TABLE OF VARCHAR2(20);
/
 
 
-- Anonymous block bulk testing program.
DECLARE
  -- Declare two scalar collection variables.
  lv_index ONE_INDEX := one_index();
  lv_list  ONE_LIST := one_list('One','Two','Three','Four','Five');
BEGIN
 
  -- Initialize the collection of the ONE_INDEX collection.
  lv_index.EXTEND(lv_list.COUNT);
  FOR i IN lv_list.FIRST..lv_list.LAST LOOP
    dbms_output.put_line('['||i||']['||lv_list(i)||']');
    lv_index(i) := i;
  END LOOP;
 
  -- Perform a trivial example of a bulk insert.  
  FORALL i IN lv_list.FIRST..lv_list.LAST
    INSERT INTO one VALUES (lv_index(i),lv_list(i));
 
END;
/
 
-- Testing INSERT statements until sequence value increments beyond bulk insert values.
INSERT INTO one VALUES (6,'Six');
INSERT INTO one VALUES (one_s1.nextval,'Seven');
INSERT INTO one VALUES (1,'Eight');
INSERT INTO one VALUES (401,'Nine');
INSERT INTO one VALUES (NULL,'Ten');
 
COLUMN msg_id   FORMAT 999 HEADING "Msg|ID"
COLUMN msg_text FORMAT A72 HEADING "Msg Text"
 
-- 
SELECT * FROM one;
SELECT * FROM msg;
 
-- Anonymous block bulk testing program.
DECLARE
  -- Declare two scalar collection variables.
  lv_list  ONE_LIST := one_list('Twenty-one','Twenty-two','Twenty-three','Twenty-four','Twenty-five');
BEGIN
 
  -- Perform a trivial example of a bulk insert.  
  FORALL i IN lv_list.FIRST..lv_list.LAST
    INSERT INTO one VALUES (NULL,lv_list(i));
 
END;
/
 
-- Query tables.
SELECT * FROM one;
SELECT * FROM msg;

If I’ve fat fingered any typing or made logical errors, please let me know.

My students wanted some additional examples on correlated update statements. I’ve been struggling with what the right way may be to illustrate them.

Correlated subqueries are a hard concept to explain to those new to SQL. While correlated update statements seem impossibly obscure to many or inordinately complex. New SQL developers often flee to the comfort of procedural programs when it comes to update statements.

This uses my video store data model. It’s a correlated update statement to clean up potential corrupt data. More or less something a DBA might run to ensure a business rule hasn’t been violated over time. It checks for the correct foreign key value in a table when a dependent table contains one or more than one row of data.

The aqua-green box highlights a subquery that aggregates foreign key columns and groups the result with the foreign key value. The results from this subquery become a run-time view or derived table. The result set is a foreign key value and a substitute string literal value for each row in the contact table. These results correlate to the update statement’s rows based on the input parameter. The input parameter is a column from each updated row.

A unique key (or check constraint) exists on the combination of the common_lookup_table, common_lookup_column, and common_lookup_type columns. This ensures that only one row is returned and assigned to the member_type column in the member table. The update statement naturally works in either Oracle or MySQL without any porting changes.

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

UPDATE member m
SET    member_type =
 (SELECT common_lookup_id
  FROM   common_lookup
  WHERE  common_lookup_table = 'MEMBER'
  AND    common_lookup_column = 'MEMBER_TYPE'           
  AND    common_lookup_type =
          (SELECT  dt.member_type
           FROM   (SELECT   c.member_id
                   ,        CASE
                              WHEN COUNT(c.member_id) > 1 THEN 'GROUP'
                              ELSE 'INDIVIDUAL'
                            END AS member_type
                   FROM     contact c
                   GROUP BY c.member_id) dt
           WHERE   dt.member_id = m.member_id));

While this type of solution is powerful in its own right, I thought it might be interesting to see their procedural equivalents. These correlated subqueries run for each row returned by the master query (or outermost statement). Therefore, they act like functions.

Procedural equivalents (or user-defined functions) simplify the update statement like so:

UPDATE member m
SET    member_type = get_member_type(m.member_id);

If you’re interested in seeing how you would implement this solution in a user-defined function, just expand the dropdown that interest you.

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

CREATE OR REPLACE FUNCTION get_member_type
(pv_member_id NUMBER) RETURN NUMBER IS
 
  -- Define a collection of strings.
  TYPE type_options IS TABLE OF VARCHAR2(10);
 
  -- Declare local variables.
  lv_dependent NUMBER := 1;
  lv_return_id NUMBER;
 
  -- Declare a local variable of the collection.
  lv_member_type TYPE_OPTIONS := type_options('INDIVIDUAL','GROUP');
 
  -- Define a dynamic cursor to count the number of foreign key values. 
  CURSOR count_contact (cv_member_id NUMBER) IS
    SELECT COUNT(c.member_id)
    FROM   contact c
    WHERE  c.member_id = cv_member_id;
 
  -- Define a dynamic cursor to find a key for an individual or group member type.    
  CURSOR get_lookup_id (cv_type VARCHAR2) IS
    SELECT common_lookup_id
    FROM   common_lookup
    WHERE  common_lookup_context = 'MEMBER'
    AND    common_lookup_type = cv_type;
 
BEGIN
 
  -- Get the number of foreign key values for a contact.
  OPEN  count_contact(pv_member_id);
  FETCH count_contact
  INTO  lv_dependent;
  CLOSE count_contact;
 
  -- Open the dynamic cursor with the required value.  
  IF lv_dependent = 1 THEN
    OPEN get_lookup_id(lv_member_type(1));
  ELSE
    OPEN get_lookup_id(lv_member_type(2));
  END IF;
 
  -- Get the correct surrogate primary key value.
  FETCH get_lookup_id
  INTO  lv_return_id;
  CLOSE get_lookup_id;
 
  -- Return the correct primary key for use as a foreign key.
  RETURN lv_return_id;
END;
/

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

SELECT 'DROP FUNCTION IF EXISTS get_member_type' AS "Statement";
DROP FUNCTION IF EXISTS get_member_type;
 
SELECT 'DELIMITER $$' AS "Statement";
DELIMITER $$
 
SELECT 'CREATE FUNCTION get_member_type' AS "Statement"$$
CREATE FUNCTION get_member_type(pv_member_id INT) RETURNS INT 
BEGIN
 
  /* Define three local variables. */
  DECLARE lv_contact_number  INT;
  DECLARE lv_member_type     CHAR(30);
  DECLARE lv_return_value    INT;
 
  /* Define a dynamic cursor to count the number of foreign key values. */  
  DECLARE contact_cursor CURSOR FOR
    SELECT COUNT(c.member_id)
    FROM   contact c
    WHERE  c.member_id = pv_member_id;
 
  /* Define a dynamic cursor to find a key for an individual or group member type. */
  DECLARE common_lookup_cursor CURSOR FOR
    SELECT common_lookup_id
    FROM   common_lookup
    WHERE  common_lookup_table = 'MEMBER'
    AND    common_lookup_column = 'MEMBER_TYPE'           
    AND    common_lookup_type = lv_member_type;
 
  /* Get the number of foreign key values for a contact. */    
  OPEN  contact_cursor;
  FETCH contact_cursor
  INTO  lv_contact_number;
  CLOSE contact_cursor;
 
  /* Assign group membership when more than one foreign key value is found; and
     assign individual membership when only one foreign key value is found. */  
  IF lv_contact_number > 1 THEN
    SET lv_member_type = 'GROUP';
  ELSE
    SET lv_member_type = 'INDIVIDUAL';
  END IF;
 
  /* Get the correct surrogate primary key value. */  
  OPEN  common_lookup_cursor;
  FETCH common_lookup_cursor
  INTO  lv_return_value;
  CLOSE common_lookup_cursor;
 
  /* Return the correct primary key for use as a foreign key. */  
  RETURN lv_return_value;
END;
$$
 
SELECT 'DELIMITER $$' AS "Statement"$$
DELIMITER ;

You can query the results of the update statement with the following.

1
2
3
4
5
6
7
8
9
10
11

SELECT   m.member_id
,        dt.quantity
,        m.member_type
,        cl.common_lookup_type
FROM     member m JOIN (SELECT   member_id
                        ,        COUNT(c.member_id) AS quantity
                        FROM     contact c
                        GROUP BY c.member_id) dt
ON       m.member_id = dt.member_id JOIN common_lookup cl
ON       m.member_type = cl.common_lookup_id
ORDER BY m.member_id;

As always, I look forward to helping and gaining insight.

Somebody, who read this post on Debugging MySQL Procedures, asked why the strategy of selecting a string literal didn’t work in a MySQL function. That’s easy, they’re not designed to support a SELECT statement, only a SELECT-INTO statement.

Why? That’s the purpose of a function to perform something and return a single reply.

That’s also why a MySQL functions only support the IN mode of operation for formal and call parameters. When formal parameters are restricted to in-mode-only operations, they implement a pass-by-value function model. This can also be expressed from the other side of the looking glass. In that case, MySQL functions don’t support pass-by-reference functions that use the INOUT or OUT mode operations.

If you put a SELECT statement in a function to print internal values or comments, it raises an error. Take for example the following attempt to create the debugging function with an echo of output (that works in stored procedures).

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT '[Debug #1]';
  RETURN 1;
END;
$$

It fails to create the function because you’ve violated a key integrity rule. It also raises the following error:

ERROR 1415 (0A000): Not allowed to return a result set from a function

You have two potential solutions to this problem. The first is limited and inflexible. The second isn’t as limited or inflexible and is the recommended way to debug your functions without a tool. That’s to use a temporary table to record run-time debugging events.

SET @counter := 0;
SET @msg := '';

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  SELECT CONCAT('[Debug #',@counter,']') INTO @msg;
  RETURN 1;
END;
$$

SELECT debugger();
SELECT @msg;

+------------+
| @msg       |
+------------+
| [Debug #1] |
+------------+

SET @counter := 0;

CREATE TABLE debugger
( debug_comment CHAR(80)) ENGINE=MEMORY;

1
2
3
4
5
6
7

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']'));
  RETURN 1;
END;
$$

SELECT debugger();
SELECT debugger();
SELECT debugger();
SELECT debug_comment FROM debugger;

+---------------+
| debug_comment |
+---------------+
| [Debug #1]    |
| [Debug #2]    |
| [Debug #3]    |
+---------------+

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
67
68
69
70
71
72
73
74
75
76

-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that returns 1.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT '[Debug #1]';
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Declare session level variables.
SET @counter := 0;
SET @msg := '';
 
-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that writes to local session variables.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  SELECT CONCAT('[Debug #',@counter,']') INTO @msg;
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Test the function code and read the session-level variable contents.
SELECT debugger();
SELECT @msg;
 
-- Declare a session level variable.
SET @counter := 0;
 
-- Conditionally drop the function when it exists.
DROP TABLE IF EXISTS debugger;
 
-- Create a temporary (in-memory) table to record debugging information.
CREATE TABLE debugger
( debug_comment CHAR(80)) ENGINE=MEMORY;
 
-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that writes to a debugging table.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']'));
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Test the function code and read the session-level variable contents.
SELECT debugger();
SELECT debugger();
SELECT debugger();
SELECT debug_comment FROM debugger;