Re: Stored procedure returning a value.....

If you declare a variable as OUT, the value assigned to it in that procedure will be passed out of that procedure into the calling one. In the example below, when the po_pth_id is assigned a value in SP_TWW_DA_PTH_UPD procedure, it is passed back up to the calling procedure and allocated to V_PTH_ID;

You need to call your procedure like..

declare

    v_pth_id number;
begin

    ..
    SP_TWW_DA_PTH_UPD ('20','56', v_pth_id);     ..
    update table
    set id = v_pth_id
    where.....
    ..
end;

Looking at your procedure though, it will be a lot easier just to use a function.

  FUNCTION SP_TWW_DA_PTH_UPD (
  p_txt_pth_nm IN varchar2,
  p_chg_agt_id IN varchar2) return number is...

Mark

mozkill_at_my-deja.com wrote in message <7ldlaf$l16$1_at_nnrp1.deja.com>...
>Is there a good example anywhere of a stored procedure#1 that uses
>stored procedure#2 to set a public variable? I want to use the
>variable as an argument for stored procedure#3 (which is also called
>from within stored procedure#1).
>
>In other words, here is a minimized explanation.
>
>I have two tables. Table#1 has a primary key called 'key'. Table#2
>includes 'key' but it is obviously not the primary key in Table#2. I
>want to query Table#1 and get a UNIQUE key and then use the value to
>pass into the next stored procedure#3 which does an update.
>
>I am still a beginner. Does this sound like it will work? When I try
>to compile my procedure using SQL Plus 8.0, I get the following error...
>339/5 PL/SQL: Statement ignored
>339/17 PLS-00306: wrong number or types of arguments in call to
> 'SP_TWW_DA_PTH_UPD'
>
>Here is the header to my procedure#2...
> PROCEDURE SP_TWW_DA_PTH_UPD (
> p_txt_pth_nm IN varchar2,
> p_chg_agt_id IN varchar2,
> po_pth_id OUT number);
>It seems like there is something wrong with the way that I am using the
>OUT in my constructor...
>I dont need help, I just dont understand the OUT and/or stored
>procedures well enough to know for sure that a) this is possible and b)
>how to use the OUT properly
>
>send me a carbon copy reply to mozkill_at_yahoo.com
>
>Jon
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Thu Jul 01 1999 - 03:29:13 CDT