MacLochlainns Weblog

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.

Somebody asked if you really have to run the MySQL Configuration Wizard when you want to shut out network connections. The answer is no.

If you want to secure the database server to perform maintenance, you can comment out the port assignment line in the [mysqld] section and add the following in the same section:

# The TCP/IP Port the MySQL Server will listen on
# port=3306

# Instruct it to skip networking and enable named pipes.
skip-networking
enable-named-pipe
 
# Define the Pipe the MySQL Server will use.
socket=mysql

This allows only users on the local system to connect to the database. You can test it by running the following PHP program as a command-line process form the server.

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

<?php
  // Attempt to connect to your database.
  $c = @mysqli_connect("localhost", "username", "password", "somedb");
  if (!$c) {
    print "Sorry! The connection to the database failed.";
    die();
  }
  else {
    // Initialize a statement in the scope of the connection.
    print "Congrats! You've connected to a MySQL database!";
  }
?>

You call a command-line PHP program like this:

php phpConnect.php

It would fail when you call it from the Apache web server’s htdocs folder because network communication across TCP/IP is closed. Only local sockets are available across the mysql pipe. There’s no magic to the pipe name of mysql but it’s the default pipe name convention.

My data warehousing class goes through traditional design methods, like Kimball. Then, we explore XML databases. We use the Community License of the MarkLogic 4.2 Server, which is the engine of O’Reilly’s Safari online.

You can find the installation, configuration, and client-tool installation/configuration of the MarkLogic Server in the following blog pages:

1. Installation, License, and first-use configuration
2. Configuration of a Forest, HTTP Server, and WebDAV
3. CQ client software

You can find the list of potential function here on the developer’s site for MarkLogic.

I had a chance (30 minutes) to play with Excel 2011 on the Mac OS X today. I was shocked to discover that Excel 2011 didn’t appear to support User-Defined Functions (UDFs) like Excel 2010 for Windows. My understanding was that this release would be one where it implemented Visual Basic for Applications (VBA) like Windows. Initially I thought it didn’t but I bought my own copy, did a full install with Visual Basic, and it appears that Microsoft has delivered. Oops, my bad for assuming the machine I played on originally had a solid installation. It appears to have had only a standard installation.

Watch out because UDFs fail with a #NAME! error on a standard install of Excel 2011. While they’re found in the Insert Function dialog in both cases, they only appear to work with a full installation. The downside for Insert Function is that, like Excel 2008, it has no quick poplist to narrow the function choices to groups. We have the big list once more.

Here are my test functions:

Function hello()
  hello = "Hello World!"
End Function
 
Function hellowhom(nameIn As Variant)
  hellowhom = "Hello " + CStr(nameIn) + "!"
End Function

I think I found the trick to get Excel 2011 recognize and run User-Defined Functions. Make sure you do a custom installation and check Visual Basic for Application. Anyway, you can test these functions like that shown below. Column A contains the text of the formulas (a preceding single quote does that), and column B contains actual function calls.

Enabling the Developer ribbon took a few moments when I got my own copy. I figured that updating this was better than making a new post on the blog and linking them. It’s a three step process.

1. Navigate to the Excel menu item and select Preferences…, as shown below.

2. In the Excel Preferences shown below, click the Ribbon icon.

3. In the Show or hide tabs, or drag them into the order you prefer: box shown below, enable the Developer checkbox.

It’s awesome, now accountants and economists can switch to Mac OS X without having to host a virtual machine with Microsoft Excel.

Rebuilding new reference environments on my MacPro, I started with Ubuntu 10.04.01 LTS (64 bit), I had to recall the step to install VMWare Tools. It’s quite simple but I know my students may need the steps to configure a VMWare virtual machine and it may benefit others. While this Ubuntu help page is a good start it isn’t a step-by-step configuration guide.

1. Navigate to the VMWare Menu, choose Virtual Machine and in the drop down menu Install VMWare Tools. This will mount a virtual CD in the Ubuntu virtual machine.
2. Open a terminal session by choosing Applications, within the drop down choose Accessories, and in the subsequent drop down choose Terminal. It will launch a terminal session for command-line entry. The screen shot will look like the following.

3. From the command-line perform the following tasks:

cd /media/VM*
cp VMwareTools*.gz /tmp
cd /tmp
gunzip VMwareTools*.gz
tar -xvf VMwareTools*.tar
cd vmware-tools-distrib
sudo ./vmware-install.pl

4. After starting the vmware-install.pl, accept all the default prompts. Alternatively, as Josh points out enter the following to skip the prompts:

sudo ./vmware-install.pl --default

5. After the configuration completes, you’re prompted to restart the X-Windows. The easiest way is to reboot. You click on the upper right corner to get the drop down menu to restart Ubuntu. A screen shot follows.

