MacLochlainns Weblog

The Zend Core Server replaces the deprecated Zend Core for Oracle. I’ve put a quick installation guide here. It’s much nicer, and the licensed server is now the recommended direction from Oracle.

The community edition also installs MySQL, phpMySQLAdmin, and a brand new console. You should try it out.

I finally got around to summarizing how to use PHP to store, retrieve, and display CLOBs and BLOBs from an Oracle database. I think too often we default to BFILEs. I put all the code in zip files with instructions and suggestions for locations. This is really the second entry that goes with configuring Zend Server Community Edition or the DEPRECATED Zend Core for Oracle.

If you’re new to PHP, check out the Underground PHP and Oracle book from Christopher Jones and Alison Holloway. It’s FREE!

The Oracle LOB Processing entry is in this blog page. I know it means another click, but I’ll probably add and modify it over time. If you’ve got time and interest, take a look and let me know what you think and what improvements you’d like to see. Thanks.

I finally got around to creating a blog page that shows you how to install Zend Core for Oracle, verify the installation of PHP, your connection to an Oracle XE database with PHP, and your connection to a MySQL database with PHP.

It’s using the folding concepts I’ve adopted in the blog. One section unfolds to display screen shots for the installation of Zend Core for Oracle. Another section shows you how to confirm your PHP and Apache installation. Two other sections show you how to confirm your connection to an Oracle or MySQL database.

I apologize to those who don’t like to click through to another page, but it was a long page with 20+ images. Hope it helps a few folks, I know it’ll help my students. 😉

My students have to do their group labs in Oracle and then port them individually to MySQL. Most of the conversion resolves around dates because Oracle spoils us with their simplicity in their SQL dialect.

For example, if we wanted to add 9 days to today’s system date (June 27, 2009) we can do this in Oracle:

SQL> SELECT SYSDATE + 9 FROM dual;

It prints

06-JUL-09

If we tried the equivalent in MySQL, we get a null because it treats any day value over 31 as a null. The maximum date in any month is 31, regardless of month. If you add more days than the maximum number minus your current date, you return a null. This is because adding a day never changes the month, and that yields invalid dates. In MySQL, you need to make this calculation with the adddate() or date_add() functions because they’ll increment months and years.

By way of example, if we only added four to today’s date (June 27, 2009) and formatted the output as a date, it works

mysql> SELECT str_to_date(utc_date() + 4,'%Y%m%d');

We’d get this:

+--------------------------------------+
| str_to_date(utc_date() + 4,'%Y%m%d') |
+--------------------------------------+
| 2009-06-31                           |
+--------------------------------------+

I always wanted an extra day in June. 😉

In short, there are three possible non-dates in February that format as dates, and one day in every thirty day month. I’ve a hunch this is a bug (I’ve logged a bug and they’ve verified it and moved it to triage).

The correct way to perform this calculation in MySQL is to use either the adddate() or date_add() functions. They take the same arguments. Here’s a quick example:

mysql> SELECT adddate(utc_date(),INTERVAL 9 DAY);

It yields the correct date:

+------------------------------------+
| adddate(utc_date(),INTERVAL 9 DAY) |
+------------------------------------+
| 2009-07-06                         |
+------------------------------------+

I know this answers a student email but I hope it helps a few others too.

Never change something that works! Not really, but sometimes you feel that way. Especially, when you toast 5 to 10 minutes working through an undocumented behavior in PL/SQL. You’d think after writing it for 19 years, I’d have seen it all but not so.

I was working through a tried and true example from Chapter 14 of the Oracle Database 11g PL/SQL Programming book to prepare for teaching my class tomorrow, when I found this nice quirk. It took a few minutes to figure out what was happening, but here it is so you don’t have to look for it too. You can only use variable names that are attributes of the object type as formal parameters in object type constructors. If you try to vary it, you’ll trigger the following exception:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11     PL/SQL: Item ignored
4/26     PLS-00307: too many declarations OF 'HELLO_THERE' MATCH this CALL
6/5      PL/SQL: Statement ignored
6/13     PLS-00320: the declaration OF the TYPE OF this expression IS incomplete OR malformed

All I did to trigger this exception was change the who variable to make it scope specific, like iv_who for instance variable, pv_who for parameter variable, and lv_who for local variable.

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

