Page 1 of 1

Wrong ORA 8192 error

Posted: Wed 05 Jun 2013 12:00
by klaus linzner
Hello,
Today I've found a bug in Timestamp with time zone Parameter handling:
At first create the following Stored Procedure in your DB, it doesn't need to do anything:

Code: Select all

CREATE OR REPLACE PROCEDURE ORA8192SAMPLE (
    P_TIMESTAMP TIMESTAMP WITH TIME ZONE
)
IS
BEGIN
    null;
END;
And then run the following code:

Code: Select all

string connectionString = "Data Source=YOURSERVER;User Id=YOURUSER;Password=YOURPASS";
string commandText = "ORA8192SAMPLE";
using (OracleConnection connection = new OracleConnection(connectionString))
{
    connection.Open();
using (OracleCommand commandFailing = connection.CreateCommand())
    {
        commandFailing.CommandText = commandText;
        commandFailing.CommandType = CommandType.StoredProcedure;
        commandFailing.PassParametersByName = true;

        commandFailing.Parameters.Add("P_TIMESTAMP", OracleDbType.TimeStampTZ);
        commandFailing.Parameters["P_TIMESTAMP"].OracleValue = new OracleTimeStamp(DateTime.MinValue);
        commandFailing.ExecuteNonQuery();
    }    connection.Close();
}
This will fail with an "ORA-08192: Flashback Table operation is not allowed on fixed tables" error, which has pretty much nothing in common with this error.
Problem seems to be with TimeStampTZ, if the param is changed to TimeStamp everything works fine (or raises the correct/expected errors and problems).

Tests were done with Devart 7.7, Ora 11.2.0.3 client/server;

BR

Re: Wrong ORA 8192 error

Posted: Thu 06 Jun 2013 15:11
by Pinturiccio
We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

Re: Wrong ORA 8192 error

Posted: Wed 03 Jul 2013 14:29
by Pinturiccio
This is a designed behavior. There are two ways to solve the issue:
1. Use DateTime.MinValue instead of OracleTimeStamp, and the Value property instead of the OracleValue property for the parameter. I.e. replace:

Code: Select all

commandFailing.Parameters["P_TIMESTAMP"].OracleValue = new OracleTimeStamp(DateTime.MinValue);
with

Code: Select all

commandFailing.Parameters["P_TIMESTAMP"].Value = DateTime.MinValue;
2. Create an object of the OracleTimeStamp type in the following way:

Code: Select all

commandFailing.Parameters["P_TIMESTAMP"].OracleValue = new OracleTimeStamp(DateTime.MinValue, OracleDbType.TimeStampTZ);