Re: PL/SQL script...
Date: Mon, 18 Dec 2000 14:31:03 -0000
Message-ID: <91l72t$f55$1_at_sun-cc204.lut.ac.uk>
Try:
[Quoted] set serveroutput on 99999
[Quoted] 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
[Quoted] > > you are concatenating numbers and characters and dates, etc. So,
around your
[Quoted] > > 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:31:03 CET