我真的需要这个存储过程中的参数吗?
以下存储过程旨在从 Oracle 数据库中获取所有 BLOB 值并将它们保存到名为 OraFolder 的文件夹中。
它编译得很好,但我有两个问题。
1、有2个参数,pname和display_name。我必须承认,我不知道它们的用途,因为我只是用谷歌搜索了似乎适合我们需要的代码。
我的问题是,考虑到我们正在尝试将所有 BLOB 值提取到文件夹中,我真的需要这 2 个参数吗?
2、如果你的答案是肯定的,我确实需要它们,我该如何使用它们?
最后,还有一个 Entry_Id,我只是不断收到未声明的错误。我不得不把它去掉。它有什么用?
抱歉,我不是 Oracle 人员,只是想找出一个方法来解决落在我腿上的问题。
预先感谢
这是完整的存储过程。
create or replace PROCEDURE blob2file(pfname VARCHAR2, display_name in varchar2) IS
vblob BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;
my_vr RAW(32000);
x NUMBER;
v_name varchar2(100);
lv_str_len NUMBER;
l_output utl_file.file_type;
BEGIN
-- define output directory
lv_str_len := length(pfname);
--v_name := display_name||upper(substr(pfname,lv_str_len-3,lv_str_len));
v_name := display_name;
l_output := utl_file.fopen('MY_FOLDER', v_name, 'w', 32760);
-- get length of blob
SELECT dbms_lob.getlength(blob_content)
INTO len
FROM portal.WWDOC_DOCUMENT$
WHERE FILENAME = pfname;
-- dbms_output.put_line('Length: '||len);
-- save blob length
x := len;
-- select blob into variable
SELECT blob_content
INTO vblob
FROM portal.WWDOC_DOCUMENT$
WHERE FILENAME = pfname;
-- if small enough for a single write
IF len < 32760 THEN
-- dbms_output.put_line('Single write ');
utl_file.put_raw(l_output,vblob);
utl_file.fflush(l_output);
ELSE -- write in pieces
-- dbms_output.put_line('multi write '||vstart);
vstart := 1;
WHILE vstart < len
LOOP
dbms_lob.read(vblob,bytelen,vstart,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);
-- set the start position for the next cut
vstart := vstart + bytelen;
-- set the end position if less than 32000 bytes
x := x - bytelen;
IF x < 32000 THEN
bytelen := x;
END IF;
END LOOP;
END IF;
dbms_output.put_line('End');
utl_file.fclose(l_output);
EXCEPTION
when others then dbms_output.put_line('ERROR:'||entry_id);
END blob2file;
The following stored procedure is intended to grab all the BLOB values from an Oracle database and save them into a folder called OraFolder.
It compiles fine but I have 2 questions.
1, there are 2 parameters, pname and display_name. I must admit that I don't know what they are there for because I just googled the code which seems to fit into our need.
My question is do I really need the 2 params given that we are trying to extract ALL BLOB values into a folder?
2, If your answer is yes, I do need them, how I do I use them?
Finally, there is an Entry_Id, I just kept getting an error that it is not declared. I had to remove it. What is it used for?
Sorr, I am not an Oracle guy, just trying to figure out a wa to fix a problem that is dropped on my laps.
Thanks in advance
Here is the complete stored proc.
create or replace PROCEDURE blob2file(pfname VARCHAR2, display_name in varchar2) IS
vblob BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;
my_vr RAW(32000);
x NUMBER;
v_name varchar2(100);
lv_str_len NUMBER;
l_output utl_file.file_type;
BEGIN
-- define output directory
lv_str_len := length(pfname);
--v_name := display_name||upper(substr(pfname,lv_str_len-3,lv_str_len));
v_name := display_name;
l_output := utl_file.fopen('MY_FOLDER', v_name, 'w', 32760);
-- get length of blob
SELECT dbms_lob.getlength(blob_content)
INTO len
FROM portal.WWDOC_DOCUMENT$
WHERE FILENAME = pfname;
-- dbms_output.put_line('Length: '||len);
-- save blob length
x := len;
-- select blob into variable
SELECT blob_content
INTO vblob
FROM portal.WWDOC_DOCUMENT$
WHERE FILENAME = pfname;
-- if small enough for a single write
IF len < 32760 THEN
-- dbms_output.put_line('Single write ');
utl_file.put_raw(l_output,vblob);
utl_file.fflush(l_output);
ELSE -- write in pieces
-- dbms_output.put_line('multi write '||vstart);
vstart := 1;
WHILE vstart < len
LOOP
dbms_lob.read(vblob,bytelen,vstart,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);
-- set the start position for the next cut
vstart := vstart + bytelen;
-- set the end position if less than 32000 bytes
x := x - bytelen;
IF x < 32000 THEN
bytelen := x;
END IF;
END LOOP;
END IF;
dbms_output.put_line('End');
utl_file.fclose(l_output);
EXCEPTION
when others then dbms_output.put_line('ERROR:'||entry_id);
END blob2file;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
pfname
参数定义了要获取的文件,因此您不需要它,因为您需要所有文件。display_name
参数定义输出目录,因此如果您想对目录进行硬编码,也可以。由于您需要所有文件,因此您需要循环遍历表中的所有记录并一次输出一个:
The
pfname
param defines which file to grab, so you don't need that since you want them all.The
display_name
param defines the output directory, so if you want to hard code the directory, you could.Since you want all files, you'll need to loop through all records in the table and output them one at a time:
看起来
vname
是 blob 写入的文件的名称。pname
是表中 blob 的键。因此,如果要转储表中的所有 blob,则不需要其中任何一个,但需要为每个 blob 提供唯一的文件名。Looks like
vname
is the name of the file which the blob is written to. Andpname
is the key of the blob in the table. So if you're dumping all blobs in the table then you don't need either of these, but you will need to come up with a unique filename for each blob.