Oracle Stored Procedure Problems

[Guest]

Hello, I am calling an Oracle Stored Procedure. It has a column which is
defined as "Float". The Oracle procedure defines a parameter for this field
as "Float". In my program I create a parameter which is a Oracle DbType of
"Double". When I execute the procedure, it fails with an ORA 06502 and ORA
06512 and these error messages talk of not being able to convert propertly
from string to numeric.

When I look at the table, I see that my transaction did apply to the
database, inspite of the error message, but the Fields which is identified as
"float" and I defined a "Double" has the amount that I passed, but the value
truncates the last four decimal places - I passed 8 decimal positions but
only 4 remained.

Am I using the correct datatype in my program to describe a float value?
Any ideas as to why I am getting the Errors begin returned?
The output paramterters, when I create them in my program, - the "value"
portion of thos parameters, do they need be to initialized with "null" in the
"Value" property?

Thanks in advance for your assistance!!

 

[Roy Fine]

Jim

Can you show the code that you are using -- especially how you are setting
up the OracleComand object before calling the Execute method.

If you have set the data type and direction on the parameters that
correspond to the stored proc out parameters, then all *should* go well.

regards
roy fine

p/s/ -- included here is an examplethat i use from time to time:

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
/*
CREATE TABLE FLOAT_TEST(cid NUMBER,value FLOAT)
TABLESPACE devel_test
STORAGE (INITIAL 4K)
/
*/

/*
create or replace procedure GetValues(p_CurPos out INTEGER,
p_MinValue out FLOAT,
p_MaxValue out FLOAT) IS
BEGIN
p_CurPos := 1;
p_MinValue := 100.1;
p_MaxValue := 200.2;
END;
*/

namespace FloatTest
{
class Class1
{
static void Main(string[] args)
{
try
{
Console.WriteLine("Trying accessing stored procedure");
OracleConnection oConn = new OracleConnection("User
ID=Devel_Test;Password=Devel_Test");
oConn.Open();
OracleCommand oCmd = new OracleCommand("Begin GetValues1,:2,:3);
END;",oConn);
OracleParameter opPos =
oCmd.Parameters.Add("p1",OracleDbType.Int32,ParameterDirection.Output);

OracleParameter opMinVal =
oCmd.Parameters.Add("p2",OracleDbType.Single,ParameterDirection.Output);

OracleParameter opMaxVal =
oCmd.Parameters.Add("p3",OracleDbType.Single,ParameterDirection.Output);

oCmd.ExecuteNonQuery();
int m_curPos = (int)opPos.Value;
float m_MinVal = (float)opMinVal.Value;
float m_MaxVal = (float)opMaxVal.Value;
Console.WriteLine(" cur pos: {0}",m_curPos);
Console.WriteLine(" Min Val: {0}",m_MinVal);
Console.WriteLine(" Max Val: {0}",m_MaxVal);
}
catch(OracleException e) {
Console.WriteLine("Fatal Oracle Exception...");
Console.WriteLine("{0}",e.Message);
}

try
{
Console.WriteLine();
Console.WriteLine("Trying fetch from table");
OracleConnection oConn = new OracleConnection("User
ID=Devel_Test;Password=Devel_Test");
oConn.Open();
string cmdSqlTxt = "SELECT cid,value from FLOAT_TEST";
OracleCommand oCmd = new OracleCommand(cmdSqlTxt,oConn);
OracleDataReader odr = oCmd.ExecuteReader();
while(odr.Read())
{
float fltVal;
int cidVal;
Type tp;
tp = odr.GetFieldType(0); // should be oracle decimal
tp = odr.GetFieldType(1); // should be oracle decimal

// good - this works
cidVal = odr.GetOracleDecimal(0).ToInt32();
fltVal = odr.GetOracleDecimal(1).ToSingle();

// good - this works
cidVal = (int)odr.GetOracleDecimal(0);
fltVal = (float)odr.GetOracleDecimal(1);

// bad - this cast cannot be performed
//cidVal = odr.GetInt32(0);
//fltVal = odr.GetFloat(1);
Console.WriteLine(" Cid: {0}, Value: {1}",cidVal,fltVal);
}
odr.Close();
oCmd.Dispose();
oConn.Close();
oConn.Dispose();
}
catch(OracleException e) {
Console.WriteLine("Fatal Oracle Exception...");
Console.WriteLine("{0}",e.Message);
}
}
}
}

 

[Guest]

I discovered the "solution" to the problem.

I am calling generic routines create the "Oracle" command, but return the
"IDbCommand" version. I did this so thinking that I could implement any
database.

So when I executed the cmd.ExecuteNonQuery() it was with the IDbCommand and
not the OracleCommand object. When I re-classed the object to the
OracleCommand object, the command functioned just fine.

Seems kind of strange to me that the IDbCommand would not work. Does that
not seem odd?

 

[John Saunders]

I discovered the "solution" to the problem.

I am calling generic routines create the "Oracle" command, but return the
"IDbCommand" version. I did this so thinking that I could implement any
database.

So when I executed the cmd.ExecuteNonQuery() it was with the IDbCommand
and
not the OracleCommand object. When I re-classed the object to the
OracleCommand object, the command functioned just fine.

Seems kind of strange to me that the IDbCommand would not work. Does that
not seem odd?

Jim,

In Framework 1.0 and 1.1, the various *Command, *Connection, *DataAdapter,
etc. objects do not all derive from the same base classes. They're fixing
that in 2.0, so we can hope for more uniform behavior in the future.

John Saunders