Muths,
I hope I'm not beginning to sound like a nag, but PL/SQL is
not really a great tool for what you're trying to do.
I *like* PL/SQL, but it does have its limits. This is one of them.
What you're trying to do in PL/SQL can be done rather simply
in Perl. The heart of a program to do this in Perl looks something
like this:
...
my $tabsql = q{
select table_name
from dba_tables
where owner = 'SCOTT'
};
my $sth = $dbh->prepare($tabsql);
$sth->execute;
while ( my @array = $sth->fetchrow_array ) {
my $tableName = $array[0];
my $file = lc($tableName) . '.txt';
open(DUMP, ">$file" ) || die "could not open $file\n";
my $dumpsql = qq{
select *
from scott.$tableName
};
my $dumpsth = $dbh->prepare($dumpsql);
$dumpsth->execute;
while ( my $aref = $dumpsth->fetchrow_arrayref ) {
print DUMP q{'}, join(q{','},@{$aref}),q{'},qq{\n};
}
}
That's all there is to it. You will write considerably more code to
do that in PL/SQL, and it won't be nearly as fast.
Jared - OCP and Part Time Perl Evangelist ;)
On Wednesday 04 September 2002 03:48, Muthaiah, VSNL wrote:
> Hi,
>
> I'm writing a procedure/script for extract the data of all the tables in a
> schema. When I am trying to spool/write into a file, I am getting the
> following error.
>
> ORA-20001: -20000ORA-20000: ORU-10028: line length overflow, limit of 255
> bytes per line ORA-06512: at "SCOTT.EXTRACT", line 115
> ORA-06512: at line 1
> First I tried to spool to a file.But got the error line length overflow. I
> have tried using the UTL_FILE option also. But getting the same error. Can
> anyone in the list has any work around for this?
>
> Thanks in Advance,
>
>
> Muths
----------------------------------------
Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description:
----------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
