Pass Empty PL/Sql Table to procedure

Pass Empty PL/Sql Table to procedure

Postby silverfox » Tue 03 Jul 2012 09:24

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/docs/Devart.Data.Oracle~Devart.Data.Oracle.OracleParameter~ArrayLength.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.
silverfox
 
Posts: 10
Joined: Wed 18 Jan 2012 10:58
Location: Belgium

Re: Pass Empty PL/Sql Table to procedure

Postby Pinturiccio » Fri 06 Jul 2012 09:27

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

Re: Pass Empty PL/Sql Table to procedure

Postby silverfox » Tue 07 Aug 2012 15:18

Hello,

Do you have any solutions or workarounds to fix that ?

Thanks in advance.
silverfox
 
Posts: 10
Joined: Wed 18 Jan 2012 10:58
Location: Belgium

Re: Pass Empty PL/Sql Table to procedure

Postby Pinturiccio » Wed 08 Aug 2012 12:06

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

Re: Pass Empty PL/Sql Table to procedure

Postby silverfox » Fri 17 Aug 2012 09:16

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
silverfox
 
Posts: 10
Joined: Wed 18 Jan 2012 10:58
Location: Belgium

Re: Pass Empty PL/Sql Table to procedure

Postby Pinturiccio » Tue 21 Aug 2012 11:11

We are investigating the issue and will notify you about the results in the nearest future.
Pinturiccio
Devart Team
 
Posts: 2044
Joined: Wed 02 Nov 2011 09:44

Re: Pass Empty PL/Sql Table to procedure

Postby Pinturiccio » Wed 29 Aug 2012 10:55

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

Re: Pass Empty PL/Sql Table to procedure

Postby Pinturiccio » Mon 10 Sep 2012 08:01

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

Re: Pass Empty PL/Sql Table to procedure

Postby silverfox » Tue 02 Oct 2012 12:55

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,
silverfox
 
Posts: 10
Joined: Wed 18 Jan 2012 10:58
Location: Belgium

Re: Pass Empty PL/Sql Table to procedure

Postby Pinturiccio » Thu 04 Oct 2012 13:27

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

Re: Pass Empty PL/Sql Table to procedure

Postby silverfox » Mon 10 Dec 2012 10:41

Hello,

Did you fix this problem ?

Thanks in advance.
silverfox
 
Posts: 10
Joined: Wed 18 Jan 2012 10:58
Location: Belgium

Re: Pass Empty PL/Sql Table to procedure

Postby Pinturiccio » Tue 11 Dec 2012 09:47

We are investigating the issue, but we can't tell any timeframe at the moment.
Pinturiccio
Devart Team
 
Posts: 2044
Joined: Wed 02 Nov 2011 09:44

Re: Pass Empty PL/Sql Table to procedure

Postby Pinturiccio » Wed 06 Feb 2013 14:30

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

Re: Pass Empty PL/Sql Table to procedure

Postby Pinturiccio » Fri 08 Feb 2013 09:28

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


Return to dotConnect for Oracle