使用PL / SQL，你如何获得一个文件的内容到一个blob？ [英] Using PL/SQL how do you I get a file's contents in to a blob?

问题描述

我有一个文件。我想将它的内容放到我的oracle数据库的blob列中，或者放到我的PL / SQL程序中的blob变量中。什么是最好的方法来做到这一点？

解决方案

要在PL / SQL中完全执行该操作，该文件需要处于打开状态该服务器位于您需要在数据库中定义的目录中。创建以下对象：

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



创建或替换过程BLOB_LOAD
AS

lBlob BLOB;
lFile BFILE：= BFILENAME（'BLOB_DIR'，'filename'）;

EGIN

插入表（id，your_blob）
VALUES（xxx，empty_blob（））
返回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;
/


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?

解决方案

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;
/

这篇关于使用PL / SQL，你如何获得一个文件的内容到一个blob？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！