Page 1 of 1

Pass Empty PL/Sql Table to procedure

Posted: Tue 03 Jul 2012 09:24
by silverfox
Hello,

I'm trying to send through a procedure an empty Pl/Sql Table but I don't know how to do it. I tried to put the ArrayLength to 0 and Value to an empty .Net Array (not null) but I have this error (The error occur in direct and client mode.):

Code: Select all

Test method Devart.dotConnect.TestProject.PLSQLTableTest.PLSQLTableEmpty_Direct threw exception: 
System.InvalidCastException: Unable to cast object of type 'System.Decimal[]' to type 'System.IConvertible'.
With this stack:

Code: Select all

System.Convert.ToDecimal(Object value)
Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Object A_2, Byte[] A_3, Hashtable A_4, Int32 A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, OracleType A_13, am A_14, Boolean& A_15)
Devart.Data.Oracle.OracleParameter.a(bb& A_0, Boolean A_1, OracleConnection A_2, Byte[] A_3, Hashtable A_4, am A_5, Boolean& A_6, Int32 A_7)
Devart.Data.Oracle.OracleCommand.a(ak A_0, Int32 A_1, OracleParameterCollection A_2, am A_3, Boolean& A_4)
Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
Devart.dotConnect.TestProject.PLSQLTableTest.PLSQLTableEmpty(OracleConnection connection) in D:\Sources\Web\Tests Devart MT\DevartDotConnectOracle-trunk\src\Devart.dotConnect.TestProject\PLSQLTableTest.cs: line 54
Devart.dotConnect.TestProject.PLSQLTableTest.PLSQLTableEmpty_Direct() in D:\Sources\Web\Tests Devart MT\DevartDotConnectOracle-trunk\src\Devart.dotConnect.TestProject\PLSQLTableTest.cs: line 32
Here is the source code I use to reproduce the exception :
Oracle:

Code: Select all

Create or Replace Function EmptyPlSqlTable
(
tab_in  In  dbms_sql.Number_Table,
x In number
)
Return Number
Is
Begin
    Return tab_in.Count;
End EmptyPlSqlTable;
/
.Net

Code: Select all

        private void PLSQLTableEmpty(OracleConnection connection)
        {
            decimal[] tab = new decimal[] {};
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ParameterCheck = true;
                cmd.CommandText = "EmptyPlSqlTable";
                cmd.Prepare();
                cmd.Parameters["X"].Value = 42;
                cmd.Parameters["TAB_IN"].Value = tab;
                cmd.Parameters["TAB_IN"].ArrayLength = tab.Length;
                cmd.ExecuteNonQuery();
            }
        }

I read the doc(http://www.devart.com/dotconnect/oracle ... ength.html) but it does not explain how to manipulate empty pl/sql table

Can you explain me how can I achieve this ?

Thanks in advance.

Re: Pass Empty PL/Sql Table to procedure

Posted: Fri 06 Jul 2012 09:27
by Pinturiccio
We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

Re: Pass Empty PL/Sql Table to procedure

Posted: Tue 07 Aug 2012 15:18
by silverfox
Hello,

Do you have any solutions or workarounds to fix that ?

Thanks in advance.

Re: Pass Empty PL/Sql Table to procedure

Posted: Wed 08 Aug 2012 12:06
by Pinturiccio
We have fixed the bug with setting a parameter value equal to empty PL/SQL Table in the OCI connection mode. Now we are working on fixing this bug in Direct mode.
The fix in the OCI mode is available in the latest build 7.1.40 of dotConnect for Oracle. It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).

Re: Pass Empty PL/Sql Table to procedure

Posted: Fri 17 Aug 2012 09:16
by silverfox
Hello,

Thanks for the update. I suppose you will post here too when a fix is available for the direct mode ?

Thanks for the support

Re: Pass Empty PL/Sql Table to procedure

Posted: Tue 21 Aug 2012 11:11
by Pinturiccio
We are investigating the issue and will notify you about the results in the nearest future.

Re: Pass Empty PL/Sql Table to procedure

Posted: Wed 29 Aug 2012 10:55
by Pinturiccio
We have fixed the bug with setting a parameter value equal to empty PL/SQL Table in the Direct connection mode. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Re: Pass Empty PL/Sql Table to procedure

Posted: Mon 10 Sep 2012 08:01
by Pinturiccio
The new build of dotConnect for Oracle 7.2.77 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=24845

Re: Pass Empty PL/Sql Table to procedure

Posted: Tue 02 Oct 2012 12:55
by silverfox
Hello,

Thanks for the fix.

I just tested the new build of dotConnect for Oracle (7.2.77) and the test I sent to you works very well. However, it does not work with an empty date pl/sql array. Here is my code :

Code: Select all

            OracleMonitor monitor;
            monitor = new OracleMonitor();
            monitor.IsActive = true;
            monitor.UseApp = true;
            monitor.UseIdeOutput = true;
			string script = "Declare d Date; Begin raise_application_error(-20001,'');For i in 1..:TableCount Loop d := :dates(i);End Loop;End;";
			OracleConnection connection = new OracleConnection(AppConfig.ConnectionString);
			connection.Open();
			using (var cmd = connection.CreateCommand())
			{
				cmd.CommandText = script;
				var dates = new DateTime[] {};
				cmd.Parameters.Add("TableCount", OracleDbType.Number, dates.Count(), ParameterDirection.Input);
				cmd.Parameters.Add("dates", OracleDbType.Date, dates, ParameterDirection.Input);
				cmd.Parameters["dates"].ArrayLength = dates.Count();
				cmd.ExecuteNonQuery();
			}
			connection.Close();
Which produce this plsql call:

Code: Select all

Declare
    d Date;
Begin
    Raise_Application_Error(-20001, '');
    For i In 1 .. :Tablecount Loop
        d := :Dates(i);
    End Loop;
End;
It works in client mode (I get the 20001 exception) but in direct mode the code freeze on "cmd.ExecuteNonQuery()". The code works if I work with Number or Varchar or if I initialize dates with at least one element.

Here is what does the monitor print during my test:

10/2/2012 2:48:41 PM n/a dotConnect for Oracle monitoring is started Complete
10/2/2012 2:48:41 PM 0.000 Creating pool manager Complete
10/2/2012 2:48:41 PM 0.000 Creating pool with connections string: "*****" Complete
10/2/2012 2:48:41 PM 0.000 Creating object Complete
10/2/2012 2:48:41 PM 0.437 Open connection: "***" Complete
10/2/2012 2:48:41 PM 0.234 Connect: "*****" Complete
10/2/2012 2:48:41 PM 0.156 Connect: "*****" Complete
10/2/2012 2:48:41 PM 0.000 Taking connection from connection pool: "*****" Complete
10/2/2012 2:48:41 PM 0.016 Connection is taken from pool. Pool has 2 connection(s). Complete
10/2/2012 2:48:41 PM 0.000 Creating object Complete
10/2/2012 2:48:41 PM 0.000 Prepare: Declare d Date; Begin raise_application_error(-20001,'');For i in 1..:TableCount Loop d := :dates(i);End Loop;End; Complete
10/2/2012 2:48:41 PM n/a Execute: Declare d Date; Begin raise_application_error(-20001,'');For i in 1..:TableCount Loop d := :dates(i);End Loop;End; Pending

Thanks in advance,

Re: Pass Empty PL/Sql Table to procedure

Posted: Thu 04 Oct 2012 13:27
by Pinturiccio
We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

Re: Pass Empty PL/Sql Table to procedure

Posted: Mon 10 Dec 2012 10:41
by silverfox
Hello,

Did you fix this problem ?

Thanks in advance.

Re: Pass Empty PL/Sql Table to procedure

Posted: Tue 11 Dec 2012 09:47
by Pinturiccio
We are investigating the issue, but we can't tell any timeframe at the moment.

Re: Pass Empty PL/Sql Table to procedure

Posted: Wed 06 Feb 2013 14:30
by Pinturiccio
We have fixed the bug with setting a parameter of Date type with value equal to empty PL/SQL Table in the Direct connection mode. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Re: Pass Empty PL/Sql Table to procedure

Posted: Fri 08 Feb 2013 09:28
by Pinturiccio
The new build of dotConnect for Oracle 7.5.179 is available for download now!
It can be downloaded at http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=25833