如何在PL/SQL中实现无硬编码获取指定文件夹文件列表？

这个需求完全可以在PL/SQL中实现，核心思路是利用**Oracle目录对象（DIRECTORY）**动态获取文件夹路径，替代硬编码的字符串。下面针对你尝试过的两种方案给出优化后的无硬编码版本：

方案一：基于SYS.DBMS_BACKUP_RESTORE的实现 #

前置准备 #

1. 确保你拥有必要权限：

   GRANT EXECUTE ON SYS.DBMS_BACKUP_RESTORE TO your_user;
   GRANT READ ON DIRECTORY MY_SCRIPTS TO your_user;
   

2. 确认目标文件夹已创建对应的Oracle目录对象（如果还没创建）：

   CREATE DIRECTORY MY_SCRIPTS AS '/实际的文件夹路径';
   

无硬编码的函数实现 #

这个函数接受目录名作为参数，动态获取路径后查询文件列表：

CREATE OR REPLACE FUNCTION get_script_files(p_dir_name VARCHAR2)
RETURN SYS_REFCURSOR
IS
    v_dir_path VARCHAR2(1000);
    v_cursor SYS_REFCURSOR;
    v_file_list SYS.DBMS_BACKUP_RESTORE.filelist;
BEGIN
    -- 动态获取目录对象对应的实际路径
    v_dir_path := SYS.DBMS_DIRECTORY.GET_DIRECTORY_PATH(p_dir_name);

    -- 初始化备份恢复包，获取文件列表
    SYS.DBMS_BACKUP_RESTORE.RESTOREFILELISTBEGIN;
    SYS.DBMS_BACKUP_RESTORE.SETSOURCEPARAM('DEVICE TYPE', 'DISK');
    SYS.DBMS_BACKUP_RESTORE.SETSOURCEPARAM('PATH', v_dir_path || '/*'); -- 匹配目录下所有文件
    SYS.DBMS_BACKUP_RESTORE.RESTOREFILELISTEND(v_file_list);

    -- 将文件列表转为游标返回，过滤SQL脚本
    OPEN v_cursor FOR
        SELECT fname AS file_name, fsize AS file_size
        FROM TABLE(v_file_list)
        WHERE fname LIKE '%.sql';

    RETURN v_cursor;
END;
/

使用示例 #

DECLARE
    v_files SYS_REFCURSOR;
    v_file_name VARCHAR2(255);
    v_dir_path VARCHAR2(1000);
BEGIN
    v_dir_path := SYS.DBMS_DIRECTORY.GET_DIRECTORY_PATH('MY_SCRIPTS');
    v_files := get_script_files('MY_SCRIPTS');
    
    LOOP
        FETCH v_files INTO v_file_name;
        EXIT WHEN v_files%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('正在执行脚本: ' || v_file_name);
        -- SQL*Plus环境下可直接调用START命令
        -- EXECUTE IMMEDIATE 'START ' || v_dir_path || '/' || v_file_name;
        -- PL/SQL中可通过UTL_FILE读取脚本内容后执行，或用DBMS_SCHEDULER调用OS命令
    END LOOP;
    CLOSE v_files;
END;
/

方案二：基于X$KRBMSFT的实现 #

前置准备 #

需要获取内部视图的访问权限：

GRANT SELECT ON SYS.X$KRBMSFT TO your_user;

无硬编码的查询实现 #

同样通过目录对象获取路径，过滤视图中的记录：

CREATE OR REPLACE FUNCTION get_script_files_x(p_dir_name VARCHAR2)
RETURN SYS_REFCURSOR
IS
    v_dir_path VARCHAR2(1000);
    v_cursor SYS_REFCURSOR;
BEGIN
    v_dir_path := SYS.DBMS_DIRECTORY.GET_DIRECTORY_PATH(p_dir_name);
    
    OPEN v_cursor FOR
        SELECT DISTINCT SUBSTR(kftftnam, INSTR(kftftnam, '/', -1) + 1) AS file_name
        FROM SYS.X$KRBMSFT
        WHERE kftftnam LIKE v_dir_path || '/%.sql'
          AND kftfttyp = 'F'; -- 仅过滤文件，排除目录
    
    RETURN v_cursor;
END;
/

关键注意事项 #

• 优先使用DBMS_BACKUP_RESTORE方案，因为X$KRBMSFT是Oracle内部视图，官方不保证跨版本兼容性。
• PL/SQL本身不支持直接执行外部SQL文件，可选择：
  • 在SQL*Plus环境中结合游标结果循环调用START命令。
  • 使用UTL_FILE读取脚本内容，再通过EXECUTE IMMEDIATE执行。
  • 通过DBMS_SCHEDULER调用操作系统命令（如sqlplus）执行脚本。

内容的提问来源于stack exchange，提问作者Albert Rafalski