如何在 oracle pl/sql 中将 CLOB 转换为 VARCHAR2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12860526/
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 convert CLOB to VARCHAR2 inside oracle pl/sql
提问by Munish Goyal
I have a clob variable, need to assign it to varchar2 variable. The data inside clob var is less than 4000 (i..e varchar2's maxsize) oracle10+
我有一个 clob 变量，需要将它分配给 varchar2 变量。clob var 中的数据小于 4000（即 varchar2 的 maxsize） oracle10+
I tried
我试过
report_len := length(report_clob);
report := TO_CHAR(dbms_lob.substr(report_clob, report_len, 1 ));
report_clob := null;
but it turns report into long value which I see while debugging. Also when I call this sql (proc) from my C# code. It complains saying buffer too small, because I send parameter as per varchar, but the above conversion might be turning it into long value.
但它将报告转换为我在调试时看到的长值。同样，当我从我的 C# 代码调用这个 sql (proc) 时。它抱怨说缓冲区太小，因为我按照 varchar 发送参数，但上述转换可能会将其转换为 long 值。
I even tried direct assignment
我什至尝试过直接赋值
report_clob := report
getting the same result.
得到相同的结果。
EDIT
编辑
Ok, to answer the questions below please see: I debug using test script in PL/SQL developer. report variable is varchar2(4000). When I step after 2nd line. report shows to be a long value and it just says (Long Value) . cant even see the contents.
好的，要回答以下问题，请参阅：我在 PL/SQL 开发人员中使用测试脚本进行调试。报告变量是 varchar2(4000)。当我踏入第二行时。报告显示是一个长值，它只是说 (Long Value) 。甚至看不到内容。
report and report_clob are out variable from the procedure. This procedure is called from C# code.
report 和 report_clob 是程序中的变量。此过程是从 C# 代码调用的。
There is an exception string buffer too small in C# when I call this procedure. I have given 5000 as size of report variable in C# sufficient to receive 4000 max characters value from the procedure. So I guess problem doesn't lie there.
当我调用此过程时，C# 中有一个异常字符串缓冲区太小。我在 C# 中给出了 5000 作为报告变量的大小，足以从过程中接收 4000 个最大字符值。所以我想问题不在于那里。
And when I assign report:= 'some string....' then C# call works fine.
当我分配 report:= 'some string....' 然后 C# 调用工作正常。
So my investigation says that report := transform (report_clob) is making report become long value or some such thing (weird) which makes C# code problematic to handle larger value in 5000 varchar out parameter.
所以我的调查表明 report := transform (report_clob) 正在使报告变成长值或一些这样的事情（奇怪），这使得 C# 代码在处理 5000 varchar out 参数中的更大值时出现问题。
Any more detail I will be happy to provide.
我很乐意提供更多细节。
回答by AnBisw
Quote (read here)-
报价（在这里阅读）-
When you use CAST to convert a CLOB value into a character datatype or a BLOB value into the RAW datatype, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target datatype.
当您使用 CAST 将 CLOB 值转换为字符数据类型或将 BLOB 值转换为 RAW 数据类型时，数据库将 LOB 值隐式转换为字符或原始数据，然后将结果值显式转换为目标数据类型。
So, something like this should work-
所以，这样的事情应该工作 -
report := CAST(report_clob AS VARCHAR2);
Or better yet use it as CAST(report_clob AS VARCHAR2)where ever you are trying to use the BLOBas VARCHAR
或者更好地将它用作CAST(report_clob AS VARCHAR2)您尝试使用BLOBas 的任何地方VARCHAR
回答by Jon Heller
Converting VARCHAR2 to CLOB
将 VARCHAR2 转换为 CLOB
In PL/SQL a CLOB can be converted to a VARCHAR2 with a simple assignment, SUBSTR, and other methods. A simple assignment will only work if the CLOB is less then or equal to the size of the VARCHAR2. The limit is 32767 in PL/SQL and 4000 in SQL (although 12c allows 32767 in SQL).
在 PL/SQL 中，可以使用简单的赋值、SUBSTR 和其他方法将 CLOB 转换为 VARCHAR2。只有当 CLOB 小于或等于 VARCHAR2 的大小时，简单的赋值才有效。PL/SQL 中的限制为 32767，SQL 中为 4000（尽管 12c 允许 SQL 中为 32767）。
For example, this code converts a small CLOB through a simple assignment and then coverts the beginning of a larger CLOB.
例如，这段代码通过简单的赋值转换了一个小的 CLOB，然后转换了一个更大的 CLOB 的开头。
declare
v_small_clob clob := lpad('0', 1000, '0');
v_large_clob clob := lpad('0', 32767, '0') || lpad('0', 32767, '0');
v_varchar2 varchar2(32767);
begin
v_varchar2 := v_small_clob;
v_varchar2 := substr(v_large_clob, 1, 32767);
end;
LONG?
长的？
The above code does not convert the value to a LONG. It merely looks that way because of limitations with PL/SQL debuggers and strings over 999 characters long.
上面的代码不会将值转换为 LONG。它看起来只是因为 PL/SQL 调试器和长度超过 999 个字符的字符串的限制。
For example, in PL/SQL Developer, open a Test window and add and debug the above code. Right-click on v_varchar2and select "Add variable to Watches". Step through the code and the value will be set to "(Long Value)". There is a ...next to the text but it does not display the contents.
例如，在 PL/SQL Developer 中，打开一个测试窗口并添加和调试上述代码。右键单击v_varchar2并选择“向手表添加变量”。单步执行代码，该值将设置为“（长值）”。...文本旁边有一个，但不显示内容。
C#?
C＃？
I suspect the real problem here is with C# but I don't know how enough about C# to debug the problem.
我怀疑这里真正的问题是 C#，但我不知道 C# 有多少调试问题。
回答by panser
ALTER TABLE TABLE_NAME ADD (COLUMN_NAME_NEW varchar2(4000 char));
update TABLE_NAME set COLUMN_NAME_NEW = COLUMN_NAME;
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;
ALTER TABLE TABLE_NAME rename column COLUMN_NAME_NEW to COLUMN_NAME;
回答by Esneyder
This is my aproximation:
这是我的近似：
Declare
Variableclob Clob;
Temp_Save Varchar2(32767); //whether it is greater than 4000
Begin
Select reportClob Into Temp_Save From Reporte Where Id=...;
Variableclob:=To_Clob(Temp_Save);
Dbms_Output.Put_Line(Variableclob);
End;
