OraFAQ Forum: SQL & PL/SQL » How to update CLOB column having more than 4000 chars

Home » SQL & PL/SQL » SQL & PL/SQL » How to update CLOB column having more than 4000 chars

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to update CLOB column having more than 4000 chars [message #188006]

Wed, 16 August 2006 12:43

sreehari
Messages: 101
Registered: May 2006

Senior Member

Hi

all pls let me know how to update column with the data more than
4000 characters..

Thanks in advance.

Report message to a moderator

Re: How to update CLOB column having more than 4000 chars [message #188007 is a reply to message #188006]

Wed, 16 August 2006 13:18

valkyr
Messages: 6
Registered: June 2006

Junior Member

I believe you have to use bind variables to update any LOB column thats larger than 4K.

pseudo code:

declare
var_text1 clob;

assign value to text1;

begin
update table
set clobcolumn = var_text1
where condition1=
and condition2= ;

end;
/

Report message to a moderator

Re: How to update CLOB column having more than 4000 chars [message #188102 is a reply to message #188006]

Thu, 17 August 2006 02:25

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

What problem are you having exactly?
Are you ding this in SQL or Pl/Sql?
Are you trying to set the CLOB to a value from another table or to a literal?

If you show us an example, we'll be able to help you better.

Report message to a moderator

Re: How to update CLOB column having more than 4000 chars [message #188743 is a reply to message #188102]

Mon, 21 August 2006 07:20

sreehari
Messages: 101
Registered: May 2006

Senior Member

Hi

I got The following stmt from the error log
Since it is written at the application level, i can't change the code like
declare
msg clob
...

end;

UPDATE SHOPPING_CART

SET CART_INFO =N'<MSG>
<PROPS>
<_NAME>CART_INFO</_NAME>
<PAYMENT_METHOD_CODE>PO</PAYMENT_METHOD_CODE>
<CART_MODE>10033</CART_MODE>
<CART_MODE_CODE>sf4</CART_MODE_CODE>
<PROGRAM_ID>10004</PROGRAM_ID>
<PROGRAM_CODE>STND</PROGRAM_CODE>
<LOGGED_IN_USER_COMPANY_TIER_CODE>ENDTR</LOGGED_IN_USER_COMPANY_TIER_CODE>
<LOGGED_IN_USER_COMPANY_ID>12520</LOGGED_IN_USER_COMPANY_ID>
<LOGGED_IN_USER_ID>16792</LOGGED_IN_USER_ID>
<SHIP_TO_PARTY_TYPE_CODE>ENDCO</SHIP_TO_PARTY_TYPE_CODE>
<CURRENCY_CODE>GBP</CURRENCY_CODE>
<CONTRACT_ID></CONTRACT_ID>
<REPRICE_CART>0</REPRICE_CART>
<SUB_TOTAL></SUB_TOTAL>
<SLDCO_COMPANY_ID>12520</SLDCO_COMPANY_ID>
<SLDCO_COMPANY_ORDER_NUM></SLDCO_COMPANY_ORDER_NUM>
<DELCO_COMPANY_ID></DELCO_COMPANY_ID>
<DELCO_COMPANY_ORDER_NUM></DELCO_COMPANY_ORDER_NUM>
<ENDCO_COMPANY_ID>12520</ENDCO_COMPANY_ID>
<ENDCO_COMPANY_ORDER_NUM></ENDCO_COMPANY_ORDER_NUM>
<END_USER_ID>16792</END_USER_ID>
</PROPS>
<SUBMSGS>
<MSG>
<PROPS>
<_NAME>CART_ITEMS</_NAME>
</PROPS>
<SUBMSGS>
<MSG>
<PROPS>
<_NAME>1</_NAME>
<LINE_NUMBER>1</LINE_NUMBER>
<IS_QTY_EDITABLE>-1</IS_QTY_EDITABLE>
<SKU_NUMBER>MKK_LIC</SKU_NUMBER>
<SKU_DESCRIPTION>MKK_LIC</SKU_DESCRIPTION>
<PRODUCT_ID>10554</PRODUCT_ID>
<PARENT_LINE_NUMBER></PARENT_LINE_NUMBER>
<RELATED_LINE_TYPE_CODE></RELATED_LINE_TYPE_CODE>
<MANUAL_PRICING_ACTION_CODE></MANUAL_PRICING_ACTION_CODE>
<MANUAL_PRICING_VALUE></MANUAL_PRICING_VALUE>
<USE_REQUESTED_PRICE></USE_REQUESTED_PRICE>
<CAN_EDIT_ADJUSTED_PRICE></CAN_EDIT_ADJUSTED_PRICE>
<CAN_DISPLAY_MANUAL_PRICE_REASON></CAN_DISPLAY_MANUAL_PRICE_REASON>
<MANUAL_PRICING_REASON_CODE></MANUAL_PRICING_REASON_CODE>
<CALCULATED_UNIT_PRICE></CALCULATED_UNIT_PRICE>
<ACTUAL_PRICE></ACTUAL_PRICE>
<EXTENDED_PRICE></EXTENDED_PRICE>
<REPRICE_ITEM>0</REPRICE_ITEM>
<CART_QTY>1</CART_QTY>
<IS_DISABLED></IS_DISABLED>
</PROPS>
<SUBMSGS>
</SUBMSGS>
</MSG>
<MSG>
<PROPS>
<_NAME>2</_NAME>
<LINE_NUMBER>2</LINE_NUMBER>
<IS_QTY_EDITABLE>-1</IS_QTY_EDITABLE>
<SKU_NUMBER>MKK_LIC</SKU_NUMBER>
<SKU_DESCRIPTION>MKK_LIC</SKU_DESCRIPTION>
<PRODUCT_ID>10554</PRODUCT_ID>
<PARENT_LINE_NUMBER></PARENT_LINE_NUMBER>
<RELATED_LINE_TYPE_CODE></RELATED_LINE_TYPE_CODE>
<MANUAL_PRICING_ACTION_CODE></MANUAL_PRICING_ACTION_CODE>
<MANUAL_PRICING_VALUE></MANUAL_PRICING_VALUE>
<CALCULATED_UNIT_PRICE></CALCULATED_UNIT_PRICE>
<ACTUAL_PRICE></ACTUAL_PRICE>
<EXTENDED_PRICE></EXTENDED_PRICE>
<REPRICE_ITEM>0</REPRICE_ITEM>
<USE_REQUESTED_PRICE></USE_REQUESTED_PRICE>
<CAN_EDIT_ADJUSTED_PRICE></CAN_EDIT_ADJUSTED_PRICE>
<CAN_DISPLAY_MANUAL_PRICE_REASON></CAN_DISPLAY_MANUAL_PRICE_REASON>
<MANUAL_PRICING_REASON_CODE></MANUAL_PRICING_REASON_CODE>
<CART_QTY>2</CART_QTY>
<IS_DISABLED></IS_DISABLED>
</PROPS>
<SUBMSGS>
</SUBMSGS>
</MSG>
<MSG>
<PROPS>
<_NAME>3</_NAME>
<LINE_NUMBER>3</LINE_NUMBER>
<IS_QTY_EDITABLE>-1</IS_QTY_EDITABLE>
<SKU_NUMBER>MKK_LIC</SKU_NUMBER>
<SKU_DESCRIPTION>MKK_LIC</SKU_DESCRIPTION>
<PRODUCT_ID>10554</PRODUCT_ID>
<PARENT_LINE_NUMBER></PARENT_LINE_NUMBER>
<RELATED_LINE_TYPE_CODE></RELATED_LINE_TYPE_CODE>
<MANUAL_PRICING_ACTION_CODE></MANUAL_PRICING_ACTION_CODE>
<MANUAL_PRICING_VALUE></MANUAL_PRICING_VALUE>
<CALCULATED_UNIT_PRICE></CALCULATED_UNIT_PRICE>
<ACTUAL_PRICE></ACTUAL_PRICE>
<EXTENDED_PRICE></EXTENDED_PRICE>
<REPRICE_ITEM>0</REPRICE_ITEM>
<USE_REQUESTED_PRICE></USE_REQUESTED_PRICE>
<CAN_EDIT_ADJUSTED_PRICE></CAN_EDIT_ADJUSTED_PRICE>
<CAN_DISPLAY_MANUAL_PRICE_REASON></CAN_DISPLAY_MANUAL_PRICE_REASON>
<MANUAL_PRICING_REASON_CODE></MANUAL_PRICING_REASON_CODE>
<CART_QTY>3</CART_QTY>
<IS_DISABLED></IS_DISABLED>
</PROPS>
<SUBMSGS>
</SUBMSGS>
</MSG>
</SUBMSGS>
</MSG>
</SUBMSGS>
</MSG>',
UPD_DATE=TO_DATE('21-Aug-2006 17:21:27','DD-MON-YYYY HH24:MI:SS') ,
UPD_USER=N'USER_16792',
UPD_VERSION=2

WHERE (SHOPPING_CART.SHOPPING_CART_ID = 10017
AND SHOPPING_CART.UPD_VERSION = 1)

Thanks in advance.

Report message to a moderator

Re: How to update CLOB column having more than 4000 chars [message #188852 is a reply to message #188743]

Tue, 22 August 2006 02:28

Aju
Messages: 94
Registered: October 2004

Member

I suggest to get the error message from the application log and debug accordingly.

SQL> CREATE TABLE SHOPPING_CART (x CLOB);

Table created.

SQL>
SQL> INSERT INTO SHOPPING_CART VALUES(NULL);

1 row created.

SQL>
SQL> UPDATE SHOPPING_CART SET x =
2 '<MSG>
3 <PROPS>
4 <_NAME>CART_INFO</_NAME>
5 <PAYMENT_METHOD_CODE>PO</PAYMENT_METHOD_CODE>
6 <CART_MODE>10033</CART_MODE>
7 <CART_MODE_CODE>sf4</CART_MODE_CODE>
8 <PROGRAM_ID>10004</PROGRAM_ID>
9 <PROGRAM_CODE>STND</PROGRAM_CODE>
10 <LOGGED_IN_USER_COMPANY_TIER_CODE>ENDTR</LOGGED_IN_USER_COMPANY_TIER_CODE>
11 <LOGGED_IN_USER_COMPANY_ID>12520</LOGGED_IN_USER_COMPANY_ID>
12 <LOGGED_IN_USER_ID>16792</LOGGED_IN_USER_ID>
13 <SHIP_TO_PARTY_TYPE_CODE>ENDCO</SHIP_TO_PARTY_TYPE_CODE>
14 <CURRENCY_CODE>GBP</CURRENCY_CODE>
15 <CONTRACT_ID></CONTRACT_ID>
16 <REPRICE_CART>0</REPRICE_CART>
17 <SUB_TOTAL></SUB_TOTAL>
18 <SLDCO_COMPANY_ID>12520</SLDCO_COMPANY_ID>
19 <SLDCO_COMPANY_ORDER_NUM></SLDCO_COMPANY_ORDER_NUM>
20 <DELCO_COMPANY_ID></DELCO_COMPANY_ID>
21 <DELCO_COMPANY_ORDER_NUM></DELCO_COMPANY_ORDER_NUM>
22 <ENDCO_COMPANY_ID>12520</ENDCO_COMPANY_ID>
23 <ENDCO_COMPANY_ORDER_NUM></ENDCO_COMPANY_ORDER_NUM>
24 <END_USER_ID>16792</END_USER_ID>
25 </PROPS>
26 <SUBMSGS>
27 <MSG>
28 <PROPS>
29 <_NAME>CART_ITEMS</_NAME>
30 </PROPS>
31 <SUBMSGS>
32 <MSG>
33 <PROPS>
34 <_NAME>1</_NAME>
35 <LINE_NUMBER>1</LINE_NUMBER>
36 <IS_QTY_EDITABLE>-1</IS_QTY_EDITABLE>
37 <SKU_NUMBER>MKK_LIC</SKU_NUMBER>
38 <SKU_DESCRIPTION>MKK_LIC</SKU_DESCRIPTION>
39 <PRODUCT_ID>10554</PRODUCT_ID>
40 <PARENT_LINE_NUMBER></PARENT_LINE_NUMBER>
41 <RELATED_LINE_TYPE_CODE></RELATED_LINE_TYPE_CODE>
42 <MANUAL_PRICING_ACTION_CODE></MANUAL_PRICING_ACTION_CODE>
43 <MANUAL_PRICING_VALUE></MANUAL_PRICING_VALUE>
44 <USE_REQUESTED_PRICE></USE_REQUESTED_PRICE>
45 <CAN_EDIT_ADJUSTED_PRICE></CAN_EDIT_ADJUSTED_PRICE>
46 <CAN_DISPLAY_MANUAL_PRICE_REASON></CAN_DISPLAY_MANUAL_PRICE_REASON>
47 <MANUAL_PRICING_REASON_CODE></MANUAL_PRICING_REASON_CODE>
48 <CALCULATED_UNIT_PRICE></CALCULATED_UNIT_PRICE>
49 <ACTUAL_PRICE></ACTUAL_PRICE>
50 <EXTENDED_PRICE></EXTENDED_PRICE>
51 <REPRICE_ITEM>0</REPRICE_ITEM>
52 <CART_QTY>1</CART_QTY>
53 <IS_DISABLED></IS_DISABLED>
54 </PROPS>
55 <SUBMSGS>
56 </SUBMSGS>
57 </MSG>
58 <MSG>
59 <PROPS>
60 <_NAME>2</_NAME>
61 <LINE_NUMBER>2</LINE_NUMBER>
62 <IS_QTY_EDITABLE>-1</IS_QTY_EDITABLE>
63 <SKU_NUMBER>MKK_LIC</SKU_NUMBER>
64 <SKU_DESCRIPTION>MKK_LIC</SKU_DESCRIPTION>
65 <PRODUCT_ID>10554</PRODUCT_ID>
66 <PARENT_LINE_NUMBER></PARENT_LINE_NUMBER>
67 <RELATED_LINE_TYPE_CODE></RELATED_LINE_TYPE_CODE>
68 <MANUAL_PRICING_ACTION_CODE></MANUAL_PRICING_ACTION_CODE>
69 <MANUAL_PRICING_VALUE></MANUAL_PRICING_VALUE>
70 <CALCULATED_UNIT_PRICE></CALCULATED_UNIT_PRICE>
71 <ACTUAL_PRICE></ACTUAL_PRICE>
72 <EXTENDED_PRICE></EXTENDED_PRICE>
73 <REPRICE_ITEM>0</REPRICE_ITEM>
74 <USE_REQUESTED_PRICE></USE_REQUESTED_PRICE>
75 <CAN_EDIT_ADJUSTED_PRICE></CAN_EDIT_ADJUSTED_PRICE>
76 <CAN_DISPLAY_MANUAL_PRICE_REASON></CAN_DISPLAY_MANUAL_PRICE_REASON>
77 <MANUAL_PRICING_REASON_CODE></MANUAL_PRICING_REASON_CODE>
78 <CART_QTY>2</CART_QTY>
79 <IS_DISABLED></IS_DISABLED>
80 </PROPS>
81 <SUBMSGS>
82 </SUBMSGS>
83 </MSG>
84 <MSG>
85 <PROPS>
86 <_NAME>3</_NAME>
87 <LINE_NUMBER>3</LINE_NUMBER>
88 <IS_QTY_EDITABLE>-1</IS_QTY_EDITABLE>
89 <SKU_NUMBER>MKK_LIC</SKU_NUMBER>
90 <SKU_DESCRIPTION>MKK_LIC</SKU_DESCRIPTION>
91 <PRODUCT_ID>10554</PRODUCT_ID>
92 <PARENT_LINE_NUMBER></PARENT_LINE_NUMBER>
93 <RELATED_LINE_TYPE_CODE></RELATED_LINE_TYPE_CODE>
94 <MANUAL_PRICING_ACTION_CODE></MANUAL_PRICING_ACTION_CODE>
95 <MANUAL_PRICING_VALUE></MANUAL_PRICING_VALUE>
96 <CALCULATED_UNIT_PRICE></CALCULATED_UNIT_PRICE>
97 <ACTUAL_PRICE></ACTUAL_PRICE>
98 <EXTENDED_PRICE></EXTENDED_PRICE>
99 <REPRICE_ITEM>0</REPRICE_ITEM>
100 <USE_REQUESTED_PRICE></USE_REQUESTED_PRICE>
101 <CAN_EDIT_ADJUSTED_PRICE></CAN_EDIT_ADJUSTED_PRICE>
102 <CAN_DISPLAY_MANUAL_PRICE_REASON></CAN_DISPLAY_MANUAL_PRICE_REASON>
103 <MANUAL_PRICING_REASON_CODE></MANUA_PRICE_REASON></CAN_DISPLAY_MANUAL_PRICE_REASON>
104 <MANUAL_PRICING_REASON_CODE></MANUAL_PRICING_REASON_CODE>
105 <CART_QTY>3</CART_QTY>
106 <IS_DISABLED></IS_DISABLED>
107 </PROPS>
108 <SUBMSGS>
109 </SUBMSGS>
110 </MSG>
111 </SUBMSGS>
112 </MSG>
113 </SUBMSGS>
114 </MSG>';

1 row updated.

SQL>

Report message to a moderator

Previous Topic:	Performance on Object type collection
Next Topic:	Bind Variables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 15:35:28 CST 2024