How to Convert Datetime to OracleTimeStamp

Question

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.

Are you sure you are not overrunning one of your `VARCHAR2` variables as in case of this error code is far more probable than issue with `TIMESTAMP`? — tpeczek, Dec 11 '13 at 15:15
I went back to the database and commented out all the code after "begin" from the function and I did not get the error! I am very curious though as to why it says the error is on line one because nothing is assigned to them at line 1. Thanks a lot for giving me that idea! — Aelphaeis, Dec 11 '13 at 15:23
From the ODP.NET perspective the entire stored procedure call is line one, it doesn't look inside it - every exception from the procedure will be thrown at line one. — tpeczek, Dec 11 '13 at 15:26
Oh, that is interesting to know. The function works as is when I run it from the database but does not work from ODP.NET. Can you give some insight as to why that might be? — Aelphaeis, Dec 11 '13 at 15:29
Honestly I have no idea at this point. I will try to recreate your issue but no promise there. — tpeczek, Dec 11 '13 at 15:37

score 1 · Accepted Answer · answered Dec 11 '13 at 16:07

1

You must specify max size of return value, i.e.

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

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

answered Dec 11 '13 at 16:07

Wernfried Domscheit

54,457
9
76
110

That was a very nice catch. Completely different from the initial question I asked. I fixed that and it ran like a charm. – Aelphaeis Dec 11 '13 at 17:02

How to Convert Datetime to OracleTimeStamp

1 Answers1