CODEWITHSUNDEEP
.netoracle-databaseplsqloracle11g
David C.
David C.

Reputation: 33

Oracle 11g : ORA-22284 on insertion into table with CLOB field > ~1350 chars

I'm running into trouble trying to perform a merge into a table containing a CLOB field.

I'm working with a .Net FrameWork v4.5 codebase that generates an SQL statement as follows:

MERGE INTO md_metadata.md_report_templates TARGET
USING (SELECT 1
       FROM   dual)
ON (TARGET.id=:template_id)
WHEN matched THEN
  UPDATE SET title = :title,
             description = :description,
             xml_data = :xml_data,
             technology = :technology,
             modification_date = :modification_date,
             reports_list = :reports_list
WHEN NOT matched THEN
  INSERT(id,
         title,
         description,
         xml_data,
         is_private,
         technology,
         owner,
         modification_date,
         reports_list)
  VALUES(:template_id,
         :title,
         :description,
         :xml_data,
         :is_private,
         :technology,
         :owner,
         :modification_date,
         :reports_list);

Here xml_data is an CLOB field. The intent of this statement is into insert (or update) a row into the table that contains an XML description of a form template. This statement works fine as long as the :xml_data parameter length does not exceed ~1350 characters or ~2700 bytes. As soon as this happens the Oracle Data Access Client (4.121.1.0) throws up an exception 'ORA-22284 Duplicate long binds are not supported'.

I'm baffled. Can anyone shed any light on why this occurs?

If the row is inserted with a shorter value in :xml_data, and then updated in a separate statement with the original (1350+ ch) value in :xml_data, this works as desired!

Thanks,

DC

Upvotes: 2

Views: 1618

Answers (1)

ThinkJet
ThinkJet

Reputation: 6745

ORA-22284: duplicate LONG binds are not supported
Cause: LOB access layer could not handle duplicate LONG binds.
Action: Provide distinct LONG or VARCHAR binds.

In your code you have 2 instances of :xml_data parameter: one for update and one for insert. So, one formal parameter value must be binded to 2 actual places in query.

One of possible workaround is to refactor a query a little bit:

MERGE INTO md_metadata.md_report_templates TARGET
USING (
  SELECT
    :template_id       as template_id
    :title             as title,
    :description       as description,
    :xml_data          as xml_data,
    :technology        as technology,
    :modification_date as modification_date,
    :reports_list      as reports_list
  FROM  dual
) SRC_TEMPLATE
ON (
  TARGET.id = SRC_TEMPLATE.template_id
)
WHEN matched THEN
  UPDATE SET title             = SRC_TEMPLATE.title,
             description       = SRC_TEMPLATE.description,
             xml_data          = SRC_TEMPLATE.xml_data,
             technology        = SRC_TEMPLATE.technology,
             modification_date = SRC_TEMPLATE.modification_date,
             reports_list      = SRC_TEMPLATE.reports_list
WHEN NOT matched THEN
  INSERT(
    id,
    title,
    description,
    xml_data,
    is_private,
    technology,
    owner,
    modification_date,
    reports_list
  ) VALUES(
    SRC_TEMPLATE.template_id,
    SRC_TEMPLATE.title,
    SRC_TEMPLATE.description,
    SRC_TEMPLATE.xml_data,
    SRC_TEMPLATE.is_private,
    SRC_TEMPLATE.technology,
    SRC_TEMPLATE.owner,
    SRC_TEMPLATE.modification_date,
    SRC_TEMPLATE.reports_list
  );

Upvotes: 4

Related Questions