如何把long类型的数据最终转换成varchar2类型呢？

显示全部楼层 · 2008-5-16 11:28:59

SELECThigh_value FROM dba_tab_partitions

WHERE table_name = 'S_xx_INFO'

AND table_owner = 'SDW'

ORDER BY partition_position DESC;
high_value字段为LONG类型，现需要把该字段的内容转为VARCHAR2。
我这样试了，不行。貌似to_lob()函数并没有把long真正转为clob。请高手看看有没有别的方法？在线急等！
SELECTDBMS_LOB.SUBSTR(to_lob(high_value),100,1) cc

FROM dba_tab_partitions

WHERE table_name = 'S_AI_PR_LOAN_ACCO_AMT_INFO'

AND table_owner = 'SDW'

ORDER BY partition_position DESC;

千问 · 2008-5-16 11:28:59

SQL> create or replace function display_raw (rawval raw, type varchar2)
2return varchar2
3is
4 cn number;
5 cv varchar2(32);
6 cd date;
7 cnvnvarchar2(32);
8 cr rowid;
9 cc char(32);
10begin
11 if (type = 'NUMBER') then
12dbms_stats.convert_raw_value(rawval, cn);
13return to_char(cn);
14 elsif (type = 'VARCHAR2') then
15dbms_stats.convert_raw_value(rawval, cv);
16return to_char(cv);
17 elsif (type = 'DATE') then
18dbms_stats.convert_raw_value(rawval, cd);
19return to_char(cd);
20 elsif (type = 'NVARCHAR2') then
21dbms_stats.convert_raw_value(rawval, cnv);
22return to_char(cnv);
23 elsif (type = 'ROWID') then
24dbms_stats.convert_raw_value(rawval, cr);
25return to_char(cnv);
26 elsif (type = 'CHAR') then
27dbms_stats.convert_raw_value(rawval, cc);
28return to_char(cc);
29 else
30return 'UNKNOWN DATATYPE';
31 end if;
32end;
33/
用这个函数try一哈

千问 · 2008-5-16 11:28:59

貌似楼上的没有LONG型的判断啊

千问 · 2008-5-16 11:28:59

http://asktom.oracle.com/pls/ask ... ION_ID:510622111991

千问 · 2008-5-16 11:28:59

参考asktom后，随便给一种方法：
SQL> create or replace function long_2_varchar (
2
p_table_ownerin all_tab_partitions.table_owner%type,
3
p_table_name in all_tab_partitions.table_name%type,
4
p_partition_name in all_tab_partitions.partition_name%type
5
)
6return varchar2
7as
8l_high_value LONG;
9begin
10select high_value
11into l_high_value
12from all_tab_partitions
13 where table_owner= p_table_owner
14 and table_name = p_table_name
15 and partition_name = p_partition_name;
16
17return substr( l_high_value, 1, 4000 );
18end;
19/

Function created

SQL> select t.table_name,t.high_value,long_2_varchar(user,t.table_name,t.partition_name) from user_tab_partitions t;

TABLE_NAME
HIGH_VALUE
LONG_2_VARCHAR(USER,T.TABLE_NA
------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SALES
TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES
TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES
TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES
TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES
TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES
TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES
TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES
TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

8 rows selected

SQL>

千问 · 2008-5-16 11:28:59

bucuo\

千问 · 2008-5-16 11:28:59

直接定义一个varchar2类型变量赋值试试
select high_value,partition_name into v_high_value,v_part_namefrom USER_TAB_PARTITIONS where table_name='S_AI_PR_LOAN_ACCO_AMT_INFO' andAND table_owner = 'SDW'

千问 · 2008-5-16 11:28:59

http://blog.csdn.net/ghostliming/article/details/50411147

千问 · 2008-5-16 11:28:59

放不下的怎么办