MacLochlainns Weblog

While helping out in the conversion of some MySQL SQL, I tried the WITH clause inside a subquery for a multiple row INSERT statement. I got a nasty surprise, it’s not supported. I got the following error:

  FROM     dual )
                *
ERROR at line 16:
ORA-32034: unsupported USE OF WITH clause

Consistent with how I’m updating old blog pages and posts, you can find the full explanation in the updated blog post on the WITH clause. As Dominic commented, I got the syntax wrong and he’s got it for a single row subquery in the comment too. The blog page is updated with both a single and multiple row subquery.

I thought it would be nice to walk through the Excel 2008 configuration steps to query Oracle. I was quite surprised when navigating the path, this error dialog was thrown:

When you navigate to the Microsoft web site, you’ll find that you have a choice of an ODBC driver from Open Link or Actual Technologies. You might think that Oracle would have their own ODBC driver that you can use without paying for a 3rd party solution. Unfortunately, there isn’t one. The most recent kits are missing the libsqora.so shared library. The only ones that I could find are for the Mac OS X Tiger edition.

I may have missed something but you’ll find the Oracle documentation here. Feel free to comment with a solution. My solution is to use Code Weaver’s CrossOver Mac, and Microsoft Office 2007. How I regret the money wasted on Microsoft Office 2008.

A quick note, addendum, it looks like Actual Technologies is the best. Unfortunately, they charge for one copy for Oracle and another for MySQL and Postgre. What a discouraging note, but I may bite the bullet on the $60 bucks for both. I’ll defer the MySQL and Postgre until they release their 2.9 version. Don’t forget to also download Microsoft’s Query tool.

I caught a post on the OTN forum asking how to do this, and it happened to be something I’m working on for a new course that I’ll be teaching on data analytics. Ultimately, Microsoft Excel is the de facto tool of many accounts and financial analysts, protests notwithstanding.

This shows you how to query an Oracle 11g database from Excel 2007. Actually, it should work on any current version of the Oracle database. The key to making this work is having the Oracle 10g Client software or an Oracle 11g database on the same machine. The Oracle client software allows you to resolve an Oracle Network Alias (found in the %ORACLE_HOME%\network\admin\tnsnames.ora file).

The steps are provided in this blog page …

I attempted to have a forward from the old WordPress.com site. That was a painful mistake! It took down both blogs with a circular referencing that was humorous I suppose. The DNS entries appear to all be corrected and forwarding straightened out. I apologize for any inconvenience.

As I update or expand entries, I’m entering a note in the old blog posts. I think the code segments are much improved, you can find SQL, PL/SQL, PHP, and Java examples in the following blog entries that are now perfect for cutting and pasting. Also, as noted the iPhone view is GREAT!

1. SQL example
2. PL/SQL example
3. PHP example
4. Java example
5. Shell example, DOS and Bash

A number of friends wanted me to do things that weren’t possible while keeping my blog on WordPress.com’s web site. Things like code formatting, downloads for software, et cetera …

You can find the new blog at: http://blog.mclaughlinsoftware.com

So, I’ve migrated it to my own domain. I’m in the process of configuring the rest of the domain. I’ll update you through the blog as I complete the process.

I’ve got grand plans (lets hope there’s time). For example, I’m planning to migrate and update the TechTinker.com domain too. I’ll plan tutorials on Java, PL/SQL, and PHP initially as well as administration tips, techniques and walk through steps for my favorite platforms. As time allows, I’ve got some stuff comparing SQL across platforms – much beyond the trivial Oracle’s SPOOL is MySQL’s tee (not herbal tea).

While my perspective may change over time, I’m quite fond of Mac OS X, Ubuntu, and Red Hat. I won’t exclude Microsoft because we must live with it, but isn’t CrossOver sweet! I’d actually tried to get Oracle to run through it. I may yet, I’m still playing with it.

I’m trilled that Chris Jones and Justin Kestelyn got me blogg’n. I’ve always wanted to contribute more but the forums have mavens with much more time than I have to answer questions. The blog lets me share ideas and concepts as they travel with me on a daily basis.

