Re: PL/SQL script...

From: Corto Maltese <corto_at_castel.nl>
Date: Mon, 18 Dec 2000 12:15:43 GMT
Message-ID: <91kv5d$g8i$1_at_nnrp1.deja.com>

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

>
>

--

[Quoted] [Quoted] Many thanks for the help, displaying results is OK now!, but:
[Quoted] [Quoted] 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

[Quoted] [Quoted] The numeric fields where displayed with the to_char as you suggested.
[Quoted] The output is appearing about 340 times (without problems) and then the
[Quoted] above error message is displayed.

Any clue?

Best regards,

CM


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

Received on Mon Dec 18 2000 - 13:15:43 CET