| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Stored procedure returning a value.....
On Wed, 30 Jun 1999 17:49:08 GMT, mozkill_at_my-deja.com wrote:
>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'
Sounds like you are calling SP_TWW_DA_PTH_UPD with less than three
parameters or the parameters being passed in are of the wrong type.
>
>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...
If you have a procedure/function with out parameter, you have to supply a variable when you call it to hold the out value when the procedure/function returns.
eg.
procedure double_it(
p_value in number,
p_value_times_two out number ) is
begin
p_value_times_two := p_value * 2;
end double_it;
/
procedure call_double_it is
l_return_value number;
begin
double_it( 10, l_return_value );
dbms_output.put_line( l_return_value );
end call_double_it;
/
As you can see, the variable l_return_value will have the value of the out parameter p_value_times_two. If you have a procedure that only has one out parameter, then you might consider writing it as a function.
eg.
function double_it( p_value in number ) return number is
begin
return p_value * 2;
end double_it;
/
procedure call_double_it is
l_return_value number;
begin
l_return_value := double_it( 10 );
dbms_output.put_line( l_return_value );
end call_double_it;
/
>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
will do.
hope this helps.
chris.
>
>Jon
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |