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;

Possible duplicate of [Converting Long to Varchar2](https://stackoverflow.com/questions/31004844/converting-long-to-varchar2) — Cyrille MODIANO, Oct 28 '17 at 15:08
@CyrilleMODIANO Mentioned answer could be helpful. `Converting from long to varchar2 right away using a single statement is not possible, as long has certain restrictions.` any link to official documentation? — Lukasz Szozda, Oct 28 '17 at 16:07

score 8 · Accepted Answer · answered Oct 31 '17 at 17:56

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.

Thanks for idea, I will test it :) I hope it will work for all my cases (I don't want to create exceptions for non-parsable XML) — Lukasz Szozda, Nov 01 '17 at 06:54
I modified this select to extract column HIGH_VALUE from ALL_TAB_PARTITIONS. — alvalongo, Feb 21 '19 at 16:55

Lukasz Szozda · Answer 2 · 2018-10-23T15:19:02.283

Using WITH FUNCTION and approach from Converting Long to Varchar2 but still it is somehow ugly and overcomplicated.

CREATE TABLE TEST(Z INT);
CREATE INDEX IF_DOUBLE_TEST_Z ON TEST(Z*2);

Query:

WITH FUNCTION s_convert(pindex_owner VARCHAR2, pindex_name VARCHAR2,
                        ptable_owner VARCHAR2, ptable_name VARCHAR2) 
               RETURN VARCHAR2
AS
  VAR1 LONG;
  VAR2 VARCHAR2(4000);
BEGIN
  SELECT column_expression 
  INTO VAR1 
  FROM all_ind_expressions
  WHERE index_owner = pindex_owner AND index_name = pindex_name
    AND table_owner = ptable_owner AND table_name = ptable_name
    AND column_position = 1;  -- only one column indexes

  VAR2 := SUBSTR(VAR1, 1, 4000);
  RETURN VAR2;
END;
SELECT aie.*, 
  REPLACE(REPLACE(REPLACE(
     q'{ALTER INDEX "<index_owner>"."<index_name>" ON ... (<column_expression>)}'
     ,'<index_owner>', index_owner )
     ,'<index_name>', index_name) 
     ,'<column_expression>', 
       s_convert(index_owner, index_name, table_owner, table_name)) AS result
FROM all_ind_expressions aie
WHERE TABLE_NAME='TEST';

db<>fiddle demo

I believe that there should be more elegant way to achieve it.

score 2 · Answer 3 · answered Nov 03 '17 at 16:20

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.

score 1 · Answer 4 · answered May 15 '19 at 16:10

The best way to deal with long is to: 1) Create a temporary table with a lob type (eg. CLOB). 2) Use the only allowed syntax by oracle: "TO_LOB converts LONG or LONG RAW values in the column long_column to LOB values. You can apply this function only to a LONG or LONG RAW column, and only in the select list of a subquery in an INSERT statement." 3) exploit the temporary table to do your stuff

How to cast LONG to VARCHAR2 inline

4 Answers4