oracle long 到数字的转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10304218/
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
oracle long to number conversion
提问by spider8
I am trying to get the name of the current partition from all_tab_partitions table. When i try to compare high_value (long data Type) and number. It throws an error:
我正在尝试从 all_tab_partitions 表中获取当前分区的名称。当我尝试比较 high_value（长数据类型）和数字时。它抛出一个错误：
ORA-00997: illegal use of LONG datatype
ORA-00997: 非法使用 LONG 数据类型
SELECT PARTITION_NAME, HIGH_VALUE FROM ALL_TAB_PARTITIONS
WHERE TABLE_OWNER='SCHEMA_NAM'
AND TABLE_NAME='TABLE_NAME'
AND HIGH_VALUE>to_number(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, 1), 'MONTH'), 'YYYYDDD'))
/
回答by Petr Pribyl
Long type stores large variable lenght character strings, it is NOT a numeric type. It has many limitations. Oracle documentation says:
Long 类型存储大的可变长度字符串，它不是数字类型。它有很多限制。Oracle 文档说：
The use of LONG values is subject to these restrictions:
LONG 值的使用受以下限制：
- A table can contain only one LONG column.
- You cannot create an object type with a LONG attribute.
- LONG columns cannot appear in WHEREor in integrity constraints (except that they can appear in and NOT NULL constraints).
- LONG columns cannot be indexed.
- LONG cannot be specified in regular expressions.
- A stored function return a LONG value.
- You can declare a variable or argument of PL/SQL program unit using the LONG datatype. However, you cannot call the program unit from SQL.
- Within a single SQL statement, LONG columns, updated tables, and locked tables must be located the same database. LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
- If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
- 一张表只能包含一个 LONG 列。
- 您不能创建具有 LONG 属性的对象类型。
- LONG 列不能出现在 WHERE或完整性约束中（除非它们可以出现在和 NOT NULL 约束中）。
- LONG 列不能被索引。
- LONG 不能在正则表达式中指定。
- 存储的函数返回 LONG 值。
- 您可以使用 LONG 数据类型声明 PL/SQL 程序单元的变量或参数。但是，您不能从 SQL 调用程序单元。
- 在单个 SQL 语句中，LONG 列、更新表和锁定表必须位于同一数据库中。LONG 和 LONG RAW 列不能在分布式 SQL 语句中使用，也不能复制。
- 如果表同时具有 LONG 和 LOB 列，则不能在同一 SQL 语句中将超过 4000 字节的数据同时绑定到 LONG 和 LOB 列。但是，您可以将超过 4000 个字节的数据绑定到 LONG 或 LOB 列。
回答by Bob Jarvis - Reinstate Monica
In order to examine or use the value of a LONG column you'll have to write some PL/SQL, similar to
为了检查或使用 LONG 列的值，您必须编写一些 PL/SQL，类似于
DECLARE
strHigh_value VARCHAR2(4000);
BEGIN
FOR aRow IN (SELECT PARTITION_NAME, HIGH_VALUE
FROM ALL_TAB_PARTITIONS
WHERE TABLE_OWNER = 'SCHEMA_NAM' AND
TABLE_NAME = 'TABLE_NAME')
LOOP
strHigh_value := aRow.HIGH_VALUE;
-- TODO: Manipulate strHigh_value in whatever manner you need
END LOOP;
END;
Share and enjoy.
分享和享受。
回答by user2265417
If you don't want to write any PL/SQL try to use (in general case):
如果您不想编写任何 PL/SQL 尝试使用（在一般情况下）：
select
sys.dbms_metadata_util.long2varchar(4000, 'TABLE_NAME', 'COLUMN_NAME', tp.rowid) as COLUMN_NAME
from
TABLE_NAME tp
where
<your_condition on table TABLE_NAME>
don't use COLUMN_NAME in ...your_condition on table TABLE_NAME...
不要在表 TABLE_NAME 的 ...your_condition 中使用 COLUMN_NAME...
In your case it's a bit more complicated because you query a VIEW. It's necessary to dig in the view code and rewrite your query as:
在您的情况下，它有点复杂，因为您查询 VIEW。有必要挖掘视图代码并将您的查询重写为：
select PARTITION_NAME, HIGH_VALUE from (
select t.PARTITION_NAME, sys.dbms_metadata_util.long2varchar(4000, 'SYS.TABCOMPART$', 'HIBOUNDVAL', tp.rowid) as high_value
from
all_tab_partitions t, sys.obj$ o, sys.tabcompart$ tp
where
t.table_name = 'TABLE_NAME'
and t.table_owner = 'SCHEMA_NAM'
and o.name = t.table_name
and o.subname = t.partition_name
and o.obj# = tp.obj#(+)
)
where
high_value > to_number(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, 1), 'MONTH'), 'YYYYDDD'))
tested on Oracle 11g
在 Oracle 11g 上测试
