如何通过Java获取Oracle包中自定义函数的参数定义

实现原理 #

Oracle控制台的desc命令本质是查询数据库内置的系统元数据视图拼接结果，包内自定义函数的参数信息全部存储在ALL_ARGUMENTS（所有当前用户有权限查看的对象参数）、USER_ARGUMENTS（当前用户创建的对象参数）系统视图中，我们可以通过JDBC直接查询该视图获取和desc命令一致的参数信息。

除直接查询系统视图外，也可以使用JDBC自带的DatabaseMetaData接口获取参数，但直接查询系统视图对Oracle包对象的适配性更高、逻辑更灵活。

实现步骤 #

• 编写参数查询SQL
  需要注意Oracle系统视图中默认存储的对象名、用户名均为大写，过滤条件需要统一大写避免查询为空，示例SQL如下：

SELECT 
  ARGUMENT_NAME, -- 参数名
  DATA_TYPE, -- 参数数据类型
  IN_OUT, -- 参数模式（IN/OUT/INOUT）
  DATA_LENGTH, -- 数据长度
  POSITION -- 参数顺序，0为函数返回值，从1开始为输入输出参数
FROM ALL_ARGUMENTS 
WHERE 
  OWNER = '你的数据库用户名（大写）' 
  AND PACKAGE_NAME = 'MYPKG' 
  AND OBJECT_NAME = 'CUSTOMFUNCTION' 
ORDER BY POSITION ASC

如果使用USER_ARGUMENTS视图查询当前用户下的对象，可以去掉OWNER过滤条件。

• 编写JDBC执行代码
  示例代码如下：

import java.sql.*;

public class OracleFuncParamFetcher {
    public static void main(String[] args) {
        // 替换为自身数据库连接配置
        String jdbcUrl = "jdbc:oracle:thin:@//127.0.0.1:1521/orcl";
        String username = "your_username";
        String password = "your_password";

        // 构造查询SQL
        String querySql = String.format(
            "SELECT ARGUMENT_NAME, DATA_TYPE, IN_OUT, POSITION FROM USER_ARGUMENTS " +
            "WHERE PACKAGE_NAME = 'MYPKG' AND OBJECT_NAME = 'CUSTOMFUNCTION' ORDER BY POSITION ASC",
            username.toUpperCase()
        );

        // 自动释放资源的try块
        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(querySql)) {

            System.out.println("参数位置\t参数模式\t参数类型\t参数名");
            while (rs.next()) {
                int position = rs.getInt("POSITION");
                String inOut = rs.getString("IN_OUT");
                String dataType = rs.getString("DATA_TYPE");
                String argName = rs.getString("ARGUMENT_NAME");

                if (position == 0) {
                    System.out.printf("0\t\t返回值\t\t%s\t\t%s%n", dataType, argName == null ? "无" : argName);
                    continue;
                }
                System.out.printf("%d\t\t%s\t\t%s\t\t%s%n", position, inOut, dataType, argName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

• 结果解析
  查询结果中POSITION为0的记录代表函数的返回值，大于等于1的记录按顺序对应函数的参数列表，IN_OUT字段标识参数的输入输出属性。

注意事项 #

• 如果创建包、函数时使用了双引号指定了小写名称，过滤条件中的PACKAGE_NAME、OBJECT_NAME需要和创建时的大小写完全一致。
• 若查询无结果，优先检查当前连接用户是否有访问ALL_ARGUMENTS视图的权限，或者切换为USER_ARGUMENTS视图重试。
• 本方案也可适配包内存储过程的参数查询，只需修改OBJECT_NAME为对应存储过程名称即可。

内容的提问来源于stack exchange，提问作者Wei Guo