如何使用PL/SQL读取Oracle Directory的所有文件并更新BLOB列

问题描述

我需要读取Oracle目录的每个文件(不知道名称)，如果文件名与PK表匹配，则用该文件更新BLOB列.

I need to read every files of a Oracle Directory (without knowing the name), and update a BLOB column with the file if the name of the file match with the PK table.

TABLE_PRUEBA
ID   NUMBER(10,0) PK
FOTO BLOB NULL

我执行了PL/SQL函数，以读取一个文件(知道名称)并使用BLOB更新表，而BLOB可以正常工作.但是，我不知道如何在不知道文件名称的情况下读取每个文件.

I did a PL/SQL function to read one file (knowing the name) and update the table with the BLOB, which work correctly. But, I dont't know how I could read every file without knowing the name and take the name of the file.

DECLARE
    l_blob      BLOB;
    v_src_loc   BFILE := BFILENAME ('IMAGE_FILES8', '4.PNG');
    v_amount    INTEGER;
BEGIN
    UPDATE TABLE_PRUEBA
       SET FOTO = EMPTY_BLOB ()
     WHERE ID = 4
    RETURN FOTO
      INTO l_blob;

    DBMS_LOB.OPEN (v_src_loc, DBMS_LOB.LOB_READONLY);
    v_amount := DBMS_LOB.GETLENGTH (v_src_loc);
    DBMS_LOB.LOADFROMFILE (l_blob, v_src_loc, v_amount);
    DBMS_LOB.CLOSE (v_src_loc);
    COMMIT;
END;

推荐答案

看看这个网站，它说明了如何从目录中读取所有文件.

Take a look of this web, it explain how to read all file from directory.

https://asktom.oracle.com/pls/asktom/asktom.search?tag=reading-files-in-a-directory-how-to-get-a-list可用文件

这篇关于如何使用PL/SQL读取Oracle Directory的所有文件并更新BLOB列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！