Bulk operations with CLOB values

Bulk operations with CLOB values

Postby 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

Postby 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.
Partizan
 
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

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

Re: Bulk operations with CLOB values

Postby 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();
         }
      }
Partizan
 
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

Postby 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/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleParameter~ArrayLength.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 };
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44

Re: Bulk operations with CLOB values

Postby 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!
Partizan
 
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

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

Re: Bulk operations with CLOB values

Postby 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

Postby 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!
Partizan
 
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

Postby Pinturiccio » Mon 05 Jan 2015 16:40

We are investigating the issue, and we will post here about the results next week.
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44

Re: Bulk operations with CLOB values

Postby Partizan » Wed 14 Jan 2015 11:33

Hi guys!

We're very interested in update on this.
Thanks!
Partizan
 
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: Bulk operations with CLOB values

Postby 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: 1978
Joined: Wed 02 Nov 2011 09:44

Re: Bulk operations with CLOB values

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

Re: Bulk operations with CLOB values

Postby Partizan » Wed 21 Jan 2015 12:04

Thank you guys, it's fixed.

As always, good job in a short time!
Thanks again!
Partizan
 
Posts: 36
Joined: Fri 13 Nov 2009 10:18


Return to dotConnect for Oracle