MacLochlainns Weblog

Installing Microsoft SQL Server 2008 Express was an interesting experience. After downloading it, the software patched and rebooted my Microsoft Vista machine 4 times before beginning the installation. Once started, it only took 55 minutes and 37 seconds to install it.

SQL Server 2008 Express Installation

Here are the steps if you’re interested in installing it.

After you download the file, you’ll need to launch the program from the following dialog. Click the Run button to continue.

The next screen requests you accept the license agreement. Please make sure you do that or you could run into trouble later on if you violate the EULA. Click the Accept button to continue.

The installation will start and then you get to wait. It is at this point that the installer will examine and patch your operating system. While you think you’re up-to-date, you’ll probably see a couple patches applied before the real installation starts.

After you start the installation, you’ll see this dialog. It’ll even reappear after completing the install, and ultimately you may need to click the Cancel button. You don’t have to worry at that point because it’ll be installed.

After you’ve started, this is the dialog you’ll get after applying operating system patches. You’ve really no choice but to reboot if you want to install SQL Server 2008 Express successfully.

This is the first installation screen. Make sure you select the correct version of SQL Server 2008 Express. Typically, you’ll want the one with Advanced Services. Click the appropriate radio button, and then the Install button.

When you see this dialog, you’re installation has truly begun. Don’t click the Cancel button now.

The file extraction takes several minutes. Have something ready while you wait or take a break.

After the file extraction, you’ll see the SQL Server 2008 Setup Support Rules dialog. Click the OK button to continue.

You only get a choice on this dialog if you’re installing the full version. Choose Next to continue.

Check the I accept the license terms box, and then the Next button to continue.

All the rest was plumbing. Now you start the SQL Server 2008 installation. Click the Install button to continue.

You’ll see this for a few minutes before you’re prompted to continue.

You should get all check marks but a lot of folks forget to open the firewall ports. If you forgot, this is what you’ll get as output. Return to the top of the post and open the required ports before continuing. When you’ve opened the ports, click the Next button to continue.

You’ll get this prompt only when you failed to open the ports before beginning the installation. Click the OK button to continue.

The Feature Selection dialog is important. You should probably make the same choices as shown if you’re deploying a development machine. That’s really all you’d want to do with SQL Server 2008 Express edition. Click the Next button to continue.

Now you accept the installation of SQLExpress as the named instance by clicking the Next button.

Now you accept the 1,446 MB space requirement by clicking the Next button.

The account name must be an authorized user account defined by the operating system or left blank. If you want to set it up for local system authentication against Operating System accounts, you must select from the list of values. If you want to provide credentials manually, leave it blank! Click the Next button to continue.

The easiest way to enter a valid credential is to click the Add Current User button. It’ll load it right where you see authorized user in the screen shot. Click Next to continue.

Choose which pieces of information you’d like to send. Not checking anything is also an option. Click Next button to continue.

Now the install will check if everything works before attempting it with the rules you’ve entered. Click the Next button to continue.

After verifying the installation is possible, you’ll see what you’re installing before you click the third Install button to continue.

It is now time for another break while SQL Server 2008 Express edition installs.

If you arrive at the next screen, SQL Server 2008 Express is installed.

Now you’ll see the final installation dialog and the link to the installation log file.

Now the final prompt before configuration. Yes, you must now reboot the system again.

You’ve now installed SQL Server 2008 Express. However, if you want to use the command line client tool, sqlcmd.exe, there’s more work. I posted those steps in this other blog entry.

It must have been disgust with learning that a Result Cache function couldn’t use an object type that made me zone on showing how to use an object type as the return type of a PL/SQL table function. The nice thing about this approach, as pointed out by Gary Myer’s comment on another blog post, is that it doesn’t require a pipelined function to translate it from PL/SQL to SQL scope.

