我想使用此代码执行批量复制，可以吗？

发布于 2024-12-15 11:43:57 字数 1348 浏览 2 评论 0原文

我认为如果我能够克服“无效操作错误”，这个将能够一次将所有文件写入一个目录，

非常感谢您一如既往的帮助。

create or replace
PROCEDURE GetbFile
IS
   l_output    utl_file.file_type;
   vstart      NUMBER := 1;
   bytelen     NUMBER := 32000;
   x           NUMBER;
   my_vr       RAW(32000);
   v_name     VARCHAR2(32760);
BEGIN
   FOR recFiles IN (SELECT dbms_lob.getlength(BLOB_VALUE) as len,
                           FILE_NAME,
                           BLOB_VALUE from Gfile)
   LOOP
      l_output := utl_file.fopen('THE_DIR', 'file_name'||'.dot', 'w', 32760);
      IF recFiles.len < 32760 THEN
         utl_file.put_raw(l_output, recFiles.BLOB_VALUE);
         utl_file.fflush(l_output);
      ELSE -- write in pieces
         vstart := 1;
         WHILE vstart < recFiles.len
         LOOP
            dbms_lob.read(recFiles.BLOB_VALUE, 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;
     End Loop;
      dbms_output.put_line('End');
      utl_file.fclose(l_output);
END GetFile;

原文

I think this one will do the job of writing all the files to a directory all at once if I can get past the "invalid operation error"

Your help, as always, is greatly appreciated.

create or replace
PROCEDURE GetbFile
IS
   l_output    utl_file.file_type;
   vstart      NUMBER := 1;
   bytelen     NUMBER := 32000;
   x           NUMBER;
   my_vr       RAW(32000);
   v_name     VARCHAR2(32760);
BEGIN
   FOR recFiles IN (SELECT dbms_lob.getlength(BLOB_VALUE) as len,
                           FILE_NAME,
                           BLOB_VALUE from Gfile)
   LOOP
      l_output := utl_file.fopen('THE_DIR', 'file_name'||'.dot', 'w', 32760);
      IF recFiles.len < 32760 THEN
         utl_file.put_raw(l_output, recFiles.BLOB_VALUE);
         utl_file.fflush(l_output);
      ELSE -- write in pieces
         vstart := 1;
         WHILE vstart < recFiles.len
         LOOP
            dbms_lob.read(recFiles.BLOB_VALUE, 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;
     End Loop;
      dbms_output.put_line('End');
      utl_file.fclose(l_output);
END GetFile;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

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

魄砕の薆 2024-12-22 11:43:57

为什么用这个版本替换以前版本的代码？如果您已经有一个可以将单个 BLOB 写入文件系统的代码版本，则可以很容易地在循环中调用该代码。这也是设计模块化代码的更好方法。
当您遇到错误时，请发布错误堆栈。其中包括 Oracle 错误号、错误消息和错误的行号。告诉我们您的代码中对应的行（特别是如果您在此处发布的内容与实际运行的代码之间存在格式差异）。
您无法在单个线程中同时将每个 LOB 复制到文件。单个线程一次可以做一件事，因此它一次可以复制一个文件。您可以循环以便按顺序复制每个文件。我仍然不清楚这是否是您想要做的，或者您是否真的想生成 800 个线程，每个线程将一个 LOB 写入文件系统。
由于在循环中打开文件，因此需要关闭循环内的文件（请注意，保留旧代码可以更轻松地避免此类错误）。假设您想使用表中的文件名，则需要在调用 fopen 时使用 recFiles.file_name，而不是硬编码字符串 ' file_name' 会尝试将每个 LOB 写入同一个物理文件。

鉴于此，我的猜测是您想要这样的东西（请注意，模块化此代码仍然是更好的形式，但由于您试图避免这种情况，我假设您有充分的理由）

create or replace
PROCEDURE GetbFile
IS
   l_output    utl_file.file_type;
   vstart      NUMBER := 1;
   bytelen     NUMBER := 32000;
   x           NUMBER;
   my_vr       RAW(32000);
   v_name     VARCHAR2(32760);
BEGIN
   FOR recFiles IN (SELECT dbms_lob.getlength(BLOB_VALUE) as len,
                           FILE_NAME,
                           BLOB_VALUE from Gfile)
   LOOP
      l_output := utl_file.fopen('THE_DIR', recFiles.file_name||'.dot', 'w', 32760);
      IF recFiles.len < 32760 THEN
         utl_file.put_raw(l_output, recFiles.BLOB_VALUE);
         utl_file.fflush(l_output);
      ELSE -- write in pieces
         vstart := 1;
         WHILE vstart < recFiles.len
         LOOP
            dbms_lob.read(recFiles.BLOB_VALUE, 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);
     End Loop;
     dbms_output.put_line('End');
END GetFile;

Why did you replace the prior version of the code with this version? If you already have a version of the code that is working to write a single BLOB to the file system, it's very easy to just call that code in a loop. It's also a better way of designing modular code.
When you get an error, please post the error stack. That will include the Oracle error number, the error message, and the line number of the error. Tell us what line that corresponds to in your code (particularly if there are formatting differences between what you post here and what code you actually run).
You cannot in a single thread copy every LOB to a file at the same time. A single thread can do one thing at a time so it can copy one file at a time. You can loop so that you copy each file sequentially. I'm still not clear whether that is what you want to do or if you really want to spawn 800 threads each of which writes a single LOB to the file system.
You need to close the file inside the loop since you open the file in the loop (note that keeping the old code would make it much easier to avoid this sort of error). And assuming that you want to use the file name from the table, you'd want to use recFiles.file_name in your call to fopen, not a hard-coded string 'file_name' which would try to write every LOB to the same physical file.

Given that, my guess is that you want something like this (note that it would still be better form to modularize this code but since you're trying to avoid that, I'll assume you have a good reason for that)

create or replace
PROCEDURE GetbFile
IS
   l_output    utl_file.file_type;
   vstart      NUMBER := 1;
   bytelen     NUMBER := 32000;
   x           NUMBER;
   my_vr       RAW(32000);
   v_name     VARCHAR2(32760);
BEGIN
   FOR recFiles IN (SELECT dbms_lob.getlength(BLOB_VALUE) as len,
                           FILE_NAME,
                           BLOB_VALUE from Gfile)
   LOOP
      l_output := utl_file.fopen('THE_DIR', recFiles.file_name||'.dot', 'w', 32760);
      IF recFiles.len < 32760 THEN
         utl_file.put_raw(l_output, recFiles.BLOB_VALUE);
         utl_file.fflush(l_output);
      ELSE -- write in pieces
         vstart := 1;
         WHILE vstart < recFiles.len
         LOOP
            dbms_lob.read(recFiles.BLOB_VALUE, 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);
     End Loop;
     dbms_output.put_line('End');
END GetFile;

回复收藏 0 原文

~没有更多了~