Re: PL/SQL script...

From: Matt B. <mcb_at_ds.znet.com>
Date: Fri, 15 Dec 2000 22:12:04 -0800
Message-ID: <t3m1qm7rt6d60f_at_corp.supernews.com>


[Quoted] "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:

[Quoted] Before your script starts, make sure that serveroutput is on:

set serveroutput on

[Quoted] [Quoted] Then, make sure you do TO_CHAR as needed because DMBS_OUTPUT.PUT_LINE expects [Quoted] [Quoted] 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 Received on Sat Dec 16 2000 - 07:12:04 CET

Original text of this message