CREATE OR REPLACE TYPE hello_there IS OBJECT
( iv_who VARCHAR2(20)
, CONSTRUCTOR FUNCTION hello_there
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION hello_there
  ( pv_who VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_who RETURN VARCHAR2
, MEMBER PROCEDURE set_who (pv_who VARCHAR2)
, MEMBER PROCEDURE to_string )
INSTANTIABLE NOT FINAL;
/

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 TYPE BODY hello_there IS
 
  CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS
    hello HELLO_THERE := hello_there('Generic Object.');
  BEGIN
    SELF := hello;
    RETURN;
  END hello_there;
 
  CONSTRUCTOR FUNCTION hello_there (pv_who VARCHAR2) RETURN SELF AS RESULT IS
 
  BEGIN
    SELF.iv_who := pv_who;
    RETURN;
  END hello_there;
 
  MEMBER FUNCTION get_who RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.iv_who;
  END get_who;
 
  MEMBER PROCEDURE set_who (pv_who VARCHAR2) IS
  BEGIN
    SELF.iv_who := pv_who;
  END set_who;
 
  MEMBER PROCEDURE to_string IS
  BEGIN
    dbms_output.put_line('Hello '||SELF.iv_who);
  END to_string;
 
END;
/

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

CREATE OR REPLACE TYPE hello_there IS OBJECT
( iv_who VARCHAR2(20)
, CONSTRUCTOR FUNCTION hello_there
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION hello_there
  ( iv_who VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_who RETURN VARCHAR2
, MEMBER PROCEDURE set_who (pv_who VARCHAR2)
, MEMBER PROCEDURE to_string )
INSTANTIABLE NOT FINAL;
/

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 TYPE BODY hello_there IS
 
  CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS
    hello HELLO_THERE := hello_there('Generic Object.');
  BEGIN
    SELF := hello;
    RETURN;
  END hello_there;
 
  CONSTRUCTOR FUNCTION hello_there (iv_who VARCHAR2) RETURN SELF AS RESULT IS
 
  BEGIN
    SELF.iv_who := iv_who;
    RETURN;
  END hello_there;
 
  MEMBER FUNCTION get_who RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.iv_who;
  END get_who;
 
  MEMBER PROCEDURE set_who (pv_who VARCHAR2) IS
  BEGIN
    SELF.iv_who := pv_who;
  END set_who;
 
  MEMBER PROCEDURE to_string IS
  BEGIN
    dbms_output.put_line('Hello '||SELF.iv_who);
  END to_string;
 
END;
/

This is certainly interesting. I’ve no clue why such a limitation exists. The name of a parameter list member in the constructor should be independent from the attribute of a user object.

Somebody felt that I’d short changed Oracle by providing only an example for calling a stored procedure in MySQL. So, here’s an equivalent post to the MySQL sample that works in an Oracle database with PHP.

The largest difference between the two approaches is that Oracle is transactional by default while MySQL isn’t. However, the use of savepoints and rollbacks are shown in the procedure, which are the appropriate transaction controls in Oracle.

Here are the detailed steps, even though there are other blog entries with information on related subjects.

1. Sign on as the system user, and create a new user. Users in Oracle have their own schema or work area, and they don’t require a database like MySQL or SQL Server.

SQL> CREATE USER student IDENTIFIED BY student;
SQL> GRANT CONNECT, resource, CREATE any VIEW TO student;

2. Create a create_oracle_procedure.sql file with the following contents:

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

-- Conditionally drop the objects to make this rerunnable.
BEGIN
  FOR i IN (SELECT table_name
            FROM   user_tables
            WHERE  table_name IN ('A','B')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('A_SEQ','B_SEQ')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/  
 
-- Create the tables and sequences.
CREATE TABLE a
( a_id   NUMBER CONSTRAINT a_pk PRIMARY KEY
, a_text VARCHAR2(12));
 
CREATE SEQUENCE a_seq;
 
CREATE TABLE b
( b_id   NUMBER CONSTRAINT b_pk PRIMARY KEY
, a_id   NUMBER 
, b_text CHAR(12)
, CONSTRAINT fk_a FOREIGN KEY(a_id) REFERENCES a (a_id));
 
CREATE SEQUENCE b_seq;
 
-- Create a stored procedure with IN-only (default) mode parameters.
CREATE OR REPLACE PROCEDURE double_insert
( input_a VARCHAR2, input_b VARCHAR2) IS
BEGIN
  SAVEPOINT starting_point;
  INSERT INTO a VALUES (a_seq.NEXTVAL, input_a);
  INSERT INTO b VALUES (b_seq.NEXTVAL, a_seq.CURRVAL, input_b);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO starting_point;
    RETURN;
END;
/
 
-- Define a couple local session variables.
VARIABLE text1 VARCHAR2(12)
VARIABLE text2 VARCHAR2(12)
 
-- Assign values to session variables.
BEGIN
  :text1 := 'This is one.';
  :text2 := 'This is two.';
END;
/
 
-- Call the local procedure.
EXECUTE double_insert(:text1,:text2);
 
-- Select the data set. 
SELECT * FROM a;
SELECT * FROM b;

3. Quit the session as the system user. You can simply reconnect to the new schema like this:

SQL> CONNECT student/student@orcl

4. Run the file from the relative directory where you started the sqlplus executable.

SQL> @create_oracle_procedure.sql

You see the following query results at the end of the script:

      A_ID A_TEXT
---------- ------------
         1 This IS one.
 
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.

5. Write the following into a call_oracle_procedure.php:

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "orcl");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Declare two variables for the test procedure call.
    $val1 = "Hello Hal!";
    $val2 = "Hello Dave!";
 
    // Set the call statement, like a SQL statement.
    $sql = "BEGIN double_insert(:a,:b); END;";
 
    // Prepare the statement and bind the two strings.
    $stmt = oci_parse($c,$sql);
 
    // Bind local variables into PHP statement.
    oci_bind_by_name($stmt, ":a", $val1);
    oci_bind_by_name($stmt, ":b", $val2);
 
    // Execute it and print success or failure message.
    if (oci_execute($stmt)) {
      print "Congrats! You've executed a Oracle stored procedure from PHP!";
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
    // Free resources.
    oci_free_statement($stmt);
    oci_close($c);
  }
?>

6. Run the call_oracle_procedure.php from the command line, like this:

php call_oracle_procedure.php

7. Now you can requery the database to see the inserts made by the PHP program.

SQL> SELECT * FROM a;
 
      A_ID A_TEXT
---------- ------------
         1 This IS one.
         2 Hello Hal!
 
SQL> SELECT * FROM b;
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.
         2          2 Hello Dave!

There’s now a sample file for both approaches. As always, I hope this helps some folks.

An interesting thing happened today, as I was explaining how you call functions with embedded DML statements. The students were stunned at seeing an ORA-06576 error for a function that they knew existed. It’s one of those imperfect error messages …

Basically, they wrote a wrapper function to a parallel enabled function, and then they tried to call it into a session level bind variable, like this:

SQL> VARIABLE verified NUMBER
SQL> CALL update_contact INTO :verified;

It failed with the following message:

CALL update_contact INTO :verified
     *
ERROR at line 1:
ORA-06576: NOT a valid FUNCTION OR PROCEDURE name

They were stunned but I was mum. I suggested that they test the call in an anonymous block program. Here’s the PL/SQL call example:

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> BEGIN
  2    IF update_contact = 1 THEN
  3      DBMS_OUTPUT.put_line('Success!');
  4    ELSE
  5      DBMS_OUTPUT.put_line('Failure!');
  6    END IF;
  7  END;
  8  /

It returns

Success!

I removed the puzzled look by explaining that while you don’t need to provide the open and close parentheses inside PL/SQL, you do generally require them in the context of a CALL statement. They’re not required for stored functions in SQL statements, but they’re required for stored instantiable object types.

Here’s the correct way call the program:

SQL> CALL update_contact() INTO :verified;
SQL> SELECT DECODE(:verified,1,'Success!','Failure!') AS answer FROM dual;

It prints this to console:

ANSWER
--------
Success!

The natural question is why don’t you just run the function as part of a query. It’s a great question because it lets me demonstrate another principle. The principle that you can’t can’t perform a DML in a query, which is abstract until you see it up front and personal.

SQL> SELECT update_contact() FROM dual;

this query raises the following exception:

SELECT update_contact() FROM dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "PLSQL.UPDATE_CONTACT", line 4

Setup Code Supplement

You can find the base code for this example here. It comes from the Oracle Database 11g PL/SQL Programming book. After you run the create_store.sql script, you’ll need to run the following:

-- Add a null allowed column for derived data.
ALTER TABLE contact ADD (full_name VARCHAR2(44));
 
-- Define a function concatenate strings.
CREATE OR REPLACE FUNCTION MERGE
( last_name     VARCHAR2
, first_name    VARCHAR2
, middle_initial VARCHAR2 )
RETURN VARCHAR2 PARALLEL_ENABLE IS
BEGIN
  RETURN last_name ||', '||first_name||' '||middle_initial;
END;
/
 
-- Define a wrapper function around the merge function.
CREATE OR REPLACE FUNCTION update_contact RETURN NUMBER IS
  ret_val NUMBER := 0; -- The default return value to false or zero.
BEGIN
  UPDATE contact c1
  SET    c1.full_name = (SELECT MERGE(c2.last_name
                                     ,c2.first_name
                                     ,c2.middle_initial)
                         FROM   contact c2
                         WHERE  c1.rowid = c2.rowid);
  IF SQL%ROWCOUNT > 0 THEN
    ret_val := 1; -- This is only reached when 1 or more rows are updated.
  END IF;
  RETURN ret_val;
END update_contact;
/

Sometimes it gets tiresome when people take shots at Oracle, MySQL, SQL Server 2008, or PostgreSQL. When I went to the MySQL for Database Administrators, the instructor mentioned a number of times how many Oracle people he’d get in his class. It was said almost as if all the Oracle customers were migrating to MySQL, which I don’t think is the case. If I’m wrong just post a comment.

After writing SQL since 1985 (IBM SQL/DS), I’ve come to appreciate a number of the features in the Oracle database. This probably makes sense because I worked there for over eight years, and probably would still be there if I hadn’t left to teach at University. I’m constantly amazed as I explore and look for points of commonality across SQL dialects and PL/SQL dialects. At least, I think it’s fair to call T-SQL stored programs and MySQL stored programs PL/SQL dialects.

This blog post is about the MERGE statement and it’s close MySQL cousin, the ON DUPLICATE KEY UPDATE statement (that only works with single row INSERT statements). I’ve recently added a post demonstrating the REPLACE INTO command syntax. You may also be interested in a newer blog post about importing external data inside a MySQL procedure with cursor loops. Upfront, I have to vote for Oracle’s MERGE statement because it’s more complete as an implementation. By the way, T-SQL supports the same MERGE syntax. They’re so exact it wasn’t worth taking the space to show the syntax for the SQL Server 2008 Express product.

Oracle’s Merge Statement

The downside of Oracle’s MERGE statement is that it takes a LOT of typing. The upside from my perspective is that it enforces that you must use the primary key column. The same is not true with the MySQL syntax.

Here’s a quick example that you can cut and paste into your environment for Oracle Database 11g or remove the Oracle specific FROM dual and it’ll run in SQL Server. You can also see how to leverage joins and imports with the MERGE statement in this more recent blog post.

-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT NULL
            FROM   user_tables
            WHERE  TABLE_NAME = 'SYSTEM_USER') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE system_user CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT NULL
            FROM   user_sequences
            WHERE  sequence_name = 'SYSTEM_USER_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE system_user_s1';
  END LOOP;
END;
/
 
-- Create the table.
CREATE TABLE system_user
( system_user_id        NUMBER        CONSTRAINT pk_su   PRIMARY KEY
, system_user_name      VARCHAR2(20)  CONSTRAINT nn_su_1 NOT NULL
, system_user_group_id  NUMBER        CONSTRAINT nn_su_2 NOT NULL
, system_user_type      NUMBER        CONSTRAINT nn_su_3 NOT NULL
, first_name            VARCHAR2(20)
, middle_name           VARCHAR2(10)
, last_name             VARCHAR2(20)
, created_by            NUMBER        CONSTRAINT nn_su_4 NOT NULL
, creation_date         DATE          CONSTRAINT nn_su_5 NOT NULL
, last_updated_by       NUMBER        CONSTRAINT nn_su_6 NOT NULL
, last_update_date      DATE          CONSTRAINT nn_su_7 NOT NULL);
 
-- Create the sequence with a default start value of 1.
CREATE SEQUENCE system_user_s1;
 
-- Insert new row.
INSERT INTO system_user
VALUES
( system_user_s1.nextval
, 'SYSADMIN'
, 1
, 1
, NULL
, NULL
, NULL
, 1
, SYSDATE - 1
, 1
, SYSDATE - 1);
 
-- Insert new or merge into existing row.
MERGE INTO system_user target
USING (SELECT   1 AS system_user_id
       ,       'SYSADMIN' AS system_user_name
       ,        1 AS system_user_group_id
       ,        1 AS system_user_type
       ,       'Samuel' AS first_name
       ,        'the' AS middle_name
       ,       'Lamanite' AS last_name
       ,        1 AS created_by
       ,        SYSDATE AS creation_date
       ,        1 AS last_updated_by
       ,        SYSDATE AS last_update_date
       FROM     dual) SOURCE
ON (target.system_user_id = SOURCE.system_user_id)
WHEN MATCHED THEN
  UPDATE SET first_name = 'Samuel'
  ,          middle_name = 'the'
  ,          last_name = 'Lamanite'
  ,          last_updated_by = 1
  ,          last_update_date = SYSDATE
WHEN NOT MATCHED THEN
  INSERT
  ( target.system_user_id
  , target.system_user_name
  , target.system_user_group_id
  , target.system_user_type
  , target.first_name
  , target.middle_name
  , target.last_name
  , target.created_by
  , target.creation_date
  , target.last_updated_by
  , target.last_update_date )  
  VALUES
  ( SOURCE.system_user_id
  , SOURCE.system_user_name
  , SOURCE.system_user_group_id
  , SOURCE.system_user_type
  , SOURCE.first_name
  , SOURCE.middle_name
  , SOURCE.last_name
  , SOURCE.created_by
  , SOURCE.creation_date
  , SOURCE.last_updated_by
  , SOURCE.last_update_date );

MySQL On Duplicate Key Update Statement

The downside of MySQL’s ON DUPLICATE KEY UPDATE statement is that it takes it lets you use an override signature on the INSERT that then creates a new row when it shouldn’t. I logged a bug when I discovered that behavior earlier today but they don’t quite agree that it’s a bug. The upside is that the typing is MUCH shorter provided you remember to use the auto increment key column in the SELECT clause.

Here’s a quick example of what not to do! The next one shows you what you should do. You can see how to tie together MySQL subroutines with an INSERT ... ON DUPLICATE KEY statement in this more recent blog entry.

-- Conditionally drop the table.
DROP TABLE IF EXISTS system_user;
 
-- Create the table.
CREATE TABLE system_user
( system_user_id              INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)      NOT NULL
, system_user_group_id        INT           NOT NULL
, system_user_type            INT           NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(10)
, last_name                   CHAR(20)
, created_by                  INT           NOT NULL
, creation_date               DATE          NOT NULL
, last_updated_by             INT           NOT NULL
, last_update_date            DATE          NOT NULL);
 
