oracle long转为char,如何将LONG数据转换为TIMESTAMP或VARCHAR2？

SELECT PARTITION_NAME,

to_char(LONG_TO_TIMESTAMP(HIGH_VALUE), 'MM/DD/YYYY HH24:MI:SS') AS HIGH_VAL

FROM USER_TAB_PARTITIONS

WHERE TABLE_NAME = 'TABLE_NAME'

输出应该是这个样子：

PARTITION_NAME HIGH_VAL

---------------- --------------------

SOME_NAME 01/01/2010 00:00:00

当我运行：

SELECT PARTITION_NAME，HIGH_值与USER_TAB_PARTITIONS

它看起来像：

PARTITION_NAME HIGH_VAL

---------------- --------------------

SOME_NAME TIMESTAMP' 2010-01-01 00:00:00'

如果我使用UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)，

我最终得到一个ORA-00997: illegal use of LONG datatype错误。

如果我使用''||HIGH_VALUE或to_clob(HIGH_VALUE)或to_char(HIGH_VALUE)，

我得到一个ORA-00932: inconsistent datatypes: expected [DATA_TYPE] got LONG错误

我的工作职能感谢尚美学园：

CREATE OR REPLACE FUNCTION GET_HIGH_VALUE_AS_DATE (

p_TableName IN VARCHAR2,

p_PatitionName IN VARCHAR2

) RETURN DATE

IS

v_LongVal LONG;

BEGIN

SELECT HIGH_VALUE INTO v_LongVal

FROM USER_TAB_PARTITIONS

WHERE TABLE_NAME = p_TableName

AND PARTITION_NAME = p_PatitionName;

RETURN TO_DATE(substr(v_LongVal, 11, 19), 'YYYY-MM-DD HH24:MI:SS');

END GET_HIGH_VALUE_AS_DATE;

SQL貌似

SELECT PARTITION_NAME, GET_HIGH_VALUE_AS_DATE(TABLE_NAME, PARTITION_NAME)

FROM USER_TAB_PARTITIONS

WHERE TABLE_NAME LIKE 'TABLE_NAME'

AND ROWNUM < 2;