ORACLE long类型转字符 LONG_TO_CHAR()

 ORACLE 11G   long类型转字符 ,

自定义函数: CUX_LONG_TO_CHAR() 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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;

  测试例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
创建索引:
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 的值显示出来。   

 

posted @   samrv  阅读(3765)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示