Bulk operations with CLOB values

[Partizan]

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]

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

Thank you.

[Pinturiccio]

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.

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]

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]

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]

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]

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]

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]

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]

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

[Partizan]

Hi guys!

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

[Pinturiccio]

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]

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]

Thank you guys, it's fixed.

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