MacLochlainns Weblog

Somebody asked how to validate foreign key constraints in an Oracle database. The following query finds constraints, and displays the table and column that holds constraint with table and column name pointed to by the constraint. Since a foreign key to primary key relationship defines the list of values for a foreign key column, the values must be found in the primary key column.

Both user_constraints and user_cons_columns are catalog views that limit you to your own schema. The user_constraints view lets you find information about constraints, while the user_cons_columns view lets you see column level detail about the constraints.

The query lets you resolve where to look for those keys without manually inspecting table creation scripts.

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A38 HEADING "References:| Table.Column"
 
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.position = ucc2.position -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
ORDER BY ucc1.table_name
,        uc.constraint_name;

You generate the following output when you run this query. The results shows you: (a) constraint names with their corresponding table and column names; and (b) table and column names that holds the primary key which is referenced by foreign keys.

Constraint Name:                       References
Table.Column                           Table.Column
-------------------------------------- --------------------------------
FK_ADDRESS_1                           REFERENCES
(ADDRESS.CONTACT_ID)                   (CONTACT.CONTACT_ID)
 
FK_ADDRESS_2                           REFERENCES
(ADDRESS.ADDRESS_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)
 
FK_ADDRESS_3                           REFERENCES
(ADDRESS.CREATED_BY)                   (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_ADDRESS_4                           REFERENCES
(ADDRESS.LAST_UPDATED_BY)              (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_COMMON_LOOKUP_1                     REFERENCES
(COMMON_LOOKUP.CREATED_BY)             (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_COMMON_LOOKUP_2                     REFERENCES
(COMMON_LOOKUP.LAST_UPDATED_BY)        (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_CONTACT_1                           REFERENCES
(CONTACT.MEMBER_ID)                    (MEMBER.MEMBER_ID)
 
FK_CONTACT_2                           REFERENCES
(CONTACT.CONTACT_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)
 
FK_CONTACT_3                           REFERENCES
(CONTACT.CREATED_BY)                   (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_CONTACT_4                           REFERENCES
(CONTACT.LAST_UPDATED_BY)              (SYSTEM_USER.SYSTEM_USER_ID)

You can then query the table and column referenced by the foreign key to determine the valid list of primary keys in the table. Extending the basic query design, you can narrow it to a specific constraint. This becomes very useful when you try to insert a row into an address table with an foreign key value that isn’t found in the list of valid primary keys.

A sample INSERT statement would be:

INSERT INTO address
VALUES
( 1101
, 1008
, 2001                                  -- This foreign key isn't a valid primary key.
,'Nowhereville'
,'Beatledom'
,'11111-1111'
, 3
, SYSDATE
, 3
, SYSDATE );

In my test instance, you would get a constraint violation error like the one below. You can download the setup scripts from McGraw-Hill’s web site for Oracle Database 11g PL/SQL Programming book that I wrote.

INSERT INTO address
*
ERROR at line 1:
ORA-02291: integrity CONSTRAINT (STUDENT.FK_ADDRESS_2) violated - parent KEY NOT found

Then, you can modify the earlier query to find the offending primary key column. Its offense is that there is no equivalent value to what you tried to input into another table. Here’s how you find the primary key column table:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A38 HEADING "References:| Table.Column"
 
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      uc.constraint_type = 'R'
AND      uc.constraint_name = UPPER('&input_constraint_name');

It returns the following in my test instance:

CONSTRAINT Name:                       REFERENCES:
 TABLE.Column                           TABLE.Column
-------------------------------------- --------------------------------------
FK_ADDRESS_2                           REFERENCES
(ADDRESS.ADDRESS_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)

You can now verify whether the value, 2001, that you tried to insert into a foreign key column exists. The query would be like follows:

SELECT   common_lookup_id
FROM     common_lookup
WHERE    common_lookup_id = 2001;

The query will say that no rows were found. You can remove the WHERE clause to find the list of valid primary key values.

I played around with a friend’s Kindle and really had to wonder why would people buy one of them. Since my books sell an electronic edition on Kindle, I hoped for a better solution.

Great news today, a better physical technology has arrived. You can now download a Kindle application for your iPhone! As a big iPhone fan, this is awesome. Naturally, I was curious how my last book looked.

I downloaded the sample chapter and it looked great on the iPhone. The images are well rendered and clear on the screen. I’m probably going to have to buy an e-copy (ouch) because they don’t provide authors with electronic copies.

Thanks a bunch to the team who ported it to the iPhone. Great job!

Somebody tweeted tonight and asked how to left pad a string in Microsoft Excel 2007. I thought it was an interesting question, and replied. Then, I thought oops … it should be on the blog.

You can use a combination of the REPLACE and REPT (believe it or not that’s the repeat function). The first step to left pad requires you to master the REPLACE function. It takes four actual parameters. The first is the old or base string, which is typically a cell reference. The second is the starting position, the third is the length of the base string to replace, and fourth the new string. The following example assumes you want to replace the whole string with a white space before the original string.

=REPLACE(A1,1,LEN(A1)," "&A1)

If you don’t like using the ampersand to concatenate, you can replace it with the CONCATENATE function, like this:

=REPLACE(A1,1,LEN(A1),CONCATENATE(" ",A1))

While more compact, the ampersand does the trick and ultimately saves space for complex mega formulas. The next step requires you learn how to use the REPT function.

The REPT function takes two parameters, which are the string you want to repeat and the number of times you want to repeat it. Assuming that you expect all strings to be smaller than twenty, you can use the REPT function this way to create a dynamic padding of physical white space.

=REPT(" ",20-LEN(A1))

When you put the concepts together, you’d get the following command to dynamically replace a string in cell A1 with a 20 character cell that is right aligned. The new value would be in the cell where you put this formula.

=REPLACE(A1,1,LEN(A1),REPT(" ",20-LEN(A1))&A1)

This lets you left pad a string, which I suppose is great when you want to output a flat position specific file. I’d prefer a CSV and a more robust programming environment but I hope it helps those who want to do it.

As Andy pointed out, you can simplify it with this:

=REPT(" ",20-LEN(A1))&A1

As an update to the original blog post, here’s a Visual Basic for Applications (VBA) User Defined Function (UDF) that would provide you with an add-in function to perform this task. It’s designed to LPAD numbers or text, and always return text. You can find how to develop and deploy Excel VBA UDFs in this more recent blog post of mine.

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

' The function takes any input as a cell and manages numbers different than strings.
Function LPAD(cell As Variant _
             , Optional offset As Variant = 0 _
             , Optional padWith As String = "0")
 
  ' Return variable.
  Dim returnString As String
  Dim whiteSpace As String * 1
 
  ' Reports a meaningful error when a non-numeric offset parameter is provided as the offset call parameter.
  If Not IsNumeric(offset) Then
 
    ' This means the function is called incorrectly but suppresses a #VALUE! error.
    MsgBox ("Offset should not be a non-numeric value of [" + offset + "]!")
 
  ' Dynamically assign the offset for the optional offset variable.
  ElseIf offset = 0 Then
    offset = Len(cell)
  Else
    ' This allows you to see the offset value when it is provided as a number.
    ' MsgBox ("offset is [" + CStr(offset) + "]")
  End If
 
  ' Assign default value.
  whiteSpace = " "
 
  ' Ensure size is at least the length of the cell and padding value.
   If IsNumeric(offset) And Len(cell) > offset Then
    offset = Len(cell) + Len(padWith)
   End If
 
  ' Assign default padding value when cell is a number.
  If IsNumeric(cell) And IsNumeric(padWith) Then
    padNumberWith = CInt(padWith)
  Else
    padNumberWith = 0
  End If
 
  ' Convert to string when numeric, use padWith text value when not null and whitespace if null.
  If IsNumeric(cell) Then
    returnString = Application.Rept("0", offset - Len(Application.Text(cell, padNumberWith))) + CStr(cell)
  ElseIf padWith <> "0" Then
    returnString = Application.Rept(padWith, offset - Len(cell)) + cell
  ElseIf padWith = "0" Then
    returnString = Application.Rept(whiteSpace, offset - Len(cell)) + cell
  Else
    returnString = Application.Rept(" ", offset - Len(cell)) + cell
  End If
 
  ' Return formatted string.
  LPAD = returnString
 
End Function

The formula for a number is different than it would be natively in Excel. That’s because some behaviors are implicitly provided. The equivalent function in Excel is:

=REPT("0",20-LEN(TEXT(A6,"0")))&A6

Microsoft Excel performs the LEN() function implicitly if you leave it out. For example, this works the same as the more complete formula above.

=REPT("0",20-(TEXT(A6,"0"))&A6

If you forget the LEN() function call in the VBA module, it raises an Excel Error 20150. This error is suppressed by a #VALUE! error. That error is returned because an error message can’t be concatenated with a string. Once you identify where the error occurs you can enclose it in a CStr() function call. The CStr() function explicitly casts the Err.number value to a string, which is then returned to the worksheet in the cell where you’ve called the function.

All that being said, it’s not nearly that complicated. You can provide the LPAD UDF in a simpler function that doesn’t use the built-in workbook functions. All you need to know is how to us the String() function, like this:

Function LPAD(padding AS Variant, character AS String, cell As String)
 
  ' Define and assign a default value, which must be a string data type.
  Dim character_default As String * 1
  character_default = " " 
  Dim number_default As String * 1
  number_default = "0" 
 
  ' Check if you've got adequate values to proceed.
  If IsNumeric(padding) And IsNumeric(cell) Then
    If IsNumeric(character) Then
      LPAD = String(padding, character) + cell
    Else
      LPAD = String(padding, number_default) + cell
    End If
  Else
    If IsNumeric(character) Then
      LPAD = String(padding, character) + cell
    Else
      LPAD = String(padding, character_default) + cell
    End If
  End If
 
End Function

You would call this UDF with any of the following function calls. You skip the last parameter because its an optional parameter.

Somebody suggested that I post a simple quick view of how a basic query works. The following illustrates how the FROM is read first. Aliases assigned in the FROM clause, like the i, replace the full name of the table or view. Column references don’t require prepending with the table or view alias but doing so adds clarity in the query. You do need prepend table aliases or names when two or more columns returned by the query have the same names. This happens when you’re joining two or more tables because the SQL parser finds that they’re ambiguously defined otherwise.

While the column aliases use the optional AS keyword, table aliases must directly follow the table name. Table name really means a table, view, or inline view name. They can also mean a subquery factoring clause, which is the fancy name for a WITH statement – blogged on it here.

Selection comes in two phases, identifying the sources or tables in the FROM clause, and then filtering the sources based on comparisons in the WHERE clause. Join statements are also filters that match rows from different tables based on value or range comparisons. You find join statements in the FROM clause as part of a ON or USING subclause when queries use key words like JOIN et cetera. You find join statements in the WHERE clause when the queries list tables as comma separated elements in a FROM clause.

Projection is the narrowing of rows into columns qualified by the select list. A select list is the comma separated columns returned by a query in the SELECT clause.

While database management systems have their own particulars about sequencing and optimization, more or less they find the data sources, read the rows or indexes to rows, and then narrow the columns returned to those qualified in a select list. That’s about it unless a query involves aggregation or sorting operations.

Aggregation typically happens after selection but before projection. While columns in the SELECT clause often set the aggregation grouping, you may use columns other than those in the select list. You qualify the grouping columns in the optional GROUP BY clause.

Sorting by a column is done through the ORDER BY clause. The sorting of data follows the selection process, unless there is an aggregation process. Sorting follows aggregation when it is present in a query. Aggregated data sets are limited to ordering by columns in the GROUP BY clause.

Parsing strings isn’t the easiest thing to do in Excel 2007 or Excel 2008 but it is an important thing to know how to do. You’ll learn how to parse a set names into first, middle and last names. You can find the data set for these examples at the bottom of the blog post.

=LEFT(A1,7)

=FIND(" ",A1,1)

=LEFT(A1,FIND(" ",A1,1),1)

=IFERROR(FIND(" ",A1,1),A1)

=IFERROR(LEFT(A1,IFERROR(FIND(" ",A1),A1)-1),A1)

=RIGHT(A1,5)

=FIND(" ",A1,1)+1

=FIND(" ",A1,FIND(" ",A1,1)+1)

=LEN(A1)

=LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+1)

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+1))

=IFERROR(RIGHT(A1,LEN(A1)-IFERROR(FIND(" ",A1,FIND(" ",A1)+1),FIND(" ",A1))),A1)

=FIND(" ",A1,1)+1

=FIND(" ",A1,FIND(" ",A1)+1)

=FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)

=MID(A5,FIND(" ",A5)+1,IFERROR(FIND(" ",A5,FIND(" ",A5)+1)-FIND(" ",A5)-1,0))

=IFERROR(MID(A5,FIND(" ",A5)+1,IFERROR(FIND(" ",A5,FIND(" ",A5)+1)-FIND(" ",A5)-1,0)),"")

Original Name         First Name  Middle Name  Last Name  Resorted Name
Joseph Smith          Joseph                   Smith      Smith, Joseph 
Brigham Young	      Brigham                  Young      Young, Brigham 
John Taylor	      John                     Taylor     Taylor, John 
Wilford Woodruff      Wilford                  Woodruff   Woodruff, Wilford 
Lorenzo Snow          Lorenzo	               Snow       Snow, Lorenzo 
Joseph F. Smith       Joseph      F            Smith      Smith, Joseph F
Heber J. Grant        Heber       J            Grant      Grant, Heber J
George Albert Smith   George      Albert       Smith      Smith, George Albert
David O. Mckay        David       O            Mckay      Mckay, David O
Joseph Fielding Smith Joseph      Fielding     Smith      Smith, Joseph Fielding
Harold B. Lee         Harold      B            Lee        Lee, Harold B
Spencer W. Kimball    Spencer     W	       Kimball    Kimball, Spencer W
Ezra Taft Benson      Ezra        Taft         Benson     Benson, Ezra Taft
Howard W. Hunter      Howard      W            Hunter     Hunter, Howard W
Gordon B. Hinckley    Gordon      B            Hinckley   Hinckley, Gordon B
Thomas S. Monson      Thomas      S            Monson     Monson, Thomas S

Somebody posted a question about a dynamic NDS example found in the Oracle Database 11g PL/SQL Programming book on page 388. They asked if there was an easier way.

The answer is yes. Here’s a different example implementing the same concept on Native Dynamic SQL (NDS) with an input parameter. I borrowed it from the example I used for an Oracle framework to mimic the MySQL ENUM data type.

Basically, the following shows how you write a function using a dynamic NDS statement with an input parameter.

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
  -- Define a weakly typed system reference cursor.
  item_cursor   SYS_REFCURSOR;
 
  -- Define a target variable for the query result.
  item_type_out VARCHAR2(30);
 
  -- Create NDS statement, with a bind or placeholder variable.
  stmt          VARCHAR2(2000) := 'SELECT type_name '
                               || 'FROM   item_type '
                               || 'WHERE  UPPER(type_name) = UPPER(:type_name_in)';
BEGIN
  -- Open the cursor and dynamically assign the function actual parameter.
  OPEN item_cursor FOR stmt USING item_type_in;
 
  -- Fetch the first row return and return the value.
  FETCH item_cursor INTO item_type_out;
 
  -- CLose the cursor.
  CLOSE item_cursor;
 
  -- Return the value.
  RETURN item_type_out;
END;
/

This is certainly overkill if you only want to substitute a single parameter into a cursor. A simpler approach would be to write a dynamic cursor, and then open the cursor by passing the actual parameter. Here’s that example.

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
  -- Define a dynamic cursor.
  CURSOR c (item_type_name VARCHAR2) IS
    SELECT type_name
    FROM   item_type
    WHERE  UPPER(type_name) = UPPER(item_type_name);
BEGIN
  -- Open the cursor and dynamically assign the function actual parameter.
  FOR i IN c(item_type_in) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

An even more primitive approach relies on implicit assignment, like the following:

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  -- Open the cursor and rely on implicit assignment within the cursor.
  FOR i IN (SELECT type_name
            FROM   item_type
            WHERE  UPPER(type_name) = UPPER(item_type_in)) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

I hope this answers the question. You can click on the Setup Code line to unfold the code. Let me know if you like this approach to posting setup code.

-- Conditionally drop table and sequence before attempting to create them.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN (UPPER('item_type')
                                 ,UPPER('item'))) 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 (UPPER('item_type_s1')
                                    ,UPPER('item_s1'))) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create item_type table, index on the natural key, and sequence; then seed data.
