On the way to AlderPump

AlderPump 2.2 released

2009-08-24T16:08:00.000-07:00

First update to publicly available release 2.1 is shipping now. While its official number is 2.2, the release has a major new feature: file management. Upgrade from 2.1 is free, existing licenses continue to work.

Oracle DataPump is a server component; hence it can't handle files on user machines or other servers. File Manager closes the gap by allowing files transfer between user workstation and database server machine. Other basic capabilities such as file deletion, renaming, etc are also present.

The biggest problem was directory listing. None of Oracle releases to date support directory browsing. One can read, write, delete, or rename files - but only whose names were known from elsewhere. There is no rational explanation to that. Security reasons - one might say? Nonsense. Limit listings to folders exposed via Directory objects, add BROWSE privilege to already existing READ and WRITE - and let us be as secure as we want.

Until this is available, there are few workarounds:

Listing server-side directories

First is Java stored procedure. Apparently Java code can browse files via java.io.File interfaces. We must ensure Java is installed on the database, compile a piece of Java code, create a PL/SQL wrapper around it, and finally grant JAVAUSERPRIV to the interested users. This is doable, but not so straightforward for developers of shrinkwrap software considering number of points of possible failure.

Another method is undocumented, but simpler. Oracle 10g+'s package DBMS_BACKUP_RESTORE has a procedure to list contents of directory. The package is not accessible by public, EXECUTE privilege must be explicitly granted to use it. The procedure populates an in-memory table which we can read. Interestingly, it not only lists contents of requested directory, but recursively dives into subdirectories and lists them too. This is better explained at Christopher Poole's page.
One may also consider capability of DBMS_SCHEDULER to execute OS commands. We could run dir and redirect its output to temp file, then parse it for file names. Again, from shrinkwrap software point of view, this is hell. Think about points of failure starting with scheduler jobs stuck (say, because job_queue_processes is 1 and job it is currently running got stuck - a real situation witnessed), recall all the OS-es out there and their variations of ls or dir, then mediate on where to write the temp file, finally think about formats of output. The method may work on a particular database with particular OS, but supporting any platform? Forget it.

AlderPump equally supports the first two methods i.e. Java and PL/SQL. Java has little advantage in terms of operations as it returns files sizes along with names; PL/SQL is simpler to configure, manage, and remove. It also runs on databases where Java is not present. Both methods with their advantages and disadvantages explained in greater detail on AlderProgs site.

But again, the entire idea of installing something on server side sucks. We live with it, but we are less than happy about it.

Reading and writing files

Another challenge was file copying. We couldn't read the entire file into big BLOB and transfer it to the client; DataPump files can easily span to gigabytes and reading them all to memory is not a good idea. So, slash them to chunks. But to read chunk, one must have file handle, and how to preserve the handle between calls? Pass it as a parameter you've said? Well, in 10g+ handle is not a single number it used to be in 9i, it is 3-field structure (see utl_file package). Worse yet, it is PL/SQL type, not Oracle type. Passing PL/SQL structures is not easy, especially with limitations of Microsoft provider for Oracle.

The only robust method is to pass file name and open/close file every time next chunk is read or written. Chunk size is limited to less than 32K. Reading 2G file would result in 64000 open/close operations not considering reads/writes themselves. Not very efficient.

AlderPump is using a hack: we pass PL/SQL in string unpacking it prior to any file operation. Should Oracle change the structure we are doomed, but the risk is measured.

As a side note, it is sad Oracle file handls are not atomic any more. There could be pretty complex structures behind them (and they are), but values exposed to users should be as simple as possible. Making it structure, especially one which can't be easily passed to client, effectively kills either performance or compatibility.

Working with remote databases

One of the features considered for implementation was working via database links. Indeed, if we can access a database, and that database has links to other databases - why can't we run AlderPump jobs there or at least manage files? As it tuned out, we can't.

What killed it was Oracle policy about types. Simply put, the fact that two types on different databases have the same name does not guarantee they are same type. Sounds logical, right? Yes, but implementation lacks forethought. Even though both types belong to SYS schema and database versions are the same up to patch level - stubborn type system still considers them different. DataPump uses types for job status, dumpfile info, and other purposes, unfortunately this effectively kills remote capabilities.

This type compatibility problem is very common, Oracle should really do something about it. Simplest coming to mind is hash, or checksum, or other sort of signature. "Sign" type with a key, then compare keys to ensure types are the same.

