473,322 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Issue in invoking procedure

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:-

Expand|Select|Wrap|Line Numbers
  1. String s_dt= "14/03/2006 09:30:30";
  2. String e_dt= "15/04/2008 09:30:30";
  3. String query = "begin ? := pkg.temp_proc(?, ?); end;";
  4. CallableStatement proc = con.prepareCall(query);
  5. proc.setString(1, s_dt);
  6. proc.setString(2, e_dt);
In the procedure I m doing this :-

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PACKAGE BODY pkg
  2. AS
  3. Procedure temp_proc
  4. (pv_startdate_in IN VARCHAR2,
  5. pv_enddate_in IN VARCHAR2)
  6. as
  7. Begin
  8.  
  9. 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')
  10.  
  11. end;
  12. 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
Mar 18 '08 #1
3 1603
amitpatel66
2,367 Expert 2GB
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:-

Expand|Select|Wrap|Line Numbers
  1. String s_dt= "14/03/2006 09:30:30";
  2. String e_dt= "15/04/2008 09:30:30";
  3. String query = "begin ? := pkg.temp_proc(?, ?); end;";
  4. CallableStatement proc = con.prepareCall(query);
  5. proc.setString(1, s_dt);
  6. proc.setString(2, e_dt);
In the procedure I m doing this :-

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PACKAGE BODY pkg
  2. AS
  3. Procedure temp_proc
  4. (pv_startdate_in IN VARCHAR2,
  5. pv_enddate_in IN VARCHAR2)
  6. as
  7. Begin
  8.  
  9. 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')
  10.  
  11. end;
  12. 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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. ALTER SESSION SET nls_Date_format = 'DD/MM/YYYY HH24:MI:SS'
  3. /
  4.  
Mar 18 '08 #2
QVeen72
1,445 Expert 1GB
Hi,

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

Expand|Select|Wrap|Line Numbers
  1. String query = "begin ? := pkg.temp_proc(?, ?); end;";
  2. CallableStatement proc = con.prepareCall(query);
  3. proc.setString(2, s_dt);
  4. proc.setString(3, e_dt);
  5.  
Regards
Veena
Mar 18 '08 #3
smruti
6
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:-

Expand|Select|Wrap|Line Numbers
  1. String s_dt= "14/03/2006 09:30:30";
  2. String e_dt= "15/04/2008 09:30:30";
  3. String query = "begin ? := pkg.temp_proc(?, ?); end;";
  4. CallableStatement proc = con.prepareCall(query);
  5. proc.setString(1, s_dt);
  6. proc.setString(2, e_dt);
In the procedure I m doing this :-

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PACKAGE BODY pkg
  2. AS
  3. Procedure temp_proc
  4. (pv_startdate_in IN VARCHAR2,
  5. pv_enddate_in IN VARCHAR2)
  6. as
  7. Begin
  8.  
  9. 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')
  10.  
  11. end;
  12. 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..
Mar 18 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: chandan | last post by:
hi, I have a insert trigger on a table. I want commit to happen after insert before invoking the trigger. So that if a quey is made before the completion of the trigger.Modified Data should be...
11
by: steingold | last post by:
Hi All. Is it possible to define an external stored procedure to be executed not with the executing user privileges, but instead with the user who created the stored procedure privileges in db2...
3
by: pinney.colton | last post by:
I would like to create a stored procedure which creates a temp table to store some XML. The # of fields of XML is dependent upon the contents of another table in the application, so the first part...
0
by: balaji krishna | last post by:
Hi, I need to handle the return set from COBOL stored procedure from my invoking Java program. I do not know, how many rows the stored proc SQL fetches.I have declared the cursor in that proc, but i...
0
by: prasadbodas2000 | last post by:
Hi, I need a sample code in ASP or VB wherein ADO command object is used to invoke oracle stored procedure. The stored procedure (SP) should have oracle specific custom data types (CDT) as...
2
by: phanikumarparimi | last post by:
Hi all , I need to write a stored procedure which will be called when an after insert trigger fires.The Parameters for the SP called would be transition tables.Can anyone help me out in how...
1
by: hussain123 | last post by:
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...
3
by: Vish4u | last post by:
Hello Everyone, I have a encountered a strange issue with the execution of my stored procedure on clients machine. My stored procedure contains a cursor in which there is a select statement...
1
by: eeriehunk | last post by:
Hi All, I wrote a procedure to clear all my text fields and disable all buttons in my form. And I call this procedure in a button (insert button). when I presses the insert button, after all my...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.