使用 PL/SQL 如何将文件内容放入 blob?

2021-12-24 00:00:00 file-io blob oracle plsql

我有一个文件.我想将其内容放入我的 oracle 数据库中的 blob 列或放入我的 PL/SQL 程序中的 blob 变量中.最好的方法是什么?

I have a file. I want to get its contents into a blob column in my oracle database or into a blob variable in my PL/SQL program. What is the best way to do that?

推荐答案

要完全在 PL/SQL 中完成，文件需要在服务器上，位于您需要在数据库中定义的目录中.创建以下对象:

To do it entirely in PL/SQL, the file would need to be on the server, located in a directory which you'd need to define in the database. Create the following objects:

CREATE OR REPLACE DIRECTORY
    BLOB_DIR
    AS
    '/oracle/base/lobs'
/



CREATE OR REPLACE PROCEDURE BLOB_LOAD
AS

    lBlob  BLOB;
    lFile  BFILE := BFILENAME('BLOB_DIR', 'filename');

BEGIN

    INSERT INTO table (id, your_blob)
        VALUES (xxx, empty_blob())
        RETURNING your_blob INTO lBlob;

    DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);

    DBMS_LOB.OPEN(lBlob, DBMS_LOB.LOB_READWRITE);

    DBMS_LOB.LOADFROMFILE(DEST_LOB => lBlob,
                          SRC_LOB  => lFile,
                          AMOUNT   => DBMS_LOB.GETLENGTH(lFile));

    DBMS_LOB.CLOSE(lFile);
    DBMS_LOB.CLOSE(lBlob);

    COMMIT;

END;
/

相关文章