The first step is to create an object type without a return type of SELF, which Oracle elected as its equivalent to this for some unknown reason. A user-defined type (UDT) defined without a return type, returns the record structure of the object, but as mentioned it is disallowed in result cache functions. After you define the base type, you create a collection of the base UDT. Then, you can use the UDT as a SQL return type in your code, like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE OR REPLACE FUNCTION get_common_lookup_object_table
( table_name  VARCHAR2
, column_name VARCHAR2 )
RETURN common_lookup_object_table IS
 
  -- Define a dynamic cursor that takes two formal parameters.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT   common_lookup_id
    ,        common_lookup_type
    ,        common_lookup_meaning
    FROM     common_lookup
    WHERE    common_lookup_table = UPPER(table_name_in)
    AND      common_lookup_column = UPPER(table_column_name_in);
 
  -- Declare a counter variable.
  counter INTEGER := 1;
 
  -- Declare a package collection data type as a SQL scope table return type.
  list COMMON_LOOKUP_OBJECT_TABLE := common_lookup_object_table();
 
BEGIN
 
  -- Assign the cursor return values to a record collection.
  FOR i IN c(table_name, column_name) LOOP
    list.extend;
    list(counter) := common_lookup_object(i.common_lookup_id
                                         ,i.common_lookup_type
                                         ,i.common_lookup_meaning);
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_object_table;
/

You can then query it in SQL like this:

COLUMN common_lookup_id      FORMAT 9999 HEADING "ID"
COLUMN common_lookup_type    FORMAT A16  HEADING "Lookup Type"
COLUMN common_lookup_meaning FORMAT A30  HEADING "Lookup Meaning"
 
SELECT   *
FROM     TABLE(get_common_lookup_object_table('ITEM','ITEM_TYPE'));

This depends on the same sample code that I use elsewhere on the blog. You can download it from McGraw-Hill’s web site. You can also find a complete and re-runnable script by clicking on the down arrow below.

BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name = 'GET_COMMON_LOOKUP_OBJECT_TABLE') LOOP
    EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
  END LOOP;
  FOR i IN (SELECT   type_name
            FROM     user_types
            WHERE    type_name = 'COMMON_LOOKUP_OBJECT_TABLE') LOOP
    EXECUTE IMMEDIATE 'DROP TYPE '||i.type_name;
  END LOOP;
  FOR i IN (SELECT   type_name
            FROM     user_types
            WHERE    type_name = 'COMMON_LOOKUP_OBJECT') LOOP
    EXECUTE IMMEDIATE 'DROP TYPE '||i.type_name;
  END LOOP;
END;
/
 
CREATE OR REPLACE TYPE common_lookup_object IS OBJECT
( common_lookup_id      NUMBER
, common_lookup_type    VARCHAR2(30)
, common_lookup_meaning VARCHAR2(255));
/
 
CREATE OR REPLACE TYPE common_lookup_object_table IS TABLE OF common_lookup_object;
/
 
CREATE OR REPLACE FUNCTION get_common_lookup_object_table
( table_name  VARCHAR2
, column_name VARCHAR2 )
RETURN common_lookup_object_table IS
 
  -- Define a dynamic cursor that takes two formal parameters.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT   common_lookup_id
    ,        common_lookup_type
    ,        common_lookup_meaning
    FROM     common_lookup
    WHERE    common_lookup_table = UPPER(table_name_in)
    AND      common_lookup_column = UPPER(table_column_name_in);
 
  -- Declare a counter variable.
  counter INTEGER := 1;
 
  -- Declare a package collection data type as a SQL scope table return type.
  list COMMON_LOOKUP_OBJECT_TABLE := common_lookup_object_table();
 
BEGIN
 
  -- Assign the cursor return values to a record collection.
  FOR i IN c(table_name, column_name) LOOP
    list.extend;
    list(counter) := common_lookup_object(i.common_lookup_id
                                         ,i.common_lookup_type
                                         ,i.common_lookup_meaning);
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_object_table;
/
 
TTITLE OFF
 
COLUMN common_lookup_id      FORMAT 9999 HEADING "ID"
COLUMN common_lookup_type    FORMAT A16  HEADING "Lookup Type"
COLUMN common_lookup_meaning FORMAT A30  HEADING "Lookup Meaning"
 
SELECT   *
FROM     TABLE(get_common_lookup_object_table('ITEM','ITEM_TYPE'));

Gary Myers made a comment on the blog that got me thinking about how to look up user defined types (UDTs). Like those UDTs that you define to leverage pipelined functions and procedures. It became more interesting while considering how Oracle Object Types act as SQL record types. At least, that’s their default behavior when you don’t qualify a return type of self (that’s this in Oracle objects for those who write in any other object-oriented programming language).

The following query creates a view of data types in your user schema. It is fairly straightforward and written to let you deploy the view in any schema. You’ll need to make changes if you’d like it work against the ALL or DBA views.

CREATE OR REPLACE VIEW schema_types AS
SELECT   ut.type_name AS type_name
,        uta.attr_no AS position_id
,        uta.attr_name AS attribute_name
,        DECODE(uta.attr_type_name
,          'BFILE'        ,'BINARY FILE LOB'
,          'BINARY_FLOAT' ,uta.attr_type_name
,          'BINARY_DOUBLE',uta.attr_type_name
,          'BLOB'         ,uta.attr_type_name
,          'CLOB'         ,uta.attr_type_name
,          'CHAR'         ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,          uta.attr_type_name||'('||uta.length||')')
,          'DATE'         ,uta.attr_type_name
,          'FLOAT'        ,uta.attr_type_name
,          'LONG RAW'     ,uta.attr_type_name
,          'NCHAR'        ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,          uta.attr_type_name||'('||uta.length||')')
,          'NVARCHAR2'    ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,          uta.attr_type_name||'('||uta.length||')')
,          'NUMBER'       ,DECODE(NVL(uta.precision||uta.scale,0)
,          0,uta.attr_type_name
,        DECODE(NVL(uta.scale,0),0
,        uta.attr_type_name||'('||uta.precision||')'
,        uta.attr_type_name||'('||uta.precision||','|| uta.scale||')'))
,          'RAW'          ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,        uta.attr_type_name||'('||uta.length||')')
,          'VARCHAR'      ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,        uta.attr_type_name||'('||uta.length||')')
,          'VARCHAR2'     ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,        uta.attr_type_name||'('||uta.length||')')
,          'TIMESTAMP'     , uta.attr_type_name,uta.attr_type_name) AS attr_type_name
FROM     user_types ut, user_type_attrs uta
WHERE    ut.type_name = uta.type_name
ORDER BY ut.type_name, uta.attr_no;

You can query and format the view as follows:

CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
 
TTITLE OFF
 
SET ECHO ON
SET FEEDBACK OFF
SET NULL ''
SET PAGESIZE 999
SET PAUSE OFF
SET TERM ON
SET TIME OFF
SET TIMING OFF
SET VERIFY OFF
 
ACCEPT INPUT PROMPT "Enter type name: "
 
SET HEADING ON
TTITLE LEFT o1 SKIP 1 -
'--------------------------------------------------------' SKIP 1
 
CLEAR COLUMNS
CLEAR BREAKS
 
BREAK ON REPORT
BREAK ON c1 SKIP PAGE
 
COL c1 NEW_VALUE o1 NOPRINT
COL c2 FORMAT 999 HEADING "ID"
COL c3 FORMAT A32 HEADING "Attribute Name"
COL c4 FORMAT A33 HEADING "Attribute Type"
 
SELECT   st.type_name c1
,        st.position_id c2
,        st.attribute_name c3
,        st.attr_type_name c4
FROM     schema_types st
WHERE    st.type_name LIKE UPPER('&input')||'%'
ORDER BY st.type_name
,        st.position_id;

Here’s a sample output for an object type named common_lookup_object:

COMMON_LOOKUP_OBJECT
--------------------------------------------------------
  ID Attribute Name                   Attribute TYPE
