我真的需要这个存储过程中的参数吗?

发布于 2024-12-14 14:23:40 字数 1949 浏览 2 评论 0原文

以下存储过程旨在从 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

爱本泡沫多脆弱 2024-12-21 14:23:40

pfname 参数定义了要获取的文件,因此您不需要它,因为您需要所有文件。

display_name 参数定义输出目录,因此如果您想对目录进行硬编码,也可以。

由于您需要所有文件,因此您需要循环遍历表中的所有记录并一次输出一个:

CREATE OR REPLACE PROCEDURE blob2file 
IS
   l_output    utl_file.file_type;
   vstart      NUMBER := 1;
   bytelen     NUMBER := 32000;
   x           NUMBER;
   my_vr       RAW(32000);
BEGIN

   FOR recFiles IN (SELECT dbms_lob.getlength(BLOB_CONTENT) as len,
                           FILENAME,
                           BLOB_CONTENT
                      FROM PORTAL.WWDOC_DOCUMENT$)
   LOOP

      l_output := utl_file.fopen('MY_FOLDER', '/hard code the path here/', 'w', 32760);

      IF recFiles.len < 32760 THEN

         utl_file.put_raw(l_output, recFiles.BLOB_CONTENT);
         utl_file.fflush(l_output);

      ELSE -- write in pieces

         vstart := 1;

         WHILE vstart < refFiles.len
         LOOP
            dbms_lob.read(recFiles.BLOB_CONTENT, 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;

      utl_file.fclose(l_output);
      dbms_output.put_line('End');

EXCEPTION
   WHEN OTHERS THEN 
      dbms_output.put_line('ERROR: ' || SQLERRM);

END blob2file;

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:

CREATE OR REPLACE PROCEDURE blob2file 
IS
   l_output    utl_file.file_type;
   vstart      NUMBER := 1;
   bytelen     NUMBER := 32000;
   x           NUMBER;
   my_vr       RAW(32000);
BEGIN

   FOR recFiles IN (SELECT dbms_lob.getlength(BLOB_CONTENT) as len,
                           FILENAME,
                           BLOB_CONTENT
                      FROM PORTAL.WWDOC_DOCUMENT$)
   LOOP

      l_output := utl_file.fopen('MY_FOLDER', '/hard code the path here/', 'w', 32760);

      IF recFiles.len < 32760 THEN

         utl_file.put_raw(l_output, recFiles.BLOB_CONTENT);
         utl_file.fflush(l_output);

      ELSE -- write in pieces

         vstart := 1;

         WHILE vstart < refFiles.len
         LOOP
            dbms_lob.read(recFiles.BLOB_CONTENT, 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;

      utl_file.fclose(l_output);
      dbms_output.put_line('End');

EXCEPTION
   WHEN OTHERS THEN 
      dbms_output.put_line('ERROR: ' || SQLERRM);

END blob2file;
陪你搞怪i 2024-12-21 14:23:40

看起来 vname 是 blob 写入的文件的名称。 pname 是表中 blob 的键。因此,如果要转储表中的所有 blob,则不需要其中任何一个,但需要为每个 blob 提供唯一的文件名。

Looks like vname is the name of the file which the blob is written to. And pname 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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文