xmltype.getStringVal function

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

xmltype.getStringVal function

Postby mano7033 » Sat Jan 26, 2013 6:39 am

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
mano7033
Member
 
Posts: 17
Joined: Tue Aug 23, 2011 12:59 pm

Re: xmltype.getStringVal function

Postby Tim... » Sat Jan 26, 2013 8:33 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: xmltype.getStringVal function

Postby mano7033 » Wed Jan 30, 2013 12:52 pm

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?
mano7033
Member
 
Posts: 17
Joined: Tue Aug 23, 2011 12:59 pm

Re: xmltype.getStringVal function

Postby Tim... » Wed Jan 30, 2013 2:04 pm

Hi.

I don't know another way myself.

Not sure why you think the alter session is inappropriate.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 2 guests