| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> ora-20000, oru-10027 buffer overflow, limit of 2000 bytes
Hello all,
I have an oracle error raised: ora-20000, oru-10027 raised on the following pl/sql code. I have the set the serveroutput on to 5000. It still doesn't resolve the error. If I increase it to 1000000 it still gets the error.
Any ideas why the error is raise? Your help is much appreciated.
pl/sql code:
set echo off
set serveroutput on size 10000
set timing off
set sqlnumber on
/* dump_latest_released_boms.sql for Agile 3.2, 4.0, 5.0, 6.0 */
/* Time-stamp: "oracle_dump_all_boms.sql 19 January 2000 13:40:33
JOHNO" */
/* It is sensitive to inconsistencies between revision and change
effective and release dates */
/* If these dates are inconsistant, it does not dump all your bom data
*/
/* This inconsistency is sometimes found in legacy data. */
/* NOTICE: */
/* This has been tested for the uses it was adapted for, but */
/* this comes with no warranty expressed or implied that this meets */
/* any other requirements. It may require customization. */
/* Test everything before you put it in production. */
/* Changes */
/* Changed so it loops through all items in the item table. */ /* Multiple changes to output format */ /* Added timing (in milliseconds) */output, for Eric; */
/* 13 Mar 1998 /*
/* Added rev.effective_date */
/* April 21 1998 */
/* Extended and edited comments. */
/* April 22, 1998
/* Reformatted to 75 columns, for printing. */
/* April 30, 1998 */
/* Undid the formatting. Make it as wide as it wants */
/* June 3, 1998 */
/* Added "clear columns" to foolproof the formatting */ /* Removed effective date, assy rev, comp rev, and RD from
/* Added change_in.released_date to output at Credence's
request. */
/* July 28, 1998 */
/* Added test for deleted_flag, hoping to speed it up. */ /* Added error_code and error_msg to exception messages */ /* Reinserted RD processing, for Heatherly */missing comma). */
/* 15 December 1999 - johno */
/* Renamed two fields for aesthetic reasons; */ /* Fixed problem preventing refdesigs from outputting (a
drop table output_bom;
create table output_bom (
bom_id number primary key, assy_number varchar2(30), comp_number varchar2(30), find_number number, seq number, quantity varchar2(20), assy_rev varchar2(20), comp_rev varchar2(20), ref_desig varchar2(3000), text01 varchar2(5), Module_ind varchar2(1), comp_type varchar2(1), effectiveDate date, dateIn date, dateOut date);
create or replace procedure get_rd(bomID IN number, list OUT varchar2) is
cursor csr is select label from refdesig where bom = bomID order by id;
str varchar2(20);
tmp varchar2(2000);
first number;
error_code number := SQLCODE;
error_msg varchar2(300) := SQLERRM;
begin
open csr;
first := 1;
loop
fetch csr into str;
if csr%notfound then
exit;
end if;
if first = 1 then
tmp := str;
first := 0;
else
tmp := tmp || ',' || str;
end if;
end loop;
close csr;
list := tmp;
exception
when others then
dbms_output.put_line('Exception called in get_rd(): ' ||
error_code || ', ' || error_msg || '.');
end;
cursor csr is select bom.id, bom.item, bom.item_number,
bom.find_number, bom.seq, bom.quantity,
bom.change_in,
bom.change_out, bom.prior_bom,
bom.text01, bom.text02,
bom.list01, rev.rev_number,
change1.release_date,
rev.effective_date, change2.release_date
from bom , item , change change1, change
change2, rev
where bom.item = itemID
and item.item_number (+)=
bom.item_number
and change1.id (+)= bom.change_in
and change2.id (+)= bom.change_out
and rev.item (+)= item.id
and rev.latest_flag (+)= 1
order by bom.item, bom.find_number;
cursor csr0 is select rev.release_date, rev.change,
rev.rev_number, item.item_number
from rev , item
where rev.item = itemID and item.id =
itemID and rev.latest_flag = 1;
bomID number;
assID number;
compNum varchar2(30);
findNum number;
seqNum number;
qty varchar2(20);
changeIn number;
changeOut number;
priorBom number;
revNum varchar2(20);
refDesig varchar2(3000);
txt01 varchar2(5);
Mod_ind Varchar2(1);
cmp_type Varchar2(1);
dateIn date;
effectiveDate rev.effective_date%type;
dateOut date;
assNum varchar2(30);
assRev varchar2(20);
relDate date;
latest number;
error_code number := SQLCODE;
error_msg varchar2(300) := SQLERRM;
begin
open csr0;
fetch csr0 into relDate, latest, assRev, assNum;
if not csr0%notfound then
open csr;
loop
fetch csr into bomID, assID, compNum, findNum,
seqNum, qty,
changeIn, changeOut, priorBom, txt01,
mod_ind,
cmp_type, revNum, dateIn, effectiveDate,
dateOut;
if csr%notfound then
exit;
end if;
if ((changeIn = 0 or dateIn is not null and
dateIn <= relDate) and
(dateOut is null or dateOut > relDate))
then
get_rd(bomID, refDesig);
insert into output_bom (bom_id,
assy_number, comp_number,
find_number, seq, quantity,
assy_rev, comp_rev,
ref_desig, text01,
Module_ind, comp_type,
effectiveDate, dateIn)
values (bomID, assNum, compNum,
findNum, seqNum, qty,
assRev, revNum,
refDesig, txt01, mod_ind, cmp_type,
effectiveDate,dateIn);
end if;
end loop;
close csr;
commit;
end if;
close csr0;
exception
when others then
dbms_output.put_line('Exception called in get_bom(): ' ||
error_code || ', ' || error_msg || '.');
end;
/* johno */
/* Changed it from a one-shot thing to a loop. */
/* cursor csr is select id from item where item_number
= '&assembly_number'; */
/* cursor csr is select id from item; */
declare
cursor csr is select id from item where nvl(delete_flag,0) != 1; itemID number; error_code number := SQLCODE; error_msg varchar2(300) := SQLERRM;
open csr;
loop
fetch csr into itemID;
exit when csr%NOTFOUND;
get_bom(itemID);
end loop;
close csr;
exception
when others then
dbms_output.put_line('Exception called in main(): ' ||
error_code || ', ' || error_msg || '.');
end;
/
show error
drop procedure get_rd;
drop procedure get_bom;
/* Please, make all output and formatting changes below. */
clear columns
clear breaks
/* Display column headers */
ttitle off
clear columns
/* set heading off */
set heading on
set pagesize 50000
/* Avoid line-wrap */
set linesize 5000
/* As you wish */
set echo off
spool c:\ag_bom_dump.txt
select bom_id, assy_number, assy_rev, find_number, comp_number, comp_rev, seq, ltrim(quantity) qty, effectiveDate dateIn, dateOut, ref_desig, text01, Module_ind, comp_type
/* select distinct(assy_number) from output_bom; */
/* drop table output_bom; */
spool off
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Aug 24 2000 - 15:08:38 CDT
 |
 |