Oracle中如何基于嵌套目录创建外部表并将目录名设为列值

1 前置准备：权限与顶层目录创建 #

• 首先用SYS用户给业务操作用户授予必要权限：

GRANT CREATE ANY DIRECTORY, CREATE EXTERNAL TABLE, EXECUTE ON SYS.UTL_FILE TO <你的业务用户名>;

• 创建对应顶层目录的Oracle目录对象，假设你示例中Country目录的操作系统绝对路径为/data/Country：

CREATE OR REPLACE DIRECTORY DIR_TOP_COUNTRY AS '/data/Country';
-- 授予目录读写权限给业务用户
GRANT READ, WRITE ON DIRECTORY DIR_TOP_COUNTRY TO <你的业务用户名>;

注意：确保操作系统层面，Oracle运行用户（通常为oracle）对/data/Country及其所有子目录、文件有读权限。

2 创建递归读取所有文件路径的外部表 #

利用Oracle外部表的PREPROCESSOR属性，调用操作系统命令递归扫描所有txt文件的全路径：

CREATE TABLE all_nested_data_files (
    file_full_path VARCHAR2(4000)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY DIR_TOP_COUNTRY
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        -- 此处写find命令的绝对路径，可通过Linux下`which find`查询，示例为/usr/bin/find
        PREPROCESSOR '/usr/bin/find': '/data/Country -name "*.txt" -type f'
        FIELDS TERMINATED BY WHITESPACE
        (file_full_path CHAR(4000))
    )
    LOCATION ('dummy.tmp') -- 无需真实存在，数据源为PREPROCESSOR的输出结果
)
REJECT LIMIT UNLIMITED;

3 创建读取文件内容的工具函数 #

用于传入文件相对路径，返回文件的完整内容：

CREATE OR REPLACE FUNCTION get_file_content(p_dir VARCHAR2, p_relative_path VARCHAR2) 
RETURN CLOB 
IS
    v_file UTL_FILE.FILE_TYPE;
    v_buffer VARCHAR2(32767);
    v_content CLOB;
BEGIN
    DBMS_LOB.CREATETEMPORARY(v_content, TRUE);
    v_file := UTL_FILE.FOPEN(p_dir, p_relative_path, 'r', 32767);
    LOOP
        UTL_FILE.GET_LINE(v_file, v_buffer);
        DBMS_LOB.APPEND(v_content, v_buffer || CHR(10));
    END LOOP;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        UTL_FILE.FCLOSE(v_file);
        RETURN v_content;
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(v_file) THEN
            UTL_FILE.FCLOSE(v_file);
        END IF;
        RAISE;
END;
/

4 拆分路径获取层级列，生成目标结果 #

使用正则表达式拆分全路径，提取各级目录名称，关联读取文件内容即可得到你需要的结果：

SELECT
    'Country' AS level1,
    REGEXP_SUBSTR(file_full_path, '[^/]+', 1, 5) AS level2, -- 对应示例的India层级
    REGEXP_SUBSTR(file_full_path, '[^/]+', 1, 6) AS level3, -- 对应示例的Karnataka/Maharashtra层级
    REGEXP_SUBSTR(file_full_path, '[^/]+', 1, 7) AS level4, -- 对应示例的Bengaluru/Mumbai层级
    get_file_content('DIR_TOP_COUNTRY', REPLACE(file_full_path, '/data/Country/', '')) AS file_content
FROM all_nested_data_files;

补充说明 #

• REGEXP_SUBSTR的最后一个数字为路径分段的序号，可根据你实际的顶层目录路径长度调整，按/分隔的分段从左到右计数即可。
• 如果目录层级不固定，可以通过动态SQL生成对应层级的查询列，或使用PIVOT语法将可变层级转为列。
• 如果不需要持久化存储结果，可以直接将上述查询封装为视图，访问视图即可拿到实时的目录+文件数据。

内容的提问来源于stack exchange，提问作者Ravi Kumar