title: How do you convert LONG data to TIMESTAMP or VARCHAR2?
body: <p><strong>I need something like:</strong></p>
  
  <pre><code>SELECT PARTITION_NAME,
         to_char(LONG_TO_TIMESTAMP(HIGH_VALUE), 'MM/DD/YYYY HH24:MI:SS') AS HIGH_VAL
    FROM USER_TAB_PARTITIONS
   WHERE TABLE_NAME = 'TABLE_NAME'
  </code></pre>
  
  <p><strong>Output should look something like this:</strong></p>
  
  <p><PRE>PARTITION_NAME         HIGH_VAL
  ----------------   --------------------
  SOME_NAME          01/01/2010 00:00:00</PRE></p>
  
  <p><strong>When I run:</strong></p>
  
  <blockquote>
    <p>SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS</p>
  </blockquote>
  
  <p><strong>It looks like:</strong>
  <PRE>PARTITION_NAME         HIGH_VAL
  ----------------   --------------------
  SOME_NAME          TIMESTAMP' 2010-01-01 00:00:00'</PRE></p>
  
  <p>If I use <code>UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)</code>,<br>
  I end up getting an <code>ORA-00997: illegal use of LONG datatype</code> error.</p>
  
  <p>If I use <code>''||HIGH_VALUE</code> or <code>to_clob(HIGH_VALUE)</code> or <code>to_char(HIGH_VALUE)</code>,<br>
  I get an <code>ORA-00932: inconsistent datatypes: expected [DATA_TYPE] got LONG</code> error</p>
  
  <p><strong>My working function thanks to shobi:</strong></p>
  
  <pre><code>CREATE OR REPLACE FUNCTION GET_HIGH_VALUE_AS_DATE (
      p_TableName     IN VARCHAR2,
      p_PatitionName  IN VARCHAR2
  ) RETURN DATE
  IS
     v_LongVal LONG;
  BEGIN
      SELECT HIGH_VALUE INTO v_LongVal
        FROM USER_TAB_PARTITIONS
       WHERE TABLE_NAME = p_TableName
         AND PARTITION_NAME = p_PatitionName;
  
      RETURN TO_DATE(substr(v_LongVal, 11, 19), 'YYYY-MM-DD HH24:MI:SS');
  END GET_HIGH_VALUE_AS_DATE;
  </code></pre>
  
  <p><strong>SQL Looks like</strong></p>
  
  <pre><code>SELECT PARTITION_NAME, GET_HIGH_VALUE_AS_DATE(TABLE_NAME, PARTITION_NAME)
    FROM USER_TAB_PARTITIONS
   WHERE TABLE_NAME LIKE 'TABLE_NAME'
     AND ROWNUM &lt; 2;
  </code></pre>