Bulk operations with CLOB values

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Partizan
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Bulk operations with CLOB values

Post by Partizan » Tue 16 Dec 2014 11:18

Hi guys,

We are using bulk operations with Array Binding and some custom anonymous procedures.
The problem we have is that values for CLOB columns are passed as Varchar2 parameters specifying string values.

When the string value is too big it leads to different errors depending on approach we use:
Like the following for example ORA-01460: unimplemented or unreasonable conversion requested


We tried to change parameter type to Clob - but with that we are not able to use Array Binding any more since we get the following error: Type "OracleDbType.Clob" cannot be used in PL/SQL table parameter.

Is there any way to use Clob parameter type in Array Binding or pass array of huge clobs (50k symbols each for example) into a stored procedure or anonymous code block?

Thank you.

Partizan
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

Post by Partizan » Thu 18 Dec 2014 11:36

Hi Devart, is there any update on this? I submitted a new support ticket, but still no reaction.

Thank you.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Bulk operations with CLOB values

Post by Pinturiccio » Thu 18 Dec 2014 15:18

Partizan wrote:The problem we have is that values for CLOB columns are passed as Varchar2 parameters specifying string values.

When the string value is too big it leads to different errors depending on approach we use:
Like the following for example ORA-01460: unimplemented or unreasonable conversion requested
If an anonymous block or stored procedure uses a parameter of a CLOB data type, and the code passes a parameter of the OracleDbType.VarChar type having large size, the following error occurs: "ORA-01460: unimplemented or unreasonable conversion requested". This is designed behaviour.
Partizan wrote:We tried to change parameter type to Clob - but with that we are not able to use Array Binding any more since we get the following error: Type "OracleDbType.Clob" cannot be used in PL/SQL table parameter.
We could not reproduce the issue. When we use the parameter type OracleDbType.Clob instead of OracleDbType.VarChar, both anonymous block and stored procedure work correctly.

Please provide the following information in order to reproduce the issue:
1. dotConnect for Oracle version;
2. The connection mode you use: Direct or OCI;
3. Please create and send us a small test project that reproduces the issue and all the necessary DDL/DML scripts for the project;
4. Please describe the steps for reproducing the issue with your test project.

Partizan
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

Post by Partizan » Mon 22 Dec 2014 15:13

DotConnect for Oracle 8.4.293

DDL:

Code: Select all

CREATE TABLE DEVART_TEST(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(150 CHAR),
INFORMATION CLOB,
LAST_UPDATED_DT DATE
);


INSERT INTO DEVART_TEST(ID, NAME, INFORMATION, LAST_UPDATED_DT) VALUES (0, 'Zero', '', sysdate);
INSERT INTO DEVART_TEST(ID, NAME, INFORMATION, LAST_UPDATED_DT) VALUES (1, 'One', '', sysdate);
INSERT INTO DEVART_TEST(ID, NAME, INFORMATION, LAST_UPDATED_DT) VALUES (2, 'Two', '', sysdate);
INSERT INTO DEVART_TEST(ID, NAME, INFORMATION, LAST_UPDATED_DT) VALUES (3, 'Three', '', sysdate);
COMMIT;
Simple repro. Huge clob value is some string value with over 10k symbols:

Code: Select all

      static void Main(string[] args)
      {
         var testIds = new decimal[]{ 0,1,2,3 };
         var testClobs = new[] { hugeClobValue, hugeClobValue, hugeClobValue, hugeClobValue };
         var testDates = new DateTime[4];

         string connectionString = "User Id=<____USER_____>;Password=<____PASS____>;Direct=true;Data Source=<____SOURCE____>;Port=1521;SID=<____SID____>;Pooling=true;Unicode=true";
         using (var transaction = new TransactionScope(TransactionScopeOption.Required,
            new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
         {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
               connection.Open();
               using (var command = connection.CreateCommand())
               {
                  var idParam = new OracleParameter("pId", OracleDbType.Number) {Value = testIds, Direction = ParameterDirection.Input, ArrayLength = 4};
                  var clobParam = new OracleParameter("pClob", OracleDbType.Clob) { Value = testClobs, Direction = ParameterDirection.Input, ArrayLength = 4 };
                  var dateParam = new OracleParameter("pDate", OracleDbType.Date) { Value = testDates, Direction = ParameterDirection.InputOutput, ArrayLength = 4 };

                  command.Parameters.Add(idParam);
                  command.Parameters.Add(clobParam);
                  command.Parameters.Add(dateParam);

                  command.CommandText =
                     "UPDATE DEVART_TEST SET INFORMATION = :pClob WHERE ID = :pId RETURNING LAST_UPDATED_DT INTO :pDate";
                  command.PassParametersByName = true;

                  command.ExecuteArray(4);
               }
            }
            transaction.Complete();
         }
      }

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Bulk operations with CLOB values

Post by Pinturiccio » Tue 23 Dec 2014 15:06

When specifying parameters, you set a value for the ArrayLength property. This property is meant for working with DML array, PL/SQL table, or VARRAY and TABLE object types, and it is not needed for working with array binding, except cases when DML array, PL/SQL table, or VARRAY and TABLE object types is a parameter. Also, the ArrayLength property has limits for the used types. Therefore, the CLOB type cannot be used together with the ArrayLength property. For more information, please refer to http://www.devart.com/dotconnect/oracle ... ength.html

To fix the issue, delete the initialization of the ArrayLength property from the parameters specification.
Parameters specification looks in the following way:

Code: Select all

var idParam = new OracleParameter("pId", OracleDbType.Number) {Value = testIds, Direction = ParameterDirection.Input };
var clobParam = new OracleParameter("pClob", OracleDbType.Clob) { Value = testClobs, Direction = ParameterDirection.Input };
var dateParam = new OracleParameter("pDate", OracleDbType.Date) { Value = testDates, Direction = ParameterDirection.InputOutput };

Partizan
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

Post by Partizan » Tue 23 Dec 2014 15:20

The problem is that when I remove ArrayLength initialization, I get the following error:

Code: Select all

ORA-03120: two-task conversion routine: integer overflow
That's easily reproduced with a demo app I submitted earlier.

Thanks!

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Bulk operations with CLOB values

Post by Pinturiccio » Wed 24 Dec 2014 13:29

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

As a temporary workaround, you can use the OCI connection mode.

Partizan
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

Post by Partizan » Wed 24 Dec 2014 13:47

Thanks!
Unfortunately OCI is not an option for us due to deployment issues.
We're looking forward to getting this issue resloved.

Thank you!

Partizan
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

Post by Partizan » Fri 02 Jan 2015 16:05

Hi There! Happy New Year, guys!
Is there any estimate for getting this fixed?
Any chance to get this by the end of Jan?

Thanks!

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Bulk operations with CLOB values

Post by Pinturiccio » Mon 05 Jan 2015 16:40

We are investigating the issue, and we will post here about the results next week.

Partizan
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

Post by Partizan » Wed 14 Jan 2015 11:33

Hi guys!

We're very interested in update on this.
Thanks!

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Bulk operations with CLOB values

Post by Pinturiccio » Wed 14 Jan 2015 15:25

We have fixed the bug with output parameters in DML arrays in the Direct mode. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Bulk operations with CLOB values

Post by Pinturiccio » Thu 15 Jan 2015 16:03

New build of dotConnect for Oracle 8.4.333 is available for download!
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=31098

Partizan
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

Post by Partizan » Wed 21 Jan 2015 12:04

Thank you guys, it's fixed.

As always, good job in a short time!
Thanks again!

Post Reply