[FWP] Perl / Oracle

["Nicholson, Dale" <DNicholson@APACMail.com>]

Does anyone have a quick perl function to "tickify" SQL blocks for loading
into an Oracle database?

I need to be able to take blocks of SQL, ticks and all, and insert them into
a table without Oracle recognizing the ticks in the block.  For instance, my
insert block is like the following:

 $SQL=" INSERT INTO doc_documents
  SET docaddeddate, doctitle, docnumber, doclink, doctext, doctype
  VALUES (to_date('$todayis','MM/DD/YYYY')),
         '$doctitle',
         doc_docnum.NextVal,
         'http://dev1/cgi-bin/fetch.cgi?'||doc_docnum.CurrVal,
         '$doctext',
         '$doctype'
         ";

My problem is with the $doctext variable sometimes having ticks or other
special chars in it.  How can I put a wrapper around it so Oracle ignores
any special chars in the variable?

I tried running the following function on the string prior to insert to put
outer ticks on the variable but it did not help.  Maybe I need to parse out
all the special chars and change them to something when I write the data and
parse it again to re-write it when I look at that data later (don't know how
I'd do this)?

sub tickify
{
        my ($str) = @_;
        $str =~ s/'/''/g;
        $str = "'$str'";
        return $str;
}

I'm using OraPerl in perl to open and write to the database but did not put
that part of the code here.  I'm trying to build a documentation database
where users can submit documentation and sample code through a html form
interface (submitting to a perl cgi program to do the insert and
validation).  Maybe there is a special field type I should be using in my
table?  I'm currently using varchar2 for the doctext field.

TIA.

Dale Nicholson

==== Want to unsubscribe from Fun With Perl?  Well, if you insist...
==== Send email to <fwp-request@technofile.org> with message _body_
====   unsubscribe