Re: How to export from Oracle to text using Perl

Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.1950edd1b793a2309897c6_at_news.la.sbcglobal.net>...
> 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?

I wish google included regular expressions so I could search for SQL*Plus! (Or have I missed something obvious?)

>
> 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.

Aside from what the others said about perceived slowness due to terminal output, I idly wonder whether perl makes better use of memory on unix boxen to be able to actually spit out data to a file faster than SQL*Plus. I've seen some situations where it appeared that piping sqlplus output through awk with some data manipulation, then to a file, appeared faster than going directly from the same sqlplus to a file. But I've never taken the time to follow up and see if it is just an artifact, illusion or what, since I usually am manipulating for a reason, and usually doing it to get rid of a huge join anyways.

>
> 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.

Ditto thanks, I never seem to get enough perl practice to get comfy with it.

jg

--
@home.com is bogus.
End of world nigh, news at eleven.  http://www.zetatalk.com