Stored Procedure with Oracle

Stored Procedure with Oracle

Postby jpdomenge » Wed 17 Mar 2010 10:48

Hi,

This is me again. I have another problem with UniDirect 3.
I'm still trying to migrate our application that was using CoreLab.UniDirect to dotconnect Universal 3

Our application was working well with the following piece of code. But when I want to use it with dotConnect Universal 3 an Oracle error is thrown.
Are you aware of this kind of problem ? Is there a different way to call the stored procedure ?
And what is the change in Unidirect that can lead to this error (Transaction ?) ?

Regards.

Original Source Code :
Code: Select all
UniConnection Connexion = new UniConnection(UtSrv.ChaineConnexion);
      UniCommand alert = new UniCommand();
      alert.Connection = Connexion;
      alert.ParameterCheck = true;
      alert.CommandType = System.Data.CommandType.StoredProcedure;
      alert.CommandText = "PR_ALERT_WAIT";
      alert.Parameters["NAME"].Value = alerter;
      alert.Parameters["TIMOUT"].Value = timeout;
      alert.ExecuteNonQuery();


New Source code :
Code: Select all
UniConnection Connexion2 = new UniConnection(UtSrv.ChaineConnexion);
   Connexion2.Open();
   Connexion2.BeginTransaction();
   UniCommand alert2 = new UniCommand("PR_ALERT_WAIT",Connexion2);
   alert2.CommandType = System.Data.CommandType.StoredProcedure;
   alert2.ParameterCheck = true;

   alert2.Parameters.Add("NAME", UniDbType.VarChar);
   alert2.Parameters.Add("TIMOUT", UniDbType.Decimal);
   alert2.Parameters.Add("STATUS", UniDbType.Int);

   alert2.Parameters["NAME"].UniDbType = UniDbType.VarChar;
   alert2.Parameters["TIMOUT"].UniDbType = UniDbType.Decimal;
   //alert2.Parameters["STATUS"].UniDbType = UniDbType.Int;
   //alert2.Parameters["STATUS"].Direction = System.Data.ParameterDirection.Output;

   alert2.Parameters["NAME"].Value = alerter;

   decimal timout = (decimal) timeout;
   alert2.Parameters["TIMOUT"].Value = timout;

   result = alert2.ExecuteNonQuery();
   Connexion2.Commit();


Procedure : PR_ALERT_WAIT :
Code: Select all
   create or replace
   PROCEDURE "PR_ALERT_WAIT" (
    NAME   in  varchar2,
    STATUS out integer,
    TIMOUT in  number
   )
   as
     mess varchar2(120);
   begin
     sys.DBMS_ALERT.WAITONE( get_user() || '_' || NAME, mess, STATUS, TIMOUT );
   end;


StackTrace
à Devart.Data.Universal.UniCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3, Boolean A_4)
à Devart.Data.Universal.UniCommand.ExecuteNonQuery()

Error message :
ORA-20000: ORU-10023: error 3 on lock request.
ORA-06512: à "SYS.DBMS_ALERT", ligne 319
ORA-06512: à "MSTOR.PR_ALERT_WAIT", ligne 9
ORA-06512: à ligne 2
jpdomenge
 
Posts: 12
Joined: Fri 12 Mar 2010 08:17

Postby jpdomenge » Wed 17 Mar 2010 14:20

Ok, if it can help, I made a simple solution that is using Unidirect v1, dotconnect for Oracle and dotConnect Universal.

The first and the second works as expected while thethirs (universal) is throwing an error.

I added the following referencies :
CoreLab.Unidirect
Devart.Data
Devart.Data.Oracle
Devart.Data.Universal

Here is the code :

Code: Select all
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;