---- -------------------------------- ------------------
   1 COMMON_LOOKUP_ID                 NUMBER
   2 COMMON_LOOKUP_TYPE               VARCHAR2(30)
   3 COMMON_LOOKUP_MEANING            VARCHAR2(255)

It certainly makes the point that a named data type is most convenient. I’m still working through the metadata to find how to link those meaningless type names back to meaningful package specifications. If you know, let me know in a comment. Hope this helps somebody.

Some tips and techniques for Word seems like a good add to the blog. At least, those changes that I run into while writing for McGraw-Hill on my new book projects. This shows you how to enable Microsoft Word 2007 and 2008 readability statistics. Yes, it’s moved in the new release.

Microsoft Word 2007 has the Office Button. The Office Button holds the key to what were once tool options. You click on the Office Button in the upper left hand corner. You’ll see the following options dialog. Click the Word Options button to change options.

Choose the Proofing option in the left column, then check the Show readability statistics check box, as shown. You click the OK button when your done setting Word options.

Microsoft Word 2008 works like Mac OS X applications. You navigate to the Word on the menu and choose Preferences. That launches the following menu set.

Double click the Spelling and Grammer icon, which launches the Spelling and Grammar options dialog. Check the Show readability statistics box and then click the OK button to save the setting.

You can’t beat play’n around with the technology. It seems that each time I experiment with something to answer a question, I discover new stuff. So, I really appreciate that Cindy Conlin asked me to net out why a PL/SQL Pipelined Table function existed at UTOUG Training Days 2009.

I found that Java and PHP have a great friend in Pipelined Table functions because when you wrap them, you can simplify your code. While a reference cursor lets you return the product of a bulk operation, it requires two hooks into the database. One for the session connection and another for the connection to the system reference cursor work area. While this was a marvelous feature of the OCI8 library, which I duly noted in my Oracle Database 10g Express Edition PHP Web Programming book, there’s a better way.

The better way is a Pipelined Table function because you can query it like you would a normal table or view. Well, not exactly but the difference involves the TABLE function, and it is really trivial.

When you call a Pipelined Table function, you only need to manage a single hook into the database. That hook is for the session connection. You can find a full (really quite detailed) treatment of Table and Pipelined Table functions in this blog page. Building on that blog page, here’s a simple PHP program that demonstrates the power of leveraging the SQL context provided by a Pipelined Table function.

