Brothers In Code

I got bit by this old error in a new way the other day so I thought I should make a quick checklist for myself since I made some mistakes in my troubleshooting process. 

First I would start by reading How the Runtime Locates Assemblies.  However, unless you are depending one of the methods that change the normal process, like including additional "codebases" or publisher policy redirection, then we can widdle this down to a few simple causes:

• A Referenced Assembly is not in the Application's Path
• A Referenced Assembly is not in the Global Assembly Cache
• The Referenced Assembly is a different version than the installed assembly.

• The Application is 32 bit and the dll is 64 bit

The Old Problems - Local Files and the GAC

Many if not most applications rely on a simple xcopy deployment, meaning that all of the application's files will be bundled together in the same directory.  If you're simply copying or app folder from one place to another and it's not working on the new machine than either you simply missed a dll or you were using a GAC'd assembly on your development machine and didn't realize it.  If you're using a third party component that was installed with a setup program, than there's a good chance that your referenced assembly is in the GAC.  In that case you need to decide if you you want to run the third party install program as part of your deployment or figure out which dlls that you need and copy them to the folder.

GAC or otherwise, your app isn't going to find anything if it references a different version than what you have installed.  There are two options if realigning the deployed version isn't an option.  Either change "Specific Version" to false in the reference properties in Visual Studio or do a policy redirection.  I actually recommend the latter since big companies will include policies for the GAC that point old references to newer versions.  On the flip side, changing specific version to false will let your app load any version including an ancient one which might give you all sorts of strange errors.

 The New Problems - 32 Bit, 64 Bit and the Platform Target Configuration

It's the last cause that is the inspiration for this post since it has now bit me multiple times.  In my case i was referencing Oracle.DataAccess.dll from Oracle's data provider for .net.  ODP.net throws in some additional variables like PATH and "ORACLE_HOME" environment variable dependencies that threw me off correctly debugging the problem.  After double checking that the dll was in the GAC, and then even copying the dll to the app folder in desperation, I still was getting the error in question.  The error also included the phrase "or one of its dependencies" which kept doubling me back to a PATH problem.  In retrospect I really don't ever remember a case where this error was caused by a dependent dll so I'm not sure why I put so much stock in that route.

Finally I got my wits back and loaded a consistent savior, Sysinternals Process Monitor.  I don't know why I'm so slow to use this tool some times.  Maybe it's the 5 minutes you have to spend getting the filters right, but 9 times out of 10 it more or less tells me exactly what is wrong.  Sure enough I saw the program trying to find my dll in GAC_32.  Why is it looking for a 32 bit dll when we are on a 64 bit machine with the 64 bit oracle provider....you dumb-ass I thought to myself, knowing that I had seen this before.  Sure enough the task manager showed the infamous *32 next to my process.  I went back to visual studio and looked that the build properties for my exe project - once again Platform Target was set for "x86".  I set it to "Any CPU", recompiled and the stupid world was right again.

If you've reading this you've probably been to several sites already and are just about ready to kill something. Why Oracle couldn't print the name of the parameter that you attepted to bind is beyond me, but after all this is a company that was very recently recommending "locator varibles." I'll be honest and say there is no magic bullet here. Compared to Sql Server's Profiler, Oracle's statement tracing functionality is not developer friendly and the ODP client side tracing is pretty worthless. I can really only give some tips for things to look for and some brute force debugging tactics.

To be clear, this is with Oracle's data provider (Oracle.DataAccess.Client) and not Microsoft's Oracle provider (System.Data.OracleClient). The Microsoft provider is being deprecated so it is not recommended that you follow some of the older suggestions of simply switching providers.

In short, this error is a catch all for some sort of mismatch between the parameters referenced in the command text, and the parameters added to the command object. To start there's a couple of simple things to look for:

• Missing Comma - This one annoys the crap out of me. Forget a comma between constant values in an insert statement, and you'll get a nice "missing comma" error. However, forget a comma between bind variables and you'll get the error in question. My guess is that some genius decided to look only for an operator to terminate a bind variable and didn't consider whitespace.
• The number of parameters match - This one is an important one and an easy one. We just ran into a problem where a misnamed command variable was causing an extra parameter to be added that we werent seeing. I simple cmd.Parameters.Count in the watch window would have quickly told us there were more params in the collection than were in the statement.
• The order of the parameters match - Despite the warm fuzzy you might get when your parameter names match what is in your statement, those names do nothing by default. The default functionality is to bind the parameters in the order they are added. You can change this by setting OracleCommand.BindByName = true;
• Use OracleCommand.BindByName=true if you use the same parameter twice. This is a bit of assumption since I've not tested to confirm it, but I assume that because the default functionality is to bind by order, duplicated parameters would also need to be duplicated on the command object if BindByName is left as false.
• The type of the parameters match - The type parameter needs to match the type in the database. There is a little bit of flexibility with this with types like numerics (excluding overflow errors, OracleDbType.Int32 can be used in place of OracleDbType.Int64), but if you got lazy and defined parameter as OracleDbType.Varchar2 when you've got a Date column you might get this error. Generally I just use the Parameters.Add overload that takes variable name and value and let oracle decide on the type. This however does not work on output parameters - I set both the type and the length (at least for varchars) for those.

If the above eye-ball debugging doesn't work, I suggest that you start trimming down your statement and params until you get a successful execution and then work backwards from there. I know that's not much to go with but if I find something else I'll be sure to post it.

Everything seems to be turned of by default in Oracle.  That includes returning the details of an error in a sql script. 

After finishing a piece of code like a stored proc or trigger, most developers immediately run the script to make sure there are no errors.  Unfortunately this is all you'll see with Oracle:

Warning: Trigger created with compilation errors. 

There is, fortunately, a way to show the detail of those errors.  If you add a "/" to terminate the script and then "show errors;" to the end of you script.  You'll see the following instead:

Warning: Trigger created with compilation errors.

Errors for TRIGGER PROJECT_BIU:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/5  PL/SQL: SQL Statement ignored
3/12  PL/SQL: ORA-02289: sequence does not exist

Here's an example:

create or replace trigger PROJECT_BIU
before insert
on PROJECT
referencing old as old new as new
for each row

begin
    --create the id
    select ProjectNuber_seq.nextval
    into :new.Project_Number
    from dual;
end;
/
show errors;

EDIT: 7/28/2014

I should mention that I'm still using Data Modeler, despite all the shortcomings below.  It's ability to reconcile changes against previous versions of the model, script, or live db, and generate a script is really great.  This could be a great tool,  but there's stilll a lot to be desired.

My current employer primarily uses Oracle as their RDBMS which I have no problem with.  While I can't say I agree that it's a "better" database than Sql Server, I definately see it's strengths and it is certainly a powerful piece of software.  But that unfortunately stops with the database engine.  Oracle's developer tools are on the abysmal side.  They are chalked full of bugs and usability problems. 

Let's take Oracle's Data Modeler.  In typical Oracle fashion it's full of bells, knobs, and whisles.  In addition to the expected relational modeller it also contains a slew of tools aimed at the database geek.  The problem is they're so busy making this the ultimate do-every-thing tool, that they run out of time when it comes down to Usablility:

• You can't scroll left, right, up, or down beyond the farthest object in that direction.  If you want to add something to the perimeter of your erd, you need to add something and push it over a little at a time, and even that only works to the right or down.  Moving an object left or up runs it into a brick wall.
• Items in drop down lists like the pk/uk index selection for a constrain or sorted in created order.  Seriously, a database company failing to recognize a decent place for a simple sort?
• At first I thought there was no such thing as automatic, right angle connectors.  But I right clicked one day on an empty area of the diagram and selected "auto route".  I didn't find such a feature in the preferences.  Worse, I quickly realized this is a worthless feature since it doesn't allow you to fix them afterwards (and it does a pretty lousy job so they do need to be fixed).
• Doing things like adding columns requires way too many windows and steps:
  • Right click
  • Click Properties
  • Click Columns
  • Click the '+' button
  • Click the Type drop down and select
  • In visio, I just started typing the column name in the pane on the bottom.