This completes the VMWare Tools installation on Ubuntu but unfortunately, you may need to setup the network connection. In a couple instances, the Ubuntu installation appears to have corrupted the VMWare networking process. The result is that the DNS setup on Ubuntu didn’t work.

When the Ubuntu /etc/resolv.conf file is empty. You should first restart the VMWare network. This can be done without rebooting your native Mac OS X. Open a Terminal session, and navigate to the following directory and restart the VMWare network or use this command with backquoting.

sudo /Library/Application\ Support/VMware\ Fusion/boot.sh  --restart

If your Ubuntu /etc/resolv.conf file is empty, you can manually edit it. The last line in this sample depends on your IP subnet. I’ve entered it by assuming that you’re on 192.168.75.0 to 192.168.75.255 with a 255.255.255.0 network mask. You can refer to this prior post for the details on how you find your VMWare NAT subnet.

When your Ubuntu /etc/resolv.conf file is empty, add these values:

# Generated by NetworkManager
domain localdomain
search localdomain
nameserver 192.168.75.2

Hope this helps some folks.

While working on a VBA write-up for some documentation on Excel 2007/2010, I ran into some interesting parameter validation rules for Excel User-Defined Functions (UDFs). I found that optional values are suppressed when you pass a cell reference that points to an empty cell.

Let’s say you develop a simple test function like the following. You may expect that it returns the number passed as a call parameter or the default value of the opt variable, 22, but it doesn’t.

1
2
3
4

Function OptionalOverride(Optional opt As Variant = 22)
  ' Return the unfiltered call parameter.
  OptionalOverride = opt
End Function

A call to an OptionalOverride function like this works when the cell reference points to a value, but fails when it points to an empty cell. It returns a value of 0 with a numeric data type, as verified by the built-in TYPE function.

=OptionalOverride(D1)

This behavior means you always need to check for empty cell references and reassigns them a value inside an if-block. That is if you really want the default value to apply in all cases. The function includes the explicit call parameter assignment in the modified function.

1
2
3
4
5
6
7
8

Function OptionalOverride(Optional opt As Variant = 22)
  ' Explicit assignments required when a cell reference points to an empty cell.
  If IsEmpty(opt) Then
    opt = 21
  End If
  ' Return the unfiltered call parameter unless empty then return 21.
  OptionalOverride = opt
End Function

The modified function returns (a) the call parameter value, (b) 21 when the call parameter points to (references) an empty cell, (c) 22 when you exclude the variable from the call parameter list, or (d) any string value found in the call parameter. The return of a string value is clearly not the desired behavior.

You must modify the if-block by checking whether the call parameter is some data type other than a number before assigning a default value. The following demonstrates the final parameter validating function.

1
2
3
4
5
6
7
8

Function OptionalOverride(Optional opt As Variant = 22)
  ' Explicit assignments required when a cell reference points to an empty cell.
  If IsEmpty(opt) Or Not IsNumeric(opt) Then
    opt = 21
  End If
  ' Return the filtered call parameter.
  OptionalOverride = opt
End Function

Hope this helps a few folks trying to avoid that ugly #VALUE! error returning from your UDFs.

Sometimes the MySQL installation goes great but students forget their root password. It’s almost like the DBA who has the only copy of the root user’s password getting hit by a bus. How do you recover it? It’s not terribly difficult when deployed on the Windows OS (you’ll find a nice article on Linux here). This page takes you to standard documentation for resetting permissions.

There are two ways to do it. The first is quick and easy but risks letting others into the database through the network. The second requires a bit more work but ensures that network is shut while you disable security to reset the root password.

1. The quick and easy way to disable security and reset the root password.

[mysqld]
 
# These let you safely reset the lost root password.
skip-grant-tables
#enable-named-pipe
#skip-networking

net stop mysql51

net start mysql51

SET PASSWORD FOR 'student'@'%' = password('cangetin');

ERROR 1290 (HY000): The MySQL server IS running WITH the --skip-grant-tables option so it cannot execute this statement

-- Connect to the data dictionary.
USE mysql
 
-- Manually update the data dictionary entry.
UPDATE USER
SET    password = password('cangetin')
WHERE  USER = 'root'
AND    host = 'localhost';

2. The secure way to disable security and reset the root password.

[mysqld]
 
# These let you safely reset the lost root password.
skip-grant-tables
enable-named-pipe
skip-networking

net stop mysql51

net start mysql51

mysql -h . -uroot

-- Connect to the data dictionary.
USE mysql
 
-- Manually update the data dictionary entry.
UPDATE USER
SET    password = password('cangetin')
WHERE  USER = 'root'
AND    host = 'localhost';

After you’ve updated the password, remove the previous statement lines from the my.ini file. Then, reboot the server.

Hope this helps a few people.

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.

Having downloaded and tested some basics of the MySQL 5.5 Release Candidate, I started checking out the new features. While testing the new LOAD XML LOCAL INFILE feature, I discovered that there are restrictions governing the configuration of source XML files.