CREATE TABLE item_type
( item_type_id NUMBER        CONSTRAINT pk_item_type   PRIMARY KEY
, TABLE_NAME   VARCHAR2(30)  CONSTRAINT nn_item_type_1 NOT NULL
, column_name  VARCHAR2(30)  CONSTRAINT nn_item_type_2 NOT NULL
, type_name    VARCHAR2(30)  CONSTRAINT nn_item_type_3 NOT NULL
, code         VARCHAR2(5)
, meaning      VARCHAR2(255) CONSTRAINT nn_item_type_4 NOT NULL);
 
CREATE UNIQUE INDEX item_type_u1
  ON item_type(TABLE_NAME,column_name,type_name);
 
CREATE TABLE item
( item_id        NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_type      NUMBER        CONSTRAINT nn_item_1  NOT NULL
, item_title     VARCHAR2(60)  CONSTRAINT nn_item_2  NOT NULL
, item_rating_id NUMBER        CONSTRAINT nn_item_3  NOT NULL);
 
CREATE SEQUENCE item_type_s1 START WITH 1001;
CREATE SEQUENCE item_s1 START WITH 1001;
 
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','DVD',NULL,'DVD');
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','VHS',NULL,'VHS');
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','Blu-ray',NULL,'BLRY');

I went back and edited that post about MySQL nuances not in Oracle from last week about the MySQL ENUM data type. You can find a framework suggestion that lets you not have to change existing string conditioning application code during a migration from MySQL to Oracle.

