How to Convert Datetime to OracleTimeStamp

I have a problem where I cannot run a function from a database using ODP.NET because I cannot convert one of my arguments to a timestamp.

I'm using : ODAC121010Xcopy_32bit ODP.NET4

I've checked quite a few questions and I've also tried converting the datetime object into a string format to pass it. I've spent hours on this and honestly can't figure out if I'm missing something. It seems like this is such a common sense thing and I can't figure out for the life of my why this isn't working, any help would be appreciated it.

Here is my code:

using (var Command = new OracleCommand("PMH.QueryGetSql", Connection))
{
    Command.CommandType = CommandType.StoredProcedure;
    Connection.Open();

    OracleTimeStamp ots = new OracleTimeStamp(DateTime.Now);
    Command.Parameters.Add("return", OracleDbType.Varchar2, ParameterDirection.ReturnValue);
    Command.Parameters.Add("a", OracleDbType.Int32, ParameterDirection.Input).Value = 1001;
    Command.Parameters.Add("b", OracleDbType.TimeStamp, ParameterDirection.Input).Value = ots;


    Command.ExecuteNonQuery();
    Console.WriteLine(Command.Parameters["return"].Value);
    Console.WriteLine("Completed");
}

Here is the exception I'm getting:

Oracle.DataAccess.Client.OracleException ORA-06502: PL/SQL: numeric or value err or: character string buffer too small ORA-06512: at line 1 at Oracle.DataAccess.Client.OracleException.HandleErrorH elper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpo SqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, Oracle Connection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx , Object src, Boolean bCheck) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()

Finally, here is the function that I'm trying to run:

  FUNCTION QueryGetSql(nQueryId INTEGER,dSyncFromTimeStamp TIMESTAMP DEFAULT NULL) RETURN VARCHAR2 IS
--nResult       INTEGER := 0;
sQuerySql       VARCHAR2(32767);
sTableName      VARCHAR2(100);
sPKName         VARCHAR2(100);
sQueryColumns   VARCHAR2(32767);
sConditions     VARCHAR2(32767);
sNullColumns    VARCHAR2(32767);

CURSOR qc_cur IS
SELECT column_name
  FROM pmh$_app_query_columns
 WHERE query_id = nQueryId
 ORDER BY seq ASC;

Also note that while I do have access to see the oracle code, I did not write it and therefore would prefer not to change it.

---

You must specify max size of return value, ie

Command.Parameters.Add("return", OracleDbType.Varchar2, 1000, null, ParameterDirection.ReturnValue);

"null" is the initial value, in VB.NET you have to use "Nothing" instead.

链接地址: http://www.djcxy.com/p/67188.html

上一篇: Connection.Open（）上的Oracle.DataAccess.Client.OracleException

下一篇: 如何将日期时间转换为OracleTimeStamp