Stats on the blog pages may not migrate but if time allows maybe I’ll write a plugin if I can’t find one. Generally, I don’t think anybody cares about the stats. It’s the information to solve problems, and I hope that’s what you find useful in my blog.

By the way, it wasn’t just the ability to have my blog pages look cool on my iPhone that drove the change, but it does look nice!!!

Somebody reminded me that I should include the $TNS_ADMIN variable to avoid unnecessary file searches (minor expense) in the Mac configuration instructions. This eliminates additional searches when running sqlplus and tnsping executables. I’ve updated that blog page with those instructions. It’s not necessary to make it work but it does avoid a read to the local and /etc directories on your Mac OS X for a tns_names.ora file before reading it from the $ORACLE_HOME/network/admin directory.

The Adapter pattern in Object-Oriented OO programming is missing when it comes to how you can read, pass, and manage a PL/SQL system reference cursor. At least, it’s missing for PL/SQL native development. It is available through the OCI but only as a multiple dimensional array of strings.

Here’s an illustration of the Adapter Pattern (courtesy of the Design Patterns book by Gamma, Helm, Johnson, and Vlissides):

There are a few ways to handle reference cursor in PL/SQL. I’ve updated the earlier blog to more completely cover the options. In all cases within a PL/SQL environment, you must know the target type of the PL/SQL record structure. The target for converting a PL/SQL reference cursor is a a PL/SQL record structure.

You can assign a PL/SQL record structure through a PIPELINED table function to an aggregate table, which can be read by SQL. The following query lets you read an aggregate table back into a PL/SQL structure but begs the question of why you’d want to do that. The actual query by itself is in the updated blog entry linked above.

BEGIN
  FOR i IN (SELECT *
            FROM TABLE(use_of_input_cursor(weakly_typed_cursor('Harry')))) LOOP
    DBMS_OUTPUT.put_line('Item Title    ['||i.item_title||']');
    IF i.item_subtitle IS NOT NULL THEN
      DBMS_OUTPUT.put_line('Item Subtitle ['||i.item_subtitle||']');
    END IF;
  END LOOP;
END;
/

The OCI8 driver lets you take the system reference cursor from a PL/SQL block and translate it to a multidimensional array of strings. The following (borrowed from my book on PHP programming with Oracle) demonstrates how to open a statement and a reference cursor, which lets you manage the OUT mode parameter (covered in this earlier post) variable of a PL/SQL reference cursor. Our thanks should go to the OCI team because they see the world of interactivity.

  // Declare a PL/SQL execution command.
  $stmt = "BEGIN
             world_leaders.get_presidents(:term_start
                                         ,:term_end
                                         ,:country
                                         ,:return_cursor);
           END;";
 
  // Strip special characters to avoid ORA-06550 and PLS-00103 errors.
  $stmt = strip_special_characters($stmt);
 
  // Parse a query through the connection.
  $s = oci_parse($c,$stmt);
 
  // Declare a return cursor for the connection.
  $rc = oci_new_cursor($c);
 
  // Bind PHP variables to the OCI input or in mode variables.
  oci_bind_by_name($s,':term_start',$t_start);
  oci_bind_by_name($s,':term_end',$t_end);
  oci_bind_by_name($s,':country',$country);
 
  // Bind PHP variables to the OCI output or in/out mode variable.
  oci_bind_by_name($s,':return_cursor',$rc,-1,OCI_B_CURSOR);
 
  // Execute the PL/SQL statement.
  oci_execute($s);
 
  // Access the returned cursor.
  oci_execute($rc);