You’ll see this in the middle of the page, just click it to unfold the details. Yes, JQuery has arrived on my blog.

As with everything else on this blog, let me know if you see an opportunity for improvement.

There’s so much energy that goes into blogging with big blog pages, I figured it was time to setup WordPress on my MacBook Pro MAMP (Mac, Apache, MySQL, and PHP) installation. That way, the world doesn’t need to know when I mess up a <div> tag.

The biggest benefit of creating a local blog becomes obvious when you start building your own plugins, templates, and themes. You also have the benefit of a built in backup if you post your work locally before moving it to your blog. Likewise, you can work on your blog when not connected to the network. By the way, thanks to Joel for pointing out that I forgot to say why this was important.

Here are the setup instructions for those interested in doing it too.

1. Download the software from WordPress.org by clicking their button or this one. If you need the tar.gz, click on the web site link.

2. When you download it, you’ll be prompted to open it. Choose to save it.

3. You’ll find it in your downloads folder, where you can double click it to launch the unzip process into a file folder. Mine happens on the desktop because it’s convenient.

4. After you’ve unzipped it into a folder. You drag it into your MAMP htdocs folder. You can check where the htdocs folder is by launching the MAMP application, and choosing Preferences.

In the preferences dialog, click the Apache tab, you’ll see where the htdocs folder location is. Mine is set to the default location.

