Insert > 2499 [Oracle Help]

Prev: Multiple Java requirements in CA (Only local candidates required)
Next: Date... Fixed Day, minus one month

From: The Magnet on 21 Jul 2010 17:23

I have some insert statements with values that are > 2500 characters.
I cannot make it a .CSV because the text has commas in it. It is a
bunch of HTML. Any suggestions?

Example:

INSERT INTO MESSAGES (message_id, sec_id, variables, name, body,
created, last_changed, created_by, changed_by, status) VALUES(5, 5,
'{ADID}', 'Premium Login Default $99', '<table border="0"
cellspacing="0" cellpadding="0" width="575" align="center">\r\n
<tbody>\r\n <tr>\r\n <td style="padding-bottom: 8px;
line-height: 18px; padding-left: 8px; padding-right: 8px; font-size:
14px; padding-top: 8px">\r\n <p><style type="text/css">\r\n
\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n</style><span
class="Headline"><script>utmx_section("BODY_HEADLINE")</script>Get
Free Access<br />\r\n to Premium Now </span></p>\r
\n <p>Now you can unlock the Premium icon<img alt="ZP"
width="18" height="14" src="/images/premium_small.gif" />, and
discover its secrets, including the best stocks to buy and sell for
free.  Premium lets you apply the full force of Rank earnings
information even in this trying market.</p>\r\n <p>When you
subscribe to Premium for 30 days free, you’ll have an entire
month to:</p>\r\n <ul>\r\n
<li><strong>Buy<strong> the best stocks</strong></strong> with our
list of #1 Rank Strong Buys. Over 20 years, it triples the
S&P 500, averaging an amazing +28% yearly gain.<br />\r
\n  </li>\r\n <li
class="mktgcopy"><strong>Sell<strong> the worst stocks</strong></
strong> after scanning the list of #5 Rank Strong Sells.<br />\r
\n  </li>\r\n
<li><strong>Target<strong> today''s hottest industries</strong></
strong> with daily updates of Industry Rank.<br />\r\n
 </li>\r\n <li><strong>Be<strong> alerted</
strong></strong> to fast-breaking buy and sell opportunities.<br />\r
\n  </li>\r\n <li><strong>Get <em>
7 Best Stocks for the Next 30 Days. </em></strong>It''s packed with
picks, facts, and stats that are expected to blast off in the days
ahead.</li>\r\n </ul>\r\n <p>This free trial
offer may be withdrawn at anytime. So please sign-up below right now.</
p>\r\n <script>utmx_section("FREE_TRIAL")</script><center>\r
\n <table border="0" cellspacing="0" cellpadding="0"
width="300">\r\n <tbody>\r\n <tr>\r
\n <td align="center"><a href="https://
www.zk.com/registration/premium/trial/?adid={ADID}"><img
border="0" hspace="5" alt="Free Trial" width="114" height="55"
src="https://woas.zk.com/adv/images/zp_freetrialBtn.gif" /></a></td>\r
\n <td align="center"><a href="https://
www.zk.com/registration/premium_more_info.php?adid={ADID}"><img
border="0" hspace="5" alt="Learn More" width="114" height="55"
src="https://woas.zk.com/adv/images/zp_learnmoreBtn.gif" /></a></td>\r
\n \r
\n </tr>\r\n </tbody>\r\n
</table>\r\n </center></td>\r\n </tr>\r\n </tbody>
\r\n</table>', '2005-11-21 12:17:10', '2010-03-17 14:20:34', 'joseph',
'fabre', 'ONLINE');

From: joel garry on 21 Jul 2010 20:02

On Jul 21, 2:23 pm, The Magnet <a...(a)unsu.com> wrote:
> I have some insert statements with values that are > 2500 characters.
> I cannot make it a .CSV because the text has commas in it. It is a
> bunch of HTML. Any suggestions?
>
> Example:
>
> INSERT INTO MESSAGES (message_id, sec_id, variables, name, body,
> created, last_changed, created_by, changed_by, status) VALUES(5,
....
> 'fabre', 'ONLINE');

http://asktom.oracle.com/pls/apex/ASKTOM.download_file?p_file=6551245110710909097
http://www.oracle.com/technology/sample_code/tech/java/codesnippet/xmldb/HowToLoadLargeXML.html
http://stackoverflow.com/questions/2976154/how-to-store-unlimited-characters-in-oracle-11g

And so on. I'm sure I've seen some other things specific to storing
html, those are just from a quick google.