Installer

There was major rework on the installer. It can now install fresh version, upgrade 1.x and 2.1 to 2.2, or repair existing 2.2 installation. Uninstaller allso got smarter. Most of unsinstallers out there only clean out files they've created. AlderPump uninstaller also wipes out temporary ones such as sqlnet.log created by SqlNet on connection failure. It hunts down and removes saved job templates too - although this may be too obsessive. Finally, there is option to remove license, say to transfer it to another machine. Preserved licenses are picked up automatically on next install, they remain valid for all 2.x versions free of charge. Owners of 1.x versions can upgrade their licenses for free.

Next release

We are mostly done planning features for next release. It is scheduled to ship in 4 to 6 months, the rate at which AlderPump version are normally shipped. Like with this release, all 2.x licenses will continue to work and owners of 1.x (should any remain) may contact sales for free license upgrade. More details will be posted closer to release date.

AlderPump Lite will remain free for everybody although with limited features.

Sample program to run an arbitrary SQL

2009-04-22T12:31:00.000-07:00

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

2009-04-17T09:22:00.000-07:00

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

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

AlderPump is shipping

2009-04-09T14:01:00.000-07:00

After 7 months of development, a year of beta testing, and 3 more months of building around infrastructure, AlderPump is shipping. Its official site is http://alderprogs.com.

Publicly available version comes in two flavors: Professional and Lite. In Professional mode with all features are enabled. The mode is available for first 30 days for evaluation or after buying a license. Lite mode with limited functionality is free. In this mode only current user's jobs can be monitored and managed. For job creation, four single-page wizards are enabled. They are to create table and schema mode export and import jobs. Command line generation for expdp/impdp is also there.

Looking at this in retrospective I must say choosing DataPump for automation wasn't very bright idea. DataPump is a new product and has ahead long way to evolve. Its interface changed quite a bit from 10.1 to 10.2 - this is why AlderPump is not really supporting 10.1 beyond checking for some quirks. Some promised functionality didn't work till later patches. Oracle 11.1 brought in new changes although not too revolutionary.

DataPump interface is quite obscure. Say, from developer's perspective division into modes is purely artificial. What is the difference between export in SCHEMA and FULL mode with schema filter? Why prefer one to another? Can one perform FULL mode import from dump taken in TABLE mode? (the answer is btw yes). Why there is a parameter to replace tables but not other objects?

Fortunately for AlderPump, expdp and impdp also suffer from artificial limitations - such as inability to mix INCLUDE and EXCLUDE filters (perfectly allowed by the API), specifying more than one expression filter (again, no limit), or applying metadata remaps basing on object types.

Needless to say, these restrictions are no subject for AlderPump which allows anything the API has exposed. Very fortunate for AlderProgs :)

Anyways, AlderPump has sailed. It is surprising how much work shipping takes, but the work was [almost] always fun so far. And ahead lies the best part: drafting plans for the next release. The time to throw in wild ideas with no real obligations, time to try new things without real need to make them working, time to travel away and claim this boosts creativity.

Transparent ListBox

2007-09-08T16:58:00.000-07:00

Like most other transparent ListBox controls found in on the Internet, mine is not the true one. I cheated, making underlying control to expose its background as a bitmap - which is then used to paint ListBox background. This doesn't work for any container, just for the one I'm using. You will not find complete source here, just the essential bits.

What I wanted:
On a form, I have a gradient-filled panel hosting various controls, one of them is a data-bound ListBox showing progress messages. Because the ListBox occupies the majority of the panel's real estate, I wanted it to be transparent. Here is the final result (colors were adjusted to better show gradient fill):

The suggestions found on the Internet were only partially working: whatever method I tried had a little glitch here or there - but that little problem was enough to ruin the entire idea.

OnDrawItem approach:
First was "transparent background" approach: we set background color to transparent and voilà - everything is magically working. Not quite. In .NET's ListBox, setting background color to transparent is illegal; an exception is thrown when trying to assign a color with alpha less than 255.

There are at least two ways to make .NET bypass transparent backgrounds: either enable transparency in control's constructor, or override CreateParams method. Both ways require deriving our class from WinForm's ListBox:


protected override CreateParams CreateParams{
  get {
    CreateParams cp = base.CreateParams;
    cp.ExStyle |= 0x20; // WS_EX_TRANSPARENT
    return cp;
  }
}

