oracle 如何内联将 LONG 转换为 VARCHAR2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46991132/
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 to cast LONG to VARCHAR2 inline
提问by Lukasz Szozda
Background: ALL_IND_EXPRESSIONShas column
背景：ALL_IND_EXPRESSIONS有列
COLUMN_EXPRESSION LONG Function-based index expression defining the column
COLUMN_EXPRESSION LONG Function-based index expression defining the column
I know that LONGis deprecated. I need to write something like (or do other text operations):
我知道这LONG已被弃用。我需要写一些类似的东西（或做其他文本操作）：
SELECT
REPLACE(REPLACE(REPLACE(
q'{ALTER INDEX "<index_owner>"."<index_name>" ON ... (<column_expression>)}'
,'<index_owner>', index_owner )
,'<index_name>', index_name)
,'<column_expression>', column_expression) AS result
FROM all_ind_expressions;
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
ORA-00932:不一致的数据类型：预期的 NUMBER 变得很长
Remarks:
评论：
- it has to be self-contained query
- no intermediate objects(creating table/view is not an option).
- no
PL/SQL block DBMS_METADATA.GET_DDL(it is not the case)- WITH FUNCTION clauseas last resort
- 它必须是独立的查询
- 没有中间对象（创建表/视图不是一个选项）。
- 不
PL/SQL block DBMS_METADATA.GET_DDL（事实并非如此）- WITH FUNCTION 子句作为最后的手段
Is it possible to cast/convert/use built-in function from LONGto VARCHAR2?
是否有可能投/转换/使用内置函数从LONG到VARCHAR2？
EDIT TL;DR:
编辑 TL; 博士：
SELECT column_expression || 'a' -- convert to working code
FROM all_ind_expressions;
采纳答案by Dr Y Wit
You can use XML unless expressions contain something which can brake XML parsing.
您可以使用 XML，除非表达式包含可以阻止 XML 解析的内容。
select *
from xmltable(
'/ROWSET/ROW'
passing (select dbms_xmlgen.getxmltype('select * from all_ind_expressions
where index_name = ''XDB$COMPLEX_TYPE_AK''')
from dual)
columns index_owner varchar2(30) path 'INDEX_OWNER',
index_name varchar2(30) path 'INDEX_NAME',
table_owner varchar2(30) path 'TABLE_OWNER',
table_name varchar2(30) path 'TABLE_NAME',
column_expression varchar2(4000) path 'COLUMN_EXPRESSION')
INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_EXPRESSION
--------------- -------------------- --------------- -------------------- -----------------------------------
XDB XDB$COMPLEX_TYPE_AK XDB XDB$COMPLEX_TYPE SYS_OP_R2O("XMLDATA"."ALL_KID")
1 row selected.
回答by hi olaf
As stated by oracle experts themselves, for legacy reasons it's not possible to inline SUBSTR a LONG to a VARCHAR2. AskTom link.
正如 oracle 专家自己所说，由于遗留原因，不可能将 SUBSTR a LONG 内联到 VARCHAR2。AskTom 链接。
On this other linkyou'll find ways to do it with a procedure and even with a function if the LONG is shorter that 32k LONG.
在另一个链接上，如果 LONG 短于 32k LONG，您将找到使用过程甚至函数来完成此操作的方法。
And this function can be called later on in a SELECT query, which is what you may want to achieve.
稍后可以在 SELECT 查询中调用此函数，这正是您可能想要实现的。
回答by Lukasz Szozda
Using WITH FUNCTIONand approach from Converting Long to Varchar2but still it is somehow ugly and overcomplicated.
WITH FUNCTION从将 Long 转换为 Varchar2 的使用和方法，但它仍然在某种程度上丑陋且过于复杂。
CREATE TABLE TEST(Z INT);
CREATE INDEX IF_DOUBLE_TEST_Z ON TEST(Z*2);
Query:
询问：
WITH FUNCTION s_convert(pindex_owner VARCHAR2, pindex_name VARCHAR2,
ptable_owner VARCHAR2, ptable_name VARCHAR2)
RETURN VARCHAR2
AS
VAR1 LONG;
VAR2 VARCHAR2(4000);
BEGIN
SELECT column_expression
INTO VAR1
FROM all_ind_expressions
WHERE index_owner = pindex_owner AND index_name = pindex_name
AND table_owner = ptable_owner AND table_name = ptable_name
AND column_position = 1; -- only one column indexes
VAR2 := SUBSTR(VAR1, 1, 4000);
RETURN VAR2;
END;
SELECT aie.*,
REPLACE(REPLACE(REPLACE(
q'{ALTER INDEX "<index_owner>"."<index_name>" ON ... (<column_expression>)}'
,'<index_owner>', index_owner )
,'<index_name>', index_name)
,'<column_expression>',
s_convert(index_owner, index_name, table_owner, table_name)) AS result
FROM all_ind_expressions aie
WHERE TABLE_NAME='TEST';
I believe that there should be more elegant way to achieve it.
我相信应该有更优雅的方式来实现它。
回答by Leon
The best way to deal with long is to: 1) Create a temporary table with a lob type (eg. CLOB). 2) Use the only allowed syntax by oracle: "TO_LOB converts LONG or LONG RAW values in the column long_column to LOB values. You can apply this function only to a LONG or LONG RAW column, and only in the select list of a subquery in an INSERT statement." 3) exploit the temporary table to do your stuff
处理 long 的最佳方法是： 1) 创建一个 lob 类型（例如 CLOB）的临时表。2) 使用oracle 唯一允许的语法：“TO_LOB 将long_column 列中的LONG 或LONG RAW 值转换为LOB 值。您只能将此函数应用于LONG 或LONG RAW 列，并且只能在子查询的选择列表中一个 INSERT 语句。” 3）利用临时表来做你的事情
