Oracle date pattern to java date pattern

Question

Is there any utility that returns java.text.SimpleDateFormat pattern from Oracle to_char format pattern?

create or replace type type_sample as object (
  f_id number,
  f_name varchar2(100),
  f_start_date date,
  f_end_date date
)
/

SET SERVEROUTPUT ON
DECLARE
  xmltype1            SYS.XMLType;
  message             type_sample;
BEGIN
  message := new type_sample(1, 'Manohar', current_date, sysdate);
  xmltype1 := XMLtype.createXML(message);
  DBMS_OUTPUT.PUT_LINE('out_errm : '|| xmltype1.getStringVal());
END;
/

<TYPE_SAMPLE><F_ID>1</F_ID><F_NAME>Manohar</F_NAME><F_START_DATE>26-JAN-13</F_START_DATE>**<F_END_DATE>26-JAN-13</F_END_DATE>**</TYPE_SAMPLE>

I have the above XML coming form DataBase. Here the date format is coming in different formats based on the session the above code is run.

What I can do here is, can get that session's dateformat pattern from to java side. If I can convert that dateformat pattern(oracle db) to java.text.SimpleDateFormat pattern, my problem is solved. Can you please help me out?

I am not getting the expected answer here. May be my presentation is not explaining the problem clearly.

I will ask a straight forward question now. As far as i know java.text.SimpleDateFormat is after all an implementation of java.text.DateFormat. This implementation of DateFormat can understand the following pattern letters

G   Era designator
y   Year
M   Month in year
w   Week in year
W   Week in month
D   Day in year
d   Day in month
F   Day of week in month
E   Day in week
a   Am/pm marker
H   Hour in day (0-23)
k   Hour in day (1-24)
K   Hour in am/pm (0-11)
h   Hour in am/pm (1-12)
m   Minute in hour
s   Second in minute
S   Millisecond
z   Time zone
Z   Time zone

Are there any other implementations of java.text.DateFormat, which can understand some other pattern letter group? If any, please let me know.

I wouldn't normally advocate putting a date into a string, but since it's going to be done implicitly for the XML payload anyway... wouldn't it be easier to have the object fields as varchars, and use `to_char()` with an explicit format mask when creating the object? Takes the session NLS out of the equation, and the Java side only needs to recognise one format. Not sure if you can enforce the format within the object - maybe a constructor that accepts a date and does the conversion? — Alex Poole, Feb 04 '13 at 18:21
Thanks Alex. But we have so many existing types, where date fields are already there. Re-defining those types will be a tedious task for me and our team. My doubt here is, I can pass the session's nls format string to java side. Is there any way to draw java.text.SimpleDateFormat string from that db nls string? Because the formatting patterns differs from oracle to java. For example 05-Feb-2013 is represented in oracle as DD-MON-YYYY, and where as in java it is dd-MMM-yyyy. — Manohar, Feb 05 '13 at 05:47
Can't you set the `NLS_DATE_FOTMAT` to be the same for all sessions started from your application? Either explicitly with an `alter session` when the connection is opened, or with a logon trigger, perhaps? — Alex Poole, Feb 05 '13 at 08:45

score 0 · Answer 1 · answered Feb 04 '13 at 15:06

0

One of the dates from the XML is 26-JAN-13. That would be a SimpleDateFormat of "dd-MMM-yy"

SimpleDateFormat oracleFormat = new SimpleDateFormat("dd-MMM-yy");
Date date = oracleFormat.parse(dateString);

answered Feb 04 '13 at 15:06

Gilbert Le Blanc

50,182
6
67
111

score 0 · Answer 2 · answered Feb 04 '13 at 18:44

Not quite what you were asking, but here's a different approach to avoid the uncertainty from the session's NLS parameters. You could make the conversion happen in the object, so you can use an explicit date format mask and it's already a string when you convert it to XML:

create or replace type type_sample as object (
    f_id number,
    f_name varchar2(100),
    f_start_date varchar2(10),
    f_end_date varchar2(10),
    constructor function type_sample(self in out nocopy type_sample,
        f_id number, f_name varchar2, f_start_date date, f_end_date date)
        return self as result
)
/

create or replace type body type_sample as
    constructor function type_sample(self in out nocopy type_sample,
        f_id number, f_name varchar2, f_start_date date, f_end_date date)
        return self as result is
    begin
        self.f_id := f_id;
        self.f_name := f_name;
        self.f_start_date := to_char(f_start_date, 'YYYY-MM-DD');
        self.f_end_date := to_char(f_end_date, 'YYYY-MM-DD');
        return;
    end;
end;
/

There's no change to how you create the object:

set serveroutput on
declare
    xmltype1 sys.xmltype;
    message type_sample;
begin
    message := new type_sample(1, 'Manohar', current_date, sysdate);
    xmltype1 := xmltype.createxml(message);
    dbms_output.put_line('out_errm : '|| xmltype1.getStringVal());
end;
/

out_errm : <TYPE_SAMPLE><F_ID>1</F_ID><F_NAME>Manohar</F_NAME><F_START_DATE>2013-02-04</F_START_DATE><F_END_DATE>2013-02-04</F_END_DATE></TYPE_SAMPLE>

The date is always in YYYY-MM-DD format in the XML, regardless of the session's NLS_DATE_FORMAT (which happens to be DD-MON-RR in this session). You could use whatever fixed format you want of course, you just need to agree that format between the object and Java.

(I suppose I'm assuming this is the only thing the object will be used for; otherwise putting dates into strings isn't a good idea...).

Oracle date pattern to java date pattern

2 Answers2

Linked

Related