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
