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  |
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;
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Dec 05 15:18:53 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
