How to cast LONG to VARCHAR2 inline
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.
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.
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.