Calling stored procedure with date parameters

[jlgdeveloper]

Hi All

This Oracle 8i stored procedure is valid:

(PK in number,
dt1 in date,
dt2 in date,
amount out number
)
is
begin
select sum(conf_numb8) into amount
from csh_main
where fk_str_main_id = PK
and (busi_date >= to_date(dt1))
and (busi_date <= to_date(dt2));
end;

My issue is calling it:

This sql plus statement:
CALL cwsr_sel_net_sales (101, '03-Nov-03' , '09-Nov-03');
produces this error:
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to
'CWSR_SEL_NET_SALES'


The sql statement that this is based on works well, and is:
select sum(conf_numb8) as amount from csh_main where fk_str_main_id = 101 and (busi_date >= to_date('03-Nov-03')) and (busi_date <= to_date('09-Nov-03'))

Any ideas on where I have err'd?

Thanks

Jonathan Galpin MCSD Õ¿Õ¬

http://www.iqzero.net

because software should be easy to use

 

[carp]

Your procedure has four arguments; you are only passing in three. Hence the &quot;wrong number&quot; part of your error message.

 

[jlgdeveloper]

Hi Carp

Previously I tried to feed one in, with no luck:

CALL cwsr_sel_net_sales (101, '03-Nov-03' , '09-Nov-03',0)
*
ERROR at line 1:
ORA-06577: output parameter not a bind variable

It is an out parameter, so why feed it in? Is there a different syntax I can apply such as declare in SQL server?

Thanks

Jonathan Galpin MCSD Õ¿Õ¬

http://www.iqzero.net

because software should be easy to use

 

[carp]

Since it's an OUT parameter, you should pass in a variable - so the procedure has someplace to put the outbound number.

You presumably are writing this procedure to get an amount out of it. If you don't pass a variable in to the procedure, how is it going to communicate with your calling environment?

Also, since this is just returning a number, you might want to recast this as a function:

CREATE OR REPLACE FUNCTION my_amount
(PK in number,
dt1 in date,
dt2 in date) RETURN NUMBER AS
l_amount NUMBER;
begin
select sum(conf_numb8) into l_amount
from csh_main
where fk_str_main_id = PK
and (busi_date >= to_date(dt1))
and (busi_date <= to_date(dt2));
RETURN l_amount;
end;

Then you can use this function from within a PL/SQL block OR a SQL command (e.g. SELECT my_amount(35,sysdate - 5, sysdate) FROM dual.

As a final note, you might also want to include some logic that will check for Stupid User Trick #37: Reversing the Dates. Users will often put the most recent date in the wrong position and your query will return no data.

 

[sem]

If your parameter is declared as OUT, you should pass a variable, not literal. In sql*plus you may define bind variable:

var amnt number
exec cwsr_sel_net_sales (101, '03-Nov-03' , '09-Nov-03', :amnt)

and then display the result:

print amnt


Regards, Dima

http://seminihin.narod.ru