如何使用存储过程将文件读入Oracle DB

Question

我正在尝试将文件作为 blob 读入我的 oracle 表中。该文件是*.gz 数据。我环顾网络并找到了一些示例，这就是我想到的：

create or replace PROCEDURE upload_supp_data
IS
   src_file   BFILE;
   dst_file   BLOB;
   lgh_file   BINARY_INTEGER;
   data_dir varchar2(20) := '/tmp/';
   file_name varchar2(50) := '200912020200.rep-ids-top50-sip.txt.gz';
BEGIN

   src_file := BFILENAME (data_dir, file_name);

   -- insert a NULL record to lock
   INSERT INTO alarms_supplemental
               (alarm_id, resource_id, supplementaldata
               )
        VALUES (13794740, 1, EMPTY_BLOB ()
               )
     RETURNING supplementaldata
          INTO dst_file;

   -- lock record
   SELECT  supplementaldata
         INTO dst_file
         FROM alarms_supplemental
        WHERE alarm_id = 13794740
   FOR UPDATE;

   -- open the file
   DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
   -- determine length
   lgh_file := DBMS_LOB.getlength (src_file);
   -- read the file
   DBMS_LOB.loadfromfile (dst_file, src_file, lgh_file);

   -- update the blob field
     UPDATE ALARMS_SUPPLEMENTAL
      SET supplementaldata = dst_file
    WHERE ALARM_ID = 13794740;

   -- close file
   DBMS_LOB.fileclose (src_file);
END upload_supp_data;

当我运行此程序时，我收到这些错误：

ORA-22285: non-existent directory or file for FILEOPEN operation

ORA-06512: at "SYS.DBMS_LOB", line 635

ORA-06512: at "AIP_DBA.UPLOAD_SUPP_DATA", line 29

ORA-06512: at line 2

Process exited.

我以各种方式使用了路径，即“/tmp/”， “tmp”、“/tmp”。文件名是正确的，所以我不知道出了什么问题。这实际上是我编写的第一个存储过程，所以这可能是一件非常简单的事情。我希望有人能帮助我解决这个问题。顺便说一句，我正在使用 Oracle SQL Developer。

Answer 1

您需要 DBA 执行以下操作：

CREATE DIRECTORY brian_tmp AS '/tmp';
GRANT READ, WRITE ON DIRECTORY brian_tmp TO brian;

然后将 brian_tmp 替换为代码中的 /tmp/。 DBA 可能不想让您访问所有 /tmp （因为您的用户现在可以在该目录中伪装成运行 Oracle 的 Unix 用户执行任何操作），在这种情况下您将需要一个子目录。

Answer 2

您需要 DBA 在 Oracle 中创建 Directory 对象（而不是磁盘上的目录）。类似于：

CREATE DIRECTORY admin AS 'oracle/admin';

然后向目录授予权限；像其他模式对象一样（视图、包等...）

Answer 3

除了其他答案之外，请注意，当您使用目录时，例如：

CREATE DIRECTORY my_dir as '/tmp';
GRANT READ, WRITE ON DIRECTORY my_dir TO this_user;
...
data_dir varchar2(20) := 'MY_DIR';

目录名称必须为大写，除非通过在 CREATE 语句中使用双引号以小写形式专门创建：

CREATE DIRECTORY "My_Dir" AS '/tmp';

在这种情况下，您始终拥有在 SQL 中引用双引号中的名称，以及在编程引用中的正确大小写：

GRANT READ, WRITE ON DIRECTORY "My_Dir" TO this_user;
...
data_dir varchar2(20) := 'My_Dir';