xmltype.getStringVal function

[mano7033]

Hi Tim,

I have a small doubt regarding xmltype.getStringVal().

the following is my type

Code: Select all

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

I am converting the above type to string xml using the below

Code: Select all

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;
/


and the output of the above execution is as below
<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 a problem with my date field. In the previous execution it is coming in DD-MON-YY format, sometimes the same field is giving the output in DD-MM-YY format. This unexpected format is creating a problem for me. Can you please suggest, how to get output in the same format everytime.

<F_START_DATE>26-JAN-13</F_START_DATE>

Thank you,
Manohar RG

[Tim...]

Hi.

The issue here is you are relying on Oracle to do an implicit conversion from a date to a string. The problem with this is that the result you get will depend totally on the setting of NLS_DATE_FORMAT for your session, or the database if the session value is not set explicitly. The only way you can guarantee the conversion when using your current methos is to explicitly set the value in your code.

EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format=''DD-MON-YY''';

Notice the double single-quotes as these a single quotes within a string.

Cheers

Tim...

[mano7033]

Hi Tim,
Thanks for the reply. Yes, that was the problem.

Is there any other way to get rid of this? ALTER SESSION is in-appropriate in my case.
When i faced this problem, my first thought was, there must be a way provided by oracle to pass a flag to getStringVal or XMLtype.createXML to get a standard date format or system defaults?

[Tim...]

Hi.

I don't know another way myself.

Not sure why you think the alter session is inappropriate.

Cheers

Tim...