namespace testStoredProcedure
{
    class Program
    {
        static void Main(string[] args)
        {
            int result;
            string connec = "Provider=Oracle;User Id=XXXX;Password=XXXX;Data Source=XXXX";

            CoreLab.UniDirect.UniConnection Connexion = new CoreLab.UniDirect.UniConnection(connec);
            Connexion.Open();
            CoreLab.UniDirect.UniTransaction tr = Connexion.BeginTransaction();
            CoreLab.UniDirect.UniCommand alert = new CoreLab.UniDirect.UniCommand("PR_ALERT_WAIT", Connexion, tr);
            alert.CommandType = System.Data.CommandType.StoredProcedure;
            alert.ParameterCheck = true;

            alert.Parameters.Add("NAME", DbType.AnsiString);
            alert.Parameters.Add("TIMOUT", DbType.Decimal);
            alert.Parameters.Add("STATUS", DbType.Int32);

            alert.Parameters["NAME"].DbType = DbType.AnsiString;
            alert.Parameters["TIMOUT"].DbType = DbType.Decimal;
            alert.Parameters["STATUS"].DbType = DbType.Int32;
            alert.Parameters["STATUS"].Direction = System.Data.ParameterDirection.Output;

            alert.Parameters["NAME"].Value = "REQREPORT";
            alert.Parameters["TIMOUT"].Value =10;
            alert.Parameters["STATUS"].Value = -1;

            alert.Prepare();
            result = alert.ExecuteNonQuery();
            Connexion.Commit();

            Console.WriteLine("Method UniDirect OK");

            connec = "User Id=XXXX;Password=XXXX;Data Source=XXXX";
            Devart.Data.Oracle.OracleConnection Connexion2 = new Devart.Data.Oracle.OracleConnection(connec);
            Connexion2.Open();
            Devart.Data.Oracle.OracleTransaction tr2 = Connexion2.BeginTransaction();
            Devart.Data.Oracle.OracleCommand alert2 = new Devart.Data.Oracle.OracleCommand("PR_ALERT_WAIT", Connexion2, tr2);
            alert2.CommandType = System.Data.CommandType.StoredProcedure;
            alert2.ParameterCheck = true;

            alert2.Parameters.Add("NAME", Devart.Data.Oracle.OracleDbType.VarChar);
            alert2.Parameters.Add("TIMOUT", Devart.Data.Oracle.OracleDbType.Number);
            alert2.Parameters.Add("STATUS", Devart.Data.Oracle.OracleDbType.Integer);

            alert2.Parameters["NAME"].DbType = DbType.AnsiString;
            alert2.Parameters["TIMOUT"].DbType = DbType.Decimal;
            alert2.Parameters["STATUS"].DbType = DbType.Int32;
            alert2.Parameters["STATUS"].Direction = System.Data.ParameterDirection.Output;

            alert2.Parameters["NAME"].Value = "REQREPORT";
            alert2.Parameters["TIMOUT"].Value = 10;
            alert2.Parameters["STATUS"].Value = -1;

            alert2.Prepare();
            result = alert2.ExecuteNonQuery();
            Connexion2.Commit();
            Console.WriteLine("Method dotConnect Oracle OK");

            connec = "Provider=Oracle;User Id=XXXX;Password=XXXX;Data Source=XXXX";
            Devart.Data.Universal.UniConnection Connexion3 = new Devart.Data.Universal.UniConnection(connec);
            Connexion3.Open();
            Devart.Data.Universal.UniTransaction tr3 = Connexion3.BeginTransaction();
            Devart.Data.Universal.UniCommand alert3 = new Devart.Data.Universal.UniCommand("PR_ALERT_WAIT", Connexion3, tr3);
            alert3.CommandType = System.Data.CommandType.StoredProcedure;
            alert3.ParameterCheck = true;

            alert3.Parameters.Add("NAME", Devart.Data.Universal.UniDbType.VarChar);
            alert3.Parameters.Add("TIMOUT", Devart.Data.Universal.UniDbType.Decimal);
            alert3.Parameters.Add("STATUS", Devart.Data.Universal.UniDbType.Int);

            alert3.Parameters["NAME"].DbType = DbType.AnsiString;
            alert3.Parameters["TIMOUT"].DbType = DbType.Decimal;
            alert3.Parameters["STATUS"].DbType = DbType.Int32;
            alert3.Parameters["STATUS"].Direction = System.Data.ParameterDirection.Output;

            alert3.Parameters["NAME"].Value = "REQREPORT";
            alert3.Parameters["TIMOUT"].Value = 10;
            alert3.Parameters["STATUS"].Value = -1;

            alert3.Prepare();
            result = alert3.ExecuteNonQuery();
            Connexion3.Commit();

            Console.WriteLine("Method dotConnect Universal OK");
        }
    }
}
jpdomenge
 
Posts: 12
Joined: Fri 12 Mar 2010 08:17

Postby StanislavK » Thu 18 Mar 2010 16:52

We have reproduced the problem. We will investigate it and notify you about the results.

Also, you may be interested in the OracleAlerter component of dotConnect for Oracle, which provides the functionality for working with the DBMS_ALERT package:
http://www.devart.com/dotconnect/oracle/docs/Devart.Data.Oracle~Devart.Data.Oracle.OracleAlerter.html
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby jpdomenge » Tue 23 Mar 2010 08:42

Thank you, I'll wait for your feedback.

Do you think this bug is also in the version 3.00 or specific to the beta 3.10 ?

Regards.
jpdomenge
 
Posts: 12
Joined: Fri 12 Mar 2010 08:17

Postby StanislavK » Tue 23 Mar 2010 16:20

I tested the 3.00.5 version, the same problem occurred.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby jpdomenge » Wed 24 Mar 2010 08:41

Thank you for your time, I'll wait the fix.

Regards.
jpdomenge
 
Posts: 12
Joined: Fri 12 Mar 2010 08:17

Postby StanislavK » Tue 25 May 2010 07:02

We have released the new 3.20.16 build of dotConnect Universal. It can be downloaded from
http://www.devart.com/dotconnect/universal/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

The new build contains the fix for problem with the stored procedure execution when ParameterCheck is true and parameters have invalid order. For more information on fixes and improvements available in version 3.20.16, please see
http://www.devart.com/forums/viewtopic.php?t=18035
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect Universal