ORACLE long类型转字符 LONG_TO_CHAR()

 ORACLE 11G   long类型转字符 ,

自定义函数： CUX_LONG_TO_CHAR() 

create   function cux_long_to_char(p_additional_where in varchar2,
                            p_table_name       in varchar2,
                            p_long_column      in varchar2) return varchar2 is
  
    v_sql  varchar2(2000);
    v_clob clob;
  
    v_clob_to_char varchar2(2000);
  
  begin
  
    v_sql := 'select ' || p_long_column || ' from ' || p_table_name ||
             ' where 1=1 ' || ' and ' || p_additional_where;
  
    v_sql := 'declare 
  begin 
    for dat in (' || v_sql || ') loop
      :v_clob := dat.' || p_long_column || ';
      end loop;
      end;';
  
    --v_clob := v_sql;
    --execute immediate v_sql into v_clob;
    execute immediate v_sql
      using out v_clob;
  
    v_clob_to_char := to_char(v_clob);
    v_clob_to_char := trim(v_clob_to_char);
  
  
    return v_clob_to_char;
  
  exception
    when others then
      return null;
  end cux_long_to_char;

　　测试例子：

创建索引：
CREATE INDEX CUX.CUX_PROJECT_REQUIREMENT_TB_N3 ON CUX.CUX_PROJECT_REQUIREMENT_TB (
ORGANIZATION_ID , ITEM_CODE, PROJECT_CODE  DESC
) LOGGING TABLESPACE APPS_TS_TX_DATA;

select column_expression ,
  CUX_long_TO_CHAR(p_additional_where =>' index_name =''CUX_PROJECT_REQUIREMENT_TB_N3'' and column_position = 3',
                            p_table_name   =>'DBA_IND_EXPRESSIONS',
                            p_long_column  =>'COLUMN_EXPRESSION') as column_name 
 from dba_ind_expressions
 where index_name ='CUX_PROJECT_REQUIREMENT_TB_N3'
  and column_position = 3 ;
  
  

把 DBA_IND_EXPRESSIONS.COLUMN_EXPRESSION 的值显示出来。