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.