your output buffer (used by dbms_output.put_line) gets exausted...
As there are only exceptions, wher put_line is used, something must be
changed, causing exceptions to be raised by the thousands...
Start debugging the code, or use dbms_pipe instead.
--
Kind Regards,
Frank
<jane_ballard_at_my-deja.com> wrote in message
news:8o3vbj$du3$1_at_nnrp1.deja.com...
> 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) */
> /* 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
> output, for Eric; */
> /* June 4, 1998 */
> /* 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 */
> /* 15 December 1999 - johno */
> /* Renamed two fields for aesthetic reasons; */
> /* Fixed problem preventing refdesigs from outputting (a
> missing comma). */
>
> 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;
> /
> show error
> create or replace procedure get_bom(itemID IN number) is
> 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;
> /
> show error
>
> /* 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;
> begin
>
> 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
> from output_bom;
>
> /* 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 Mon Aug 28 2000 - 06:33:23 CDT
