Sample program to run an arbitrary SQL

The goal
In the previous post we've discussed how to package .NET application with self-contained Oracle client so it can be copied to any machine and still be able to access Oracle.

An example provided there wasn't really bright: it was merely demonstrated proof of concept. In this post we'll develop a bit more sophisticated application to execute arbitrary SQL statements. The utility is not SqlPlus (which is good, world doesn't need another SqlPlus - and Oracle is already shipping it bundled with Instant Client), but has somewhat similar functionality:

• First parameter is user credentials and connect string: username/password@connect_string. Connect string can be in any form supported by Oracle Instant Client: ezConnect, full descriptor, TNS, or other.
  
• Second and other parameters are concatenated to a SQL statement.
  
• Only one statement is allowed (but you can pass PL/SQL block). If the statement is SELECT, returned rows are printed.
  
• Talking about PL/SQL blocks, DBMS_OUTPUT is not supported.
  
• We fetch all output to memory first to find proper column width. This makes the program unsuitable for large result sets.
  
• Return code on syntax error is 2; on runtime error 1, on success it is 0.
  

The implementation
It starts with standard header, parameters checking, and usage printing:

using System;
using System.Text;
using System.Collections.Generic;
using Oracle.DataAccess.Client;

namespace OraCmd {
  class Program {

    static int Main(string[] args) {

      /* Uncomment to ignore client machine's settings
     Environment.SetEnvironmentVariable("ORA_TZFILE", null);
     Environment.SetEnvironmentVariable("NLS_LANG", "AMERICAN_AMERICA.AL32UTF8");
     Environment.SetEnvironmentVariable("NLS_DATE_FORMAT", "DD-MON-RR");
     Environment.SetEnvironmentVariable("NLS_TIME_FORMAT", "HH.MI.SSXFF AM");
     Environment.SetEnvironmentVariable("NLS_TIMESTAMP_FORMAT", "DD-MON-RR HH.MI.SSXFF AM");
     Environment.SetEnvironmentVariable("NLS_TIMESTAMP_TZ_FORMAT", "DD-MON-RR HH.MI.SSXFF AM TZR");
      */

      if( args.Length < 2 ) {
        Console.WriteLine("Parameters: username/password[@connect_string] sql_command");
        Console.WriteLine("* For connect string format please see Oracle® 11.1 Call Interface Programmer's Guide, OCI: Introduction and Upgrading,");
        Console.WriteLine("  Instant Client Light (English) at http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28395/oci01int.htm#CHDCFHAC");
        Console.WriteLine("* All remaining parameters are concatentated to form sql command to execute");
        Console.WriteLine("  alternatively the command can be enclosed in double quotes. Only one command is allowed.");
        return 2;
      }

Then we parse parameters, build Sql statement to execute, and print them. Function parse_args() is defined later.

      string usr, pwd, ds_str;
      parse_args(args[0], out usr, out pwd, out ds_str);
      string conn_str = string.Format("User Id={0};Password={1};Data Source={2}", usr, pwd, ds_str);

      StringBuilder sb = new StringBuilder(args[1]);
      for( int i = 2; i < args.Length; i++ )
        sb.Append(' ').Append(args[i]);
      string sql = sb.ToString();

      Console.WriteLine("Connect: {0}", conn_str);
      Console.WriteLine("SQL:     {0}", sql);

Finally, we open connection and execute statement as SELECT getting back reader. If number of fields in the result set is zero, we assume the command wasn't SELECT and cleanup. If number of fields is positive, we print the result set.

      try {
        OracleConnection conn = new OracleConnection(conn_str);
        conn.Open();

        OracleCommand cmd = new OracleCommand(sql, conn);
        OracleDataReader rdr = cmd.ExecuteReader();
        if( /*rdr.HasRows &&*/ rdr.FieldCount > 0 )
          print_rows(rdr);

        rdr.Dispose();
        cmd.Dispose();
        conn.Dispose();
      } catch( Exception x ) {
        Console.WriteLine();
        Console.WriteLine(x.Message);
        if( x.InnerException != null )
          Console.WriteLine(x.InnerException.Message);
        Console.WriteLine(x.StackTrace);
        return 1;
      }

      // end of Main: exit success
      return 0;
    }

Function parse_args breaks credentials string into user name, password, and connect string. This could be more elegantly written in RegExp, but we don't bother:

    static void parse_args(string str, out string usr, out string pwd, out string ds_str) {
      string usr_pwd = usr = pwd = ds_str = string.Empty;
      int k = str.IndexOf('@');
      usr_pwd = str.Substring(0, k == -1 ? str.Length : k  );
      ds_str  = str.Substring(   k == -1 ? str.Length : k+1);
      if( usr_pwd.Length > 0 ) {
        k = usr_pwd.IndexOf('/');
        usr = usr_pwd.Substring(0, k == -1 ? str.Length : k  );
        pwd = usr_pwd.Substring(   k == -1 ? str.Length : k+1);
      }
    }

To work with result sets, we first define a helper struct for field definitions. We print strings left-aligned and all other values aligned to the right, hence the LeftAligned member:

    struct FieldDesc {
      public string Name;
      public int    MaxLength;
      public bool   LeftAligned;
    }

For result set printing we first determine field names and then store all values in a list. While fetching, we find maximum value length. Initial length is field name's to ensure names fit. Storing all values in a list is not too practical for real-world use, but sufficient for sample program like this.

    static void print_rows(OracleDataReader rdr) {

      FieldDesc[] Desc = new FieldDesc[rdr.FieldCount];
      for( int k = 0; k < rdr.FieldCount; k++ ) {
        Desc[k].Name = rdr.GetName(k);
        Desc[k].MaxLength = Desc[k].Name.Length;
        Desc[k].LeftAligned = rdr.GetFieldType(k) == typeof(string);
      }

      string[] vals;
      List Vals = new List();
      while( rdr.Read() ) {
        vals = new string[rdr.FieldCount];
        for( int k = 0; k < rdr.FieldCount; k++ ) {
          vals[k] = rdr.GetValue(k).ToString();
          if( vals[k].Length > Desc[k].MaxLength )
            Desc[k].MaxLength = vals[k].Length;
        }
        Vals.Add(vals);
      }

      const string titleSep = "-";
      const string colSep   = " ";

      // print field names
      Console.WriteLine();
      for(int k=0; k < Desc.Length; k++) {
        if( k > 0 )
          Console.Write(colSep);
        Console.Write(Desc[k].LeftAligned ? Desc[k].Name.PadRight(Desc[k].MaxLength) : Desc[k].Name.PadLeft(Desc[k].MaxLength));
      }

      // print title separator
      Console.WriteLine();
      for(int k=0; k < Desc.Length; k++) {
        if( k > 0 )
          Console.Write(colSep);
        Console.Write(titleSep.PadRight(Desc[k].MaxLength, titleSep[0]));
      }

      // print values
      foreach(string[] vls in Vals) {
        Console.WriteLine();
        for(int k=0; k < Desc.Length; k++) {
          if( k > 0 )
            Console.Write(colSep);
          Console.Write(Desc[k].LeftAligned ? vls[k].PadRight(Desc[k].MaxLength) : vls[k].PadLeft(Desc[k].MaxLength));
        }
      }

    }

  }
}

In nutshell, this is it. Package the application with Instant client, share over network, and gain access to any Oracle database in sight. Again, the program won't work from network share directly, it must be copied to local drive in order to find DLLs.

Test run

C:\Tmp> OraCmd scott/tiger@orahost10g/ora10 select rownum,rowid,level from dual connect by level ^<= 5
Connect: User Id=scott;Password=tiger;Data Source= orahost10g/ora10
SQL:     select rownum,rowid,level from dual connect by level <= 5

ROWNUM ROWID              LEVEL
------ ------------------ -----
     1 AAAADeAABAAAAZqAAA     1
     2 AAAADeAABAAAAZqAAA     2
     3 AAAADeAABAAAAZqAAA     3
     4 AAAADeAABAAAAZqAAA     4
     5 AAAADeAABAAAAZqAAA     5

Note how we had to mask the < character because it is Windows interpreter's special symbol. Alternatively, the entire SQL could be enclosed in double quotes.

Deploying ODP.NET with Oracle Instant Client

While ago I wrote about choosing right Data Provider to access Oracle. That time's choice was OleDB data provider from Microsoft.

A bit of history
Its main advantage was zero installation (the provider comes with .NET runtime) plus moderate functionality. On the downside it doesn't support SYSDBA connections, unable to configure fetch buffer size, and has no adequate method to retrieve Oracle error number from Exception. The provider still needs Oracle Client software to be installed and configured on the user's machine.

Oracle's .NET Provider came in 174M package and contained ODP.NET dlls along with full-blown client. At that time Oracle also shipped Instant Client which was "only" 80M in size, but required no installation. And the major pitfall was that ODP.NET didn't work with the Instant Client.

Finally in 2008 Oracle released production version of ODP.NET provider for NET 2.0 which could work over Instant Client. To developers, this means we can now enjoy all the luxuries of genuine ODP.NET and relax prerequisite requirements. We can deploy all components needed to access databases with XCOPY method without messing with client machine's registry or other settings.

What's discussed
In this post we will create a small Visual Studio project for program fetching sysdate from Oracle database. Connection settings will be hardcoded for simplicity. In the coming post we will write a complete program connecting to any Oracle database and running arbitrary SQL statement passed as a parameter - with no need for any Oracle software installed on the client.

Only 32-bit clients are discussed, 64-bit components for 11.x are not yet available at the moment of writing. Also we only bother about supporting English language.

ETL stands for Extract, Trash, and depLoy
Deployment files come from ODP.NET and Instant Client, so create a directory for extracted DLLs. They will be shipped to client as part of our XCOPY deployment.

First, we'll need to download ODP.NET provider. It is coming bundled along with other Oracle Data Access Components (ODAC) and available on Oracle OTN site. Registration is required to download software, but it is free and if you work with Oracle, registering at OTN is a good idea anyway. Get "With XCOPY deployment" version. At the moment the available version is 11.1.0.6.21, size 43M.

It comes in a ZIP archive, unpack it to temp directory. There is readme file there instructing to run install.bat but there is no need to do that. In subdirectories of ODP.NET20 locate and copy OraOps11w.dll and Oracle.DataAccess.dll to your deployment directory. You can erase downloaded file and unpacked directory now. Yes, we only need 2 files from the entire installation. They are ODP.NET provider, size ~1.3M.

Second, get Instant Client. Our provider is 11.1.0.6, but client can be of any version newer than 11.6. Right now version 1.1.0.7.0 is available. There are 2 versions listed at the top: Instant Client Package - Basic and Basic Lite. It is up to you what version to pick; ODP.NET works happily with both. Lite version is significantly smaller, but supports limited national languages and client charsets:

• It supports US7ASCII, WE8DEC, WE8ISO8859P1, WE8MSWIN1252, UTF8, AL16UTF16, and AL32UTF8 character sets.
  
• It can connect to databases with charsets US7ASCII, WE8DEC, WE8ISO8859P1, WE8MSWIN1252, WE8EBCDIC37C, WE8EBCDIC1047, UTF8, and AL32UTF8. All client-side messages are in English.
  

Basic is going to add ~105M to your distribution; Basic Lite is ~32M.

Upon downloading the client, copy 3 DLLs from it to our deployment directory. For Basic version they are: oci.dll, orannzsbb11.dll, and oraociei11.dll (111M). For Lite version they are oci.dll, orannzsbb11.dll, and oraociicus11.dll (~31M). Even though first 2 names are the same, file sizes differ - so don't mix up DLLs from different distributions.

In your deployment directory there should be 5 files now: 2 Data Provider DLLs and 3 Instant Client ones. This is all what's needed to access Oracle.

Writing test app
Create Visual Studio Console project and in Solution Explorer "Add Reference" from References subtree or Project context menu. Navigate to your deployment directory and select Oracle.DataAccess.dll. Make sure its "Copy Local" property is true (so file will be copied to output directory).



Now add remaining DLLs: right-click on project and "Add existing item". They also should be deployed to client, so set property "Copy to Output Directory" to "Copy if newer" for each item.



We are now ready to write simple test code:

using System;
using Oracle.DataAccess.Client;

namespace InstantClientApp {
 class Program {
   static void Main(string[] args) {
     OracleConnection conn = new OracleConnection("User Id=scott;Password=tiger;Data Source=machine_name/service_name");
     conn.Open();
     OracleCommand cmd = new OracleCommand("select sysdate from dual", conn);
     DateTime dtm = (DateTime)cmd.ExecuteScalar();
     Console.WriteLine("Database time is {0}", dtm.ToString("F"));
     cmd.Dispose();
     conn.Dispose();
   }
 }
}

Replace connection string parameters with valid values.

Build the project. Your Debug or Release directory should now contain 5 DLLs, the executable, and a couple of other now unneeded files generated by Visual Studio. This is it, a complete application packaged along with all what's needed to access Oracle, and ready to be deployed to the client. Try XCOPY deployment now: copy the files to another machine with no Oracle client installed, only .NET 2.0 runtime is needed. Run it, and if connection string is right, you should get database server time. Warning: it won't work from network share, you should really copy files to client's local drive.

C:\Tmp>dir
 Volume in drive C has no label.
 Volume Serial Number is 30A5-4F0E

 Directory of C:\Tmp

04/17/2009  10:00 AM            16,384 InstantClientApp.exe
10/01/2008  04:22 AM           520,192 oci.dll
12/20/2007  03:53 AM           917,504 Oracle.DataAccess.dll
09/18/2008  10:47 PM         1,130,496 orannzsbb11.dll
10/01/2008  04:59 AM        29,802,496 oraociicus11.dll
12/20/2007  04:02 AM           385,024 OraOps11w.dll
               6 File(s)     32,772,096 bytes

C:\Tmp>InstantClientApp.exe
Database time is Friday, April 17, 2009 9:09:09 AM

Connection string variations
You've probably noted we used EZCONNECT method to specify database: machine_name/service_name. This form was a short version of:

  [//]host[:port][/service_name]

Instant client also recognizes full descriptor format:

  "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=port#))(CONNECT_DATA=(SERVICE_NAME=service)))"

If Oracle Client software is already installed on the machine, Instant Client can employ TNSNAMES.ORA too. There are two methods to do that: either set environment variable TNS_ADMIN to path to network\admin directory, or set variable ORACLE_HOME to point to installation home and tnsnames.ora will be looked up in %ORACLE_HOME%\network\admin.

More exotic methods such as LDAP or LOCAL variable are also supported, but I haven't tried them.

Environment variables
There are several environment variables which may affect your application. To be completely isolated from machine's configuration, override them manually before opening connection:

     Environment.SetEnvironmentVariable("ORA_TZFILE", null);
     Environment.SetEnvironmentVariable("NLS_LANG", "AMERICAN_AMERICA.AL32UTF8");
     Environment.SetEnvironmentVariable("NLS_DATE_FORMAT", "DD-MON-RR");
     Environment.SetEnvironmentVariable("NLS_TIME_FORMAT", "HH.MI.SSXFF AM");
     Environment.SetEnvironmentVariable("NLS_TIMESTAMP_FORMAT", "DD-MON-RR HH.MI.SSXFF AM");
     Environment.SetEnvironmentVariable("NLS_TIMESTAMP_TZ_FORMAT", "DD-MON-RR HH.MI.SSXFF AM TZR");

Not sue if this is a complete set though.

Applicability
Will AlderPump switch to Instant Client? Right now it doesn't seem very likely. There are several reasons:

• The product's audience is DBAs or Oracle professionals who definitely have Oracle Client installed on their machines. Demand to have at least client software configured is not an obstacle for them.
  
• Switching from MS provider would involve code rewrite. Not major, but visible. And the only real feature missing is ability to connect AS SYSDBA which is not a good idea anyways.
  
• AlderPump 2.1 installation is 410K. Instant Client Lite is 32M. The numbers don't look quite right together.
  
• The technology may not be mature enough yet, there were too few versions released so far. It probably needs more polishing.
  

Having said that, I'll definitely will keep eye on it and who knows? One day the gains may outweigh the cons. I kind of hope they will, because feature-wise Oracle's ODP.NET is really good.


Referencs

1. OTN forum discussing deployment.
   
2. Section OCI Instant Client of Oracle® Call Interface Programmer's Guide.
   
3. OTN article Instant ODP.NET Deployment by Mark A. Williams.
   

In search for Data Provider

In .NET, access to Oracle in accomplished via so called Data Providers. To a developer - they are merely assemblies exposing objects and their methods to connect, run a query, traverse result set, etc. A few providers come with .NET; Oracle supplies their own; and a number of third party ones exist.

Picking a right data provider is essential for applications deployed to end users, there are several options to consider:


Third party providers do not quite fit for this project.

The product is a simple (at least from user perspective) self-contained utility. There should be as little external dependencies as possible, ideally zero. Shipping with a third party assembly may require extra configuration. And any bugs in their code will be seen as flaws of the application. Depending on other company's will to fix bugs may ruin product's creditability - I'm not taking this chance.

For reference, not all third party providers introduce extra dependencies. As an example, OraDirect for .NET reduces dependency to Oracle client software by offering a direct TCP database access (similarly to Java type-4 thin driver).


Oracle supplied provider does not fit for the same reason
To use the application, users must download a 450M Oracle client CD and install support for .NET. A bit of overkill for a little program like this. Think I'll be having hard time to convince people to install my program - not talking about registering on OTN, downloading the multi-meg file, unzipping it, running installer, navigating to .NET assembly and installing it. They'll forget the installer's option to pick by the time they get there.

I really regret this because Oracle provider is such a luxury. In addition to the basic features every provider has, it adds tons of useful functionality including a few critical options. Oracle Corporation really should stop thinking big projects and enterprise clients. [Added after July 11, 2007: Oracle just announced "Oracle 11" which supports instant client for .NET". This should address all Oracle Provider issues below - after it is released officially].


We are left with providers coming along with .NET framework. All of them work out of the box and need no extra configuration.

Microsoft Data Provider for Oracle
This was my choice previously, but this time it didn't work. Here is why:

• SYSDBA connections are not supported.
  Connect in SYSDBA is accomplished by adding "DBA Privilege=SYSDBA" to the connection string. To a surprise, attempts to do so yields "keyword is not supported" exception. Searched Google and yes, Microsoft provider does not support AS SYSDBA.
  
  I'm working on a system utility to be used by DBAs. There are big chances the DBAs would want to connect as SYS or other SYSDBA user - and what do I tell them? "Sorry, because of limitation of Microsoft Data Provider, SYSDBA connections are not supported"? People do not care who's fault it is, they'll just march away.
  

• Can't cancel running statements
  Not widely used feature, but crucial in my case. The application calls DataPump procedure get_status() waiting indefinitely for an event of interest to occur. Now, when user wants to stop or pause the job, we're ought to tell Oracle to do so by calling stop_job(). But we can't - we're in the middle of get_status call.
  
  One workaround is to invoke get_status() in non-blocking way. The procedure takes timeout parameter; we could poll database in a loop checking cancellation flag between the calls. The question is the value of the timeout interval. Make it too short, and program will trash the database (which is already busy pumping data). Make it too long, and user will loose patience waiting for response. Saved the idea for last resort.
  
  Another method is to abort the running query. OracleCommand class has method Cancel() which is a bit strange: it is not guaranteed to work - and doesn't. As if this wasn't enough, there is no indication of whenever cancellation succeeded. MSDN reference reads:

  "If there is nothing to cancel, nothing happens. However, if there is a command in process, and the attempt to cancel fails, no exception is generated."

  Way to go.
  

• There is no way to control fetch buffer size.
  When query is returning resultset, there is a number of exchanges between server and the client. Server fills a buffer and ships it to the client, indicating whenever more data is available. The client consumes the buffer and asks server for another chunk. This is called a roundtrip. The number of roundtrips depends on amount of data to retrieve (which we can not control) and buffer size. Obviously, the smaller the buffer, the more roundtrips are needed.
  
  How bad the roundtrips are? In one of the companies I worked for, a test fetch of 100,000 rows one row at a time, took 30 minutes. Changing buffer size to 5000 rows reduced the time to 2 seconds. There is no typo in the figures: the same query ran 900 times faster.
  
  Who controls the buffer size? The client program, by calling OCI function OCIAttrSet with either OCI_ATTR_PREFETCH_ROWS or OCI_ATTR_PREFETCH_MEMORY.
  
  How this function is exposed to us, end users and developers - depends on the client program or high-level library we're using. SqlPlus has parameter ARRAYSIZE. Oracle Export and Import utilities are using BUFFER. Informatica is using DatabaseArrayOperatorSize setting. Oracle provider for .NET offers property FetchSize in OracleCommand and OracleDataReader.
  
  Microsoft Data Provider for Oracle supplies nothing.
  
  This is really strange shortcoming, and I don't believe there is a technical reason for this.
  
  AlderPump does not fetch lots of data. Think the biggest result set comes from DATABASE_EXPORT_OBJECTS and similar tables - when creating a job.
  

Considering the above, the decision was to stick with OleDb provider.
The provider comes with .NET just as Microsoft Data Provider, and does not require configuration nor brings extra dependencies. Connecting AS SYSDBA works. Queries are cancelled as expected. The only limitation remaining is fetch buffer size.

So far I only bumped into one strange problem, but it was easy to fix:

When running certain anonymous PL/SQL blocks, the provider fails to bind output variables and input variables are replaced with literal values.

Here is sample program:

using System;
using System.Data;
using System.Data.OleDb;

public class test {
 public static void Main() {
   try {
     OleDbConnection conn = new OleDbConnection();
     conn.ConnectionString = "Provider=MSDAORA;User ID=scott;Password=tiger;Data Source=ora10g";
     conn.Open();
 
     OleDbCommand cmd = new OleDbCommand("", conn);
     cmd.CommandText = "declare n number; begin ? := 123; end;";

     cmd.Parameters.Add("?", OleDbType.Numeric).Direction = ParameterDirection.Output;
     //cmd.Parameters.Add("?", OleDbType.Numeric).Value = 321;
     Console.WriteLine("parameter added");
     cmd.ExecuteNonQuery();
     decimal d = (decimal)cmd.Parameters[0].Value;
     Console.WriteLine("result: {0}", d);
   } catch( Exception x ) {
     Console.WriteLine(x.Message);
   }
 }
}

The code is throwing Oracle exception "Not all variables bound".

After several hours trying to find a workaround, I almost accidentally enclosed the block into another begin/end pair - and it worked:

     cmd.CommandText = "begin declare n number; begin ? := 123; end; end;";

More problems may be lurking around, but so far OleDb provider is working.


And yes, the ODBC Data Provider.
I didn't test this one. ODBC requires an Oracle Driver to be installed (from Oracle, Microsoft or other party), and I don't think the driver is installed on every machine. Installing it requires either MDAC (Microsoft) or Oracle Client CD (Oracle). Nah.