5. After you drag the wordpress folder into the htdocs folder, you return to the MAMP application and click the Open start page button.

6. Click the phpMyAdmin link and create a database, like the screen shot.

After a moment, you should see that you’ve created a new database named WordPress.

7. Go to your /Applications/MAMP/htdocs/wordpress folder, and open up wp-config-sample.php file in a text editor. You should see the same lines 19, 22, and 25. The editor screen capture is from the TextMate editor.

Edit those lines to reflect your database name, and the root password. Naturally, if you’re concerned that your local host isn’t secure, you’ll need to purchase MAMP Pro to change your root password. You can’t change it in the free version.

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'WordPress');
 
/** MySQL database username */
define('DB_USER', 'root');
 
/** MySQL database password */
define('DB_PASSWORD', 'root');

After these changes, save the wp-config-sample.php file as wp-config.php in the same folder.

8. Enter the following in URL in your browser, and press the enter key.

http://localhost:8888/wordpress/wp-admin/install.php

9. On the Welcome web page, enter your blog name and email (mine is omitted for obvious reasons). Click the Install WordPress button to proceed.

10. You’ll then be prompted with the admin user and a password. You should print it or copy it to your buffer because you’ll need it when you first login. Then, click the Log In button to proceed. You should probably change your password to something that you’ll remember before you do anything else.

