Uses: Oracle9i Standard Edition, ODAC 11.2.0.4, Oracle Instant Client 11.2.0.4
I'm converting my projects from Microsoft's Oracle Provider to Oracle's ODP.NET
The Error
Unable to cast object of type 'Oracle.DataAccess.Types.OracleDecimal' to type 'System.IConvertible'.Couldn't store <145982> in RECEIPTID Column. Expected type is Int64.
This error is poped up when my Stored Proc has a return parameter. The return is of type Number in Oracle. I had no difficulties while using Microsoft's Oracle Provider.
Originally I had my Command Parameter set to System.Data.OracleClient.OracleType.Number
for Column RECEIPTID
; after inclusion of ODP.NET I converted it to Oracle.DataAccess.Client.OraclDbType.Double
. Then the error throwed up. Change to Oracle.DataAccess.Client.OracleDbType.Int64
as the error suggests but nothing seems to keep the error a way.
I am executing using OracleDataAdapter
.
The Initialization of OracleCommand
public class ReceiptAccountBusinessTable : EquityBroker32.APP_DATA.config.GenericBusinessTable
{
OracleCommand[] nCommandCollection;
public ReceiptAccountBusinessTable()
{
this.Tablename = "JKSBSCHEMA.RECEIPTACCOUNT";
this.KeyDBField = "RECEIPTID";
this.DefaultSql = string.Format("SELECT * FROM {0} WHERE {1}= (SELECT MAX({1}) FROM {0} )", this.Tablename, this.KeyDBField);
//select command
this.nCommandCollection = new Oracle.DataAccess.Client.OracleCommand[4];
this.nCommandCollection[0] = new OracleCommand();
this.nCommandCollection[0].Connection = this.DataConnection;
this.nCommandCollection[0].CommandText = this.DefaultSql;
this.nCommandCollection[0].CommandType = System.Data.CommandType.Text;
//update command
this.nCommandCollection[1] = new OracleCommand();
this.nCommandCollection[1].Connection = this.DataConnection;
this.nCommandCollection[1].CommandText = "JKSBSCHEMA.RECEIPTACCOUNT_ADDCHG_PROC";
this.nCommandCollection[1].BindByName = true;
this.nCommandCollection[1].CommandType = System.Data.CommandType.StoredProcedure;
this.nCommandCollection[1].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PRECEIPTID", Oracle.DataAccess.Client.OracleDbType.Int64, 10, System.Data.ParameterDirection.InputOutput, false, 10, 0, "RECEIPTID", System.Data.DataRowVersion.Current, null));
this.nCommandCollection[1].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PACCOUNTID_NEW", Oracle.DataAccess.Client.OracleDbType.Char, 13, System.Data.ParameterDirection.Input, false, 0, 0, "CLIENTACCOUNTID", System.Data.DataRowVersion.Current, null));
this.nCommandCollection[1].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PACCOUNTID_OLD", Oracle.DataAccess.Client.OracleDbType.Char, 13, System.Data.ParameterDirection.Input, false, 0, 0, "CLIENTACCOUNTID", System.Data.DataRowVersion.Original, null));
this.nCommandCollection[1].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PXSACTVALUE", Oracle.DataAccess.Client.OracleDbType.Double, 22, System.Data.ParameterDirection.Input, false, 0, 0, "XSACTVALUE", System.Data.DataRowVersion.Current, null));
this.nCommandCollection[1].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PXSACTDESC", Oracle.DataAccess.Client.OracleDbType.Varchar2, 255, System.Data.ParameterDirection.Input, false, 0, 0, "XSACTDESC", System.Data.DataRowVersion.Current, null));
this.nCommandCollection[1].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PPAGEBREAK", Oracle.DataAccess.Client.OracleDbType.Int32, 22, System.Data.ParameterDirection.Input, false, 0, 0, "PAGEBREAK", System.Data.DataRowVersion.Current, null));
//insert command
this.nCommandCollection[2] = new OracleCommand();
this.nCommandCollection[2].Connection = this.DataConnection;
this.nCommandCollection[2].CommandText = "JKSBSCHEMA.RECEIPTACCOUNT_ADDCHG_PROC";
this.nCommandCollection[2].CommandType = System.Data.CommandType.StoredProcedure;
this.nCommandCollection[2].BindByName = true;
this.nCommandCollection[2].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PRECEIPTID", Oracle.DataAccess.Client.OracleDbType.Int64, 10, System.Data.ParameterDirection.InputOutput, false, 10, 0, "RECEIPTID", System.Data.DataRowVersion.Proposed, null));
this.nCommandCollection[2].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PACCOUNTID_NEW", Oracle.DataAccess.Client.OracleDbType.Char, 13, System.Data.ParameterDirection.Input, false, 0, 0, "CLIENTACCOUNTID", System.Data.DataRowVersion.Current, null));
this.nCommandCollection[2].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PACCOUNTID_OLD", Oracle.DataAccess.Client.OracleDbType.Char, 13, System.Data.ParameterDirection.Input, false, 0, 0, "CLIENTACCOUNTID", System.Data.DataRowVersion.Original, null));
this.nCommandCollection[2].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PXSACTVALUE", Oracle.DataAccess.Client.OracleDbType.Double, 22, System.Data.ParameterDirection.Input, false, 0, 0, "XSACTVALUE", System.Data.DataRowVersion.Current, null));
this.nCommandCollection[2].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PXSACTDESC", Oracle.DataAccess.Client.OracleDbType.Varchar2, 255, System.Data.ParameterDirection.Input, false, 0, 0, "XSACTDESC", System.Data.DataRowVersion.Current, null));
this.nCommandCollection[2].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PPAGEBREAK", Oracle.DataAccess.Client.OracleDbType.Int32, 22, System.Data.ParameterDirection.Input, false, 0, 0, "PAGEBREAK", System.Data.DataRowVersion.Current, null));
//delete command
this.nCommandCollection[3] = new OracleCommand();
this.nCommandCollection[3].Connection = this.DataConnection;
this.nCommandCollection[3].CommandText = "DELETE FROM " + this.Tablename + " WHERE RECEIPTID=:p1 AND ACCOUNTID=RPAD(:p2,15)";
this.nCommandCollection[3].CommandType = System.Data.CommandType.Text;
this.nCommandCollection[3].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("p1", Oracle.DataAccess.Client.OracleDbType.Int64, 22, System.Data.ParameterDirection.Input, false, 0, 0, "RECEIPTID", System.Data.DataRowVersion.Current, null));
this.nCommandCollection[3].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("p2", Oracle.DataAccess.Client.OracleDbType.Char, 15, System.Data.ParameterDirection.Input, false, 0, 0, "ACCOUNTID", System.Data.DataRowVersion.Current, null));
this.CommandCollection = this.nCommandCollection;
}
}
The glimpse of the Stored Proc
PROCEDURE RECEIPT_ADDCHG_PROC
(
preceiptid IN OUT NUMBER,
preceiptmode IN CHAR,
pxsactdate IN DATE,
pxsactvalue IN NUMBER,
pbankaccountid IN VARCHAR2,
pchequeno IN VARCHAR2,
pclientbankid IN VARCHAR2,
pdescription IN VARCHAR2,
pstatementno in varchar2 default null,
pcustodypayment IN NUMBER)
is
nrow number;
begin
--check if posted
SELECT COUNT(*) INTO nrow from accountledger
WHERE receiptid = preceiptid;
IF nrow > 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Record Already Posted');
END IF;
UPDATE Receipt
Set pmodeid = preceiptmode,
xsactdate = pxsactdate,
xsactvalue = pxsactvalue,
bankaccountid = pbankaccountid,
chequeno = pchequeno,
clientbankid = pclientbankid,
description = pdescription,
statementno = pstatementno,
custodypayment = pcustodypayment
WHERE receiptid = preceiptid;
IF (SQL%NOTFOUND) THEN
INSERT INTO RECEIPT(receiptid,
pmodeid,
xsactdate,
xsactvalue,
bankaccountid,
chequeno,
clientbankid,
description,
statementno,
custodypayment
)
VALUES(preceiptid,
preceiptmode,
pxsactdate,
pxsactvalue,
pbankaccountid,
pchequeno,
pclientbankid,
pdescription,
pstatementno,
pcustodypayment
) RETURNING receiptid into (preceiptid);
END IF;
end;
This is how I update the Table
public void UpdateData(DataTable tbl, Oracle.DataAccess.Client.OracleTransaction oTran)
{
try
{
SetTransaction(oTran);
this._adapter.Update(tbl);
//this was added on 03-Aug-2011
tbl.AcceptChanges();
}
//handling concurrency problem
catch (DBConcurrencyException dbcx)
{
MessageBox.Show(CreateMessage(dbcx.Row), "Concurrency Exception", MessageBoxButtons.YesNo);
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
The Stack Trace Says
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at Oracle.DataAccess.Client.OracleDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at EquityBroker32.APP_DATA.config.GenericBusinessTable.UpdateData(DataTable tbl, OracleTransaction oTran) in d:\REVERSION\Latest\APP_DATA\config\GenericBusinessTable.cs:line 293
Voila Found it!
I add the following code chunk to my existing and it worked!
this.nCommandCollection[1].Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("PRECEIPTID", Oracle.DataAccess.Client.OracleDbType.Double, 10, System.Data.ParameterDirection.InputOutput, false, 10, 0, "RECEIPTID", System.Data.DataRowVersion.Current, null));
this.nCommandCollection[1].Parameters["PRECEIPTID"].DbType = DbType.Decimal;
Appreciation goes to this website and Individual who answered it