Avatar of Jeff Fillegar
Jeff Fillegar
Flag for United States of America asked on

c# - Oracle 11.2 - insert html into column

c# - Oracle 11.2 - store html in column.

What is the recommended column type when storing html?

html files > 100K

Snapshot example html (obvisouly not 100K):
<html>
  <body>
    <table width='100%'>
      <tr>
        <td colspan='2' align='center'><b>Legend:</b>
          <font style='background-color: yellow' color='black'>added
          </font>&nbsp;&nbsp;
          <font style='background-color: red' color='black'>removed
          </font>&nbsp;&nbsp;
          <font style='background-color: lightgreen' color='black'>changed
          </font>&nbsp;&nbsp;
          <font style='background-color: red' color='blue'>moved from
          </font>&nbsp;&nbsp;
          <font style='background-color: yellow' color='blue'>moved to
          </font>&nbsp;&nbsp;
          <font style='background-color: white' color='#AAAAAA'>ignored
          </font></td>
      </tr>
      <tr><td><b> File Name : 20120507T094230.xml</b></td>
      </tr>
      <tr>
        <td style="padding-left: 10pt;">
          <font style="background-color: white" color="black">&lt;Envelope
          </font>
          <font style="background-color: white" color="black">xmlns:p1="http://www.w3.org/2001/XMLSchema-instance"
          </font><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          <font style="background-color: white" color="black">xmlns="http://schemas.xmlsoap.org/soap/envelope/"
          </font>
          <font style="background-color: white" color="black">&gt;
          </font></td>
        <td style="padding-left: 10pt;">
          <font style="background-color: white" color="black">&lt;Envelope
          </font>
          <font style="background-color: white" color="black">xmlns:p1="http://www.w3.org/2001/XMLSchema-instance"
          </font><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          <font style="background-color: white" color="black">xmlns="http://schemas.xmlsoap.org/soap/envelope/"
          </font>
          <font style="background-color: white" color="black">&gt;
          </font></td>
      </tr>
      <tr>
        <td style="padding-left: 25pt;">
          <font style="background-color: white" color="black">&lt;Body
          </font>
          <font style="background-color: white" color="black">&gt;
          </font></td>
        <td style="padding-left: 25pt;">
          <font style="background-color: white" color="black">&lt;Body
          </font>
          <font style="background-color: white" color="black">&gt;
          </font></td>
      </tr>        
    </table>
  </body>
</html>

Can I store this in XMLType?  I can view the contents of XMLTYPE using SQLDeveloper vs Clob\Blob.

Thoughts?

Thanks in advance.
Oracle DatabaseC#

Avatar of undefined
Last Comment
Jeff Fillegar

8/22/2022 - Mon
slightwv (䄆 Netminder)

>>What is the recommended column type when storing html?

CLOB or XMLTYPE.

>> I can view the contents of XMLTYPE using SQLDeveloper

Yes.  an XMLTYPE is baically a CLOB with some extra stuff added.  You can easiy convery it to a CLOB with the .getclobval() method.
Jeff Fillegar

ASKER
If I use XMLType do I have to wrap the <html> markup in <xml> tags?
slightwv (䄆 Netminder)

>>If I use XMLType do I have to wrap the <html> markup in <xml> tags?

No.

You will need to ensure the HTML is XHTML compliant.  If you can have 'bad' html, then use a CLOB.

A lot also depends on what you will be doing with the html once you store it.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeff Fillegar

ASKER
1) How can I convert my basic html (example markup in org post) to be XHTML compliant?

2) If I am storing CLOB...is it true that I have to insert the record then update the CLOB column inside a transaction?  I am using ODP.net with OracleParameters.

3) We have a asp.net page that displays the data from the db.  If the entity contains xml or html then we will allow the user to click a link to display it in another browser (popup).
slightwv (䄆 Netminder)

1:  Follow XHTML standards?

http://www.w3.org/TR/xhtml1/

You can check validity at:
http://validator.w3.org/

It is basically making sure your HTML fits a certain model and has no minor flaws that browsers compensate for.

for example, every browser I know of will allow <br> and <hr>.  These are not proper XML syntax.  You need to close the tags:  <br /> <hr />.

2:  "is it true that I have to insert the record then update the CLOB column inside a transaction?"

I do not know what this means.  ODP.Net has a CLOB data type.  You should just be able to insert it like you would any other parameter.

The only gotcha I know about is out output.  You cannot do a simple param.value.  You need to do (param.value).value to see the CLOB.

3:  What you do with the info once you retrieve it is up to you.  Should be a simple asp:hyperlink or similar object with distinct target.  This should cause a new window to open.
Jeff Fillegar

ASKER
The DBA gave us type BLOB.  We are storing html in the column.  Any pros\cons of CLOB vs BLOB?

We are using asp.net dynamic data with EF 4 to display the data with basic CRUD options.  Trying to figure out how to create\modify a custom entity type to display a new html page with the user clicks in the column with the html data.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeff Fillegar

ASKER
I asked the dynamic data comment in another topic:

asp.net - dynamic data site - display blob data
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeff Fillegar

ASKER
Change request submitted to get CLOB.
slightwv (䄆 Netminder)

>>Change request submitted to get CLOB.

Well worth the trouble....  You will thank me later!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

j420exe1,

Why the "B" grade?  This is a penality.  The question originally asked was:

"What is the recommended column type when storing html?"
and
"Can I store this in XMLType?"


How better could I have answered this to receive an "A" grade?

Please review:
https://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=26

A B grade should be given if the solutions require you to do a good amount of extra work to resolve your problem.
slightwv (䄆 Netminder)

j420exe1,

I would still like to know what I could have done to receive an "A" grade.
Jeff Fillegar

ASKER
Oh sorry...didn't know that a B was a "penalty".
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.