-2

I have following huge HTML string and I need to insert it into Oracle table. What is the most suitable data type to store those data? I searched on google and found some source, Clob is most suitable for that. is it correct? If Clob but How can I insert following data into Clob column?

<!DOCTYPE html><html><head><style></style> </head><body><div style="display: flex; justify-content:center;"> <div style="padding: 0.75em; z-index: -1; background: rgb(79,206,213); background: linear-gradient(180deg, rgba(79,206,213,.1) 0%, rgba(20,173,227,.1) 50%, rgba(253,0,243,.1) 100%);"> <div style="border-radius: 1em; padding: 3em 1em 1em 1em; z-index: 0; background: white;"> <p style='margin-top:0in;margin-right:0in;margin-bottom:8.0pt;margin-left:0in;line-height:107%;font-size:15px;font-family:"Calibri",sans-serif;text-align:center;'><img width="109" src="" alt="image"></p> <p style='margin-top:0in;margin-right:0in;margin-bottom:8.0pt;margin-left:0in;line-height:107%;font-size:15px;font-family:"Calibri",sans-serif;text-align:center;'>&nbsp;</p> <p style='margin-top:0in;margin-right:0in;margin-bottom:8.0pt;margin-left:0in;line-height:107%;font-size:15px;font-family:"Calibri",sans-serif;text-align:center;'>Transaction Summary</p> <p style='margin-top:0in;margin-right:0in;margin-bottom:8.0pt;margin-left:0in;line-height:107%;font-size:15px;font-family:"Calibri",sans-serif;text-align:center;'><span style="font-size:12px;line-height:107%;color:#AFABAB;">Amount</span></p> <p style='margin-top:0in;margin-right:0in;margin-bottom:8.0pt;margin-left:0in;line-height:107%;font-size:15px;font-family:"Calibri",sans-serif;text-align:center;'><span style="font-size:11px;line-height:107%;color:#AFABAB;">LKR&nbsp;</span><strong><span style="font-size:16px;line-height:107%;color:#0070C0;">2,000.00</span></strong></p> <p style='margin-top:0in;margin-right:0in;margin-bottom:8.0pt;margin-left:0in;line-height:107%;font-size:15px;font-family:"Calibri",sans-serif;text-align:center;'><span style="font-size:12px;line-height:107%;">Commercial Bank</span></p> <p style='margin-top:0in;margin-right:0in;margin-bottom:8.0pt;margin-left:0in;line-height:107%;font-size:15px;font-family:"Calibri",sans-serif;text-align:center;'><span style="font-size:12px;line-height:107%;">&nbsp;</span></p> <ul style="display: flex;flex-direction:column;list-style:none;width:319.5pt;row-gap: .5em;margin-bottom: 2em; padding: 0 2em; font-family:'Calibri',sans-serif;"> <li style="border:1px solid #e3e3e3;padding: .4em; border-radius: .5em; display:grid; grid-template-columns: 1fr 1fr;align-items:center;"> <span style="color:grey;">Payment type</span> <span style="color:blackt;text-align:right;">Card</span> </li> <li style="border:1px solid #e3e3e3;padding: .4em; border-radius: .5em; display:grid; grid-template-columns: 1fr 1fr;align-items:center;"> <span style="color:grey;">Paid to</span> <span style="color:blackt;text-align:right;">12345678</span> </li> <li style="border:1px solid #e3e3e3;padding: .4em; border-radius: .5em; display:grid; grid-template-columns: 1fr 1fr;align-items:center;"> <span style="color:grey;">Bank</span> <span style="color:blackt;text-align:right;">BTC</span> </li> <li style="border:1px solid #e3e3e3;padding: .4em; border-radius: .5em; display:grid; grid-template-columns: 1fr 1fr;align-items:center;"> <span style="color:grey;">Date of time</span> <span style="color:blackt;text-align:right;">16-Aug-2021 11:50</span> </li> <li style="border:1px solid #e3e3e3;padding: .4em; border-radius: .5em; display:grid; grid-template-columns: 1fr 1fr;align-items:center;"> <span style="color:grey;">Payment Status</span> <span style="color:blackt;text-align:right;">Success</span> </li> <li style="border:1px solid #e3e3e3;padding: .4em; border-radius: .5em; display:grid; grid-template-columns: 1fr 1fr;align-items:center;"> <span style="color:grey;">Reference number</span> <span style="color:blackt;text-align:right;">3243322</span> </li> <li style="border:1px solid #e3e3e3;padding: .4em; border-radius: .5em; display:grid; grid-template-columns: 1fr 1fr;align-items:center;"> <span style="color:grey;">Remark</span> <span style="color:blackt;text-align:right;">Monthly bonus</span> </li> </ul> <p style='border-top:1px solid #e3e3e3; margin-top:0in;margin-right:0in;margin-bottom:8.0pt;margin-left:0in;line-height:107%;font-size:15px;font-family:"Calibri",sans-serif;text-align:center;'><span style="color:#2E75B6;">&nbsp;</span></p> <p style='margin-top:0in;margin-right:0in;margin-bottom:8.0pt;margin-left:0in;line-height:107%;font-size:15px;font-family:"Calibri",sans-serif;text-align:center;'><span style="color:#2E75B6;">Aug-2021 12:56 PM</span></p> </div> </div> </div></body></html>
Gamma
  • 317
  • 1
  • 6
  • 22
  • 4
    Where is the HTML coming from - are you copying/pasting it, or is it in a file that can be access from the DB server (so you could load as an external table), or a client (to use SQL\*Loader), or from an HTTP call made from within the DB? – Alex Poole Feb 09 '22 at 11:54

1 Answers1

0

You can try, divide html string with less than 4000 chars and concatenate them with || and use the to_clob method

update YOURTABLENAME set YOURCOLUMN = to_clob('HTML string with less than 4000 chars') 
             || to_clob('HTML string with less than 4000')
Sachith Wickramaarachchi
  • 5,546
  • 6
  • 39
  • 68
  • 1
    You do not need to wrap every string literal with `TO_CLOB()`. It is sufficient to only wrap the first string literal or to use `EMPTY_CLOB() || 'literal1' || 'literal2' || '...'`. The alternative is to pass the CLOB value via a bind variable. – MT0 Feb 09 '22 at 14:06