How to update BLOB column in Oracle which contains HTML code

Question

I want to update a BLOB column in Oracle DB with HTML. I am using Oracle Oracle Database 11g Release 11.2.0.3.0

The column contains HTML code which would be used in front end in JSP Servlets. The content of cell should be updated with below code

<table width="100%" border="0" cellspacing="0">
 <tbody>
     <tr>
       <td height="130">&nbsp;</td>
     </tr>
     <tr>
       <td height="130">&copy; 2013</td>
     </tr>
 </tbody>
</table>

The above code is formatted.The whole thing is single line.Now when I run a update query as below its showing the message

  UPDATE TemplateTbl
     SET TemplateConetent = (RAWTOHEX (UTL_RAW.cast_to_raw ('<table width="100%" border="0" cellspacing="0"><tbody><tr><td height="130">&nbsp;</td></tr><tr><td height="130">&copy; 2013</td></tr></tbody></table>')))
   WHERE TemplateId = TL2600

Now oracle is asking for variable values because of © and as below

enter image description here

I have tried using underscore, Backslash and percentage in front of &copy and &nbsp. But nothing worked. How do I solve this issue?

sunysen · Accepted Answer · 2013-09-25 06:25:04Z

4

1、sql plus or plsql command window

set define off;

UPDATE TemplateTbl
     SET TemplateConetent = (RAWTOHEX (UTL_RAW.cast_to_raw ('<table width="100%" border="0" cellspacing="0"><tbody><tr><td height="130">&nbsp;</td></tr><tr><td height="130">&copy; 2013</td></tr></tbody></table>')))
   WHERE TemplateId = TL2600

answered Sep 25, 2013 at 6:25

sunysen

2,3711 gold badge15 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Java Beginner Over a year ago

Thank you soo much for your reply.Its working.But in production server I cannot use set define off.

Egor Skriptunoff Over a year ago

@JavaBeginner - Use ...="130">&'||'nbsp;</td>... or ...="130">'||chr(38)||'nbsp;</td>... to avoid magic of & symbol.

Michael-O · Accepted Answer · 2013-09-25 06:14:21Z

2

HTML ist plain text, use an (N)CLOB for that.

answered Sep 25, 2013 at 6:14

Michael-O

18.4k6 gold badges58 silver badges129 bronze badges

Collectives™ on Stack Overflow

How to update BLOB column in Oracle which contains HTML code

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related