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 的值显示出来。
标签:char,clob,column,long,CUX,CHAR,LONG,sql From: https://www.cnblogs.com/samrv/p/16626427.html