oracle 将 BLOB/CLOB 作为参数传递给 PL/SQL 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1367830/
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
Passing BLOB/CLOB as parameter to PL/SQL function
提问by Ula Krukar
I have this procedure i my package:
我的包裹中有这个程序：
PROCEDURE pr_export_blob(
p_name IN VARCHAR2,
p_blob IN BLOB,
p_part_size IN NUMBER);
I would like for parameter p_blobto be either BLOB or CLOB.
我希望参数p_blob是 BLOB 或 CLOB。
When I call this procedure with BLOB parameter, everything is fine. When I call it with CLOB parameter, I get compilation error:
当我用 BLOB 参数调用这个过程时，一切都很好。当我用 CLOB 参数调用它时，出现编译错误：
PLS-00306: wrong number or types of arguments in call to 'pr_export_blob'
Is there a way to write a procedure, that can take either of those types as parameter? Some kind of a superclass maybe?
有没有办法编写一个过程，可以将这两种类型中的任何一种作为参数？也许是某种超类？
回答by Chris Gill
Why don't you just overload the procedure to have a CLOB implementation as well
为什么不重载过程以实现 CLOB
PROCEDURE pr_export_lob(
p_name IN VARCHAR2,
p_blob IN BLOB,
p_part_size IN NUMBER);
PROCEDURE pr_export_lob(
p_name IN VARCHAR2,
p_clob IN CLOB,
p_part_size IN NUMBER);
You'll then need to work out the logic of what to do with in each procedure. As Colin says, a CLOB is not a BLOB - so I'm not sure what you plan to do with this
然后，您需要确定在每个过程中要做什么的逻辑。正如科林所说，CLOB 不是 BLOB - 所以我不确定你打算用它做什么
回答by Colin Pickard
Stupid question first, are you actually changing the procedure in the package to accept a CLOB? A CLOB is not interchangable with a BLOB.
先问个蠢问题，你真的把包里的程序改成接受一个CLOB吗？CLOB 不能与 BLOB 互换。
It is possible to convert a CLOB to BLOB:
create or replace procedure CLOB2BLOB (p_clob in out nocopy clob, p_blob in out nocopy blob) is
-- transforming CLOB a BLOB
l_off number default 1;
l_amt number default 4096;
l_offWrite number default 1;
l_amtWrite number;
l_str varchar2(4096 char);
begin
begin
loop
dbms_lob.read ( p_clob, l_amt, l_off, l_str );
l_amtWrite := utl_raw.length ( utl_raw.cast_to_raw( l_str) );
dbms_lob.write( p_blob, l_amtWrite, l_offWrite,
utl_raw.cast_to_raw( l_str ) );
l_offWrite := l_offWrite + l_amtWrite;
l_off := l_off + l_amt;
l_amt := 4096;
end loop;
exception
when no_data_found then
NULL;
end;
end;
