Hee hee hee! I just LOVE that phrase 'That''s all there is to it'! (notice my correct SQL syntax here...)
Ummm, errr, well, not quite all. Just a tiny bit of in-line documentation would a) help those of us thinking about embarking on Perl, and b) further enhance your evangelical status, Jared! (Yes yes, it could probably be all worked out, but lets save some time here...) peter edinburgh > -----Original Message----- > From: Jared Still [mailto:[EMAIL PROTECTED]] > Sent: 04 September 2002 17:14 > To: Multiple recipients of list ORACLE-L > Subject: Re: Any woraround for this ....? > > > > 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). > ********************************************************************* This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments. http://www.bgs.ac.uk ********************************************************************* -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter 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).
