How to cast LONG to VARCHAR2 inline

Question

Background: ALL_IND_EXPRESSIONS has column

COLUMN_EXPRESSION   LONG   Function-based index expression defining the column

I know that LONG is deprecated. I need to write something like (or do other text operations):

SELECT 
  REPLACE(REPLACE(REPLACE(
    q'{ALTER INDEX "<index_owner>"."<index_name>" ON ... (<column_expression>)}'
   ,'<index_owner>', index_owner )
   ,'<index_name>', index_name) 
   ,'<column_expression>', column_expression) AS result
FROM all_ind_expressions;

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

DBFiddle Demo

Remarks:

• it has to be self-contained query
• no intermediate objects(creating table/view is not an option).
• no PL/SQL block
• DBMS_METADATA.GET_DDL (it is not the case)
• WITH FUNCTION clause as last resort

Is it possible to cast/convert/use built-in function from LONG to VARCHAR2?

EDIT TL;DR:

SELECT column_expression || 'a'  -- convert to working code
FROM all_ind_expressions;

Answer 1

You can use XML unless expressions contain something which can brake XML parsing.

select *
  from xmltable(
          '/ROWSET/ROW'
          passing (select dbms_xmlgen.getxmltype('select * from all_ind_expressions
                                                   where index_name = ''XDB$COMPLEX_TYPE_AK''')
                     from dual)
          columns index_owner varchar2(30) path 'INDEX_OWNER',
                  index_name varchar2(30) path 'INDEX_NAME',
                  table_owner varchar2(30) path 'TABLE_OWNER',
                  table_name varchar2(30) path 'TABLE_NAME',
                  column_expression varchar2(4000) path 'COLUMN_EXPRESSION')

INDEX_OWNER     INDEX_NAME           TABLE_OWNER     TABLE_NAME           COLUMN_EXPRESSION                  
--------------- -------------------- --------------- -------------------- -----------------------------------
XDB             XDB$COMPLEX_TYPE_AK  XDB             XDB$COMPLEX_TYPE     SYS_OP_R2O("XMLDATA"."ALL_KID")    
1 row selected.

Answer 2

As stated by oracle experts themselves, for legacy reasons it's not possible to inline SUBSTR a LONG to a VARCHAR2. AskTom link.

On this other link you'll find ways to do it with a procedure and even with a function if the LONG is shorter that 32k LONG.

And this function can be called later on in a SELECT query, which is what you may want to achieve.