Home » SQL & PL/SQL » SQL & PL/SQL » How to update CLOB column having more than 4000 chars (Oracle11i)
How to update CLOB column having more than 4000 chars [message #352273] Mon, 06 October 2008 23:02 Go to next message
jackzwang
Messages: 1
Registered: October 2008
Junior Member
Hi, I tried to update a clob column, but failed with the message: SQL Error: ORA-01704: string literal too long
01704. 00000 - "string literal too long"

update xxsundq_rules
set rule_execute_clob = q'{INSERT INTO xxsundq_quotes
SELECT seq_quo.nextval, xxsundq_rules.rule_id, quo.quote_header_id, quo.quote_line_id, quo.inventory_item_id, quo.organization_id, sysdate
FROM (SELECT a.quote_header_id quote_header_id, a.quote_line_id quote_line_id, a.inventory_item_id inventory_item_id, a.organization_id organization_id
FROM
(
SELECT h.quote_name,
h.quote_number,
ttl.name order_type_name,
H.quote_expiration_date,
l.line_number Quote_line_number,
L.PRICED_PRICE_LIST_ID,
H.quote_header_id,
L.quote_line_id,
L.organization_id,
L.inventory_item_id,
MSI.SEGMENT1 Item,
hl.country,
(SELECT qll.end_date_active
FROM APPS.qp_pricing_attributes qpa
,APPS.qp_list_lines qll
WHERE qpa.pricing_attribute_context = 'SUN PRICING ATTRIBUTE'
AND qpa.pricing_attribute = 'PRICING_ATTRIBUTE1'
AND qpa.comparison_operator_code = 'NOT ='
AND qll.list_header_id = L.PRICED_PRICE_LIST_ID
AND qll.inventory_item_id = L.inventory_item_id
AND qpa.pricing_attr_value_from = hl.country
AND qll.list_header_id = qpa.list_header_id
AND qll.list_line_id = qpa.list_line_id
AND NVL(qll.end_date_active,TO_DATE('31/12/3714','MM/DD/YYYY')) =
-- AND qll.end_date_active =
(SELECT MAX(NVL(qll1.end_date_active,TO_DATE('31/12/3714','MM/DD/YYYY')))
-- (SELECT MAX(qll1.end_date_active)
FROM APPS.qp_pricing_attributes qpa1
,APPS.qp_list_lines qll1
WHERE qpa1.pricing_attribute_context = 'SUN PRICING ATTRIBUTE'
AND qpa1.pricing_attribute = 'PRICING_ATTRIBUTE1'
AND qpa1.comparison_operator_code = 'NOT ='
AND qll1.list_header_id = L.PRICED_PRICE_LIST_ID
AND qll1.inventory_item_id = L.inventory_item_id
AND qpa1.pricing_attr_value_from = hl.country
AND qll1.list_header_id = qpa1.list_header_id
AND qll1.list_line_id = qpa1.list_line_id)
) EOL_DATE,
(SELECT qll.end_date_active
FROM APPS.qp_pricing_attributes qpa
,APPS.qp_list_lines qll
WHERE qpa.pricing_attribute_context = 'SUN PRICING ATTRIBUTE'
AND qpa.pricing_attribute = 'PRICING_ATTRIBUTE1'
AND qpa.comparison_operator_code = 'NOT ='
AND qll.list_header_id = L.PRICED_PRICE_LIST_ID
AND qll.inventory_item_id = L.inventory_item_id
AND qpa.pricing_attr_value_from = hl.country
AND qll.list_header_id = qpa.list_header_id
AND qll.list_line_id = qpa.list_line_id
AND NVL(qll.end_date_active,TO_DATE('31/12/3714','MM/DD/YYYY')) =
(SELECT MAX(NVL(qll1.end_date_active,TO_DATE('31/12/3714','MM/DD/YYYY')))
FROM apps.qp_pricing_attributes qpa1
,apps.qp_list_lines qll1
WHERE qpa1.pricing_attribute_context = 'SUN PRICING ATTRIBUTE'
AND qpa1.pricing_attribute = 'PRICING_ATTRIBUTE1'
AND qpa1.comparison_operator_code = 'NOT ='
AND qll1.list_header_id = L.PRICED_PRICE_LIST_ID
AND qll1.inventory_item_id = L.inventory_item_id
AND qpa1.pricing_attr_value_from = hl.country
AND qll1.list_header_id = qpa1.list_header_id
AND qll1.list_line_id = qpa1.list_line_id)
) GLOBAL_EOL_DATE,
(SELECT qll.end_date_active
FROM APPS.qp_list_lines_v qll
WHERE qll.list_header_id = L.PRICED_PRICE_LIST_ID
AND NVL(qll.inventory_item_id,qll.product_attr_value) = L.inventory_item_id
-- AND NVL(qll.end_date_active,hr_general.end_of_time)
AND qll.end_date_active
= (SELECT MAX(NVL(qll1.end_date_active,TO_DATE('31/12/3714','MM/DD/YYYY')))
-- = (SELECT MAX(qll1.end_date_active)
FROM APPS.qp_list_lines_v qll1
WHERE qll1.list_header_id = L.PRICED_PRICE_LIST_ID
AND NVL(qll1.inventory_item_id,qll1.product_attr_value) = L.inventory_item_id)
) LINE_EOL_DATE
FROM apps.aso_quote_headers_all h,
apps.ASO_QUOTE_STATUSES_TL ST,
apps.ASO_QUOTE_LINES_ALL L,
apps.GL_DAILY_RATES GDL,
apps.FND_USER USERS,
apps.OE_TRANSACTION_TYPES_TL ttl,
apps.MTL_SYSTEM_ITEMS_B msi,
APPS.xxsun_extend_dff_attribs EDFFH,
APPS.xxsun_extend_dff_attribs EDFFL,
APPS.hz_locations HL,
APPS.hz_party_sites HPS,
APPS.aso_shipments ASH
WHERE h.order_type_id = ttl.TRANSACTION_TYPE_ID
AND TTL.LANGUAGE ='US'
AND H.QUOTE_STATUS_ID = ST.QUOTE_STATUS_ID
AND ST.LANGUAGE ='US'
AND GDL.FROM_CURRENCY(+) = h.currency_code
AND GDL.TO_CURRENCY(+) = 'USD'
AND GDL.CONVERSION_TYPE(+) = 'Spot'
AND GDL.CONVERSION_DATE(+) = TRUNC(SYSDATE)
AND H.QUOTE_HEADER_ID = L.QUOTE_HEADER_ID
AND L.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND L.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND H.CREATED_BY = USERS.USER_ID
AND USERS.USER_NAME = 'IBIS_D4_CONV'
AND NVL(L.LINE_quote_price,0) <> 0
AND EDFFh.attribute_context = 'QUOTE_HEADER' --CHECK FOR QUOTE HEADER ATRIBUTES
AND EDFFh.object_pk1 = L.quote_HEADER_ID
AND EDFFh.attribute62 IS NULL -- End customer EDFF on header
AND EDFFh.attribute11 IS NULL -- COFD on header
AND EDFFl.attribute_context = 'QUOTE_LINE' --CHECK FOR QUOTE HEADER ATRIBUTES
AND EDFFl.object_pk1 = L.quote_line_ID
AND EDFFl.attribute63 IS NULL -- End Customer EDFF on line
AND ASH.quote_header_id = L.quote_header_id
AND ASH.quote_line_id = L.quote_line_id
AND ASH.ship_to_party_site_id = HPS.party_site_id
AND HPS.location_id = HL.location_id --get country code
AND hl.country IS NOT NULL
) A
WHERE a.quote_expiration_date > NVL(a.EOL_DATE,NVL(GLOBAL_EOL_DATE,LINE_EOL_DATE))
) quo}'
where rule_id = 6001;
Re: How to update CLOB column having more than 4000 chars [message #352281 is a reply to message #352273] Mon, 06 October 2008 23:23 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Previous Topic: Bulk insert problems
Next Topic: problem in outer join query
Goto Forum:
  


Current Time: Fri Dec 09 21:20:21 CST 2016

Total time taken to generate the page: 0.20844 seconds