Re: Any woraround for this ....?

From: Jared Still <jkstill_at_cybcon.com>
Date: Wed, 04 Sep 2002 08:13:33 -0800
Message-ID: <F001.004C7741.20020904081333@fatcity.com>

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';
   new Object 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: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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).

Received on Wed Sep 04 2002 - 11:13:33 CDT