Hi all,
I have written procedure as follows:
---------------------------------------------------------
--procedure begin
Create Procedure sp_getNoOfDays(
ip_startDate IN date,
ip_endDate IN date,
op_noofdays OUT bigint
)
is
v_DOBMonth number(20);
v_currMonth number(20);
v_absMonthsDiff number(20);
cnt_noOfDays bigint;
Begin
dbms_output.put_line('1 '||ip_startDate);
dbms_output.put_line('2 '||ip_endDate);
v_DOBMonth:=to_char(ip_startDate,'MM');
dbms_output.put_line('3 ');
v_currMonth:=to_char(ip_endDate,'MM');
dbms_output.put_line('4 ');
v_absMonthsDiff:=abs(to_number(v_currMonth-v_DOBMonth));
dbms_output.put_line('5 '||v_absMonthsDiff);
if v_absMonthsDiff<1
then
dbms_output.put_line('6 ');
select extract(day from ip_endDate)-extract(day from ip_startDate)
into cnt_noOfDays
from dual;
dbms_output.put_line('7 ');
else
select ip_endDate-ip_startDate
into cnt_noOfDays
from dual;
--cnt_noOfDays:=0;
end if;
op_noofdays:=cnt_noOfDays;
dbms_output.put_line('8 ');
Exception
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Message : '||SQLERRM||'Error Code : '||SQLCODE);
End;
--procedure end
---------------------------------------------------------
--Here I am executing
declare
opn bigint;
begin
sp_getNoOfDays('02-Feb-2009','29-Jan-2009',opn);
dbms_output.put_line('No of Days is =>'||opn);
end;
------------------------------------------------------------------
--I am not getting required results but getting error as follows:
INFO: 1 02-FEB-09 00:00:00
INFO: 2 29-JAN-09 00:00:00
INFO: 3
INFO: 4
INFO: 5 1
INFO: Error Message : EDB-22P02: invalid input syntax for integer: "@ 4 days ago"Error Code : 22P02
INFO:
---------------------------------------------
Please any one can help me
--
Thanks & Regards,
-Sanjeev (MIT)
