Bulk operations with CLOB values
Bulk operations with CLOB values
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.
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.
Re: Bulk operations with CLOB values
Hi Devart, is there any update on this? I submitted a new support ticket, but still no reaction.
Thank you.
Thank you.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Bulk operations with CLOB values
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: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 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.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.
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.
Re: Bulk operations with CLOB values
DotConnect for Oracle 8.4.293
DDL:
Simple repro. Huge clob value is some string value with over 10k symbols:
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;
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();
}
}
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Bulk operations with CLOB values
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:
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 };
Re: Bulk operations with CLOB values
The problem is that when I remove ArrayLength initialization, I get the following error:
That's easily reproduced with a demo app I submitted earlier.
Thanks!
Code: Select all
ORA-03120: two-task conversion routine: integer overflow
Thanks!
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Bulk operations with CLOB values
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.
As a temporary workaround, you can use the OCI connection mode.
Re: Bulk operations with CLOB values
Thanks!
Unfortunately OCI is not an option for us due to deployment issues.
We're looking forward to getting this issue resloved.
Thank you!
Unfortunately OCI is not an option for us due to deployment issues.
We're looking forward to getting this issue resloved.
Thank you!
Re: Bulk operations with CLOB values
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!
Is there any estimate for getting this fixed?
Any chance to get this by the end of Jan?
Thanks!
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Bulk operations with CLOB values
We are investigating the issue, and we will post here about the results next week.
Re: Bulk operations with CLOB values
Hi guys!
We're very interested in update on this.
Thanks!
We're very interested in update on this.
Thanks!
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Bulk operations with CLOB values
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.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Bulk operations with CLOB values
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
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
Re: Bulk operations with CLOB values
Thank you guys, it's fixed.
As always, good job in a short time!
Thanks again!
As always, good job in a short time!
Thanks again!