You can query the results of the reference cursor ($rc), like this:

  // Print the table header with known labels.
  print '<table border="1" cellpadding="3" cellspacing="0">';
 
  // Set dynamic labels control variable true.
  $label = true;
 
  // Read the contents of the reference cursor.
  while($row = oci_fetch_assoc($rc))
  {
    // Declare header and data variables.
    $header = "";
    $data = "";
 
    // Read the reference cursor into a table.
    foreach ($row as $name => $column)
    {
      // Capture labels for the first row.
      if ($label)
      {
        $header .= '<td class="e">'.$name.'</td>';
        $data .= '<td class="v">'.$column.'</td>';
      }
      else
        $data .= '<td class=v>'.$column.'</td>';
    }
 
    // Print the header row once.
    if ($label)
    {
      print '<tr>'.$header.'</tr>';
      $label = !$label;
    }
 
    // Print the data rows.
    print '<tr>'.$data.'</tr>';
  }
 
  // Print the HTML table close.
  print '</table>';

Moreover, it would be wonderful if Oracle let you implement a full Adapter pattern but there are constructive ways to work with what we’ve got now. You actually get a bit more through the JDBC implementation but that’s for another blog I guess. As to adapter or not adapter, it’s clearly not.

My students wanted a supplement on how variable modes work in PL/SQL, so I figured it would fit nicely in a quick blog entry. If you’re interested, read on …

