How to make LONG for VARCHAR2 inline

Question

Background: ALL_IND_EXPRESSIONS has a column

 COLUMN_EXPRESSION LONG Function-based index expression defining the column 

I know that LONG 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 data types: expected NUMBER received LONG

DBFiddle Demo

Notes:

• it must be a standalone request
• no intermediate objects (creating a table / view is not an option).
• no PL/SQL block
• DBMS_METADATA.GET_DDL (this is not the case)
• With a FUNCTION clause as a last resort

Can I enable / convert / use the 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 if the expressions do not contain something that can slow down 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

Using WITH FUNCTION and the approach from converting Long to Varchar2, but still it is somewhat ugly and too complicated.

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

Request:

 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 a more elegant way to achieve this.

Answer 3

As pointed out by oracle experts themselves, for old reasons it is not possible to include SUBSTR LONG in VARCHAR2. AskTom link .

On this other link, you will find ways to do this using the procedure and even with the if LONG function shorter than 32k LONG.

And this function can be called later in the SELECT query, which you might want to achieve.

Answer 4

The best way to deal with long is: 1) Create a temporary table of type lob (for example, CLOB). 2) Use only valid syntax from oracle: "TO_LOB converts the LONG or LONG RAW values ​​in the long_column column to LOB values. This function can only be applied to the LONG or LONG RAW column and only in the subquery select list in the INSERT expression." 3) use a temporary table to do your things