从Blob字段读取多个值PL/SQL [英] reading multiple values from a blob field PL/SQL

问题描述

我使用以下代码在一个 BLOB字段中写入了多个文件:

I wrote several files into one BLOB field using this code:

    create or replace procedure blob_loader as

      dir    varchar2(50) := 'FILES_TO_LOAD';
      lblob  BLOB;
      lfile  BFILE;
      f      UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN(dir, 'index.txt', 'R');
      buffer VARCHAR2(30000);

    begin

      insert into blob_table
        (blob_file)

  values
    (empty_blob())
  returning blob_file into lblob;

  dbms_lob.open(lblob, dbms_lob.lob_readwrite);

  LOOP
    BEGIN
      utl_file.get_line(f, buffer);

      lfile := BFILENAME(dir, buffer);
      dbms_lob.open(lfile, dbms_lob.lob_readonly);

      dbms_lob.loadfromfile(lblob, lfile, dbms_lob.getlength(lfile));
      dbms_lob.close(lfile);

    EXCEPTION
      WHEN no_data_found THEN
        EXIT;
    END;
  END LOOP;

  dbms_lob.close(lblob);
  commit;

end blob_loader;

现在，我想将该字段中的文件读回到磁盘，以便它们再次成为单独的文件.

Now I want to read the files from that field back to the disk, so that they were separate files again.

有人知道如何定义blob字段中的一个文件何时结束而另一个文件何时开始吗?

Does anyone have an idea how to define when one file in the blob field ends and another one starts?

有什么帮助吗?

推荐答案

抛开显而易见的建议，将每个文件存储为单独的BLOB ...

Putting aside the obvious advice to just store each file as a separate BLOB ...

在我看来，您有两种选择.要么将每个文件的长度存储在某个地方，然后用它来控制您从BLOB读取的数据量.或在文件之间的BLOB中放入一些定界符值.对我来说，定界符似乎是更糟糕的选择-您必须选择一个保证在文件数据中不存在的值，并且必须读取单个字节才能找到定界符.

Seems to me you have two choices. Either store the length of each file somewhere, and use that to control the amount of data you read from the BLOB; or put some delimiter value into the BLOB between the files. The delimiter seems like worse choice to me - you have to pick a value that is guaranteed not to be present in the file data, and you have to read single bytes to find the delimiter.

因此，请存储长度.可能还有文件名.这意味着每个文件只有一行的子表.这使我们回到问题上来-为什么不只在每个行中都存储一个BLOB，每个文件中存储一个BLOB?

So store the lengths. And probably the filenames. Which implies a child table with one row per file. Which brings us back to the question -- why not just store a BLOB in each of those rows, one per file?

这篇关于从Blob字段读取多个值PL/SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！