Re: How to export from Oracle to text using Perl

tamarr_at_atomica.com said...
> We are using Oracle 8.1.7 and were surprised to find that there is no
> utility to export entire tables to text files. I searched around and
> found that most people suggested using SQLPlus for this, but I find
> that its relatively slow and really not efficient with space, since
> you have to define the line size in advance.
>
> Using Perl with DBI and DBD we were able to write a very short script
> that not only dumps the entire schema, including CLOBs, but prepares
> it for import into MySql by adding "" around the fields, and escaping
> both " and \. The script is as follows:
>

...[snip code]...

Yes, Perl is a nice - though cryptic - language. How many developers have wished that SQL included regular expressions?

However, I find it surprising that SQL*Plus is slower than Perl. And as far as the inefficient space goes, I assume you're talking about all the trailing spaces you get when you spool to a fixed linesize ... but all you have to do is set trimspool on and - presto! - no more trailing blanks in your spooled output.

Still, that being said, thanks for the perl code! I printed your message and saved it in my "tips" file in case I find a situation where the regexp will come in handy.

-- 
/Karsten
DBA > retired > DBA