Wrong ORA 8192 error

Wrong ORA 8192 error

Postby klaus linzner » Wed 05 Jun 2013 12:00

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
klaus linzner
 
Posts: 28
Joined: Thu 16 May 2013 09:18

Re: Wrong ORA 8192 error

Postby Pinturiccio » Thu 06 Jun 2013 15:11

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44

Re: Wrong ORA 8192 error

Postby Pinturiccio » Wed 03 Jul 2013 14:29

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);
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle