This discussion is archived
2 Replies Latest reply: Jul 19, 2012 2:29 AM by 950472 RSS

xmltype.toObject() date attribute

950472 Newbie
Currently Being Moderated
Hi guys,

I use a 11g database and I am trying to convert an xml into an object
but I am having problems at oracle date fields
eg:<CREATED_DATE>2012-08-18T18:15:31.8673829+01:00</CREATED_DATE> cannot be inserted into CREATED_DATE DATE,

I tried the implicit oracle format yyyy-mm-ddThh24:mi:ss but it does not work, the only things that work is NLS_DATE_FORMAT (dd-mon-yy) .
Is there any way to do this?
CREATE OR REPLACE TYPE GROUP_STATEMENT_REC AS OBJECT(
GROUP_STATEMENT_ID           NUMBER(10),  
GROUP_ID                     VARCHAR2(5), 
CUSTOMER_ID                  NUMBER(10),  
CREATED_DATE                 DATE,        
REPORT_GENERATED_DATE        DATE,        
EVENT_ID                     NUMBER(10),  
YEAR                         VARCHAR2(4), 
MONTH                        VARCHAR2(2), 
CURRENCY_CODE                VARCHAR2(3), 
OPENING_BALANCE              NUMBER(17,2),
CLOSING_BALANCE              NUMBER(17,2),
CUSTOMER_COLLECTION_HOLD_IND VARCHAR2(1), 
CUSTOMER_DISPUTE_IND         VARCHAR2(1) 
) FINAL INSTANTIABLE;



CREATE OR REPLACE TYPE GROUP_STATEMENT_COL IS TABLE OF GROUP_STATEMENT_REC;


create or replace type gsbox is object (container GROUP_STATEMENT_COL);


 
 
 declare

    lc_gs   GROUP_STATEMENT_COL := GROUP_STATEMENT_COL(GROUP_STATEMENT_REC(1,'40666',1000,sysdate,sysdate,11,'2004','10','EUR',123.23,32.23,'Y','N'),
                                                         GROUP_STATEMENT_REC(2,'40600',1001,sysdate-1,sysdate,12,'2004','10','EUR',123.23,32.23,'Y','N'));
    xmlgs         xmltype;
    l_box         gsbox;
    newgs         GROUP_STATEMENT_COL;
    
  begin

--dbms_output.put_line(lc_gs.count);
  -- select sys_xmlgen(gsbox(lc_gs)) into xmlgs from dual;
  xmlgs := new XMLType('<?xml version="1.0" encoding="utf-8"?>
<ROW xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <CONTAINER>
    <GROUP_STATEMENT_REC>
      <GROUP_STATEMENT_ID>0</GROUP_STATEMENT_ID>
      <GROUP_ID>30888</GROUP_ID>
      <CUSTOMER_ID>39</CUSTOMER_ID>
      <CREATED_DATE>2012-08-18T18:15:31.8673829+01:00</CREATED_DATE>
      <REPORT_GENERATED_DATE xsi:nil="true" />
      <EVENT_ID>0</EVENT_ID>
      <YEAR>2012</YEAR>
      <MONTH>7</MONTH>
      <CURRENCY_CODE>GBP</CURRENCY_CODE>
      <OPENING_BALANCE>4405.08</OPENING_BALANCE>
      <CLOSING_BALANCE>4405.08</CLOSING_BALANCE>
      <CUSTOMER_COLLECTION_HOLD_IND>N</CUSTOMER_COLLECTION_HOLD_IND>
      <CUSTOMER_DISPUTE_IND>N</CUSTOMER_DISPUTE_IND>
      
    </GROUP_STATEMENT_REC>
  </CONTAINER>
</ROW>'); 
   --dbms_output.put_line(xmlgs.getStringVal());
   xmlgs.toObject(l_box);
   newgs := l_box.container;

   for i in 1..newgs.count loop
    dbms_output.put_line(newgs(i).GROUP_STATEMENT_ID || ' : ' || newgs(i).GROUP_ID||newgs(i).REPORT_GENERATED_DATE||newgs(i).YEAR||'<<<');
   end loop;
 
  end;
  
  • 1. Re: xmltype.toObject() date attribute
    odie_63 Guru
    Currently Being Moderated
    Hi,

    I am having problems at oracle date fields
    eg:<CREATED_DATE>2012-08-18T18:15:31.8673829+01:00</CREATED_DATE> cannot be inserted into CREATED_DATE DATE,
    This format maps to the TIMESTAMP WITH TIME ZONE datatype in Oracle.

    Change the CREATED_DATE attribute to :
    CREATED_DATE                 TIMESTAMP WITH TIME ZONE, 
    Then you should be able to do this :
    SQL> alter session set nls_timestamp_tz_format = 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM';
     
    Session altered
     
    SQL> declare
      2  
      3    xmlgs         xmltype;
      4    l_box         gsbox;
      5    newgs         GROUP_STATEMENT_COL;
      6  
      7  begin
      8  
      9    xmlgs := new XMLType('<?xml version="1.0" encoding="utf-8"?>
     10  <ROW xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     11    <CONTAINER>
     12      <GROUP_STATEMENT_REC>
     13        <GROUP_STATEMENT_ID>0</GROUP_STATEMENT_ID>
     14        <GROUP_ID>30888</GROUP_ID>
     15        <CUSTOMER_ID>39</CUSTOMER_ID>
     16        <CREATED_DATE>2012-08-18T18:15:31.8673829+01:00</CREATED_DATE>
     17        <REPORT_GENERATED_DATE xsi:nil="true" />
     18        <EVENT_ID>0</EVENT_ID>
     19        <YEAR>2012</YEAR>
     20        <MONTH>7</MONTH>
     21        <CURRENCY_CODE>GBP</CURRENCY_CODE>
     22        <OPENING_BALANCE>4405.08</OPENING_BALANCE>
     23        <CLOSING_BALANCE>4405.08</CLOSING_BALANCE>
     24        <CUSTOMER_COLLECTION_HOLD_IND>N</CUSTOMER_COLLECTION_HOLD_IND>
     25        <CUSTOMER_DISPUTE_IND>N</CUSTOMER_DISPUTE_IND>
     26  
     27      </GROUP_STATEMENT_REC>
     28    </CONTAINER>
     29  </ROW>');
     30  
     31    xmlgs.toObject(l_box);
     32    newgs := l_box.container;
     33  
     34    for i in 1..newgs.count loop
     35      dbms_output.put_line(newgs(i).CREATED_DATE);
     36    end loop;
     37  
     38  end;
     39  /
     
    2012-08-18T18:15:31.867383+01:00
     
    PL/SQL procedure successfully completed
     
  • 2. Re: xmltype.toObject() date attribute
    950472 Newbie
    Currently Being Moderated
    thanks a lot
    alter session set makes the trick

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points