dcsimg
JDBC and ORACLE StoreProcedure ??
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Anonymous
Posted On:   Thursday, October 25, 2001 04:25 AM

the Oracle StoreProcedure :
CREATE PROCEDURE assign_conf(d1 in Date,id out Number) AS ....


now I have to call this StoreProc with JDBC.

and I have got the Date in String TYPE the same as :

String dateStr = "2001-10-25 22:10:00";

....How to deal with ....??????

CallableStatement cstmt =conn.prepareCall("{call assign_conf(?,?)}");

cstmt.setDate(1,d1);

cstmt.registerOutParameter(2,Types.INTEGER);

cstmt.executeQuery();



Please give me the Sample Code,thx!

Re: JDBC and ORACLE StoreProcedure ??

Posted By:   Bernie_Acs  
Posted On:   Tuesday, November 6, 2001 10:34 AM


The default NLS_DATE_FORMAT for the database is probably set to something like 'dd-mon-yyyy' which would be the generic defaulted value.

Two methods for handling this situation are:



METHOD 1.

This praticular setting can be set on a per session basis using the following DDL statement:

Alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss' ;


The above will make all dates returned from the database to be presented in the character format represented, alternatively all character date representations being presented to the database for insert or update would have to be in that format as well.



METHOD 2.

you could handle the conversion of the input string by embedding a to_date function complete with the format string right into the call defination.



CallableStatement cstmt =conn.prepareCall("{call assign_conf( TO_DATE( ?,'yyyy-mm-dd hh24:mi:ss' ), ? )}");



To further capitalize on this same principle you could make the format a variable as well which may increase your overall flex as follows:



String my_format = "yyyy-mm-dd h24:mi:ss";

CallableStatement cstmt =conn.prepareCall("{call assign_conf( TO_DATE( ?,? ), ? )}");

cstmt.setDate(1,d1);

cstmt.setString(2,my_format);

cstmt.registerOutParameter(3,Types.INTEGER);





Hope that is Helpful
About | Sitemap | Contact