Brothers In Code

I recently ran into this error when trying to run a stored proc from a c# app.  Looking at all of values that I was passing in, as well as the declarations in the stored proc,
nothing was beyond size limit of the given variables.

OracleCommand cmd = new OracleCommand(@"Begin INSERT_CMDData(
    P_PROJECT_NO => :P_PROJECT_NO,
    P_JOB_NO => :P_JOB_NO,
    P_ACTIVITY_NO => :P_ACTIVITY_NO,
    P_CLOCK_TIME => Sysdate,
    P_TRX_TYPE => :P_TRX_TYPE,
    P_IP_ADDR => :P_IP_ADDR,
    P_RETURN_STRING => :P_RETURN_STRING
  ); End;".Replace(Environment.NewLine, " "));
      cmd.CommandType = CommandType.Text;
      #region Params
      cmd.Parameters.Add("p_project_no", OracleDbType.Varchar2).Value = projectNumber;
      cmd.Parameters.Add("p_job_no", OracleDbType.Varchar2).Value = jobNumber;
      cmd.Parameters.Add("p_activity_no", OracleDbType.Varchar2).Value = activityNumber;
      //cmd.Parameters.Add("p_clock_time", OracleDbType.Date).Value = clockTime;
      cmd.Parameters.Add("p_trx_type", OracleDbType.Varchar2).Value = timeClockTransactionType(transactionType);
      cmd.Parameters.Add("p_return_success", OracleDbType.Int32, ParameterDirection.Output);
      cmd.Parameters.Add("p_return_string", OracleDbType.Varchar2, ParameterDirection.Output);
      #endregion
      using (cmd.Connection = new OracleConnection(AppConfig.General.ConnectionString))
      {
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();

        Response response = new Response();
        response.Success = (Int32)(OracleDecimal)cmd.Parameters["p_return_success"].Value;
        response.DisplayMessage = cmd.Parameters["p_return_string"].Value.ToString();
        return response.DisplayMessage;
      }

The issue turned to be the return variables and not and not the inserted values.  I simply needed to explicitly add the size to returned varchar.

cmd.Parameters.Add("p_return_string", OracleDbType.Varchar2, ParameterDirection.Output);

cmd.Parameters.Add("p_return_string", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;

I recently picked up a solution that my brother had previously started and I was confounded by an error received during the pre-build events on a particular project.  The script was simple enough

@if $(SkipPlatformVerification) == true (

 echo "Warning Platform Verification Task is disabled"

) else (

 echo "PVT is Enabled"

)

He was checking an environment variable to alert whether or not the platform verification task was enabled.

I started the build and was presented with the error “The command "@if == true (

 echo "Warning Platform Verification Task is disabled"

) else (

 echo "PVT is Enabled"

)"
exited with code 255.

After a quick Google search I found that the very detailed “exited with code 255” can mean a myriad of things and would not point me towards a solution, it just let me know there was a problem with the script.

My first solution was obvious, I did not have the environment variable [SkipPlatformVerification] and it was failing to read a value thus throwing the error.  After adding the environment variable (and rebooting VS) and building again, it did indeed succeed this time.   Looking at the output is what I found interesting.  The statement appeared in the output as below.

@if true == true (

 echo "Warning Platform Verification Task is disabled"

) else (

 echo "PVT is Enabled"

)

"Warning Platform Verification Task is disabled"

Visual Studio apparently pre-evaluates the $(variable) syntax in the same way that javascript handles variables in that by the time the statement is evaluated at run time, the variable has been replaced by the variable value itself.   So in the case of our statement above, when I didn’t have the environment variable created the output looked like this.

