Brothers In Code

...a serious misallocation of .net resources

Regular Expressions In Oracle

More than once I've needed to find all non-ascii characters in a string.  In a .net regular expression this is relatively easy:


I thought that regex would port over reletively easily to Oracle - wrong.

First, I found more than one site reference the \x sequence.  However I just couldn't get it to work.

I then tried a brute force version:

[^A-Za-z 0-9 \.,\?''""!@#\$%\^&\*\(\)\-_=\;:<>\/\\\|\}\{\[\]`~]

To my surprise that didn't work either.  After struggling with it for a while I found the regex reference page from Oracle.  It states:

The following regular expression operators are allowed within the character list, any other metacharacters included in a character list lose their special meaning (are treated as literals):

  • Range operator '-'
  • POSIX character class [::]
  • POSIX collating sequence [. .]
  • POSIX character equivalence class [= =]

So in other words, character sets are pretty limited in what they can have in it.  If a backslash is treated as a literal and not as an escape sequence, then that explained why my brute force version didn't work - sort of.  I did eventually get it to work but for some reason I had to remove the ']' within the character class.  So that wasn't perfect either.

Finally I gave it one last shot.  Rather than the hex range, I tried the character range for the same values - from space to tilde (look at an ascii table of values and this will make sense).

[^ -~]

Finally this seemed to work as expected (keep in mind thought that character ranges are affected by the NLS_SORT variable - anything other than binaray, might yield unexpected results).  I ran the following query as a test:

select regexp_replace('This is a test '||chr(191), '[^ -~]', '$') from dual;

The only thing left was to handle non-printable chars like crlf and tabs.  I new the hex code was out so I generated the first ascii character with the chr function to replace the space:

select regexp_replace('This is a test crlf'||chr(10)|| 'blah', '[^' || chr(1) ||'-~]', '$') from dual;

Update: After I wrote this article I found a section on that explains some of the oracle differences.


Simple Stopwatch/Timer in Oracle

I needed to record the execution time in a stored proc.  I thought this would be straight forward until i realized the resolution on an oracle date datatype is only to the second.  Timestamp has the additional resolution (as well as timezone if  you want it).  Even cooler is that when you subtract two timestamps you get an interval datatype with the difference.  From there, however, I wanted to pass out the value in milliseconds, but that required each component of the interval to be extracted, multiplied, and added together.  Here's some sample code of the whole process.

  v_start timestamp := systimestamp;
  v_end timestamp;
  v_elapsed interval day to second(3);
  v_elapsed_ms number;
  i number;
  for i in 1..100000 loop
    v_elapsed_ms := 0;
  end loop;

  v_end := systimestamp;
  v_elapsed := v_end - v_start;
  v_elapsed_ms :=
    ( extract(hour from v_elapsed)* 3600 +
      extract(minute from v_elapsed)* 60 +
      extract(second from v_elapsed) ) * 1000;

  dbms_output.put_line('elapsed time:');

Oracle Indexes - Nulls and Function Based Indexes

I had a hard time creating a couple of indexes for some oracle tables.  Things I thought for sure would work in SQL Server just would not work in oracle.  Consider the following query:

  trunc(min(expected_ship_date)) as Expected_Ship_Date,
  trunc(min(actual_ship_date)) as First_Ship_Date,
  nullif(max(nvl(trunc(actual_ship_date), to_date('1-dec-9999'))),to_date('1-dec-9999')) as Last_Ship_Date,
  count(actual_ship_date) as Completed_Ship_Count,
  count(slh_label_batch_id) as Expected_Ship_Count
from ship_label
group by slh_label_batch_id
having min(trunc(expected_ship_date)) between trunc( to_date('1-mar-2009','dd-mon-yyyy')) and trunc(to_date('31-mar-2009','dd-mon-yyyy'))
order by min(expected_ship_date) desc

I thought i'd speed up the above with the following index:

create index ix_ship_label_batch on ship_label(slh_label_batch_id, expected_ship_date, actual_ship_date);

