Re: PL/SQL script...

Scott, Andrew, Sybrand and Matt,

Many thanks for help me out, was quite simple (like everything if you know about it) but very frustrating when it doesn't do what you want, and it work out!

Thanks again guys!

CM.

In article <91l72t$f55$1_at_sun-cc204.lut.ac.uk>,   "Andrew Hardy" <Andrew.Hardy_at_AdvanticaTech.com> wrote:
> 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/
>
>

--




Sent via Deja.com
http://www.deja.com/