Or, use SetStyle method in control's constructor:


public TransparentListBox(): base() {
  SetStyle(ControlStyles.SupportsTransparentBackColor, true);
  this.BackColor = Color.FromArgb(0, Color.Transparent);
}

This is where another gotcha is awaiting. In WinForms, transparency is not real. It is simulated by asking the control's form to paint its background in the rectangle where our control resides. Any intermediate controls are not painted. If we'd placed our transparent control on a Panel hosted within a TabPage, not the Panel nor TabPage is painted, only the underlying form.

Obviously, this is not too useful, in my case the ListBox is sitting on a gradient Panel.

The workaround is to bypass WinForms and paint item's background manually. This is achieved by setting DrawStyle to OwnerDrawFixed and implementing custom OnDrawItem method. Details aside, the method's skeleton would look similarly to:


protected override void OnDrawItem(DrawItemEventArgs e) {
  SolidBrush brush = new SolidBrush(this.ForeColor);
  string val = this.GetItemText(this.Items[e.Index]);
  e.Graphics.DrawString(val, this.Font, brush, e.Bounds.X, e.Bounds.Y);
  brush.Dispose();
}

Here we do not paint background thus letting our gradient panel to shine through.

Note the use of GetItemText rather than Items[e.Index].ToString(), that is because the control is data-bound.

This mostly works except for two things. Firstly, the text flickers when items are added. That is because ListBox painting routine first erases everything with BackColor before calling our OnDrawItem(). And secondly, when the number of items in ListBox is too small to fill it up entirely, the remaining bottom space is still filled up with BackColor. Did you think of making background transparent as described above? Won't help: the background has been filled up with BackColor already.

The first problem could be partially alleviated by setting ListBox BackColor to a color close to the gradient's background, so the flicker is not that noticeable.

The second one could be approached by setting DrawMode to OwnerDrawVariable and making the last item span to the bottom of the control. This would be handled in OnMeasureItem() method (which we must provide in Variable mode). Still, this won't work when ListBox is empty - and a possible solution is to never have it empty: always add an empty line and replace it with real text when it is first added.

I didn't try these ideas - my ListBox is data bound and the logic seems to get overcomplicated.

OnPaint approach:
Giving up on OnDrawItem() idea, we're left with the last resort: custom painting. This requires to override OnPaint() and OnPaintBackground() and draw the entire control's contents (at least its client area). To tell .NET we're paitning on our own, we set a few styles in the control's constructor:


public TransparentListBox(): base() {
  SetStyle(ControlStyles.OptimizedDoubleBuffer, true);
  SetStyle(ControlStyles.UserPaint, true);
  SetStyle(ControlStyles.AllPaintingInWmPaint, true);
  SetStyle(ControlStyles.ResizeRedraw, true);
  SetStyle(ControlStyles.Opaque, false);
}

UserPaint instructs .NET to call our OnPaint method and AllPaintingInWmPaint tells it to omit calls to OnPaintBackground(): we draw all ListBox items in OnPaint() and let non-covered underlying background to remain untouched. The method loops through all visible items (first visible item is determined by ListBox.TopIndex property).


protected override void OnPaint(PaintEventArgs e) {

  float x = this.ClientRectangle.X;
  float y = this.ClientRectangle.Y;

  SolidBrush sel_bg_brush = new SolidBrush(this.SelBackColor);
  SolidBrush fore_brush   = new SolidBrush(this.ForeColor);

  int cnt = this.Height / this.ItemHeight;

  for(int k = 0; k < cnt; k++ ) {
    int idx = k + this.TopIndex;
    if( idx >= this.Items.Count )
      break;

    bool selected = this.SelectedIndices.Contains(idx);

    if( selected )
      e.Graphics.FillRectangle(sel_bg_brush, x, y, this.ClientRectangle.Width, this.ItemHeight);

    fore_brush.Color = selected ? this.SelForeColor : this.ForeColor;
    string val = this.GetItemText(this.Items[idx]);
    e.Graphics.DrawString(val, this.Font, fore_brush, x, y);
    y += this.ItemHeight;
  }

  sel_bg_brush.Dispose();
  fore_brush.Dispose();
}