• The properties box is modal-less, which means you can make changes outside of the properties box but you'll need to close it to refresh.
• The connectors are pretty dumb.  They stay stuck to the edge they were originally attached to even if that's the opposite edge.
• Inconsistent context menus.  Some things have them, but try to add a table or domain by right clicking?  Nope.  For tables you need you use the designer tool.  For domains you need to go to Tools > Domains Administration
• In Domains Administration, The Save Button doesn't enable after changing something.  You need to move to something else and wait for the "Do you want to save..." dialog.  After that, then you click save.
• An additional unique constraint that includes the PK, it is not shown for the PK.  It only shows "P".  Even worse, if the alternate key is composite, the other column looks like it's unique by itself.
• Mouse wheel only scrolls up and down.  No zoom or horizontal scroll.
• I just about had a seizure when I saw this color scheme:
  
• Very Confusing button sets - On many dialogs there will be a Save, Apply, and Close button.
• Custom design rules can only be deleted.  They cannot be deactivated.
• Design Rules is a modal dialog.  You can't leave it open while fix the errors that it lists.
• Want to rename a table?
  • Table Properties
  • Rename the table
  • Click OK
  • Back into table properties
  • Click Naming Rules
  • Click OK to rename the related constraints
  • Click OK
  • And, no you can't do the rename and the naming rules in the same step - it doesn't rename the primary key constraint until you close out of the properties window.
  • By the way, despite not having an option listed for columns, the naming rules are also going to rename your columns (which was great when my Parent_Object_Id, Child_Object_Id columns were renamed Object_Id and Object_Id1).
• No feedback - try deleting a column with a foreign key constraint.  Nothing will happen.  You have to delete the FK, click ok, then go back in a delete the column.
• Save As always defaults to your My Documents directory.  So if you want to save a copy as a different name but under the same directory, you better not forget to change the path
• Multiple periods in the file name are ignored.  So if you save an erd as erd1.2.dmd it will be saved as erd1 instead.
• As far as I can tell there is no way to change your subversion server url.  They give you a cute Edit "server" button that opens a text editor for other subversion parameters, but to change my server from a local repository to an online one, I had to rename repositories.xml under C:\Documents and Settings\brandon\Application Data\Oracle SQL Developer Data Modeler\system3.1.0.691\o.jdeveloper.subversion.11.1.1.4.37.59.48 and start again.
• Can't associate a DMD file - I've tried opening the program by clicking on a dmd file.  However, associating the dmd file extension to data modeler causes the dmd file to be opened in sql developer instead as an xml file??
• No indicator that current file has changes.  Asterisk, italics, anything would be nice to let me know that changes have been made but are not saved.
• CTRL-S doesn't save.  Ever other app on the planet, including Oracle's SQL Developer (so I know they got the memo), uses CTRL-S to save.  I thought I was saving, but of course with the item immediately above this one, I didn't realizing that I was doing CTRL-S just to stretch my index finger and not for any other purpuse.

I think everybody has had to do this a dozen times: return a row of data having a minimum value.  The first reaction is to do a subquery in the where clause or as a derived table:

select *
from scan s
join (
  select min(scan_date) as scan_date
  from scan 
) s_min on s.scan_date=s_min.scan_date
where rownum=1

A more performant solution is to use analytic functions:

select *

from (
  select s.*, row_number() over (order by s.scan_date) rn 
  from scan s) 
where rn = 1; 

However, I thought this was a relatively simple and database agnostic solution:

