title: How to cast LONG to VARCHAR2 inline
body: <p>Background: <a href="https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1065.htm" rel="nofollow noreferrer"><code>ALL_IND_EXPRESSIONS</code></a> has column</p>
  
  <blockquote>
  <pre><code>COLUMN_EXPRESSION   LONG   Function-based index expression defining the column
  </code></pre>
  </blockquote>
  
  <p>I know that <a href="https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i3056" rel="nofollow noreferrer"><code>LONG</code></a> is deprecated. I need to write something like (or do other text operations):</p>
  
  <pre><code>SELECT 
    REPLACE(REPLACE(REPLACE(
      q'{ALTER INDEX "&lt;index_owner&gt;"."&lt;index_name&gt;" ON ... (&lt;column_expression&gt;)}'
     ,'&lt;index_owner&gt;', index_owner )
     ,'&lt;index_name&gt;', index_name) 
     ,'&lt;column_expression&gt;', column_expression) AS result
  FROM all_ind_expressions;
  </code></pre>
  
  <blockquote>
    <p>ORA-00932: <strong>inconsistent datatypes: expected NUMBER got LONG</strong></p>
  </blockquote>
  
  <p><strong><a href="http://dbfiddle.uk/?rdbms=oracle_11.2&amp;fiddle=e1a7c592349d7305ca78568aaaa06697" rel="nofollow noreferrer">DBFiddle Demo</a></strong></p>
  
  <p>Remarks:</p>
  
  <ul>
  <li>it has to be self-contained query</li>
  <li>no intermediate objects(creating table/view is not an option).</li>
  <li>no <code>PL/SQL block</code></li>
  <li><code>DBMS_METADATA.GET_DDL</code> (it is not the case)</li>
  <li><a href="https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1#functions" rel="nofollow noreferrer">WITH FUNCTION clause</a> as last resort</li>
  </ul>
  
  <p>Is it possible to cast/convert/use built-in function from <code>LONG</code> to <code>VARCHAR2</code>?</p>
  
  <p><strong>EDIT TL;DR:</strong></p>
  
  <pre><code>SELECT column_expression || 'a'  -- convert to working code
  FROM all_ind_expressions;
  </code></pre>