<?php
  // Connect to the database.
  if ($c = @oci_connect("plsql","plsql","orcl"))
  {
    // Parse a query to a resource statement.
    $s = oci_parse($c,"SELECT * 
                       FROM TABLE(get_common_lookup_plsql_table('ITEM','ITEM_TYPE'))");
 
    // Execute query without an implicit commit.
    oci_execute($s,OCI_DEFAULT);
 
    // Open the HTML table.
    print '<table border="1" cellspacing="0" cellpadding="3">';
 
    // Read fetched headers.
    print '<tr>';
    for ($i = 1;$i <= oci_num_fields($s);$i++)
      print '<td class="e">'.oci_field_name($s,$i).'</td>';
    print '</tr>';
 
    // Read fetched data.
    while (oci_fetch($s))
    {
      // Print open and close HTML row tags and columns data.
      print '<tr>';
      for ($i = 1;$i <= oci_num_fields($s);$i++)
        print '<td class="v">'.oci_result($s,$i).'</td>';
      print '</tr>';
    }
 
    // Close the HTML table.
    print '</table>';
 
    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
?>

You’ll notice that all the information that is expected from a query against a table or view is also available from the result of Pipelined Table function. That’s because the Pipeline Table function actually places the internal record structure of a PL/SQL collection into the SQL context along with the data.

This sample PHP program produces the following XHTML output:

Naturally, you can parameterize your PHP program and add bind variables to make this more dynamic. An example of parameterizing the call to a Pipelined Function is provided in the next program example.

You would use the following URL to call the dynamic PHP program:

http://mclaughlin11g/GetCommonLookup.php?table=ITEM&column=ITEM_TYPE

The working PHP program code is:

<?php
  // Declare input variables.
  (isset($_GET['table'])) ? $table = $_GET['table'] : $table = "ITEM";
  (isset($_GET['column'])) ? $column = $_GET['column'] : $column = 'ITEM_TYPE';
 
  // Connect to the database.
  if ($c = @oci_connect("plsql","plsql","orcl"))
  {
    // Parse a query to a resource statement.
    // Don't use table and column because they're undocumented reserved words in the OCI8.
    $s = oci_parse($c,"SELECT * 
                       FROM TABLE(get_common_lookup_plsql_table(:itable,:icolumn))");
 
    // Bind a variable into the resource statement.
    oci_bind_by_name($s,":itable",$table,-1,SQLT_CHR);
    oci_bind_by_name($s,":icolumn",$column,-1,SQLT_CHR);
 
    // Execute query without an implicit commit.
    oci_execute($s,OCI_DEFAULT);
 
    // Open the HTML table.
    print '<table border="1" cellspacing="0" cellpadding="3">';
 
    // Read fetched headers.
    print '<tr>';
    for ($i = 1;$i <= oci_num_fields($s);$i++)
      print '<td class="e">'.oci_field_name($s,$i).'</td>';
    print '</tr>';
 
    // Read fetched data.
    while (oci_fetch($s))
    {
      // Print open and close HTML row tags and columns data.
      print '<tr>';
      for ($i = 1;$i <= oci_num_fields($s);$i++)
        print '<td class="v">'.oci_result($s,$i).'</td>';
      print '</tr>';
    }
 
    // Close the HTML table.
    print '</table>';
 
    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
?>

You may note that the parameter values (placeholders or bind variables inside the SQL statement) are prefaced with an i. That’s because TABLE and COLUMN are restricted key words in the context of OCI8, and their use triggers an ORA-01036 exception.

This makes PHP more independent of the OCI8 library and easy to cross port to other databases if that’s a requirement. Hope this helps some folks.

When I presented the concept at the Utah Oracle User’s Group (UTOUG) Training Days 3/12-3/13/2009 it became clear the community could benefit from more detail about table and pipelined table functions. The question asked was: “What’s the primary purpose for pipelined table functions?”

My answer is: The primary purpose of a pipelined table function lets you retrieve PL/SQL record collection structures in a SQL context.

If there’s another reason that I missed, please let me know. It took a couple days to expand the older post to be more complete.

You can see the new one here …

Today, one of my students made a common error working in a PL/SQL programming unit. The stumped student called the tutor over, and then the tutor called me over. The tutor asked me how the DBMS_OUTPUT package could be out of scope. When I saw the SP2-0552 error, it was straightforward to remove a stray colon, but I realized that recognizing the colon as an error wasn’t straightforward.

The error picks the string that follows a colon inside a program unit. This behavior is the same regardless of whether the colon is found in the declaration, execution, or exception block. Most likely, the string is the beginning of the next line because the colon is most frequently a mistyped semicolon.

SP2-0552: Bind variable "DBMS_OUTPUT" NOT declared.

The easiest way to find it is not to look for the string but simply for a colon with the find feature of the editor. Then, you must either replace it or give it a valid bind variable name. You define bind variables in the SQL*Plus environment with the VARIABLE keyword.

You may also be interested in how to put a colon inside an NDS statement. If so, you can check this blog post.

I really like Alan Beaulieu’s Learning SQL because its simple, direct, and clearer than other books on SQL. While his focus is MySQL, he does a fair job of injecting a bit about Oracle’s syntax. Comparative concatenation syntax is one of topics I wished he’d spent more time on. Here’s some clarification on cross platform SQL concatenation.

Oracle

Oracle supports two forms of string concatenation. Concatenation for those new to the idea means gluing two strings into one, or three strings into one, et cetera. One uses the || operator, which looks like two pipes. You can use the || operator between any number of string elements to glue them together. A quick example of the || operator that returns an ABCD string is:

SELECT 'A' || 'B' || 'C' || 'D' FROM dual;

The Oracle database also supports the CONCAT operator that many use in MySQL. Those converting to an Oracle database should beware the difference between how the CONCAT function is implemented in Oracle versus MySQL. In an Oracle database, the CONCAT function only takes two arguments. When you call it with three or more arguments like this:

SELECT CONCAT('A','B','C','D') FROM dual;

It raises the following exception:

SELECT CONCAT('A','B','C','D') FROM dual
       *
ERROR at line 1:
ORA-00909: invalid NUMBER OF arguments

You can use the CONCAT function to process more than two arguments but you must do so by calling the function recursively. You’d do it like this if you must use it:

SELECT CONCAT('A',CONCAT('B',CONCAT('C','D'))) FROM dual;

As to an Oracle specific SQL book recommendation, I’d go with Alan’s as a beginner even though it’s focus is MySQL. By the way, if you don’t own Learning SQL hold off on buying it until the second edition is available in May 2009. If you’re using Oracle and have some basic SQL competence, I’d suggest Mastering Oracle SQL, 2nd Edition by Sanjay Mishra and Alan Beaulieu as a reference. Just make sure you get the 2^nd Edition of it too.

MySQL

MySQL appears to support the two same forms of string concatenation as an Oracle database. The one that uses the || operator (known as pipe concatenation), actually only returns a zero unless you configure the sql_mode to allow pipe concatenation.

The following concatenation statement uses pipe concatenation:

mysql> SELECT 'A'||'B'||'C'||'D';
+--------------------+
| 'A'||'B'||'C'||'D' |
+--------------------+
|                  0 |
+--------------------+
1 ROW IN SET, 4 warnings (0.00 sec)

By default, this fails and returns a zero unless you’ve added the PIPES_AS_CONCAT mode to your sql_mode variable. It returns a zero because it attempts to see whether either of the adjoining elements are true. Strings inherently fail to resolve as expressions or Boolean values and the function returns a zero, which means the composite expression was evaluated as false.

You can query the sql_mode variable as follows. The default values are shown in the results.

mysql> SELECT @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 ROW IN SET (0.00 sec)

You can modify the sql_mode as follows from the command line:

SET sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT';

If you want to make this a permanent change, you can edit the my.ini file in Windows or the my.conf file in Unix or Linux. The following shows the modified line in a configuration file.

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

With these changes pipe concatenation works in MySQL, as follows:

mysql> SELECT 'A'||'B'||'C'||'D';
+--------------------+
| 'A'||'B'||'C'||'D' |
+--------------------+
| ABCD               |
+--------------------+
1 ROW IN SET (0.02 sec)

You can use the CONCAT function to glue any number of string elements together when you’ve no control of the sql_mode variable. The CONCAT function in MySQL takes several arguments. I’ve never needed to use more than the limit and suspect that there isn’t one (based on the documentation). It appears to use a recursive algorithm for parameter processing. Please post a note correcting me if I’m wrong on this.

You call the CONCAT function like this:

SELECT CONCAT('A','B','C','D');

As to a MySQL specific SQL book recommendation, I’d go with Alan Beaulieu’s Learning SQL as a beginner. As noted earlier, don’t buy it until the 2^nd Edition ships in May 2009.

Microsoft® Access or SQL Server

Microsoft® SQL Server doesn’t support two forms of string concatenation like Oracle and MySQL. You can only use the + operator. There is no CONCAT function in Microsoft® Access or SQL Server. A quick example of the + operator in Microsoft’s SQL returns an ABCD string like this:

SELECT 'A' + 'B' + 'C' + 'D';

As to a Microsoft® T-SQL book recommendation, I’d go with Itzik Ben-Gan’s Microsoft SQL Server 2008 T-SQL Fundamentals. Just understand, that like most things Microsoft, T-SQL is a dialect and approach that differs substantially from other commercial products.

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!