2 Replies Latest reply: Jul 19, 2012 4:29 AM by 950472 RSS

    xmltype.toObject() date attribute

    950472
      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
          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
            thanks a lot
            alter session set makes the trick