SQL 如何将 LONG 数据转换为 TIMESTAMP 或 VARCHAR2？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9643920/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you convert LONG data to TIMESTAMP or VARCHAR2?
提问by ScrappyDev
I need something like:
我需要类似的东西：
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'
Output should look something like this:
输出应如下所示：
PARTITION_NAME HIGH_VAL ---------------- -------------------- SOME_NAME 01/01/2010 00:00:00
When I run:
当我运行时：
SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS
SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS
It looks like:
看起来像：
PARTITION_NAME HIGH_VAL ---------------- -------------------- SOME_NAME TIMESTAMP' 2010-01-01 00:00:00'
If I use UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE),
I end up getting an ORA-00997: illegal use of LONG datatypeerror.
如果我使用UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)，
我最终会得到一个ORA-00997: illegal use of LONG datatype错误。
If I use ''||HIGH_VALUEor to_clob(HIGH_VALUE)or to_char(HIGH_VALUE),
I get an ORA-00932: inconsistent datatypes: expected [DATA_TYPE] got LONGerror
如果我使用''||HIGH_VALUE或to_clob(HIGH_VALUE)或者to_char(HIGH_VALUE)，
我得到一个ORA-00932: inconsistent datatypes: expected [DATA_TYPE] got LONG错误
My working function thanks to shobi:
感谢 shobi 我的工作功能：
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 Looks like
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;
回答by shofee
The only way to convert LONG columns is in PL/SQL. Look at the following example, which determines the length of the LONG field:
转换 LONG 列的唯一方法是在 PL/SQL 中。看下面的例子，它决定了 LONG 字段的长度：
SET SERVEROUTPUT ON SIZE 10000;
DECLARE
long_var LONG;
BEGIN
SELECT text_column INTO long_var
FROM table_with_long
WHERE rownum < 2;
DBMS_OUTPUT.PUT_LINE('The length is '||LENGTH(long_var));
END;
Basically, you define a variable as the LONG type, then SELECT the column INTO the variable. Finally, it is output to the user. SET SERVEROUTPUT ON SIZE 10000 allows spooling from the PUT_LINE to go to the screen.
基本上，您将变量定义为 LONG 类型，然后将列 SELECT 到变量中。最后输出给用户。SET SERVEROUTPUT ON SIZE 10000 允许从 PUT_LINE 假脱机到屏幕。
You can use a similar method to select the LONG into a varchar field. The following example puts the first 2000 characters into TABLE_B, which for our purposes has one column, TEXT_FIELD:
您可以使用类似的方法将 LONG 选择到 varchar 字段中。以下示例将前 2000 个字符放入 TABLE_B，出于我们的目的，它只有一列 TEXT_FIELD：
DECLARE
long_var LONG;
var_var VARCHAR2(2000);
BEGIN
SELECT text_column INTO long_var
FROM table_with_long
WHERE rownum < 2;
var_var := substr(long_var,1,2000);
INSERT INTO table_b
VALUES (var_var);
END;
回答by David Faber
It would also be possible to do the following:
还可以执行以下操作：
CREATE TABLE long_to_clob
( partition_name VARCHAR2(30)
, high_value_clob CLOB
, high_value_text VARCHAR2(4000)
);
INSERT INTO long_to_clob (partition_name, high_value_clob)
SELECT partition_name, TO_LOB(high_value)
FROM user_tab_partitions;
UPDATE long_to_clob
SET high_value_text = DBMS_LOB.SUBSTR(high_value_clob, 1, 4000);
The only caveat with using TO_LOB is that it must be used in an INSERT statement as above. On the other hand there are fewer restrictions on using the DBMS_LOB package. Obviously once you have the value in a VARCHAR2 column you can do anything you want with it.
使用 TO_LOB 的唯一警告是它必须在上面的 INSERT 语句中使用。另一方面，使用 DBMS_LOB 包的限制较少。显然，一旦您在 VARCHAR2 列中拥有值，您就可以对它做任何想做的事情。
Hope this helps.
希望这可以帮助。
回答by Kaushik Nayak
There's a good trick to do this using dynamic sql ( EXECUTE IMMEDIATE)
有一个使用动态 sql ( EXECUTE IMMEDIATE)的好技巧
SET SERVEROUTPUT ON
DECLARE
l_date DATE;
BEGIN
FOR r IN (
SELECT partition_name,high_value
FROM user_tab_partitions
WHERE table_name = 'MYTABLE'
) LOOP
EXECUTE IMMEDIATE 'BEGIN :v := ' || r.high_value || '; END;'
USING OUT l_date;
DBMS_OUTPUT.PUT_LINE(r.partition_name||','|| l_date);
END LOOP;
END;
/
This produces an output of the form
这会产生以下形式的输出
SYS_P601452,19-10-18 --(partition_name,high_value)
SYS_P601451,18-10-18
SYS_P601453,20-10-18
SYS_P601454,21-10-18
SYS_P601455,22-10-18
..
..
Here I used a DATEtype partitioned column. You may use a TIMESTAMPvariable.
这里我使用了DATE类型分区列。您可以使用一个TIMESTAMP变量。
回答by OldOwl
SELECT
PARTITION_NAME,
high_value
FROM XMLTABLE(
'/ROWSET/ROW'
PASSING
DBMS_XMLGEN.GETXMLTYPE
(Q'~
select
p.partition_name,
p.high_value
from user_tab_partitions p
where table_name = 'MYTABLE'
order by partition_position
~'
)
COLUMNS PARTITION_NAME, HIGH_VALUE
);
