从Java创建并将SYS_REFCURSOR作为输入参数传递给Oracle过程 [英] Create and pass SYS_REFCURSOR as an input parameter to Oracle procedure from Java

问题描述

我必须与具有SYS_REFCURSOR作为输入参数的外部Oracle过程进行通信：

I have to communicate with an external Oracle procedure that has a SYS_REFCURSOR as an input parameter:

过程merge_objects（varchar2中的p_table_name，
varchar2中的p_id_array，SYS_REFCURSOR中的p_cur_data）

procedure merge_objects(p_table_name in varchar2, p_id_array in varchar2, p_cur_data in SYS_REFCURSOR)

我需要根据我的数据传递SYS_REFCURSOR参数从客户收到。有没有办法在Java中创建这样的参数？

I need to pass SYS_REFCURSOR parameter based on the data that I receive from a client. Is there any way to create such parameter in Java?

推荐答案

直接从Java传递SYS_REFCURSOR的解决方案存在。无需在数据库中插入数据。

The solution to pass SYS_REFCURSOR from Java directly DOES exist. Without the need to insert data in database.

以下语句在Oracle中生成SYS_REFCURSOR（示例包含值和列名称）：

The following statement generates SYS_REFCURSOR in Oracle (example with values and column names):

OPEN cur_data FOR select'000000 inn，'ch'lastname from dual;

OPEN cur_data FOR select '000000' inn, 'Ch' lastname from dual;

现在我将展示如何实现这一点。这是代码的测试工作示例。过程 merge_objects 将SYS_REFCURSOR作为第三个输入参数。 Oracle的示例：

Now I will show how to implement this. Here is a test working example of code. The procedure merge_objects has SYS_REFCURSOR as a third input param. The example for Oracle:

public static void main(String[] args) {
    try {

         Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@...", "username", "password");

         String plsql =
                        "declare cur_data SYS_REFCURSOR;\n" +
                        "BEGIN\n" +
                        "OPEN cur_data FOR select '000000' inn, 'Ch' lastname from dual;\n" +
                        "END;\n" +
                        "merge_objects('tbl_o_persons',\n" +
                        "                '19863572,19863598',\n" +
                        "                cur_data);\n" +
                        "CLOSE cur_data;\n" +
                        "end;";

        try (PreparedStatement stmt = conn.prepareStatement(plsql)) {
            stmt.execute();
        }

        conn.close();

    }catch(Exception ex){
        System.out.println("Error: " + ex.toString());
    }
}

因此根据您的数据，您可以使用OPEN修改字符串声明，包括你的数据，所以直接从Java传递CURSOR到neccesary程序。

So based on your data you can modify string with OPEN statement, include your data and so pass CURSOR to neccesary procedure directly from Java.

这篇关于从Java创建并将SYS_REFCURSOR作为输入参数传递给Oracle过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！