@if  == true (…

… which is obviously a syntax error. 

However using the syntax ‘%Variable%’ is not pre-evaluated and looking at the build output shows the following.

@if %SkipPlatformVerification% == true (

 echo "Warning Platform Verification Task is disabled"

) else (

 echo "PVT is Enabled"

)

The simple solution to the problem is to treat everything as a string.  In the case of the instance where the environment variable is missing, the output looked like this and completed successfully.

@if '' == 'true' (

 echo "Warning Platform Verification Task is disabled"

) else (

 echo "PVT is Enabled"

)

While this ended up to ultimately be a simple syntax problem, I wanted to make note of the way Visual Studio handles the different variables for future reference.

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.

A quick google search will find you a ton of information on this topic.  Unfortunately the majority of them are simple path issues with a binding expression.  I had a somewhat unique issue from a very simple mistake.

I had the following ComboBox:

          <ComboBox Grid.Row="7" Grid.Column="1" Name="IptGpSalesPersonSlprsnid2" SelectedValue="{Binding Data.SLPersonId, ValidatesOnDataErrors=True}" ItemsSource="{Binding SalesPeople}" DisplayMemberPath="DisplayName" SelectedValuePath="SalesPersonId" />

This mostly did what I expected.  It displayed "DisplayName" for the entire list of objects, it showed "SalesPersonId" as the SelectedValue, it would update another TextBox with the SelectedValue, and the selection could be changed by changing the SelectedValue in that TextBox.  But for the life of me, it would not update the SLPersonId property on the "Data" object it was bound too. 

"Data" is of type DynamicObject that I'm using as a binding proxy.  I really didn't expect anything wrong with it since a TextBox bound to the same property would update the property without a problem.  Still I trolled the overridable properties of DynamicObject and ended up adding the default overrides for the Equals and TryConvert methods.  Sure enough, upon selecting a new item from the ComboBox, the debugger broke on "Equals."  To my surprise, base.Equals was actually returning false.  Why?  This should be true since this was a reference type and I was returning the same object....damn....there's my problem...

In my view-model, my data property looked like this:

    public DynamicProxy Data
    {
      get { return new DynamicProxy(this.data); }
    }

I quickly added that code as a test and never made a variable to hold the new DynamicProxy instance.

Why ComboBox checks the variable instances to see if they are the same, i don't know, but since i look at what I did as bad code anyway, it doesn't really matter :P.

B

I had a combo box was being displayed based on the the value of another control.  To call attention to it's appearance, I wanted it to "fly in" by animating its size.  Below is my WPF style.  There are a couple of things that I couldn't figure out.  First, having it "fly away" in the same fashion is out since it disappears before any animation takes place (although I have seen people do it in code behind).  Second, I'm not quite sure why I need the first setter value.  I seems like the one in the trigger should work but by itself, it wasn't enough.

    <Style TargetType="{x:Type ComboBox}" >
      <Setter Property="RenderTransform">
        <Setter.Value>
          <ScaleTransform ScaleX="0" ScaleY="0"/>
        </Setter.Value>
      </Setter>
      <Style.Triggers>
        <Trigger Property="Visibility" Value="Visible">
          <Trigger.EnterActions>
            <BeginStoryboard>
              <Storyboard>
                <DoubleAnimation Storyboard.TargetProperty="RenderTransform.ScaleX" To="1" Duration="00:00:2">
                  <DoubleAnimation.EasingFunction>
                    <PowerEase Power="3" EasingMode="EaseInOut"/>
                  </DoubleAnimation.EasingFunction>
                </DoubleAnimation>
                <DoubleAnimation Storyboard.TargetProperty="RenderTransform.ScaleY" To="1" Duration="00:00:2">
                  <DoubleAnimation.EasingFunction>
                    <PowerEase Power="3" EasingMode="EaseInOut"/>
                  </DoubleAnimation.EasingFunction>
                </DoubleAnimation>
              </Storyboard>
            </BeginStoryboard>
          </Trigger.EnterActions>
        </Trigger>
        <Trigger Property="Visibility" Value="Hidden">
          <Setter Property="RenderTransform">
            <Setter.Value>
              <ScaleTransform ScaleX="0" ScaleY="0"/>
            </Setter.Value>
          </Setter>
        </Trigger>
      </Style.Triggers>
    </Style>

I'm still pretty new to WPF so you can chalk this one up to inexperience.  I was trying to make a textbox scale in/scale out based on a trigger monitoring visibility.  My thought is I needed to set Storyboard.Target="RenderTransform" and Storyboard.TargetProperty="ScaleX" but doing so got me the above error.  The problem is that Target expects a dependency object and RenderTransform is an attached property.  The simple fix was just to set Storyboard.TargetProperty="RenderTransform.ScaleX.

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;