Reputation: 33
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: 1616
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