2 Replies Latest reply on Dec 17, 2013 4:18 PM by Jason_(A_Non)

    How to query HIGH_VALUE  LONG column using xml trick .

    user621309

      Hi, I'm on 11.2.0.3 and trying to query high_Value column from dba_tab_partitions .

       

       SELECT t.table_name
           ,      t.partition_name
           ,      t.partition_position
           --,      to_date(x2.dt,'YYYYMMDDHH24MISS') hi
       ,      x2.dt hi
          FROM   dba_tab_partitions t
           ,      xmltable('for $i in /ROWSET/ROW/HI
                            return $i'
                           passing  dbms_xmlgen.getxmltype
                                    ('select high_value hi from dba_tab_partitions x'
                                   ||' where x.table_name     = '''||t.table_name||''''
                                   ||' and   x.partition_name = '''|| t.partition_name|| '''')
                          columns dt varchar2(4000) path '/') x
       ,      xmltable('for $i in /ROWSET/ROW/DT
                              return $i'
                          passing dbms_xmlgen.getxmltype(x.dt)
                          columns dt varchar2(16) path '/') x2
          WHERE  t.table_name = 'COR_IRR'
      

       

      Above gives me :

      SQL> /

                         passing dbms_xmlgen.getxmltype(x.dt)

                                 *

      ERROR at line 16:

      ORA-19202: Error occurred in XML processing

      ORA-24333: zero iteration count

      ORA-06512: at "SYS.DBMS_XMLGEN", line 288

      ORA-06512: at line 1

       

      Any idea how to display date data in that column so I can make sure partition range definition is ok ?

      Regards

      GregG