Re: PL/SQL script...

O'Reilly's Oracle built-in Packages says the size varies from 2,000 to 1,000,000 bytes and is set by the 'DBMS_OUTPUT.ENABLE size' or 'SET SERVEROUTPUT ON SIZE x' commands

.
"Andrew Hardy" <Andrew.Hardy_at_AdvanticaTech.com> wrote in message news:91l72t$f55$1_at_sun-cc204.lut.ac.uk...
> Try:
>
> set serveroutput on 99999
>
> This sets your buffer to 99999 bytes (I forget what the limit is).
>
> Andy
> Corto Maltese <corto_at_castel.nl> wrote in message
> news:91kv5d$g8i$1_at_nnrp1.deja.com...
> > In article <t3m1qm7rt6d60f_at_corp.supernews.com>,
> > "Matt B." <mcb_at_ds.znet.com> wrote:
> > > "Corto Maltese" <corto_at_castel.nl> wrote in message
> > > news:91ddos$11t$1_at_nnrp1.deja.com...
> > > > Guys,
> > > >
> > > > Don't laugh on me but I have a quite dumb and annoying problem:
> > > >
> > > > The following script is inserting the rows at the insert statement
 but,
> > > > more important, I need to display the records.
> > > > The insert was done to see if it was working, It did, should not be
> > > > there.
> > > > I want to display all records that are matching the loop with the
> > > > dbms_output line (the only possible to display data so far I know).
> > > > I have tried the dbms_output statement in many ways but didn't work
> > > > either.
> > > >
> > > > Any thoughts, please help...
> > > >
> > > > CM.
> > > >
> > > >
> > > > -- WHENEVER SQLERROR EXIT FAILURE;
> > > > DECLARE
> > > >
> > > > v_xzx varchar2(2);
> > > >
> > > > CURSOR cs_prods is
> > > > select msi.inventory_item_id,
> > > > msi.segment1
> > > > from mtl_system_items msi
> > > > where msi.organization_id = 7
> > > > and msi.inventory_item_status_code = 'Active'
> > > > and msi.customer_order_enabled_flag = 'Y'
> > > > ;
> > > >
> > > > cs_item_id mtl_system_items.inventory_item_id%type;
> > > > cs_item_num mtl_system_items.segment1%type;
> > > > l_item_id mtl_system_items.inventory_item_id%type;
> > > > l_item_num mtl_system_items.segment1%type;
> > > > l_open_qty number;
> > > > l_onhand_qty number;
> > > >
> > > > BEGIN
> > > >
> > > > OPEN cs_prods;
> > > >
> > > > LOOP
> > > >
> > > > FETCH cs_prods INTO
> > > > cs_item_id,
> > > > cs_item_num;
> > > >
> > > > EXIT when cs_prods%NOTFOUND;
> > > >
> > > > BEGIN
> > > >
> > > > select sum(moq.transaction_quantity)
> > > > into l_onhand_qty
> > > > from mtl_onhand_quantities moq
> > > > where moq.organization_id = 2
> > > > and moq.inventory_item_id = cs_item_id
> > > > and moq.subinventory_code = 'Finish'
> > > > group by moq.inventory_item_id;
> > > >
> > > > exception
> > > > when no_data_found then
> > > > l_onhand_qty := 0;
> > > >
> > > > END;
> > > >
> > > > insert into items_stocks
> > > > (item_id,
> > > > product_number,
> > > > quantity_ordered)
> > > > values (cs_item_id,
> > > > cs_item_num,
> > > > l_onhand_qty);
> > > > commit;
> > > >
> > > > dbms_output.put_line
> > > > ('stock: '||cs_item_id||cs_item_num||l_onhand_qty);
> > > >
> > > > END LOOP;
> > > >
> > > > close cs_prods;
> > > >
> > > >
> > > > END;
> > > > /
> > > > -- exit;
> > > > $
> > > >
> > > >
> > > > Sent via Deja.com
> > > > http://www.deja.com/
> > >
> > > Two things:
> > >
> > > Before your script starts, make sure that serveroutput is on:
> > >
> > > set serveroutput on
> > >
> > > Then, make sure you do TO_CHAR as needed because DMBS_OUTPUT.PUT_LINE
 expects
> > > everything to be a character, and I think this has to be the case
 especially if
> > > you are concatenating numbers and characters and dates, etc. So,
 around your
> > > non-character variables that you are feeding to PUT_LINE, make sure
 you add
> > > TO_CHAR as needed.
> > >
> > > -Matt
> > >
> > >
> >
> > --
> >
> > Many thanks for the help, displaying results is OK now!, but:
> > sorry to bother you again with this issues, the following errors are
> > appearing:
> > DECLARE
> > *
> > ERROR at line 1:
> > ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
> > ORA-06512: at "SYS.DBMS_OUTPUT", line 106
> > ORA-06512: at "SYS.DBMS_OUTPUT", line 65
> > ORA-06512: at line 36
> >
> > The numeric fields where displayed with the to_char as you suggested.
> > The output is appearing about 340 times (without problems) and then the
> > above error message is displayed.
> >
> > Any clue?
> >
> > Best regards,
> >
> > CM
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>
Received on Mon Dec 18 2000 - 15:27:17 CET