Overloading Methods Must Be New To Oracle!
After loosing almost two days of work after getting the following error, I found what the real issue was.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
The issue is being the code optimizer that I am, I wrote the following line of code to create an out parameter:
OracleParameter parameterModuleID = new OracleParameter("@o_someoutdata", OracleDbType.Int32, 4, ParameterDirection.Output);
I based this decision on the intellisense I got from the Oracle object below:
As you can see, the third parameter is the length of the parameters. This is very important for out parameters because it sets up a buffer. After almost two days of not understanding why the stored procedure was not working, checking and rechecking the parameters lengths about ten times and banging my head against the desk… I found the problem.
Enter the genius of Oracle method overloading:
As you can see, the third parameter is defined as object and is actually the value of the parameter! WHAT? Doesn’t Oracle know that every type is basically an object in .NET and they have screwed this up? What morons!
The Solution
Go back to SQL Server! Well, if you cannot do that, the Microsoft Oracle namespace does not suffer from this stupid mistake, only the namespace from Oracle. The other solution is just to define the parameter on two lines:
OracleParameter parameterModuleID = new OracleParameter("@o_ someoutdata", OracleDbType.Int32, 4);
parameterModuleID.Direction = ParameterDirection.Output;
Alternatively, just use this overloaded method:
OracleParameter parameterModuleID = new OracleParameter("@o_someoutdata", OracleDbType.Int32, 4,
DBNull.Value, ParameterDirection.Output);
The forth parameter “null” is the value of the parameter. Have fun!
Lockup’s Rule My Life Now
Another thing I have noticed is that my computer locks up three or more times a day now since I have started to use the Oracle namespace! For some reason I am suspecting that it is pegging out the CPU via the aspnet_wp.exe process. I am converting an ASP.NET portal application from SQL Server to Oracle. This did not start happening until I started to implement the Oracle code. It is not just me! The two other people where I work using the Oracle namespace are having the same issue.
Order DOES Matter?
Here is a gotcha, when adding parameters to the command object with the Oracle namespace, they must be in the same order as defined in the stored procedure!?!? Get just one out of order and you will not get back the expected results. What is the point in naming the parameters then? SQL Server does not work this way, you can send in the parameters any way that you want... as long as they are all there and named correctly.
Oracle Stored Procedures are Impotent
Stored procedure names in Oracle can only be 30 characters long while SQL Server allows for 128 characters. I hope to never see a stored procedure 128 characters in length, but 30 characters is way too short to name many stored procedures descriptively and easy to read (no abbreviations)!
Me No Like GUID’s
When the SQL Server database we are using was converted to Oracle, a few tables had a column in it with a GUID (not used as a unique identifier) type (uniqueidentifier in SQL Server). Well, come to find out Oracle does not have any knowledge of GUID’s, so on the Oracle side the data type of those columns were changed to RAW. While this holds the GUID just fine, using the GUID is not so easy.
When I get the data out as a DataSet, it looks like this (when I output my DataSet as xml):
<SOMEGUID>+FTyMzclhkSpHehUTUByAA==</SOMEGUID>
It should really look like this:
<SOMEGUID>CE55A821-2449-4903-BA1A-EC16DB93F8DB</SOMEGUID>
As you can see, the GUID from Oracle is useless. What to do? Well, not knowing how this can be fixed on the Oracle side (no one else where I worked seemed to know either), I wrote nifty little method that will fix a table.
public static void ConvertGuidColumns(DataTable data, params string[] columnNames)
{
foreach (DataRow row in data.Rows)
{
foreach(string columnName in columnNames)
{
try
{
row[columnName] = new Guid((byte[])row[columnName]);
}
catch
{}
}
}
}
This simply takes in a DataTable and a list of column names. Loops though the rows and converts any column from the Oracle RAW (really a Byte array) to a GUID .NET can use.
You need to also watch out when saving GUID’s to the database via a stored procedure. This won’t work:
OracleParameter parameterGUID = new OracleParameter("@I_SOMEGUID", OracleDbType.Raw);
parameterGUID.Value = someGUID;
This will:
OracleParameter parameterGUID = new OracleParameter("@I_SOMEGUID", OracleDbType.Raw);
parameterGUID.Value = someGUID.ToByteArray();
Tip By: David McCarter