将小型 Oracle 长原始值转换为其他类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/426079/
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
Converting small-ish Oracle long raw values to other types
提问by Chris Farmer
I have an Oracle table that contains a field of LONG RAW type that contains ASCII character data. How can I write a query or view that will convert this to a more easily consumed character string? These are always going to be single-byte characters, FWIW.
我有一个 Oracle 表，其中包含一个 LONG RAW 类型的字段，该字段包含 ASCII 字符数据。如何编写查询或视图将其转换为更容易使用的字符串？这些总是单字节字符，FWIW。
回答by tuinstoel
Maybe
也许
select ...., to_lob(long_raw) from old_table
select ...., to_lob(long_raw) from old_table
(http://www.psoug.org/reference/convert_func.html)
( http://www.psoug.org/reference/convert_func.html)
or
或者
UTL_RAW.CAST_TO_VARCHAR2(b)
回答by tuinstoel
I found this quote:
我找到了这个报价：
In Oracle9i, you can even:
alter table old_table modify ( c clob );
to convert it.
在 Oracle9i 中，您甚至可以：
更改表 old_table 修改（ c clob ）；
转换它。
See here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1037232794454
请参阅此处：http: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 1037232794454
Edit
编辑
The max length of a varchar2 column is 4000. Is that too short?
varchar2 列的最大长度是 4000。是不是太短了？
回答by Bob
I have found this works well on CLOB data types. I would believe the same would hold true for LOB types.
我发现这适用于 CLOB 数据类型。我相信 LOB 类型也是如此。
create or replace function lob2char(clob_col clob) return varchar2 IS
buffer varchar2(4000);
amt BINARY_INTEGER := 4000;
pos INTEGER := 1;
l clob;
bfils bfile;
l_var varchar2(4000):='';
begin
LOOP
if dbms_lob.getlength(clob_col)<=4000 THEN
dbms_lob.read (clob_col, amt, pos, buffer);
l_var := l_var||buffer;
pos:=pos+amt;
ELSE
l_var:= 'Cannot convert. Exceeded varchar2 limit';
exit;
END IF;
END LOOP;
return l_var;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return l_var;
END;
INSERT INTO NEWTABLE (NEWCOLUMN) SELECT RTRIM(lob2char(OLDCOLUMN)) FROM OLDTABLE;
