Problem invoking procedure from java code

[hussain123]

Hi All,
I am invoking a procedure which takes 2 IN parameters and both are dates which are passed as string.In the procedure I am using those 2 IN parameters to query the db and fetch record between those two dates.
In java I m doin something like:-

String s_dt= "14/03/2006 09:30:30";
String e_dt= "15/04/2008 09:30:30";
String query = "begin ? := pkg.temp_proc(?, ?); end;";
CallableStatement proc = con.prepareCall(query);
proc.setString(1, s_dt);
proc.setString(2, e_dt);

In the procedure I m doing this :-

CREATE OR REPLACE PACKAGE BODY pkg
AS
Procedure temp_proc
(pv_startdate_in IN VARCHAR2,
pv_enddate_in IN VARCHAR2)
as
Begin

select * from table_temp where col_one between to_date(pv_startdate_in,'DD/MM/YYYY HH24:MI:SS') AND to_date(pv_enddate_in,'DD/MM/YYYY HH24:MI:SS')

end;
end pkg;

When I run my java code I get the following error message:-
ORA-01830: date format picture ends before converting entire input string

I have gone mad trying to figure out why it is throwing this error.

Any help would be highly appreciated.

Thanks in advance,
Hussain

[r035198x]

Hi All,
I am invoking a procedure which takes 2 IN parameters and both are dates which are passed as string.In the procedure I am using those 2 IN parameters to query the db and fetch record between those two dates.
In java I m doin something like:-

String s_dt= "14/03/2006 09:30:30";
String e_dt= "15/04/2008 09:30:30";
String query = "begin ? := pkg.temp_proc(?, ?); end;";
CallableStatement proc = con.prepareCall(query);
proc.setString(1, s_dt);
proc.setString(2, e_dt);

In the procedure I m doing this :-

CREATE OR REPLACE PACKAGE BODY pkg
AS
Procedure temp_proc
(pv_startdate_in IN VARCHAR2,
pv_enddate_in IN VARCHAR2)
as
Begin

select * from table_temp where col_one between to_date(pv_startdate_in,'DD/MM/YYYY HH24:MI:SS') AND to_date(pv_enddate_in,'DD/MM/YYYY HH24:MI:SS')

end;
end pkg;

When I run my java code I get the following error message:-
ORA-01830: date format picture ends before converting entire input string

I have gone mad trying to figure out why it is throwing this error.

Any help would be highly appreciated.

Thanks in advance,
Hussain

That's an Oracle error better asked in the Oracle forum. There is, however, also a Java side fix. Just use PreparedStateme nt and it's setXX methods will take care of the conversion for you.