Custom properties SelForeColor and SelBackColor are exposed to Designer - to customize color of selected items. Since our mode is OwnerDrawFixed, we still override OnDrawItem(), but this time it just invalidates the control, making Windows to invoke our OnPaint(). One more important method to override is OnSelectedIndexChanged(), we call Refresh() there to immediately redraw the control (unlike Invalidate, where redraw is postponed till next Update). Without this, our selection bar does not get redrawn until selection changes the next time: it is always behind, showing the previously selected item


protected override void OnDrawItem(DrawItemEventArgs e)  { this.Invalidate(); }
protected override void OnSelectedIndexChanged(EventArgs e) { this.Refresh(); }

The code worked quite well, failing in only one point: the vertical scrollbar. The scrollbar gets painted by Windows at intervals which do not to seem to be related to control redrawns - and I couldn't find any way to force its redraw. Moreover, sometimes the bar is only partially painted: only slider is shown without the up/down buttons. While this is unacceptable, the behavour is only observed for data-bound controls. Finally, I ended up catching BindingSource's ListChanged event and adding items to the list manually. Since the ListBox is append-only, that was simple.

This is not the complete truth though. My ListBox's binding source is a subordinate to another BS. I had to catch changes to that parent BS as well and reload entire ListBox contents. Another improvement was to make ListBox auto-scroll when selected item is the last one. This way the list would auto-scroll unless user moved out of the last line. This is achieved by setting SelectedIndex to Items.Count-1, although this would add the item to selection rather than replace it for multi-selected ListBoxes. The code is irrelevant, I might provide it some other time.

BackgroundBitmap:
With the scrollbar workaround the solution worked, but I wanted more. While ListBox is placed immediately on gradient panel, everything works fine. But a couple of days later I ran out of screen space and decided to move the ListBox into a TabControl page. This changed immediate parent to TabControl and the transparency effect was lost.

The next idea was to give up on transparency and paint ListBox background from an image, saved by its any parent. The "parent" may not be immediate nor even host ListBox: we just tell the ListBox where to get its underlying image from.

For this to work, parent must expose its background image somehow: an interface was extracted to indicate so:


public interface IExposingBackground { Bitmap BackgroundBitmap(); }

Implementation is simple enough: since parent is drawing itself with OnPaint(), we modify it a bit to save image in a bitmap prior to drawing:


public class FancyPanel : ..., IExposingBackground {
  private Bitmap bgr_bmp;

  public Bitmap BackgroundBitmap() { return bgr_bmp; }

  private void init_bitmap(Graphics g) {
    if(    bgr_bmp == null
        || bgr_bmp.Width  != this.ClientRectangle.Width
        || bgr_bmp.Height != this.ClientRectangle.Height
    ) {
      if( bgr_bmp != null )
        bgr_bmp.Dispose();
      bgr_bmp = new Bitmap(this.ClientRectangle.Width, this.ClientRectangle.Height, g);
    }
  }

  private void OnPaint(PaintEventArgs e) {
    init_bitmap(e.Graphics);
    Graphics g = Graphics.FromImage(bgr_bmp);
    ... draw background on g ...
    e.Graphics.DrawImageUnscaledAndClipped(bgr_bmp, this.ClientRectangle);
    g.Dispose();
  }
}

The background bitmap works as a double buffer, therefore buffering provided by .NET can be disabled to conserve memory. This is achieved by calling SetStyle(ControlStyles.OptimizedDoubleBuffer, false) in the control's constructor.

Transparent ListBox can now use parent's bitmap to draw any part of its background:


private Control parentBgr;
private Point   offset_in_parent = new Point();

private void paint_background(Graphics g, Rectangle rect) {
  Bitmap bmp;
  if( ParentBackground != null &&
    (bmp = ((IExposingBackground)ParentBackground).BackgroundBitmap()) != null
  ) {
    g.DrawImage(bmp, rect,
      offset_in_parent.X + rect.X, offset_in_parent.Y + rect.Y,
      rect.Width, rect.Height,
      GraphicsUnit.Pixel
    );
  } else {
    Brush brush = new SolidBrush(this.BackColor);
    g.FillRectangle(brush, rect);
    brush.Dispose();
  }
}

Variable parentBgr (exposed via property ParentBackground) is set at design time.

There is one more thing though. Since our "background-exposing" parent control may not be the immediate one, it takes a bit of coding to properly compute our offset in it. Should the parent be immediate, our offset would be the one available in our Location property. But since there could be any number of controls between us and the parent, all these Locations must be added up to compute the proper offset. Two helper methods are used for this:


private void compute_offset() {
  Point loc = new Point(>this.Location.X, this.Location.Y);
  if( this.ParentBackground != null ) {
    Control p = this.Parent;
    while( p != null && p != this.ParentBackground ) {
      loc.Offset(p.Location);
      p = p.Parent;
    }
    if( p == null )
      return;
  }
  this.offset_in_parent = loc;
}

private void find_suitable_parent() {
  if( DesignMode && this.ParentBackground == null )
    for(Control p = this.Parent; p != null; p = p.Parent)
      if( p is IExposingBackground ) {
        this.ParentBackground = p;
        break;
      }
}

These methods are called whenever our Location changes, parent is changed, or our control is resized. All these handlers look similar, they call the base handler and then recompute the offset. Here is, for example, OnParentChanged:


protected override void OnParentChanged(EventArgs e) {
  base.OnParentChanged(e);
  find_suitable_parent();
  compute_offset();
}

The first procedure, compute_offset(), calculates our position within the parent. The second, find_suitable_parent, is only working in Designer mode. Whenever our ListBox is placed on a form, it re-computes its "background parent" - if not already set. The procedure is merely for programmer's convenience.

Other uses of BackgroundBitmap:
Having fancy panels expose their background turned out to be quite handy. Later on I wanted to add a transparent DataGridView. Working with DGV is much easier than with LisBox, which is "all or nothing" sort of thing: we can only override OnPaint thus taking full responsibility for painting the entire control.

Unlike ListBox, DGV allows us to handle every aspect of cell painting, we can override just a little bit and let the control do the rest. All I had to do is to override PaintBackground and OnRowPrepaint methods. Both methods looked very similar to paint_background() above, except that they called their base handlers in the "else" part.

I've also overrode OnScroll() (another method missing in ListBox, one must override WndProc to handle scrolls) to call the base handler and then Refresh() the DGV. That is because when scrolling, Windows copies unchanged portion of the screen up or down, and then asks our Paint routine to draw just the first or last line. This doesn't work for gradient backgrounds and Refresh() is taking care of it.

As a final touch, double buffering was enabled to eliminate flicker.

Even later, I added a transparent TabControl with very little pain. There, the main challenge was to make page headers sit on the right side of the control, but this is another story.

This post was written a while ago, but it took me ages to fight Blogger's editor. The silly thing won't preserve code formatting: whenever text is reopened, some "improvements" sneak through. For example, Blogger removes one space character in pre-formatted blocks whenever entry is closed/opened.

Preview looks ok, hopefully publishing won't garble formatting.

Obscurities in DataPump API: OPEN procedure

2007-07-30T09:39:00.000-07:00

A couple of weeks ago, I ranted on Oracle DataPump API. The post wasn't published because the wording needed polishing; I wanted emotions to calm down before posting it. Today I'm glad it wasn't published at that time: Oracle released version 11g and I'm happy to see some problems were addressed there. I'll publish the original post followed by 11g comments.

The original 10g post:

Don't know who projected Oracle DataPump API, but obviously these people didn't invest much brain in their work. Feels like they started with a robust vision, but as project's deadline approached, something has changed. Maybe their chief architect got replaced with a summer intern. Or perhaps they strengthened their team with a bunch of unexperienced new hires. Or maybe a desperate manager decided to keep team's spirit high by stuffing their fridges with beer.

Whatever the reason was, the results were demolishing. I'm trying to summarize today's findings, updating the series as new "discoveries" come up.

OPEN function

The definition is as follows:

DBMS_DATAPUMP.OPEN (
operation    IN VARCHAR2,
mode         IN VARCHAR2,
remote_link  IN VARCHAR2 DEFAULT NULL,
job_name     IN VARCHAR2 DEFAULT NULL,
version      IN VARCHAR2 DEFAULT 'COMPATIBLE'
compression  IN NUMBER DEFAULT KU$_COMPRESS_METADATA
) RETURN NUMBER;

"If you have a procedure with more than 5 parameters, you're probably missing some". Indeed. Parameter "compression" is so important, that programmers shall think of it every time they create a job. The choices are so broad, that we can't make up our mind. Yes, we want compression! No, we don't! Yes, we do! Developers spend hours on meetings and management schedules a golf session to decide whenever they want to use such an important option.

