SQL issues comparing Long field to sysdate

Applications Programming
1

I am having hard time with this sql:

select partition_name, high_value
    FROM user_tab_partitions
   WHERE table_name = 'WNP_TPRESPONSE_INSTANCE'
    and to_char(high_value) <= to_char(sysdate - 15, 'yyyymm')
;

I get an error: ORA-00932: inconsistent datatypes: expected CHAR got LONG

high_value is defined as long and here is a sample data:

PARTITION_NAME HIGH_VALUE
CURRENT_P '000001'
P_201009 '201010'
P_201010 '201011'

How to do i do this conversion !

2

One technique is to use PL/SQL, which converts LONG to VARCHAR2 during cursor fetches:

SQL> 
SQL> --
SQL> select partition_name, high_value
  2    from user_tab_partitions
  3   where table_name = 'WNP_TPRESPONSE_INSTANCE';

PARTITION_NAME    HIGH_VALUE
--------------- ----------
CURRENT_P    '000001'
P_201009    '201010'
P_201010    '201011'

3 rows selected.

SQL> 
SQL> -- Anonymous PL/SQL block
SQL> begin
  2       for rec in (select partition_name, high_value
  3                 from user_tab_partitions
  4                where table_name = 'WNP_TPRESPONSE_INSTANCE'
  5              )
  6       loop
  7           if to_char(rec.high_value) <= to_char(sysdate - 15, 'yyyymm')
  8           then
  9           dbms_output.put_line(rec.partition_name ||' '|| rec.high_value);
 10           end if;
 11       end loop;
 12  end;
 13  /
CURRENT_P '000001'
P_201009 '201010'
P_201010 '201011'

PL/SQL procedure successfully completed.

SQL> 
SQL>

---------- Post updated at 01:10 PM ---------- Previous update was at 01:09 AM ----------

Another technique is to use Oracle's supplied package "dbms_xmlgen" for XML processing. Like PL/SQL, this package converts LONG data to VARCHAR2 as well.

First, check if you have access to the package by executing this sqlplus command:

desc dbms_xmlgen

It should list down all the public procedures and functions in this package.
Then, pass your query to the "getxmltype()" function of this package to convert the output into a canonical XML document.

SQL>
SQL> --
SQL> SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT partition_name, high_value
  2                                   FROM user_tab_partitions
  3                                  WHERE table_name = ''WNP_TPRESPONSE_INSTANCE''') AS xml
  4    FROM dual;

XML
--------------------------------------------------------------------------------
<ROWSET>
 <ROW>
  <PARTITION_NAME>CURRENT_P</PARTITION_NAME>
  <HIGH_VALUE>'000001'</HIGH_VALUE>
 </ROW>
 <ROW>
  <PARTITION_NAME>P_201009</PARTITION_NAME>
  <HIGH_VALUE>'201010'</HIGH_VALUE>
 </ROW>
 <ROW>
  <PARTITION_NAME>P_201010</PARTITION_NAME>
  <HIGH_VALUE>'201011'</HIGH_VALUE>
 </ROW>
</ROWSET>

1 row selected.

SQL>
SQL>

From the output, it can be seen that:
(a) The outermost tags are <ROWSET> and </ROWSET>, and they are always returned.
(b) Each row of data is then sandwiched within <ROW> and </ROW> tags.
(c) Each column heading (partition_name, high_value in your case) has its own opening and closing tag.
(d) The actual data shows up as the content between the relevant column tags.

This is the canonical XML output. You can then use the "extractValue()" function to convert this XML format to relational aka "rows-and-columns" format.

SQL>
SQL> --
SQL> WITH xml AS (
  2      SELECT XMLTYPE(DBMS_XMLGEN.GETXML('SELECT partition_name, high_value
  3                                           FROM user_tab_partitions
  4                                          WHERE table_name = ''WNP_TPRESPONSE_INSTANCE'''
  5                                       )
  6                    ) AS xml
  7        FROM dual
  8      ),
  9  parsed_xml AS (
 10      SELECT extractValue(xs.object_value, '/ROW/PARTITION_NAME') AS partition_name,
 11             extractValue(xs.object_value, '/ROW/HIGH_VALUE')     AS high_value
 12        FROM xml x,
 13             TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
 14      )
 15  SELECT *
 16    FROM parsed_xml
 17   WHERE TO_CHAR(high_value) <= TO_CHAR(SYSDATE - 15, 'YYYYMM')
 18  ;

PARTITION_NAME       HIGH_VALUE
-------------------- --------------------
CURRENT_P            '000001'
P_201009             '201010'
P_201010             '201011'

3 rows selected.

SQL>
SQL>