Issue in invoking procedure

[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

[amitpatel66]

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

Run this statement before running your code:

[code=oracle]

ALTER SESSION SET nls_Date_format = 'DD/MM/YYYY HH24:MI:SS'
/
[/code]

[QVeen72]

Hi,

I guess, Your Proc returns some data and it is the first parameter..
second and third would be your Date parameters..

[code=java]
String query = "begin ? := pkg.temp_proc(? , ?); end;";
CallableStateme nt proc = con.prepareCall (query);
proc.setString( 2, s_dt);
proc.setString( 3, e_dt);
[/code]

Regards
Veena

[smruti]

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

try to put cast or convert functions
i think it will help u..