select s1.* 
from scan s1
left join scan s2 on s1.scan_date>s2.scan_date
where s2.scan_date is null
and rownum=1

This will return the row  where no other row in the same table is older - in other words, the oldest row.  This is probably the worst performer in the bunch but might be useful for an already complicated query.

Making a web request from oracle is as easy as:

select utl_http.request('www.google.com')
from dual;

But if you're making a request from Oracle, chances are you need to do a little work and not just google something so here's a little pl/sql code...

declare
  l_url varchar2(4000) := 'www.google.com';
  
  l_httpRequest   utl_http.req;
  l_httpResponse  utl_http.resp;
  l_tempResponse clob;
  l_buffer varchar2(32767);
begin  
  utl_http.set_detailed_excp_support (true);

  --setup the http connection
  l_httpRequest := UTL_HTTP.begin_request(l_url, 'GET','HTTP/1.1');

  l_httpResponse := utl_http.get_response(l_httpRequest);
  
  --write the response (in blocks) to a temporary clob
  dbms_lob.createtemporary(l_tempResponse, FALSE);
  BEGIN
    LOOP
       UTL_HTTP.read_text(l_httpResponse, l_buffer, 32767);
       dbms_lob.writeappend(l_tempResponse, length(l_buffer), l_buffer);
    END LOOP;
  EXCEPTION
    WHEN utl_http.end_of_body THEN NULL;
  END;
  UTL_HTTP.end_response(l_httpResponse);

  DBMS_OUTPUT.PUT_LINE('Response: ' || l_tempResponse);
end;
/

It would be a simple job to add a couple of query string parameters on to the URL.  But it's very likely you'll be sending data to some sort of a web service and then expecting a response.

Here's an example of a POST request.

  ....
  l_httpRequest := UTL_HTTP.begin_request(l_serviceUrl, 'POST','HTTP/1.1');
  utl_http.set_header(l_httpRequest, 'Content-Type', 'application/x-www-form-urlencoded');
  
  --these are likely constant for the application
  l_postData :=
    'x_login='|| 'xxxxxx' 
    || '&x_tran_key='|| 'yyyyyyy'
    || '&x_delim_data='|| 'TRUE'

   utl_http.set_header(l_httpRequest, 'Content-Length', length(l_postData));


  utl_http.write_text(l_httpRequest, l_postData);
  --get a reference to the response
  l_httpResponse := utl_http.get_response(l_httpRequest);
  ....

That's it.  In Part 2 I'll go over some of the finer points of SSL requests and the Oracle Wallet.

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

[^\x20-\x7F]

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 regular-expressions.info that explains some of the oracle differences.

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.

declare
  v_start timestamp := systimestamp;
  v_end timestamp;
  v_elapsed interval day to second(3);
  v_elapsed_ms number;
  i number;
begin
  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:');
  dbms_output.put_line(to_char(v_elapsed));
  dbms_output.put_line(to_char(v_elapsed_ms));
end;

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:

SELECT
 trunc(to_date('1-mar-2009','dd-mon-yyyy')) + NUMTODSINTERVAL(n,'day') AS Full_Date
 FROM (
 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'))
 );

I just got done wrestling with multiple versions of the oracle client.  We wanted to install an Asp.net app that uses the 11g ODP.net 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);

Inline TNSNAMES

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 = orahost1.company.com)(PORT = 1522))
          (ADDRESS = (PROTOCOL = TCP)(HOST = orahost2.company.com)(PORT = 1522))
          (LOAD_BALANCE = on)
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = MyTNSService.company.com)
            (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))
          )
        )
      ;
      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 (2.111.6.20 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:

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\2.111.7.20]
"PromotableTransaction"="local"

After that everything seems to be working.

P.S. 

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

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 .

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\odp.net\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 ODP.net supports the following configuration that should be a replacement for setting the path and oracle_home environment variables in code:

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

 See the following for more info:http://docs.oracle.com/html/E10927_01/InstallODP.htm