wrong cast varchar to date in websphere IBM

Question

I'm calling a PL/SQL function who returns a table of varchar2. Here is its signature:

create or replace FUNCTION fn_get_estados
        (Pcuenta IN varchar2,Pdia_corte integer) 
        RETURN t_estado_disp_table 
AS

Here is the type:

create or replace TYPE t_estado_Disp_type AS OBJECT 
     (date_inicial    VARCHAR2(15),
              date_final     VARCHAR2(15));

create or replace TYPE t_estado_detalle_table AS TABLE OF t_estado_detalle_type;

When I call that function:

select date_inicial,date_final from table( fn_get_estados( '0000268') );

in SQL Developer the results are like :

10/01/2015

but when I call that function with the exact SQL from a Java web application deployed in a websphere 8.5.5 and the println(resulset.getString(1)) it's like this:

10-JAN-2015

I just don't want the app server to do any casting. Is there any configuration to avoid that casting?

You don't show us enough details. My bet goes to function `fn_get_estados` doesn't use explicit format model when it converts Oracle `date` data type to a string presentation. Instead a session specific format model is used. Your SQLDeveloper and web application have different session settings. — user272735, Aug 04 '15 at 06:11

score 1 · Accepted Answer · edited May 23 '17 at 12:32

Oracle date types are stored in a special format. Each client decides how to display them; the display formats are governed by the client's NLS Language Settings. Find out more.

DD-MON-YYYY is Oracle's default display format for dates. So the app server is not doing any casting, it is just rendering the dates in the default Oracle fashion.

You probably shouldn't be changing the NLS settings in Websphere without conducting a thorough impact analysis. Other people might be quite happy with the default format (apart from anything else, in a global environment it removes ambiguity: it's 10th January not 1st October).

wrong cast varchar to date in websphere IBM

1 Answers1