--- title: 通过 UTL_FILE 系统包来读写文件-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于通过 UTL_FILE 系统包来读写文件相关的常见问题和使用技巧，帮助您快速解决通过 UTL_FILE 系统包来读写文件的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # 通过 UTL_FILE 系统包来读写文件 更新时间：2026-05-29 08:46 适用版本： V4.2.x、V4.3.x、V4.4.x 内容类型：TechNote `UTL_FILE` 软件包是 Oracle 数据库提供的用来管理操作系统上的 text file 的一套程序接口，类似于 C 语言中对于 stream file 的功能。它提供了类似于操作系统对文件的基本操作功能（OPEN，GET，PUT，CLOSE）。OceanBase 数据库从 V2.2.77 版本开始也兼容支持了 Oracle 数据库中的 `UTL_FILE` 软件包。 本文主要介绍如何使用 `UTL_FILE` 软件包中提供的接口来进行本地文件的读写。 ## 详细说明 **测试如下：** 1. create a test user first（首先创建一个测试用户）。 ```sql [admin@observer-node /home/admin]$ obclient -h127.0.0.1 -P2881 -usys@oracle -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221711815 Server version: OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient [SYS]> select @@version_comment from dual; +---------------------------------------------------------------------------------------------------------------+ | @@VERSION_COMMENT | +---------------------------------------------------------------------------------------------------------------+ | OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13) | +---------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec) obclient [SYS]> create user test_user identified by xxx; Query OK, 0 rows affected (0.090 sec) obclient [SYS]> grant connect,resource,dba to test_user; Query OK, 0 rows affected (0.101 sec) ``` 2. create a directory in theDBA_DIRECTORIES（在 DBA_DIRECTORIES 中创建一个目录）。 ```sql $ obclient -h127.0.0.1 -P2881 -utest_user@oracle -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221735590 Server version: OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13) obclient [TEST_USER]> create or replace directory my_test_dir as '/home/admin/test_dir'; ORA-00600: internal error code, arguments: -5036, Access denied ``` 为确保文件操作的安全性，创建目录时必须将系统变量 secure_file_priv 设置为指定的安全目录。更多信息，请参阅：[secure_file_priv](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001972189)。 ```sql [admin@observer-node /home/admin]$ obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221548025 Server version: OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13) obclient [SYS]> show variables like 'secure_file_priv'; +------------------+-------+ | VARIABLE_NAME | VALUE | +------------------+-------+ | secure_file_priv | NULL | +------------------+-------+ 1 row in set (0.005 sec) obclient [SYS]> set global secure_file_priv='/'; Query OK, 0 rows affected (0.051 sec) ``` ```sql [admin@observer-node /home/admin]$ obclient -h127.0.0.1 -P2881 -utest_user@oracle -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221488877 Server version: OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13) obclient [TEST_USER]> create or replace directory my_test_dir as '/home/admin/test_dir'; Query OK, 1 row affected (0.078 sec) obclient [TEST_USER]> select * from dba_directories; +-------+----------------+----------------------+---------------+ | OWNER | DIRECTORY_NAME | DIRECTORY_PATH | ORIGIN_CON_ID | +-------+----------------+----------------------+---------------+ | SYS | MY_TEST_DIR | /home/admin/test_dir | 1048 | +-------+----------------+----------------------+---------------+ 1 row in set (0.004 sec) ``` 3. manually check and create corresponding directory on ALL observer nodes if it's not already existing（手动检查所有观察节点上的相应目录，如果该目录尚不存在，则创建该目录）。 ```sql [admin@observer-node /home/admin]$ id uid=500(admin) gid=500(admin) groups=500(admin),10(wheel) [admin@observer-node /home/admin]$ mkdir /home/admin/test_dir [admin@observer-node /home/admin]$ ls -ld /home/admin/test_dir drwxr-xr-x 2 admin admin 4096 Oct 25 16:21 /home/admin/test_dir [admin@observer-node /home/admin]$ ls -ltr /home/admin/test_dir/ total 0 ``` #### 注意 由于 OceanBase 数据库是一个分布式数据库系统，您必须确保所有 OBServer 节点都能访问相同的绝对路径。通常，您可以在用于创建目录对象的语句中，将共享文件系统（例如 NFS）上的路径指定为文件系统路径。 4. use the subprograms provided by theULT_FILEsystem package to open/read/write/close a file（使用 ULT_FILE 系统软件包提供的子程序来打开/读取/写入/关闭文件）。 ```sql [admin@observer-node /home/admin]$ obclient -h127.0.0.1 -P2881 -utest_user@oracle -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221695914 Server version: OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13) obclient [TEST_USER]> set serveroutput on; obclient [TEST_USER]> DECLARE l_file UTL_FILE.file_type; l_text VARCHAR2(32767); BEGIN -- Open the file l_file := UTL_FILE.FOPEN( location => 'MY_TEST_DIR', filename => 'new_test_file', open_mode => 'w', max_linesize => 32767 ); -- Write a line to the file UTL_FILE.PUT_LINE(l_file, 'Hello' || ' ' || 'World' || ' ' || '!'); -- Close the file UTL_FILE.FCLOSE(l_file); -- Open the file l_file := UTL_FILE.fopen('MY_TEST_DIR', 'new_test_file', 'r', 32767); -- Read and output first line UTL_FILE.get_line(l_file, l_text, 32767); DBMS_OUTPUT.put_line(l_text); -- Close the file UTL_FILE.FCLOSE(l_file); EXCEPTION WHEN OTHERS THEN UTL_FILE.FCLOSE(l_file); RAISE; END; / Query OK, 1 row affected (0.213 sec) Hello World ! obclient [TEST_USER]> Bye ``` 5. check and verify the contents of the file is correct or not（检查并确认文件内容是否正确）。 ```sql [admin@observer-node /home/admin]$ ls -ltr /home/admin/test_dir/ total 4 -rw-r--r-- 1 admin admin 14 Oct 25 16:52 new_test_file [admin@observer-node /home/admin]$ cat /home/admin/test_dir/new_test_file Hello World ! ``` ## 备注 1. If the directory does NOT exist on some observer node, you may hitORA-29283: invalid file operationerror（如果某个 OBServer 节点上不存在该目录，则可能会遇到 ORA-29283: invalid file operationerror 错误）。 ```sql obclient [TEST_USER]> select * from dba_directories; +-------+----------------+----------------------+---------------+ | OWNER | DIRECTORY_NAME | DIRECTORY_PATH | ORIGIN_CON_ID | +-------+----------------+----------------------+---------------+ | SYS | MY_TEST_DIR | /home/admin/test_dir | 1048 | +-------+----------------+----------------------+---------------+ 1 row in set (0.004 sec) [admin@observer-node /home/admin]$ rm -rf /home/admin/test_dir/ obclient [TEST_USER]> DECLARE l_file UTL_FILE.file_type; BEGIN -- Open the file l_file := UTL_FILE.FOPEN( location => 'MY_TEST_DIR', filename => 'new_test_file', open_mode => 'w', max_linesize => 32767 ); END; / ORA-29283: invalid file operation at package body oceanbase.UTL_FILE.FOPEN , line : 149, col : 1 ORA-29283: invalid file operation at package body oceanbase.UTL_FILE.FOPEN_INTERFACE , line : 20, col : 1 at package body oceanbase.UTL_FILE.FOPEN , line : 113, col : 11 at anonymous block , line : 5, col : 11 ``` 2. If user (i.e. the owner of observer process) does not have sufficient privileges to acess the diretory, you may also hitORA-29283: invalid file operationerror（如果用户（即 OBServer 进程的所有者）没有足够的权限访问该目录，则可能同样会遇到 ORA-29283:invalid file operationerror 错误）。 ```sql obclient [TEST_USER]> select * from dba_directories; +-------+----------------+----------------------+---------------+ | OWNER | DIRECTORY_NAME | DIRECTORY_PATH | ORIGIN_CON_ID | +-------+----------------+----------------------+---------------+ | SYS | MY_TEST_DIR | /home/admin/test_dir | 1048 | | SYS | ROOT_DIR | /root | 1048 | +-------+----------------+----------------------+---------------+ 2 rows in set (0.000 sec) obclient [TEST_USER]> DECLARE l_file UTL_FILE.file_type; BEGIN -- Open the file l_file := UTL_FILE.FOPEN( location => 'ROOT_DIR', filename => 'new_test_file', open_mode => 'w', max_linesize => 32767 ); END; / ORA-29283: invalid file operation at package body oceanbase.UTL_FILE.FOPEN , line : 149, col : 1 ORA-29283: invalid file operation at package body oceanbase.UTL_FILE.FOPEN_INTERFACE , line : 20, col : 1 at package body oceanbase.UTL_FILE.FOPEN , line : 113, col : 11 at anonymous block , line : 5, col : 11 ``` ## 适用版本 OceanBase 数据库 V2.2.77 及更高版本的 Oracle 模式租户。 Previous [ARM 平台下 OceanBase 数据库集群执行 PL 期间因 allocate_mapped_memory 内存分配问题 hang 在编译阶段](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002401683) Next [关于 DBMS_OUTPUT 包 BUFFER_SIZE 缓冲区的说明](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000004570263) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线