通过 UTL_FILE 系统包来读写文件

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（首先创建一个测试用户）。

   [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 中创建一个目录）。

   $ 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)
   
   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 [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。

   [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)
   
   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]> 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)
   

   [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)
   
   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 [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（手动检查所有观察节点上的相应目录，如果该目录尚不存在，则创建该目录）。

   [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 系统软件包提供的子程序来打开/读取/写入/关闭文件）。

   [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)
   
   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 [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（检查并确认文件内容是否正确）。

   [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 错误）。

   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 错误）。

   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 模式租户。