11. You now have a complete localhost WordPress installation. Now, you can go to your real web accessible WordPress install and export your blog from the Tools menu. Then, on your localhost blog, you can import from the export file of your blog. If you’re using plugins (like anybody doesn’t), then copy the contents from the wp-content/plugins folder from your online blog to your local one; and then, activate them. You can snag your Akismet key from your online site too, but it is unlikely anybody will see your localhost blog.

Violà, c’est fine.

You can now access WordPress on your local machine by typing in the following URL:

http://localhost:8888/wordpress/

I use Alan Beaulieu’s book in my entry level SQL class because it is simple and short. The problem is that we focus on Oracle products as an Oracle Academic Partner. Three items that come up frequently are the MySQL ENUM and SET data types, and how to perform multiple row inserts.

MySQL’s ENUM data type

The ENUM data type lets you enter a list of possible string values. It acts like a check constraint in an Oracle database. As such, it restricts what you enter in the column to a value found in the list, or a NULL value provided you’ve not added a not null column constraint.

You could define a table that contains video store item types, like the following:

CREATE TABLE item_type
( id INT
, text ENUM ('VHS','DVD','Blu-ray') );

You should note that the case sensitivity for display is set by how you define them in the ENUM data type when you create the table. They may be entered in mixed, lowercase, or uppercase in an INSERT statement because they’re actually stored as a number. The numbers correlate to their order in an internal list of values, and that list start with the number one.

