OraFAQ Forum: SQL & PL/SQL » Replace HTML codes with the special characters in CLOB data types.

Home » SQL & PL/SQL » SQL & PL/SQL » Replace HTML codes with the special characters in CLOB data types.

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

Replace HTML codes with the special characters in CLOB data types. [message #613855]

Tue, 13 May 2014 01:30

rameshaimhigh@gmail.com
Messages: 10
Registered: October 2013

Junior Member

I need to replace HTML codes with the special characters.

Table A having column as C with datatype as CLOG

In that we are having data like 'informaci&oacute' with HTML codes
i need to replace the original symbol

i tried like below

select select UTL_I18N.unescape_reference (C) from A.

and i got below errow
Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 5924, maximum: 4000)

is there any way to support clob content

Report message to a moderator

Re: Replace HTML codes with the special characters in CLOB data types. [message #613856 is a reply to message #613855]

Tue, 13 May 2014 01:44

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

UTL_I18N.unescape_reference input/output parameters are a VARCHAR2 limited to 4000 bytes in SQL. So split your input.
ALWAYS post your Oracle version with a question.

Report message to a moderator

Re: Replace HTML codes with the special characters in CLOB data types. [message #613857 is a reply to message #613855]

Tue, 13 May 2014 01:46

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

is there any way to support clob content

Create your own function that splits the input CLOB, converts the chunks, concatenates them and returns a CLOB.

[Edit: English]

[Updated on: Tue, 13 May 2014 03:19]

Report message to a moderator

Re: Replace HTML codes with the special characters in CLOB data types. [message #613859 is a reply to message #613855]

Tue, 13 May 2014 01:57

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

As Michel suggested, to read the input clob in chunks, you could use DBMS_LOB.SUBSTR function.

Report message to a moderator

Re: Replace HTML codes with the special characters in CLOB data types. [message #613861 is a reply to message #613859]

Tue, 13 May 2014 02:30

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Since 10.2 you don't need DBMS_LOB package to extract a substring of a CLOB, the standard SUBSTR function works with CLOB.

[Edit: typo]

[Updated on: Tue, 13 May 2014 03:18]

Report message to a moderator

Re: Replace HTML codes with the special characters in CLOB data types. [message #613863 is a reply to message #613861]

Tue, 13 May 2014 03:01

rameshaimhigh@gmail.com
Messages: 10
Registered: October 2013

Junior Member

Thanks for your solution. Yes i too think this way (splitting the text , change and merge).
My only concern is, In the text if the chunk position is 3998 to 4002 position means, then half of the chunk character will be in one set and remaining will be in another set. Any idea please welcome

Report message to a moderator

Re: Replace HTML codes with the special characters in CLOB data types. [message #613864 is a reply to message #613863]

Tue, 13 May 2014 03:07

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

All you need to do is CONCATENATE.

Edit : Typo

[Updated on: Tue, 13 May 2014 03:48]

Report message to a moderator

Previous Topic:	ORA-01555 snapshot too old
Next Topic:	ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 23:05:46 CDT 2024