-- Insert new row.
INSERT INTO system_user
( system_user_name
, system_user_group_id
, system_user_type
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
('SYSADMIN'
, 1
, 1
, 1
, DATE_SUB(NOW(),INTERVAL 1 DAY)
, 1
, DATE_SUB(NOW(),INTERVAL 1 DAY));
 
-- Insert new or merge into existing row.
INSERT INTO system_user
( system_user_name
, system_user_group_id
, system_user_type
, first_name
, middle_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
('SYSADMIN'
, 1
, 1
,'Samuel'
,'the'
,'Lamanite'
, 1
, NOW()
, 1
, NOW())
ON DUPLICATE KEY 
UPDATE first_name = 'Samuel'
,      middle_name = 'the'
,      last_name = 'Lamanite'
,      last_updated_by = 1
,      last_update_date = UTC_DATE();

This script ends up inserting two rows when only one should be present. Why did that happen? Great question! You can fix this by adding a unique key defined for the columns that make up the natural key for the SYSTEM_USER table. As noted by a comment below, the unique key must only include columns that are NOT NULL constrained. Here’s the results without such a unique key:

mysql> SELECT * FROM system_user\G
*************************** 1. ROW ***************************
      system_user_id: 1
    system_user_name: SYSADMIN
system_user_group_id: 1
    system_user_type: 1
          first_name: NULL
         middle_name: NULL
           last_name: NULL
          created_by: 1
       creation_date: 2009-05-24
     last_updated_by: 1
    last_update_date: 2009-05-24
*************************** 2. ROW ***************************
      system_user_id: 2
    system_user_name: SYSADMIN
system_user_group_id: 1
    system_user_type: 1
          first_name: Samuel
         middle_name: NULL
           last_name: Lamanite
          created_by: 1
       creation_date: 2009-05-25
     last_updated_by: 1
    last_update_date: 2009-05-25
2 ROWS IN SET (0.02 sec)

The correct way to do this in MySQL is shown in the next example.

-- Conditionally drop table.
DROP TABLE IF EXISTS system_user;
 
-- Create table.
CREATE TABLE system_user
( system_user_id              INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)      NOT NULL
, system_user_group_id        INT           NOT NULL
, system_user_type            INT           NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(10)
, last_name                   CHAR(20)
, created_by                  INT           NOT NULL
, creation_date               DATE          NOT NULL
, last_updated_by             INT           NOT NULL
, last_update_date            DATE          NOT NULL);
 
-- Insert new row.
INSERT INTO system_user
( system_user_name
, system_user_group_id
, system_user_type
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
('SYSADMIN'
, 1
, 1
, 1
, DATE_SUB(UTC_DATE(),INTERVAL 1 DAY)
, 1
, DATE_SUB(UTC_DATE(),INTERVAL 1 DAY));
 
-- Insert new or merge into existing row.
INSERT INTO system_user
VALUES
( 1
,'SYSADMIN'
, 1
, 1
,'Samuel'
,'the'
,'Lamanite'
, 1
, NOW()
, 1
, NOW())
ON DUPLICATE KEY
UPDATE first_name = 'Samuel'
,      middle_name = 'the'
,      last_name = 'Lamanite'
,      last_updated_by = 1
,      last_update_date = NOW();

This ensures that the auto increment column values are matched. It returns what you’d expect, a single row inserted into or updated in the table.

mysql> SELECT * FROM system_user\G
*************************** 1. ROW ***************************
      system_user_id: 1
    system_user_name: SYSADMIN
system_user_group_id: 1
    system_user_type: 1
          first_name: Samuel
         middle_name: NULL
           last_name: Lamanite
          created_by: 1
       creation_date: 2009-05-24
     last_updated_by: 1
    last_update_date: 2009-05-25
1 ROW IN SET (0.00 sec)

I hope this saves somebody from an insertion anomaly.

I’ve begun putting together an online database tutorial and expanded this entry and added horizontal scrolling to it. You can find the improved version of the blog post as blog page here.

Surrogate keys are interesting structures in databases. They’re essential if you want to make sure you optimize your design. They’re also very useful when you want to capture the automatic numbering value for a prior INSERT statement and reuse the automatic numbering value as the foreign key value in a subsequent statement. It was interesting to see how they’re implemented differently across Oracle, MySQL, and SQL Server while providing the same utility.

Below is a synopsis of how you implement these in Oracle, MySQL, and SQL Server.

Oracle

The first thing to qualify is that Oracle is generally always in a transactional mode. That means you don’t need to do anything special to set this example up.

Oracle doesn’t support automated numbering in tables prior to Oracle 12c. Oracle 12c introduces identity columns, and the mechanics change. However, you can use sequences to mimic automated numbering prior to Oracle 12c and without identity columns in Oracle 12c. A sequence is a structure in the database that holds a current value, increments by a fixed value – typically 1. Sequences are available in SQL and PL/SQL scopes through two pseudo columns. The pseudo columns are .nextval and .currval (note the two r’s because it’s not a stray dog).

The sequence_name.nextval call in any session places the next number from the sequence into your Personal Global Area (PGA), which is a memory context. After you’ve called the sequence into memory, you can access it again by using sequence_name.currval. The sequence only changes when you call it again with the .nextval pseudo column.

-- Conditionally drop data sturctures - tables and sequences.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
			WHERE  TABLE_NAME IN ('ONE','TWO')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINT';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
			WHERE  sequence_name IN ('ONE_S1','TWO_S1')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create base table and sequence.
CREATE TABLE one
( one_id   INT         NOT NULL CONSTRAINT pk_one PRIMARY KEY
, one_text VARCHAR(10) NOT NULL );
 
CREATE SEQUENCE one_s1;
 
-- Create dependent table and sequence.
CREATE TABLE two
( two_id   INT         NOT NULL CONSTRAINT pk_two PRIMARY KEY
, one_id   INT         NOT NULL
, two_text VARCHAR(10) NOT NULL );
 
CREATE SEQUENCE two_s1;
 
-- Insert rows into the tables with sequence values.
INSERT INTO one VALUES (one_s1.nextval,'One!');
INSERT INTO one VALUES (one_s1.nextval,'Two!');
INSERT INTO two VALUES (two_s1.nextval, one_s1.currval,'Other Two!');
 
-- Display the values inserted with sequences.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;

If you mimic automatic numbering with database triggers, you may not have access to the .currval value for the second INSERT statement. This occurs when you provide a NULL value expecting the trigger to manage .NEXTVAL call for you.

Transactions require that you keep the primary key value for the first table in a locally scoped variable for reuse. Then, you can pass it to the next INSERT statement. You do that with the .CURRVAL value.

You can make a potentially erroneous assumption that you’re the only user updating the table. Operating under that assumption, you can query the highest sequence number from the table before an insert, add one to it, and then attempt the INSERT statement. In a multi-user system, it’s possible that somebody beats you to the finish line with their INSERT statement. Your insert would then have a duplicate surrogate key value for the one_id column, and fail on an ORA-00001 error for a uniqueness violation on a primary key column.

A database trigger can help you avoid a race condition. The trigger would ensure sequence values are unique but it may also introduce problems. A common Oracle trigger with a pseudo automatic numbering paradigm is represented by the following trigger (found in APEX generated code).

CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
BEGIN
  :NEW.one_id := one_s1.nextval;
END;
/

Caution is required on this type of automated sequence trigger. There are two problems with this type of trigger.

One scenario is where you include a call to sequence_name.NEXTVAL in your INSERT statement. It then increments the sequence, and attempts to insert the value whereupon the trigger fires and repeats the behavior. Effectively, this type of logic creates a sequence that increments by one when you submit a null value in the values clause and by two when you submit a sequence_name.NEXTVAL value.

Another scenario occurs when you attempt a bulk INSERT operation on the table. The sequence call and substitution occurs on each row of the sequence.

You face another problem when you rewrite the trigger to only fire when a surrogate primary key isn’t provided, like this:

CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
WHEN (NEW.one_id IS NULL)  -- Asynchronous with bulk insert operations when a value is provided by the bulk operation to the surrogate key column.
BEGIN
  :NEW.one_id := one_s1.nextval;
END;
/

This trigger design causes a problem only with bulk INSERT statements. Effectively, the sequence remains unaltered when you provide surrogate key values as part of inserting an array of values. The next non-bulk INSERT statement would then grab the .NEXTVAL value, attempt to use it, and raise a unique constraint violation because the bulk operation probably already used the value from the sequence.

The fix to bulk operations requires that you lock the table, disable a trigger like this, and get the .NEXTVAL value. Then, you assign the .NEXTVAL value to two local variables. One of these remains unchanged while the other increments as you populate the array for the bulk insert operation. After assigning the result from the .NEXTVAL, you drop the sequence and find the highest key value for the bulk insertion operation, add one to the highest key value, and store it in another locally stored variable. You perform the bulk insert operation and then recreate the sequence with a value one greater than the highest value in the table, which should already be in a locally scored variable. Don’t forget that you’d locked the table, so unlock it now.

You should note that database triggers run in a subshell with access only to the immediate shell that fired them. Therefore, you can’t set a bind variable in a SQL*Plus session and subsequently reference it inside the trigger body because it doesn’t have access to the variable.

MySQL

MySQL supports automatic numbering but not a default transactional mode like Oracle. You need to disable auto commit and start a transaction. You also need to assign the last automatic numbering value to a variable before using it in a subsequent INSERT statement. You must also provide an overriding list of mandatory columns when you opt to exclude the automated numbering column value. The one thing that we should all appreciate about MySQL is their desire to stay close to and comply with ANSI standards.

-- Conditionally drop the tables.
DROP TABLE IF EXISTS one;
DROP TABLE IF EXISTS two;
 
-- Create the tables with a surrogate key that automatically increments.
CREATE TABLE one ( one_id   INT PRIMARY KEY AUTO_INCREMENT
                 , one_text VARCHAR(20));
 
CREATE TABLE two ( two_id INT PRIMARY KEY AUTO_INCREMENT
                 , one_id INT
                 , two_text VARCHAR(20));
 
-- Start transaction cycle.
START TRANSACTION;
 
-- Insert first row, transfer auto increment to memory.
INSERT INTO one (one_text) VALUES ('One');
 
-- Assign last auto increment to local scope variable, the = works too.
SET @one_fk := last_insert_id();
 
-- Insert second row with auto increment and local scope variable.
INSERT INTO b (one_id, two_text) VALUES (@one_fk,'Two');
 
COMMIT;
 
-- Display the values inserted with auto incremented values.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;

SQL Server

SQL Server supports automatic numbering but they call it the identity value. There are two ways to use it but the one I’m showing is for SQL Server 2005 or newer. You can replace the older @@identity for the SCOPE_IDENTITY() function call but Microsoft has already removed first level support from SQL Server 2000. While they’ve not said @@identity is deprecated, it sure appears that’s possible in a future release.

USE student;
 
BEGIN TRAN;
 
-- Conditionally drop tables when they exist.
IF OBJECT_ID('dbo.one','U') IS NOT NULL DROP TABLE dbo.one;
IF OBJECT_ID('dbo.two','U') IS NOT NULL DROP TABLE dbo.two;
 
-- Create auto incrementing tables.
CREATE TABLE one
( one_id   INT         NOT NULL IDENTITY(1,1) CONSTRAINT pk_one PRIMARY KEY
, one_text VARCHAR(10) NOT NULL );
 
CREATE TABLE two
( two_id   INT         NOT NULL IDENTITY(1,1) CONSTRAINT pk_two PRIMARY KEY
, one_id   INT         NOT NULL
, two_text VARCHAR(10) NOT NULL );
 
-- Insert the values, and magically no override signature required.
INSERT INTO one VALUES ('One!');
INSERT INTO one VALUES ('Two!');
INSERT INTO two VALUES (SCOPE_IDENTITY(),'Other Two!');
 
-- Query the results.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;
 
COMMIT TRAN;

You should note that T-SQL doesn’t require an override signature when you use an automatic numbering column. This is different, isn’t it?

While the prior example works with two tables, it doesn’t scale to a series of tables. You should consider the following assignment pattern when you’ll have multiple last identity values in a single transaction scope.

DECLARE @one_pk AS INT;
SET @one_pk = SCOPE_IDENTITY();

As mentioned, this style is important when you’ve got a series of primary and foreign keys to map in the scope of a single transaction. Also, I’d suggest that you put all the declarations at the beginning of the transaction’s scope.

As always, I hope this helps some folks.

I saw an interesting post on INTERVAL YEAR TO MONTH while checking things out today. It struck me as odd, so I thought I’d share a similar sample along with my opinion about how it should be done in a PL/SQL block.

The example is a modification of what I found in a forum. You should see immediately that it’s a bit complex and doesn’t really describe what you should do with any months. Naturally, the example only dealt with years.

DECLARE
  lv_interval  INTERVAL YEAR TO MONTH;
  lv_end_day   DATE := '30-APR-2009';
  lv_start_day DATE := '30-APR-1975';
BEGIN
  lv_interval := TO_CHAR(FLOOR((lv_end_day - lv_start_day)/365.25))||'-00';
  DBMS_OUTPUT.put_line(lv_interval);
END;
/

I suggest that the better way is the following because it allows for months, which are a bit irregular when it comes to divisors.

DECLARE
  lv_interval  INTERVAL YEAR TO MONTH;
  lv_end_day   DATE := '30-APR-2009';
  lv_start_day DATE := '30-JAN-1976';
BEGIN
  lv_interval := TO_CHAR(EXTRACT(YEAR FROM lv_end_day) -
                            EXTRACT(YEAR FROM lv_start_day)) ||'-'||
                   TO_CHAR(EXTRACT(MONTH FROM lv_end_day) -
                             EXTRACT(MONTH FROM lv_start_day));
  DBMS_OUTPUT.put_line(lv_interval);
END;
/

Let me know if you’ve another alternative that you prefer.