Get real. In 99.(9)% of the cases the fraction of metadata is so small, that nobody gives a dime. Everybody wants compression. Just turn it on and put it to a dusty corner - such as SET_PARAMETER() procedure.

Oh, wait - is it here because 11g will offer new compression mode - data compression? Still, everybody loves compression. Turn it on and move it away.

I will not rant on "mode" much. Perhaps implementation difficulties make it necessary to decide early in the game what kind of 5 exports we want. Perhaps the paradigm was inherited from old exp/imp. I don't know. All I know is that choosing mode imposes limitations on other API calls. More on this in metadata_filter section.

Comments after 11g release:

The comments are based on documentation published on OTN. Maybe the real package is different (this was the case with some API calls in the past) - 11g installation is still being downloaded, but specification of OPEN has changed: parameter COMPRESSION now belongs to SET_PARAMETER procedure.

<paranoid mode>Oracle is reading my mind !</paranoid mode>

Not quite. The default is still to compress metadata only.

Hope Oracle left old version of OPEN in the package to preserve compatibility. Removing it would break existing code (my code will be broken, good thing it is not yet released).

Later, after installing 11.1.0.6/Linux:
Parameter "Compression" is still in OPEN, it just gone undocumented.

Two C++ snippets

2007-07-18T18:53:00.000-07:00

These code fragments do not belong to AlderPump. I'm posting them here because they came handy few times in the past (about every 4 years), and every time I spent hours searching for them in the piles of files in my source code directories. Let the Internet store them now, hopefully they will be easier to find.

The first snippet parses SQL statement for bind variables. In Oracle, bind variables begin with colon and followed by variable name: INSERT INTO tbl(col1, col2, col3) VALUES(:val1, :val2, :val3). Here, val1, val2, and val3 are bind variables.

Function sqlNextBind() is called with an argument, pointing at the beginning of the statement. It returns pointer to the next bind variable or NULL at the end. To find next variable, call the function with the pointer returned by the previous call, plus one.

Literals (strings, enclosed in single quotes) are recognized and skipped, even for strings contain embedded quotes (by convention, such quotes are doubled).

SQL comments are recognized and skipped too: both /* multi-line */ and -- single line As with SQL, nested multi-line comments are not supported.

Function sqlCountBinds() demonstrates how to use sqlNextBind().

const char *sqlNextBind(const char *ptr) {
int lit = 0, cmt = 0;
for(; ptr && *ptr; ptr++) {
if( !cmt ) {
if( *ptr == '\'' ) lit = !lit;
if( !lit ) {
  if( ptr[0] == ':' ) break;
  if( ptr[0] == '-' && ptr[1] == '-' ) {
    ptr = strchr(ptr+2, '\n');
    if( !ptr ) break;
  } else if( ptr[0] == '/' && ptr[1] == '*' ) {
    ptr++;
    cmt = 1;
  }
}
} else if( ptr[0] == '*' && ptr[1] == '/' ) {
ptr++;
cmt = 0;
}
}

return ptr;
}

int sqlCountBinds(const char *stmt) {
  int cnt;
  const char *p = stmt-1;
  for(cnt=0; (p = sqlNextBind(p+1)) && p && *p; cnt++) /* do nothing */;
  return cnt;
}

Another snippet comes from unknown author. I found it 20 years ago in a program whose name is forgotten. Nevertheless the code had not rusted, it still works perfectly. The program matches string against a wildcard pattern, just like OS does when looking for files matching the mask. Recognized wildcards are '*' and '?', the comparison is case-insensitive. To make it case-sensitive, remove calls to tolower().


static bool matches(const char *pString, const char *pWild) {
register int  i;
register bool star;

new_segment:
star = false;
while( *pWild == '*') { star = true; pWild++; }

test_match:
for (i = 0; pWild[i] && (pWild[i] != '*'); i++) {
  if(tolower(pWild[i]) != tolower(pString[i])) {
      if( !pString[i] ) return false;
      if(  pWild[i] == '?' ) continue;
      if( !star) return false;
      pString++;
      goto test_match;
  }
}

if( pWild[i] == '*' ) {
  pString += i;
  pWild += i;
  goto new_segment;
}

if( !pString[i] ) return true;
if( i && pWild[i-1] == '*' ) return true;
if( !star ) return false;
pString++;
goto test_match;
}