PL/SQL supports three patterns of variable modes in functions and procedures. The easiest supports a pass-by-value function or procedure, and it is the IN mode of operation. The other two are related but different, and support a pass-by-reference function or procedure. The differences between a function and procedure are straightforward: (1) A function can return a value as an output, which is known as an expression; (2) A function can be used as a right operand; (3) A procedure can’t return a value because it more or less returns a void (borrowing from the lexicon of C, C++, C#, or Java and many other languages), (4) A procedure can be used as a statement by itself in a PL/SQL program while a function can’t. The variables you define in a function or procedure signature (or prototype) are the formal paramters, while the values or variables assigned when calling a function or procedure are the actual parameters.

IN mode:

An IN mode variable is really a copy of the variable, but you can ask to pass a reference. PL/SQL typically obliges when using the IN mode of operation. The following defines a pass-by-value PL/SQL function (other than the return type, you could do the same in a procedure too):

You can test the values of the actual parameter before and after the function call while also testing it inside the function. You can also assign a literal number or string as the actual parameter because the IN mode only requires a value because it discard the variable reference and value when it completes.

There is an exception data type for this IN mode operation, and it is the PL/SQL system reference data type (more on this type can be found in the following post). A PL/SQL reference cursor can only be passed when it is already opened, and it actually passes a reference to the cursor work area in the Private Global Area (PGA).

You can’t assign a value to a formal parameter inside a function when the variable has the default (or IN) mode of operation. Any attempt to do so raises a PLS-00363 with a warning that expression (formal parameter) can’t be used as an assignment target. A test of the function follows:

This seems to be the preferred way to implement functions for beginning programmers.

IN OUT mode:

An IN OUT mode variable is typically a reference to a copy of the actual variable for a couple reasons. If the function or procedure fails the original values are unchanged (this is a departure from the behavior of collections passed as actual parameters in Java). You can assign values to the formal parameter at runtime when using an IN OUT mode variable.

At the conclusion of the function or procedure the internal variable’s reference is passed to the calling program scope and replaces the original reference to the actual parameter. Here’s an example of an IN OUT mode variable in a function.

As you can see the external value is changed inside the function and at completion of the function the external variable passed as an actual parameter is changed:

This seems to be used more frequently in procedures than functions in PL/SQL. However, you can use the approach in either. I’d recommend it for functions that you call through the OCI or Java.

OUT mode:

An OUT mode variable is very much like an IN OUT mode variable with one exception. There is no initial value in it. You must assign a value to an OUT mode variable because it has no value otherwise. If the function or procedure fails, the external variable is unchanged. At the successful conclusion of the function or procedure, the reference for the internal variable replaces the reference to the external scoped variable.

The following shows you the test case:

The OUT mode also has an exception, which relates to CLOB and BLOB datatypes. You can find more about large objects in this presentation made at the Utah Oracle Users Group – Oracle LOBs.

This should be pretty straightforward but if you have suggestions to improve it let me know.

I tried to answer a question in the forum a couple weeks ago about connections. It contained all the trite stuff about check this, then that, et cetera because I couldn’t remember why I’d seen an TNS-03505 error. It returns an illustrative message “Failed to resolve name” error. Along the way, I tripped into ORA-12560 and one I’d never seen before an ORA-12518. If you want the dirt on these read on …

TNS-03505
You’ll typically encounter this error when you’re working on a laptop. It gets triggered when you run the tnsping utility. When you’ve qualified the hostname and hostname.domain name in the hosts file and in the tnsnames.ora file, it’ll throw the TNS-03505 error because you’re on another network. You may also encounter it when there’s a change or discrepancy between the machine hostname and DNS server results. Lastly, you may encounter it when you’ve lost the lease on an IP address and now have a new lease with a different IP address.

These errors effectively block successful tnsping calls. You get around it by shutting down the listener, modifying the IP address in the tnsnames.ora file if you’re not using a hostname, setting the %TNS_ADMIN% environment variable in a command session when you have multiple Oracle homes, and restarting the listener.

TNS-12533
This is an doubly annoying error because while it can occur for a meaningful reasons like fat fingering a key word in the ADDRESS descriptor, it can be a stray tab character. That’s why the error text is: TNS:illegal ADDRESS parameters

In vi or vim, you can see hidden characters by typing the : (colon), and enabling list. List shows you hidden characters, like the $ for line returns, et cetera. You run it like this:

:set list

If you see a stray tab character, ^I, remove it and the error should go away. You can disable list by setting nolist.

ORA-01017
This is a principally new error with Oracle 11gR2, and involves the Oracle Call Interface (OCI). You’ll see the errors when you try to change the password of a connect user, and Chris Jones at Oracle explains it well.

ORA-12170
This is generally an easy fix. It typically means that the listener can’t be found. The most frequent cause of the error is a generic installation of the Oracle database or client on a DHCP configured laptop. If there’s no hostname to IP address map defined in the /etc/hosts (Linux or Unix) or the C:\WINDOWS\system32\drivers\etc\hosts file, the installation uses the current IP number. You typically see this error after you’ve fixed your Oracle listener that wouldn’t start by replacing the IP address with a hostname, which is done in the listener.ora file. Unfortunately, you also need to change the same thing in the tnsnames.ora file.

When you connect your laptop to another network, a TNS-12170 exception is thrown. You can fix it by replacing the (HOST = nnn.nnn.nnn.nnn) with the machine name, like (HOST = some_hostname).

ORA-12500
This one generally has two causes. First, when the service was created by somebody other than the Administrator, which can be a user with Administrator role but not in the Administrator group. Second, when the machine doesn’t have enough physical or virtual memory to spawn a new dedicated process.

ORA-12514
This one states that Oracle Listener can’t find the connect descriptor. You get an error like the following:

ORA-12514: TNS:listener does NOT currently know OF service requested IN CONNECT descriptor

The ORA-12514 typically means that the Oracle Listener is running but that Oracle on a Linux or Unix machine, or the Oracle Service on a Windows machine is not started.

ORA-12518
This one is cute. I only hit it because my touchpad is too sensitive on the Dell laptop. It occurs if you pause the Windows listener service. You fix it by restarting the service. It is an enigma within a conundrum (Churchill on the old Soviet Union) why anybody would create a service like this with a pause option. Wouldn’t it be cool if somebody at Oracle were listening now (that is reading this blog) and they got the service fixed.

ORA-12541
This typically means one of two things, which depends on the text of the error. It generally returns either a "no listener" or "could not resolve the connect identifier specified" message text.

The former means the listener isn’t running or can’t be found by the Oracle Instant Client software. If the listener isn’t running, you can start it with the lsnrctl utility, or in Windows by starting the service.

# lsnrctl start

When the listener is running but isn’t seen by an Oracle Instant Client installation or another database through a database link, you probably have a firewall rule in place blocking the port that is listening for incoming traffic. Check your server firewall rules.

The latter error means sqlplus can’t find the tnsnames.ora file. Oracle checks for the tnsnames.ora file in the present working directory, then the location of the $TNS_ADMIN directory (or Windows %TNS_ADMIN%), and finally the location of the $ORACLE_HOME/network/admin directory. If you’re getting this error in a PHP configuration, like a CGI mode, put a copy of the tnsnames.ora file in the same directory as the php-cgi.exe file. Alternatively, you can define the following in your httpd.conf file.

SetEnv TNS_ADMIN "tns_admin_directory"

ORA-12560
This is the sqlplus utility cousin of the TNS-03505. All the rules that apply to it apply to this.

Sometimes working with a product like Oracle brings a surprise, like a new feature you failed to catch when it was released. I’ve seen a lot of entries using inline views through the WITH clause in the Oracle forums. It caught my eye because it is such a radical departure from portable SQL syntax of an inline view. I finally went searching to find the rationale for this approach.

The answer doesn’t lie with the Charlotte like National Treasure, but with simplifying the join syntax, as qualified in the Oracle Database 2 Day + Data Warehousing Guide 11g, Release 1. The following looks at this approach, and compares using the WITH clause instead of the inline view to perform table fabrication.

Oracle tells us to use the WITH clause when a query has multiple references to the same query block and there are joins and aggregations. The official name of the WITH clause is a subquery factoring clause. Basically, the WITH clause lets you name inline views and then reuse them inside other inline views. This behavior avoids having to call different copies of the same inline view in different parts of a query or DML statement, which reduces overhead and increases view response time for resolution.

Like local named PL/SQL blocks, subquery factoring clauses must be defined before they can be referenced. Unlike PL/SQL, they have no equivalent function and procedure stubs used for forward referencing in a PL/SQL program unit. They simply become available in sequence, the top most universally available and the next only available below to those statements below it.

The WITH clause (or subquery factoring clause) feature comes to us from the ANSI SQL:1999 specification. It is implemented in Oracle databases with the same syntax as it is in Microsoft SQL Server 2005. The only difference is Microsoft brands it as a Common Table Expression (CTE). It also happens to be the only way to implement a recursive query in Microsoft SQL Server.

The basic syntax is:

The first code block is assigned the subquery factoring clause’s name. You can then reuse the name in subsequent code blocks or the master query. The idea is that this syntax is simpler than the traditional inline view approach and more efficient.

The WITH clause is also capable of letting you create tables from literal values, which is known as table fabrication. The following syntax uses the with clause to fabricate a table of two columns (x and y) and two rows.

SQL> WITH fabricated AS
  2   (SELECT 1 AS x, 2 AS y FROM dual
  3    UNION ALL
  4    SELECT 3 AS x, 4 AS y FROM dual)
  5    SELECT x, y FROM fabricated;

This produces the following results:

         X          Y
---------- ----------
         1          2
         3          4

The next shows the traditional way of fabricating a table using an inline view:

SQL> SELECT x, y
  2   FROM (SELECT 1 AS x, 2 AS y FROM dual
  3         UNION ALL
  4         SELECT 3 AS x, 4 AS y FROM dual) fabricated;

This also produces the same results as before, two rows of X and Y variables.

You can also use this type of syntax in MySQL to fabricate a table. You can’t use the WITH clause in MySQL because it’s not supported. You’ll notice in the example that the FROM dual portion is omitted in MySQL. Wouldn’t it be nice if Oracle let that happen too?

SQL> SELECT x, y
  2  FROM  (SELECT 1 AS x, 2 AS y
  3         UNION ALL
  4         SELECT 3 AS x, 4 AS y) fabricated;

A neat function that I picked up on the Oracle Technical Network is the NUMTODSINTERVAL (number to date-stamp interval) function, which can create intervals for qualifying sales by hour or quarter hour. More or less it is a way to fabricate timing intervals. Here’s a quick example:

SQL> SELECT   TO_CHAR(SYSDATE - NUMTODSINTERVAL(dist,'HOUR')
  2                  ,'DD-MON-YYYY HH24:MI:SS') bracket
  3  FROM    (SELECT 1 AS dist FROM dual
  4           UNION ALL
  5           SELECT 2 AS dist FROM dual
  6           UNION ALL
  7           SELECT 3 AS dist FROM dual) fabricated;

The output is:

BRACKET
-------------------
22-OCT-2008 23:07:15
22-OCT-2008 22:07:15
22-OCT-2008 21:07:15

This has been the syntax, now I’ll have to check whether there are any performance differences. I suspect that since the execution plan is the same that there aren’t any performance differences but you never know until you test it.

More or less they were but I tripped into a performance shortfall of the WITH clause. It was a complete accident when I was trying to convert a MySQL SQL syntax model into Oracle SQL. The smaller change to the code was to use a WITH clause but I found it didn’t work.

You can’t use the WITH clause inside a subquery for a multiple row insert. It raises an ORA-32034 error if you attempt it, which struck me as bizare. A normal inline view works fine but the WITH clause doesn’t.

Here’s a simple example of embedding an inline view into an INSERT statement. It works seamlessly in Oracle 11g:

INSERT INTO contact_copy
( SELECT   contact_s1.nextval
  ,        1001
  ,        cl.contact_type
  ,        'Doe'
  ,        'John'
  ,        NULL
  ,        3
  ,        SYSDATE
  ,        3
  ,        SYSDATE
  FROM     dual
  CROSS JOIN
 (SELECT   common_lookup_id AS contact_type
  FROM     common_lookup
  WHERE    common_lookup_type LIKE '%DAY RENTAL') cl );

When I switched to what appeared as the equivalent syntax using a WITH clause, it failed and raised the ORA-32034: unsupported use of with clause error. The following shows you how the WITH would be used, if it could be used:

INSERT INTO contact_copy
( WITH  cl AS
 (SELECT   common_lookup_id AS contact_type
  FROM     common_lookup
  WHERE    common_lookup_type LIKE '%DAY RENTAL')
  SELECT   contact_s1.nextval
  ,        1001
  ,        cl.contact_type
  ,        'Doe'
  ,        'John'
  ,        NULL
  ,        3
  ,        SYSDATE
  ,        3
  ,        SYSDATE
  FROM     dual );

I thought that might be the reason why Oracle didn’t bother putting it in the SQL reference manual for Oracle Database 10g or 11g. However, Dominic Brooks provided the correct syntax. Very interesting that you simply start with the WITH clause and exclude the enclosing parentheses. Quite a departure from the normal syntax for a multiple row insert.

The correct syntax when the subquery cl returns only one row is like Dominic’s suggestion:

INSERT INTO contact_copy
WITH  cl AS
(SELECT   common_lookup_id AS contact_type
 FROM     common_lookup
 WHERE    common_lookup_type LIKE '%DAY RENTAL')
 SELECT   contact_s1.nextval
 ,        1001
 ,       (SELECT cl.contact_type FROM cl)
 ,        'Doe'
 ,        'John'
 ,        NULL
 ,        3
 ,        SYSDATE
 ,        3
 ,        SYSDATE
 FROM     dual;

The correct syntax when the subquery cl subquery returns more than one row differs from Dominic’s. You need a CROSS JOIN to multiply the other static values by the number of rows returned by the subquery so that you have a multiple row insert statement.

INSERT INTO contact_copy
WITH  cl AS
(SELECT   common_lookup_id AS contact_type
 FROM     common_lookup
 WHERE    common_lookup_type LIKE '%DAY RENTAL')
 SELECT   contact_s1.nextval
 ,        1001
 ,        cl.contact_type
 ,        'Doe'
 ,        'John'
 ,        NULL
 ,        3
 ,        SYSDATE
 ,        3
 ,        SYSDATE
 FROM     dual CROSS JOIN cl;

It turned into a longer discussion than I anticipated. Hope it helps solve problems and tune Oracle queries.