An alternative syntax with a VARCHAR data type is:

CREATE TABLE item_type
( id INT
, TYPE VARCHAR(20) CHECK ( TYPE IN ('DVD','VHS','Blu-ray') ));

In MySQL, this syntax is exactly equivalent in behavior to an ENUM data type. The same isn’t true in an Oracle database. While the equivalent check constraint statement is simple, it isn’t alike behavior. The comparable statement for Oracle names the constraint. You can’t name constraints in MySQL.

CREATE TABLE item_type
( id   NUMBER
, TYPE VARCHAR2(20) CONSTRAINT it_type CHECK ( TYPE IN ('DVD','VHS','Blu-ray') ));

The difference between an ENUM type and a check constraint in MySQL is that the data may not display in a uniform way. MySQL check constraints don’t impose case sensitive validation on input strings, and they also store the data however it is input. Whereas, Oracle does impose case sensitive check constraints and rejects non-conforming strings.

Both databases support single and multiple row INSERT statements. The syntax for single row INSERT statements is very much alike. Multiple row INSERT statement syntax differs between the implementations, as shown later in the blog post.

-- Conditionally drop table and sequence before attempting to create them.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN (UPPER('item_type')
                                 ,UPPER('item'))) 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 (UPPER('item_type_s1')
                                    ,UPPER('item_s1'))) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create item_type table, index on the natural key, and sequence; then seed data.
CREATE TABLE item_type
( item_type_id NUMBER        CONSTRAINT pk_item_type   PRIMARY KEY
, TABLE_NAME   VARCHAR2(30)  CONSTRAINT nn_item_type_1 NOT NULL
, column_name  VARCHAR2(30)  CONSTRAINT nn_item_type_2 NOT NULL
, type_name    VARCHAR2(30)  CONSTRAINT nn_item_type_3 NOT NULL
, code         VARCHAR2(5)
, meaning      VARCHAR2(255) CONSTRAINT nn_item_type_4 NOT NULL);
 
CREATE UNIQUE INDEX item_type_u1
  ON item_type(TABLE_NAME,column_name,type_name);
 
