RE: Any woraround for this ....?
Title: Message What is the line of code at line 119? Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 1:58 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Any woraround for this ? Thanks. I did as you said but now with the following error. It writes for the first table and then this error crops. SQL exec extractBEGIN extract; END; *ERROR at line 1:ORA-20001: 1User-Defined ExceptionORA-06512: at "SCOTT.EXTRACT", line 119ORA-06512: at line 1 Am i missing some thing? Rgds, Muths - Original Message - From: Seefelt, Beth To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 7:18 PM Subject: RE: Any woraround for this ? Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AMTo: Multiple recipients of list ORACLE-LSubject: Any woraround for this ? 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: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-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
RE: Any woraround for this ....?
Title: Message Muths, What is the purpose of having the table data dumps? If you're just going to use them to load data back into Oracle, why not just use table level export/import. -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 05, 2002 1:58 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Any woraround for this ? Thanks. I did as you said but now with the following error. It writes for the first table and then this error crops. SQL exec extractBEGIN extract; END; *ERROR at line 1:ORA-20001: 1User-Defined ExceptionORA-06512: at "SCOTT.EXTRACT", line 119ORA-06512: at line 1 Am i missing some thing? Rgds, Muths - Original Message - From: Seefelt, Beth To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 7:18 PM Subject: RE: Any woraround for this ? Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AMTo: Multiple recipients of list ORACLE-LSubject: Any woraround for this ? 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: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-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
Re: Any woraround for this ....?
Title: Message Richard, You are right. But for me, the organization for which I work, has around 20 branches and not all the branches has support person. So It will be difficult for us to get the DMP's on a regular basis. The requirement is that, we are in the process of making a sort of global db of all our branches by automating this. The script that I aim will be run and able to gather the data entered for the day and send it by mail. The other station can just run the same to store the data. Muths - Original Message - From: Richard Huntley To: Multiple recipients of list ORACLE-L Sent: Thursday, September 05, 2002 7:03 PM Subject: RE: Any woraround for this ? Muths, What is the purpose of having the table data dumps? If you're just going to use them to load data back into Oracle, why not just use table level export/import. -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 05, 2002 1:58 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Any woraround for this ? Thanks. I did as you said but now with the following error. It writes for the first table and then this error crops. SQL exec extractBEGIN extract; END; *ERROR at line 1:ORA-20001: 1User-Defined ExceptionORA-06512: at "SCOTT.EXTRACT", line 119ORA-06512: at line 1 Am i missing some thing? Rgds, Muths - Original Message - From: Seefelt, Beth To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 7:18 PM Subject: RE: Any woraround for this ? Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AMTo: Multiple recipients of list ORACLE-LSubject: Any woraround for this ? 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: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-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
RE: Any woraround for this ....?
Title: Message Muths, 2 things. 1. You can easily write a very small script file to take the export, which can be run by even a novice. You can also automate the script so that it runs a particular time and gets mailed to a particular e-mail id. 2. The error which you are getting is due to a user defined exception. Check the code at line 119, and debug the procedure. If you don't have the procedure code, you can extract it from the Data Dictionary. Regards, Naveen -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]]Sent: Friday, September 06, 2002 10:08 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Any woraround for this ? Richard, You are right. But for me, the organization for which I work, has around 20 branches and not all the branches has support person. So It will be difficult for us to get the DMP's on a regular basis. The requirement is that, we are in the process of making a sort of global db of all our branches by automating this. The script that I aim will be run and able to gather the data entered for the day and send it by mail. The other station can just run the same to store the data. Muths - Original Message - From: Richard Huntley To: Multiple recipients of list ORACLE-L Sent: Thursday, September 05, 2002 7:03 PM Subject: RE: Any woraround for this ? Muths, What is the purpose of having the table data dumps? If you're just going to use them to load data back into Oracle, why not just use table level export/import. -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 05, 2002 1:58 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Any woraround for this ? Thanks. I did as you said but now with the following error. It writes for the first table and then this error crops. SQL exec extractBEGIN extract; END; *ERROR at line 1:ORA-20001: 1User-Defined ExceptionORA-06512: at "SCOTT.EXTRACT", line 119ORA-06512: at line 1 Am i missing some thing? Rgds, Muths - Original Message - From: Seefelt, Beth To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 7:18 PM Subject: RE: Any woraround for this ? Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AMTo: Multiple recipients of list ORACLE-LSubject: Any woraround for this ? 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: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-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
Any woraround for this ....?
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: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-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
RE: Any woraround for this ....?
Title: Message Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AMTo: Multiple recipients of list ORACLE-LSubject: Any woraround for this ? 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: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-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
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: -2ORA-2: 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).
RE: Any woraround for this ....?
You can use the sqlplus parameter LINESIZE to specify the size of each line for output. I have used it in the past with lengths of 5000 and more, and it has worked, including spooling to files. The only drawback in this case is that it adjusts each line to LINESIZE (adds spaces to the right where the length is less than that of the parameter LINESIZE), but you can get around with this by setting another sqlplus variable TRIMSPOOL to ON. HTH, Manav. At 05:48 AM 9/4/2002 -0800, you wrote: Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message- From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AM To: Multiple recipients of list ORACLE-L Subject: Any woraround for this ? 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: -2ORA-2: 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
RE: Any woraround for this ....?
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: -2ORA-2: 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).
RE: Any woraround for this ....?
Well, I did say that this was the 'heart' of the program.
A full fledged one with online help is about 420 lines.
It also creates sqlloader files for each of the tables dumped.
If you download the PDBA toolkit from:
http://www.oreilly.com/catalog/oracleperl/pdbatoolkit/
you will find the sqlunldr.pl script in the toolkit.
If you also want detailed instructions on setting up the
toolkit, there is a book available at:
http://www.oreilly.com/catalog/oracleperl/
:)
Jared
Robson, Peter [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/04/2002 10:33 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Any woraround for this ?
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: -2ORA-2: 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
Re: Any woraround for this ....?
Title: Message Thanks. I did as you said but now with the following error. It writes for the first table and then this error crops. SQL exec extractBEGIN extract; END; *ERROR at line 1:ORA-20001: 1User-Defined ExceptionORA-06512: at "SCOTT.EXTRACT", line 119ORA-06512: at line 1 Am i missing some thing? Rgds, Muths - Original Message - From: Seefelt, Beth To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 7:18 PM Subject: RE: Any woraround for this ? Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AMTo: Multiple recipients of list ORACLE-LSubject: Any woraround for this ? 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: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-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