You must restrict the XML file to a list of tag names that correspond to column names within a tag defined as <row>. The tag names are case sensitive to your column names. You can replace the <row> tag name with any name of your choosing provided you append a clause that maps rows to your substitution XML tag name.

You can’t convert a file that has multiple child XML tags with the same name. Any attempt simply loads the last tag name found in the row hierarchy. Therefore, you should ensure that all source files have a unique list of case-sensitive child tags that map to the column definitions of the import table.

Either of the following table definition provides for lowercase column names. The first one uses nothing to delimit the column names.

CREATE TABLE CHARACTER
( ROLE CHAR(30) NOT NULL
, actor CHAR(30) NOT NULL
, part CHAR(20) NOT NULL
, film CHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

If you run SHOW CREATE TABLE character; you would see the more conventional definition below.

CREATE TABLE `character`
( `role` CHAR(30) NOT NULL
, `actor` CHAR(30) NOT NULL
, `part` CHAR(20) NOT NULL
, `film` CHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The following source file format supports the table definition because the XML tags are lowercase names. The source file wouldn’t work if the column names where uppercase or mixed case names.

<row>
  <role>Indiana Jones</role>
  <actor>Harrison Ford</actor>
  <part>protagonist</part>
  <film>Indiana Jones and Raiders of the Lost Ark</film>
  <film>Indiana Jones and the Temple of Doom</film>
  <film>Indiana Jones and the Last Crusade</film>
</row>

When the XML tags and column names match in case, you can load the file with the following syntax:

LOAD XML LOCAL FILE 'C:/Data/XML/character.html'
INTO TABLE `character`;

If you name the <row> tag <character>, you need to add a clause as noted below.

LOAD XML LOCAL FILE 'C:/Data/XML/character.html'
INTO TABLE `character`
ROWS IDENTIFIED BY '<character>';

If you have two film tags in a row tag, MySQL 5.5 doesn’t raise an error or warning. It simply loads the last <film> value. If you create tables with column names that don’t match on a case-sensitive basis, you’ll receive a 1263 warning message. You can see the warning message with the following command.

SHOW warnings;

The warning message only occurs when a column is not null constrained and the column name fails to match an XML tag attribute in the source file. No error or warning is raised when a column isn’t not null constrained under the same scenario. You can test it and then show warnings.

You should see something like this:

+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Role' at ROW 1   |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Actor' at ROW 1  |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Part' at ROW 1   |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Film' at ROW 1   |
+---------+------+---------------------------------------------------------------------------------+

When you define a table with the AUTO_INCREMENT set to a value other than 1, the LOAD XML LOCAL FILE command resets the sequence to 1 before loading. I’d recommend you import into a table without an auto incrementing column and then use the REPLACE INTO to set the surrogate key values of auto incremented columns.

The last day of Oracle Open World 2010. My focus today was on attending OracleDevelop and JavaOne.

The weather picked up today and it was a nice warm Indian Summer day. There were lots of tourists out too. The photo is taken of the building where you board the Powell Street cable car. There was a lot of walking with the way the events are dispersed among the Moscone South, Moscone West, Marriott Hotel, Westin Hotel, Hilton Hotel, W Hotel, and Hotel Nikko. I can’t quite remember how many times I walked back and forth across the 6 blocks between the Moscone centers and Union Square hotels. I can tell you that we went only twice to Mel’s Drive-in.

It was amazing to see how quickly the various conference expedition centers shutdown, packed up, and had their materials shipped out. We had to step over all the plywood that protected tiles and carpets to attend events.

Day 4 also brought a smaller audience for venues. I’m not quite sure if they left earlier or slept in because they were out too late last night attending the Wednesday night event on Treasure Island. The reduced number of attendees was great for those of us who remained. You can see how few attended the .NET/Oracle hands-on lab in the Hilton, which made finding a nice spot easy. You can find the .NET/Oracle hands-on tutorial materials on the Oracle Technical site.

If you opt to use the tutorials, you may benefit from these hints. You should be able to avoid some of the issues that I ran into when working through the open labs. First, you should expand the Microsoft Studio to full screen. Second, you should look for context pop-ups attached to a small arrow at the top right corner of grids, et cetera. Lastly, there are a few small mistakes that you’ll need to work through. Look at the errors as an opportunity to think and experiment and they’re great basic .NET/Oracle tutorials.

Oracle Open World 2010 is done. Time to review the keynotes for those things that I missed while listening to them, and consider the new role of Tuxedo in the life of Oracle’s product stack. It’s also time to download and play with the MySQL 5.5 candidate release; and it’s time to kick off my shoes, put up my feet, and play with the technology again.

Tomorrow I turn my fate over to the airlines, and hope to arrive home on schedule.