To my surprise, explain plan still said it was doing a full table scan.  This didn't make any sense to me - the index a "covering" index for this query, which means it shouldn't need the table at all.

After some time i descovered that oracle will exclude index rows that are all nulls.  Even still, I'm not looking for nulls, right?  Wrong, no nulls would make the group by return one less row.  The fix was to add one not null column to the index.  Unfortunately, there wasn't a single not null column on the table (ugghh).  The simple fix was to use Oracle's function-based indexing feature and index the result of a non-null function.  My new index was:

create index ix_ship_label_batch on epi_ship_label, label_batch_id, expected_ship_date, actual_ship_date, nvl(confirm_flag,'N'));


Cool Way to Generate a Date Dimension Table in Oracle

Say you want a weekly sales report grouped by each day and you want to show days with zero sales. Usually this is accomplished by creating and joining to a date table that has one row for each day. If you're going to be doing this report all the time or need additional groupings like quarter or fiscal year, than it makes sense to create this table. But sometimes I need to do this for one report in a database that doesn't already have this table. For oracle, Uli Bethke describes what I think is the best way to date. In short, it's clever use of oracle's 'connect by' clause to recursively join dual to itself. For example, the following would generate all days in march:

trunc(to_date('1-mar-2009','dd-mon-yyyy')) + NUMTODSINTERVAL(n,'day') AS Full_Date
select (level-1) n
from dual
connect by level-1 <= trunc(to_date('31-mar-2009','dd-mon-yyyy')) - trunc(to_date('1-mar-2009','dd-mon-yyyy'))

Executing SQL Plus Batches with Bind Variables from .Net

In an earlier post I talked about bind variables in SQL Plus and PL/SQL and only linked to an OTN forum post to show how these bind variables related to .net.  I didn't realize there were a couple of mistakes in that post so i thought I go thru the entire process here.

Using bind variables normally doesn't amount to much more than using the SQL parameters you're probably already familiar with.  The only difference I can think of is the use of a colon instead of the '@' character and that you only use the colon when using it in a parameterized query (you do not use it for stored proc params).  However, with very little SQL Plus experience, i found executing a batch of statements from .net a little tricky.

I suggest starting with a raw SQL Plus / PL/SQL batch and get that working.  In the following example, i wanted to call a stored proc with fewer params than it asked for which required a lookup in a separate table.

var badge_no varchar2(15);
var p_return_success number;
var p_return_string varchar2(4000);
exec :badge_no := '1234';
exec :p_return_success := -1
  l_myKey varchar(50);
  select rf_key into l_myKey from rf_security where badge_no=:badge_no;
    P_BADGE_NO => :badge_no,
    P_KEY => l_myKey,
    P_RETURN_SUCCESS => :p_return_success,
    P_RETURN_STRING => :p_return_string);
  when NO_DATA_FOUND then
    :p_return_success := 0;
    :p_return_string := 'User not found';
print badge_no
print p_return_success
print p_return_string

For those of you that aren't familiar with SQL Plus and PL/SQL, the PL/SQL portion of this starts with the 'declare' and ends with end; 

Note: One minor thing that bit me.  If you're using SQL Developer as your query tool, I found that it's a bit buggy with bind variables and output.  For example, I originally had the ':p_return_success := -1 ' inside of the 'begin' block.  That worked as expected in SQL Plus but not in SQL Developer (which is much easier to use).

When you add a parameter like this:

cmd.Parameters.Add("P_BADGE_NO", OracleDbType.Varchar2).Value = AuthHeader.Token.UserId.ToString();

That gets translated into 'var badge_no varchar2' by the provider.  So the statement trimmed down for .net is (assuming you're using Oracle.OracleClient from and not System.Data.OracleClient):

      OracleCommand cmd = new OracleCommand(
          l_myKey varchar(50);
          select rf_key into l_myKey from rf_security where badge_no=:badge_no;
            P_BADGE_NO => :badge_no,
            P_KEY => l_myKey,
            P_RETURN_SUCCESS => :p_return_success,
            P_RETURN_STRING => :p_return_string);
          when NO_DATA_FOUND then
            :p_return_success := 0;
            :p_return_string := 'User not found,';
      cmd.Parameters.Add("P_BADGE_NO", OracleDbType.Varchar2).Value = AuthHeader.Token.UserId.ToString();
      cmd.Parameters.Add("P_RETURN_SUCCESS", OracleDbType.Decimal).Direction = ParameterDirection.Output;
      cmd.Parameters.Add("P_RETURN_STRING", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;

      using (cmd.Connection = new OracleConnection(AppConfig.General.ConnectionString))

Notice that we left l_myKey variable in.  This variable is only used locally within the batch and we do not need it's value to go in or out.  That's why we declared it as pl/sql variable inside of the declare instead of a SQL Plus variable outside.

However, as is, this will blow up with the following error:

ORA-06550: line 1, column 1:
PLS-00103: Encountered the symbol "" when expecting one of the following

It turns out that the very old SQL Plus has a problem with the windows newline.  I just added a .Replace to my string and it worked from there.

      OracleCommand cmd = new OracleCommand(
          l_myKey varchar(50);
          select rf_key into l_myKey from rf_security where badge_no=:badge_no;
            P_BADGE_NO => :badge_no,
            P_KEY => l_myKey,
            P_RETURN_SUCCESS => :p_return_success,
            P_RETURN_STRING => :p_return_string);
          when NO_DATA_FOUND then
            :p_return_success := 0;
            :p_return_string := 'User not found,';
        ".Replace(Environment.NewLine, " "));

 Good Luck,


Oracle xcopy deployment for

I just got done wrestling with multiple versions of the oracle client.  We wanted to install an app that uses the 11g install on a server that already had a 10g client installed.  While 11g and 10gR2 are supposed to have decent support for multiple oracle homes, the 10g client that was installed was only R1.  To make matters worse, the app using the 10g client was an old asp app via an ODBC dsn.  So rather than risking messing up the existing asp apps, I decided to give the xcopy deployment a shot. 

Getting the actual software setup is pretty well documented with an article on Oracle's website.  It really came down to just unzipping the package and running:

install.bat odp.net20 c:\oracle\product\1.1.0-xcopy-dep odac11_xcopy

However, my whole point in doing this was to avoid polluting the existing environment so a couple of items were out.   First of all, the last step in the article is to add a couple of environment variables.  Because I didn't want to interfer with the oracle home of an existing client install, i wanted to avoid this.  However without it, I got a nice exception: "Oracle.DataAccess.Client.OracleException The provider is not compatible with the version of Oracle client"

The fix was to add the following to my Application_Start event in the global.asax:

Environment.SetEnvironmentVariable("PATH", @"c:\oracle\product\1.1.0-xcopy-dep;c:\oracle\product\1.1.0-xcopy-dep\bin;", EnvironmentVariableTarget.Process);
Environment.SetEnvironmentVariable("ORACLE_HOME", @"c:\oracle\product\1.1.0-xcopy-dep;", EnvironmentVariableTarget.Process);


This got me to an oracle error that I recognized: "Oracle.DataAccess.Client.OracleException ORA-12154: TNS:could not resolve the connect identifier specified."  This is usally fixed by changing tnsnames.ora but in this case it didn't work.  While it might be possible to use tnsnames with this xcopy deployment, I decided on a different route.  I could have used the host/service name format in the connection string, but this oracle environment is setup with RAC (clustering).  The oracle driver has a nice feature where the tns infomation can be inline in the connection string. 
So this:"Data Source=MyTNSName;User Id=user;Password=pwd;"
became this (which is right out of my tnsnames.ora):

 "Data Source=  (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = = 1522))
          (ADDRESS = (PROTOCOL = TCP)(HOST = = 1522))
          (LOAD_BALANCE = on)
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME =
      User Id=user;Password=pwd;"

Bypassing Distributed Transactions and the OracleMTSRecoveryService

That nearly got everything working.  The only thing left was a particular block of code that I had wrapped in a System.Transactions.TransactionScope block.  This normally requires the OraceMTSRecoveryService.  This is something that is installable with the xcopy deployment (run install.bat with no params for usage), but this would have overwritten an existing version of the service if it was already there.  Instead I decided to force the transactions to local only (it was only a single call to the db so I knew I wouldn't need a distributed transaction).  This could be done by adding "Promotable Transactions=local" to the connection string but I'm compiling against an older version of the driver ( x64) in order to support both 64 and 32 bit with the same code and this option wasn't supported in this version.  Instead I changed the following registry key:


After that everything seems to be working.


Consider using a config change instead of a registry change with:
      <add name="PromotableTransaction" value="local"/>

Reason? I forgot to change the registry fix back and fought with "there was an error promoting the transaction to a distributed transaction oracle" for a day before I figured it out Embarassed.

5/17/2011 - Update:

I've done this again with the 4.0 provider and tnsnames.ora worked fine.  I think the first time I did this I didn't also update sqlnet.ora to use tnsnames with names.directory_path=(TNSNAMES,EZCONNECT). 

Another thing I don't remember doing in the 2.0 version was using the xcopydir\\bin\4\oraprovcfg utility.  I needed to put Oracle.DataAccess.dll in the GAC, but doing it manually didn't work.  I had to run oraprovcfg /action:gac /providerpath:<fullpath to Oracle.DataAccess.dll>

Last, I've also discovered that supports the following configuration that should be a replacement for setting the path and oracle_home environment variables in code:

    <add key="DllPath" value="c:\oracle\product\1.1.0-xcopy-dep\BIN"/>

 See the following for more info:

SQL Plus vs. PL/SQL and binding variables between the two.

I had a lightbulb moment yesterday.  My new employer is mostly an oracle shop so I end up spending a lot of time translating what I know about sql server into oracle terms.  But one thing I've struggled with is SQL Plus vs. PL/SQL.  That is until yesterday.   Maybe I never took the time before but a sequence of searches forced me in the right direction.

I'm using very few stored proceedures lately (reasons for which will come in a different post).  Consequently I was looking for ways to execute a batch of statements .net code with the Oracle Data Provider and found my answer here on OTN.  But then i looked at how he was getting variable data out of the command.  He was basically doing what I've always wanted to do with Oracle's SQL Developer.  When using the Sql Server query tools (query analyzer, SSMS), I'd often write myself maintenance procedures that I just kept in a file.  It's no problem to create variables and run sql statements in the exact same way you would from inside of a stored procedure.  Sometimes I could copy nearly the same code into a SqlCommand and it would run just fine.  Oracle isn't like that.  Variables (pl/sql variables at least) must be declared within the 'declare' section of a begin/end block.  These blocks can be 'anonymous' (not in a stored procedure) but once you enter the block things change.  You can't simply do a 'select column from table' for example or you get an 'INTO clause expected' error.  Oracle assumes that you're now in a programming block and not connected to some user interface so it wants you to store the data in some variable.  But how was this .net code getting this v_maxnum variable out?  On some sort of protocol level there must be a way for a human to do the exact same thing.

Finally thanks to Wikipedia, I got it.  SQL Plus is very similar to the combination of cmd.exe and SqlCmd/isql?  The big difference is that line between sqlcmd and t-sql is blurred a little more blurred than it is with SQL Plus and pl/sql.  But once I dug in to sqlcmd a little I realized that other than begin/end blocks it's really not that different.  I guess I really never used sqlcmd for anything but a single statement from the command line.

The big realization came now when I understood the variable types in SQL Plus.  It has both substitution variables (&var and &&var) and regular variables (var myvar varchar2).  But unlike sqlcmd's variables being t-sql variables, SQL Plus's variables are different than PL/SQL variables.  However, PL/SQL can 'bind' SQL Plus variables.  Take the following example.

var myDate varchar2;
  select sysdate into :myDate from dual;
print myDate

Now I finally understood what the data provider was doing and consequently I understood SQL Plus a lot better too.