PS: The indentation got partially lost during formatting. This blog editor is not well suited for code.

GUI: A wizard layout

2007-07-16T21:16:00.000-07:00

The back end part is somewhat complete. There are still glitches especially with job control, but it is mostly working. I'm concentrating on the GUI now. The program consists of two major forms: one to show running jobs and their progress, and another is job creation wizard.

To my surprise, .NET does not have facility to create wizards. Searching the Internet revealed several commercial and non commercial components, but I didn't like either of them much: some enforce the "standard" layouts which waste too much of valuable space. In others, every page is a separate form making them hard to cross-reference variables.

After all, the approach doesn't seem to work well in my case: working your way through wizards is like observing a big painting though a keyhole. We're guided through myriad of pages (some having a single check box on the entire page) and on page ten nobody remembers where it all started. Navigating back and forth is a joke: we unmark a checkbox on page two to find that this action has invalidated page five and all the data must be re-typed again.

In DataPump, there are several dozen parameters for user to set, most of them depend on each other. Because of these dependencies, a Wizard approach kind of makes sense, although putting every page on a separate form is out of question.

TabControl looks like a good starting point; but it needs polishing:

Navigation by clicking on tabs doesn't work well. There will be quite a few tabs and they are unlikely to fit on a single line. Multi-line tabs could alleviate the problem but not cure it: I'm planning to support job templates with every template located on a separate tab. With multi-line tabs I'll loose control on the program's appearance.

I'm planning to set tab labels style according to their state: valid/invalid, required/optional, and enabled/disabled. This computes to 8 different styles. But TabControl does not support style changes. Not only we can not color individual tabs, but we can not change the color at all!

Tab Control is extremely useful, but it has several serious limitations. Color of tabs band is not changeable; the tabs band can not be hidden; there is no page visibility property: to show/hide a tab one must add/remove the page from the container control; when tabs are located on the left or right, the text is always vertical. These things are so little, but .NET 3.0 is shipping, and they are still not there.

Hiding tabs band is simple enough, thanks to Mick Dohertys who published "Add a HideTabs property to turn on/off the Tabs" snippet. The tabs are visible at design mode and turned off at runtime. This causes all the text to jump up a bit, but most of my pages have a docked control which resizes itself to fill the extra space.

For navigation, I've put a tree view on left side of the form. Tree view adds ability to collapse branches thus making more space for items currently in use. This allows to extend program's functionality. Job templates will be grouped under top level "Templates" node.

Here is how it looks:

The tree view on the left shows status of every job component. Invalid items are red, required are bold, disabled are gray.

The ability to color TreeView items helps a lot - items requiring attention are easily spotted. I've also added "Consistency problems" page: it summarizes all inter-parameter issues. Setting a parameter to a conflicting value highlights the problem pages and adds a record to the consistency tab. This way users can spot the effect of their changes right away.

Btw, Joel has criticized such design in his "User Interface Design for Programmers" book. He says the tab control and tree view look disconnected: users have troubles linking them together. I have two excuses: firstly I hope users learned a bit or two since 1992; and secondly, the program is designed for Oracle DBAs who are familiar with computers and were able to learn much more complex concepts than a disjoined control.

Talking about usability, there is one thing I don't like about Files page: the order of controls. It is natural to have directory to come before file. But directory is limited to 30 characters, while file can be up to is 512. Current layout leaves more horizontal space to file name. Moving file below directory would bring Max Size field before File, making the order unnatural again.

The good thing typically missing in other GUI programs is immediate response. Problem items are highligted with pink background. And the text of "Add file" button is changing depending on the intended action. The text can read "Add log" or "Modify dump" or "Type mismatch" or other message.

The Directories dialog is also customized. I wanted to present not only directory name, but also its status (readable and/or writable) and the server path. This requires 3 columns, but WinForms ComboBox control only supports one. Fortunately, RemcoJVG posted a good example on how to extend the control. I rewrote the code in C# and tweaked it a bit exposing gradient colors as properties and computing text area more precisely. Now, the problem is that the control with its gradient brush and mouse navigation looks and behaves so good, that other built-in ComboBoxes do not look right any more. I'm ought to either replace all such controls to RemcoJVG's or let users suffer from the inconsistency.

In search for Data Provider

2007-07-04T21:21:00.000-07:00

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.