buffer overflow, limit of 2000 bytes (how to get around)
Fred Zimmerman
get the buffer overflow message; how do I get rid
of the message, and/or increase the buffer?
DECLARE
aDate DATE;
hType VARCHAR2(4);
hNo VARCHAR2(4);
Loc VARCHAR2(8);
CURSOR chRows IS
SELECT HULLTYPE, HULLNO, LOCATION, ASOFDATE FROM CIMSHIST
GROUP BY HULLTYPE, HULLNO, LOCATION, ASOFDATE;
BEGIN
FOR currentCH in chRows LOOP
DBMS_OUTPUT.PUT_LINE(currentCH.HULLTYPE || ' ' ||
currentCH.HULLNO || ' ' || currentCH.LOCATION || ' ' ||
currentCH.ASOFDATE);
END LOOP;
END;
/
AFDB 1C 701002/S 08-APR-92
AFDB 1C 701002/S 20-SEP-93
AFDB 1C 701002/S 03-MAY-94
AFDB 1C 701002/S 27-DEC-94
AFDB 1C 701002/S 10-JAN-95
AFDB 1C 701002/S 05-FEB-96
AFDB 1C 701002/S 18-APR-96
AFDB 1C 701002/S 23-APR-96
...
...
...
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at line 14
Scott Mattes
dbms_output is a dinosaur for doing debug work. But then, this is Oracle
"Fred Zimmerman" <silver...@yahoo.com> wrote in message
news:a695a242.0211...@posting.google.com...
Sybrand Bakker
<Sc...@TheMattesFamily.ws> wrote:
>Go to tahiti.oracle.com and search for serveroutput.
>
>dbms_output is a dinosaur for doing debug work. But then, this is Oracle
>
No one forces you to use Oracle.
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Jusung Yang
SET SERVEROUTPUT ON SIZE 1000000
from SQL*PLUS b4 running the block or use
dbms_output.enable(1000000)
within the block.
- Jusung Yang
silver...@yahoo.com (Fred Zimmerman) wrote in message news:<a695a242.0211...@posting.google.com>...
John
"Jusung Yang" <Jusun...@yahoo.com> wrote in message
news:130ba93a.0211...@posting.google.com...
> Try :
>
> SET SERVEROUTPUT ON SIZE 1000000
>
> from SQL*PLUS b4 running the block or use
>
> dbms_output.enable(1000000)
>
> within the block.
>
> - Jusung Yang
>
would it have killed them to add a buffer flush routine? :/
Tim Cross
> In PL-SQL I run the following SQL Script, and
> get the buffer overflow message; how do I get rid
> of the message, and/or increase the buffer?
>
DBMS_OUTPUT has a buffer limit of 1000000 bytes. There is no way
around this AFAIK.
The DBMS_OUTPUT is really only useful as a debugging tool.
Some workarounds are -
1. Use UTL_FILE and write the output to a file
2. Insert the data into a table and then query from that table
Personally, I prefer to use the first method. I like to avoid the
second method as it is a type of solution which tends to lead to
temporary tables popping up all over the place. However, if you do
decide to use method 2 and your running 8i or later, look into
temporary global tables (or is it global temporary tables) -
whichever, if you have/want to use temporary tables, they are better
than just creating a normal table in your default tablespace etc.
Tim
Scott Mattes
any long running PL/SQL will over run the buffer and you won't see what you
need AND you can't see the output until the script is all done.
I have developed a package that uses the PRAGMA AUTONOMOUS_TRANSACTION'
compiler directive, found in at least 8i and higher, to save data to a
table. You can select from this table while the script is running to see
what is going on.
Anyone interested should let me know and I can post it.
Sybrand, I don't have a problem with the RDBMS itself, just the ugly tools
Oracle slapped together to interface to it. When I can, I do my work in
Delphi, but since the customer has settled on Oracle tools that is what I
have to program with. I am not one of those who 'likes' to job hop, and this
is a sweet telecommute job otherwise, but I don't like the tools and I think
Larry should be ashamed to be making money with them.
"Tim Cross" <tcr...@pobox.une.edu.au> wrote in message
news:87n0o33...@blind-bat.une.edu.au...
Tim Cross
> To what I am use to, DBMS_OUTPUT does not seem all that useful for debug --
> any long running PL/SQL will over run the buffer and you won't see what you
> need AND you can't see the output until the script is all done.
>
> I have developed a package that uses the PRAGMA AUTONOMOUS_TRANSACTION'
> compiler directive, found in at least 8i and higher, to save data to a
> table. You can select from this table while the script is running to see
> what is going on.
>
> Anyone interested should let me know and I can post it.
>
i agree. DBMS_OUTPUT, even for debugging is really only useful for the
simple case. Not only will any long running PL/SQL overrun the buffer
(though I do try to avoid "long running plsql" when debugging :-), if
you do get an exception (common when debugging!) you get nothing
because displaying the buffer contents is the last thing performed and
not performed if there is an exception.
There does seem to be some syncronicity here though. I have just been
reading Tom Kytes book and was considering using the autonomous
transaction pragma for exactly what you have done, so I'd be very
interested in having a look at your package.
Tim
Scott Mattes
http://www.themattesfamily.ws/scott/
That main page hasn't been reworked since leaving GeoCities, so it has some
missing graphics. The text following the cursor around are for my niece.
"Tim Cross" <tcr...@pobox.une.edu.au> wrote in message
news:871y5e3...@blind-bat.une.edu.au...
Alex Filonov
There is another method of PL/SQL debugging which let's you to receive
debugging messages real time. You just send your messages through
dbms_pipe package. You need a listener which would receive this message
and print it. Usually it's a small Pro*C or java program. I can post
a sample if anyone is interested.
Alex.
Scott Mattes
things to find a adequate real time msgr I didn't seem to be able to do the
pipe idea correctly (msgs didn't always arrive, if I remember correctly).
"Alex Filonov" <afil...@yahoo.com> wrote in message
news:336da121.02112...@posting.google.com...
Hulk
"Sybrand Bakker" <gooid...@sybrandb.demon.nl> wrote in message
news:fcintu0i8qsdi0m0g...@4ax.com...
Alex Filonov
> I would be interested in the JAVA version. When I was trying different
> things to find a adequate real time msgr I didn't seem to be able to do the
> pipe idea correctly (msgs didn't always arrive, if I remember correctly).
>
OK. Java version is the only one I currently have. Some history. I've got
the idea from Sergei Kuchin (http://otl.sourceforge.net/) in 1996. He had
it written in Pro*C at the time. Later (2000) I wrote java version. Never had
problems with messages, all messages always arrived.
You need 3 pieces of software.
1. PL/SQL procedure to simplify message sending.
procedure send_msg(m in varchar2) is
ms number;
begin
dbms_pipe.pack_message(m);
ms := dbms_pipe.send_message('JavaPipe');
fnd_file.put_line(fnd_file.log, m);
end send_msg;
2. Java class implementing the listener.
// This is a listener class which connects to Oracle instance and
// listens for requests. It uses Oracle package dbms_pipe.
// This package allows you to send messages between different sessions.
// It is implemented as a thread, so you can start several listeners for
// several databases. You also can periodically check if connection is
// alive by checking piperet variable. If connection appears to be dead,
// you can kill and restart thread. The messages in pipe would stay there for
// a long time, so if listener didn't listen for a while, it'd pick up all
// messages from pipe after restart.
// You need to import the java.sql package to use JDBC
import java.sql.*;
public class PipeThread extends Thread
{
String ConnectS;
String usrn;
String pwd;
int piperet;
public PipeThread(String ConnectString, String username, String passwd)
{
// Constructor. ConnectString parameter has to have format:
// jdbc:oracle:<drivertype>:@<host_name/host_address>:<port>:<instance>
// Example:
// jdbc:oracle:thin:@alex:1521:oracle
super();
piperet = 0;
ConnectS = ConnectString;
usrn = username; // Oracle username
pwd = passwd; // Oracle password
}
public void run() {
try {
// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Connect to the database
Connection conn =
DriverManager.getConnection (ConnectS, usrn, pwd);
// Create a Statement
Statement stmt = conn.createStatement ();
int i = 0;
int n;
String zip;
String Address;
String GetZip = "begin dbms_pipe.unpack_message(?); end;";
// Prepare statement listening the pipe.
// Get a message from pipe, set delay to 60 sec.
String GetFromPipe =
"begin ? := dbms_pipe.receive_message('JavaPipe',60); end;";
CallableStatement PipeListen = conn.prepareCall(GetFromPipe);
PipeListen.registerOutParameter(1,Types.INTEGER);
CallableStatement PipeGetZip = conn.prepareCall(GetZip);
PipeGetZip.registerOutParameter(1,Types.CHAR);
// Main loop
while (true){
PipeListen.execute();
piperet = PipeListen.getInt(1);
// Return codes from receive_message
// 0: message received
// 1: timeout
// >1: Oracle error, shoudn't happen
// If message received:
if (piperet == 0) {
PipeGetZip.execute();
zip = PipeGetZip.getString(1);
zip = zip.trim();
System.out.println(zip);
}
if (piperet > 1) System.out.println("Oops!");
}
} catch (SQLException e){
// Something wrong. Use your own printout/logging.
// I'd prefer to end thread here and restart it from
// calling class.
// I'd also recommend to check class variable piperet
// once in a while
// and kill and restart thread if it > 1.
System.out.println(e);
}
}
}
3. Example of java program running the listener.
public class PipeTest {
int myCounter = 0;
static int classCounter = 0;
public static void main (String[] args) {
PipeThread t1 = new
PipeThread("jdbc:oracle:thin:@machine_name:1521:instance_name",
"username", "password");
t1.start();
}
}
It's possible to run several listeners at the same time. I don't think
that reading output would be easy though. All restrictions of dbms_pipe
package should be taken into account. And, of course, you need jdbc thin
driver.
>
> "Alex Filonov" <afil...@yahoo.com> wrote in message
> news:336da121.02112...@posting.google.com...
<some snip>
Scott Mattes
What is the 'fnd_file.put_line' all about? I don't recognize that as
standard PL/SQL.
Alex Filonov
> Thank you.
>
> What is the 'fnd_file.put_line' all about? I don't recognize that as
> standard PL/SQL.
>
into pipe. Just remove it.
Ubiquitous
silver...@yahoo.com wrote:
>In PL-SQL I run the following SQL Script, and
>get the buffer overflow message; how do I get rid
>of the message, and/or increase the buffer?
Use the SET SERVEROUTPUT to a really high value (100,000 is the limit?)
or ENABLE.SERVEROUTPUT(100000) (I _think_ that's the command) in the
script.
Galen Boyer
> how do I get rid of the message,
[...]
> DBMS_OUTPUT.PUT_LINE(currentCH.HULLTYPE || ' ' ||
> currentCH.HULLNO || ' ' || currentCH.LOCATION || ' ' ||
> currentCH.ASOFDATE);
Comment out these lines.
--
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.