CREATE TABLE item
( item_id        NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_type      NUMBER        CONSTRAINT nn_item_1  NOT NULL
, item_title     VARCHAR2(60)  CONSTRAINT nn_item_2  NOT NULL
, item_rating_id NUMBER        CONSTRAINT nn_item_3  NOT NULL);
 
CREATE SEQUENCE item_type_s1 START WITH 1001;
CREATE SEQUENCE item_s1 START WITH 1001;
 
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','DVD',NULL,'DVD');
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','VHS',NULL,'VHS');
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','Blu-ray',NULL,'BLRY');

CREATE OR REPLACE FUNCTION proper_item_type
( item_type VARCHAR2 ) RETURN VARCHAR2 IS
  CURSOR c (type_name_in VARCHAR2) IS
    SELECT type_name
    FROM   item_type
	  WHERE  UPPER(type_name) = UPPER(type_name_in);
BEGIN
  FOR i IN c (item_type) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

CREATE OR REPLACE TRIGGER proper_item_type_t1
  BEFORE INSERT OR UPDATE ON item
  FOR EACH ROW
BEGIN
  :NEW.item_type := proper_item_type(:new_item_type);
END;
/

INSERT INTO item VALUES (item_s1.nextval, 'BLU-RAY', 'Star Wars I', 1001);

SELECT * FROM item;

MySQL’s SET data type

The SET data type lets you enter a list of possible string values but differs from the ENUM data type only in how the values are indexed. Values in the SET are stored as bit values. You may store up to 64 members in a set.

You create a table the same way as you did with ENUM data type, except that you use the SET key word.

CREATE TABLE item_type
( id INT
, TYPE SET ('DVD','VHS','Blu-ray'));

All insert patterns shown for the ENUM data type work with the SET data type.

Multiple row INSERT statements

The most common multiple row insert statement in MySQL is a comma delimited set of parenthetical values. Each parenthetical set of values maps to a row in the INSERT statement.

INSERT INTO item_type
VALUES
( 1, 'DVD' ), ( 2, 'Blu-ray'), ( 3, 'VHS' );

You can’t use the foregoing syntax in Oracle. Oralce only supports a multiple row insert with a subquery.

You can write a subquery in MySQL that fabricates an aggregate table by using numeric and string literals, like the following statement.

INSERT INTO item_type
SELECT 1, 'DVD'
UNION ALL
SELECT 2, 'Blu-ray'
UNION ALL
SELECT 3, 'VHS' ;

Another way to perform multiple row inserts in MySQL is to select from an existing table or filtered result set, like this:

INSERT INTO item_type
SELECT some_int, some_varchar FROM some_table;

You raise an error when you use parentheses in a multiple INSERT statement in MySQL. It’s important to note because the Oracle allows you to write the statement either way.

If you’re coming from MySQL to Oracle, you should note that you may use parentheses to enclose a subquery in Oracle. The Oracle SQL parser works with or without them in an INSERT statement.

The MySQL subquery example is the closest to the Oracle syntax for a multiple row insert. The difference is that Oracle give you the option to enclose a subquery in parentheses when using them inside INSERT statements. Oracle requires that you use the FROM dual clause. I actually wish Oracle would adopt the shorter syntax and maintain backward compatibility to the dual pseudo table.

INSERT INTO item_type
( SELECT 1, 'DVD' FROM dual
  UNION ALL
  SELECT 2, 'Blu-ray' FROM dual
  UNION ALL
  SELECT 3, 'VHS'  FROM dual);

Another way to perform a multiple row insert is to select from an existing table, like this:

INSERT INTO item_type
(SELECT some_int, some_varchar FROM some_table);

Other resources:

You should check Oracle’s document that qualifies differences between MySQL and Oracle. It is the Oracle® Database SQL Developer Supplementary Information for MySQL Migrations.

Somebody commented last week that they’d like the source code for an XSL example file found in this older post. I’ve added the requested code there. The code lets you query directly from your XSL against an Oracle database.

I’ve also created a top page for collecting references to XML blog pages and posts. It’s only got a few but it is an area that will be expanded.