jg
--
@home.com is bogus.
Interesting truncation in url different than meaning of post:
http://blogs.oracle.com/otn/2010/06/finding_it_difficult_to_keep_u.html

From: Mark D Powell on 23 Jul 2010 08:34

On Jul 21, 8:02 pm, joel garry <joel-ga...(a)home.com> wrote:
> On Jul 21, 2:23 pm, The Magnet <a...(a)unsu.com> wrote:
>
> > I have some insert statements with values that are > 2500 characters.
> > I cannot make it a .CSV because the text has commas in it. It is a
> > bunch of HTML. Any suggestions?
>
> > Example:
>
> > INSERT INTO MESSAGES (message_id, sec_id, variables, name, body,
> > created, last_changed, created_by, changed_by, status) VALUES(5,
> ...
> > 'fabre', 'ONLINE');
>
> http://asktom.oracle.com/pls/apex/ASKTOM.download_file?p_file=6551245....http://www.oracle.com/technology/sample_code/tech/java/codesnippet/xm...http://stackoverflow.com/questions/2976154/how-to-store-unlimited-cha...
>
> And so on. I'm sure I've seen some other things specific to storing
> html, those are just from a quick google.
>
> jg
> --
> @home.com is bogus.
> Interesting truncation in url different than meaning of post:http://blogs..oracle.com/otn/2010/06/finding_it_difficult_to_keep_u.html

I will just add if the data has commas in it then use a different
delimiter such as the tab character or the pipe character.

HTH -- Mark D Powell --

From: Tim X on 25 Jul 2010 18:54

The Magnet <art(a)unsu.com> writes:

> I have some insert statements with values that are > 2500 characters.
> I cannot make it a .CSV because the text has commas in it. It is a
> bunch of HTML. Any suggestions?
>

Why not just put quotes around the fields with commas in them?

I use external tables to load data with commas in the fields by doing
this. The fields are not as large as yours, but it should work - though
I don't know about column limitations and clobs in this context.

Tim

--
tcross (at) rapttech dot com dot au

From: joel garry on 26 Jul 2010 18:55

On Jul 23, 5:34 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote:
> On Jul 21, 8:02 pm, joel garry <joel-ga...(a)home.com> wrote:
>
>
>
> > On Jul 21, 2:23 pm, The Magnet <a...(a)unsu.com> wrote:
>
> > > I have some insert statements with values that are > 2500 characters.
> > > I cannot make it a .CSV because the text has commas in it. It is a
> > > bunch of HTML. Any suggestions?
>
> > > Example:
>
> > > INSERT INTO MESSAGES (message_id, sec_id, variables, name, body,
> > > created, last_changed, created_by, changed_by, status) VALUES(5,
> > ...
> > > 'fabre', 'ONLINE');
>
> >http://asktom.oracle.com/pls/apex/ASKTOM.download_file?p_file=6551245.......
>
> > And so on. I'm sure I've seen some other things specific to storing
> > html, those are just from a quick google.
>
> > jg
> > --
> > @home.com is bogus.
> > Interesting truncation in url different than meaning of post:http://blogs.oracle.com/otn/2010/06/finding_it_difficult_to_keep_u.html
>
> I will just add if the data has commas in it then use a different
> delimiter such as the tab character or the pipe character.
>
> HTH -- Mark D Powell --

That just pushes the problem along to "how do you know whatever
character you pick won't eventually be in your data?" Not long ago I
broke an EDI program. A downstream processor complained about DOS-
style ^Z at the end of a file, so my boss told me to take it out. So
I added a line at the end of the program logic to use the unix command
to remove that line. This vendor-specific program source can be
accessed from unix, or from the application thin client. I habitually
work on the server. My boss decided to make some minor change to the
program, accessing it through the app. The app sucks it down to his
favorite local PC editor. Which sees the ^Z and says "oh, that's the
end of the program source file." But of course, there is no evidence
of this if you don't look past the line. Until all of a sudden your
EDI doesn't work and you notice a truncated source file. The fix is
simple, but the possibilities before you figure out what is going on
can be hair-eliminating.

I've seen untold variants of this with data going into and out of
databases. The "just put quotes around it" has it's own fails,
depending on what you are doing. The example HTML had urls - are
those immune from quotes and commas? (Sorry Tim, I imagine your
reader choked on the example in the OP.)

jg
--
@home.com is bogus.
http://blogs.sun.com/bmc/entry/good_bye_sun

| Next | Last
Pages: 1 2
Prev: Multiple Java requirements in CA (Only local candidates required)
Next: Date... Fixed Day, minus one month