如何通过Java JPA Hibernate调用Oracle的insert_order函数

错误原因分析 #

1. 第一种写法错误：使用{call integ_pkg.insert_order(:input)}是调用存储过程的语法，而insert_order是函数，Oracle会判定这不是合法的过程调用，因此抛出PLS-00221错误。
2. 第二种写法错误：SQL语句存在语法错误——select integ_pkg.insert_order(:input)} from dual中多了一个闭合大括号}，导致JDBC解析SQL失败，触发空指针异常。

解决方案 #

方案一：修正原生SQL查询

使用标准的Oracle函数调用语法（select 函数名(参数) from dual），并正确处理CLOB类型的返回值：

String input = "{ \"p_principal\":\r\n" + 
               "                                [\r\n" + 
               "                                    {\r\n" + 
               "                                    \"p_emp\": 200,\r\n" + 
               "                                    \"p_request\": 23,\r\n" + 
               "                                 \"p_date\": \"23-10-2024\",\r\n" + 
               "                                    \"p_info\": \"test\"\r\n" + 
               "                                    }\r\n" + 
               "                                ]\r\n" + 
               "                                ,\"p_detail\":\r\n" + 
               "                                [\r\n" + 
               "                                    {\r\n" + 
               "                                    \"p_emp\": 200,\r\n" + 
               "                                    \"p_date\": \"23-10-2024\",\r\n" + 
               "                                    \"p_info\": \"test\"\r\n" + 
               "                                    }\r\n" + 
               "                                ]\r\n" + 
               "                        }";

// 修正SQL语法，移除多余的闭合大括号
Query query = em.createNativeQuery("select integ_pkg.insert_order(:input) from dual");
query.setParameter("input", input);

// 处理Oracle CLOB返回值
Clob clobResult = (Clob) query.getSingleResult();
String result = clobResult.getSubString(1, (int) clobResult.length());
System.out.println("result: " + result);

说明：

• 使用getSingleResult()替代getResultList().get(0)，更贴合函数返回单行结果的场景
• 直接将CLOB强转为String可能在大文本场景下出错，通过Clob对象的getSubString方法读取更可靠

方案二：使用JPA StoredProcedureQuery（规范写法）

通过StoredProcedureQuery注册函数的输入输出参数，更符合JPA的规范调用方式：

String input = "{ \"p_principal\":\r\n" + 
               "                                [\r\n" + 
               "                                    {\r\n" + 
               "                                    \"p_emp\": 200,\r\n" + 
               "                                    \"p_request\": 23,\r\n" + 
               "                                 \"p_date\": \"23-10-2024\",\r\n" + 
               "                                    \"p_info\": \"test\"\r\n" + 
               "                                    }\r\n" + 
               "                                ]\r\n" + 
               "                                ,\"p_detail\":\r\n" + 
               "                                [\r\n" + 
               "                                    {\r\n" + 
               "                                    \"p_emp\": 200,\r\n" + 
               "                                    \"p_date\": \"23-10-2024\",\r\n" + 
               "                                    \"p_info\": \"test\"\r\n" + 
               "                                    }\r\n" + 
               "                                ]\r\n" + 
               "                        }";

StoredProcedureQuery query = em.createStoredProcedureQuery("integ_pkg.insert_order");
// 注册输入参数：位置1，类型String（对应Oracle CLOB）
query.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
// 注册输出参数：位置2，类型String（对应Oracle CLOB）
query.registerStoredProcedureParameter(2, String.class, ParameterMode.OUT);

query.setParameter(1, input);
query.execute();

String result = (String) query.getOutputParameterValue(2);
System.out.println("result: " + result);

如果处理超大文本，建议直接使用Clob类型避免转换问题：

StoredProcedureQuery query = em.createStoredProcedureQuery("integ_pkg.insert_order");
query.registerStoredProcedureParameter(1, Clob.class, ParameterMode.IN);
query.registerStoredProcedureParameter(2, Clob.class, ParameterMode.OUT);

// 将字符串转为Hibernate管理的Clob对象
Clob inputClob = em.unwrap(org.hibernate.Session.class).getLobHelper().createClob(input);
query.setParameter(1, inputClob);

query.execute();
Clob outputClob = (Clob) query.getOutputParameterValue(2);
String result = outputClob.getSubString(1, (int) outputClob.length());
System.out.println("result: " + result);

注意事项 #

• 确保执行代码的数据库用户拥有integ_pkg.insert_order函数的调用权限
• 确认函数参数类型与Java传入类型匹配：Oracle函数参数为CLOB时，Java传入String或Clob均可，JDBC驱动会自动处理转换
• 若函数返回超大文本，优先使用Clob类型读取，避免内存溢出问题

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