无法使用表类型为IN参数的存储过程插入Varchar2数据 [英] Unable to insert Varchar2 data using stored procedure with Table type as IN parameter

问题描述

我们已经使用以下内容在Oracle数据库中创建用户定义的类型.第一个是记录类型(类似于行)，第二个是使用记录类型构造的表类型.

We have used the following to create user defined types in Oracle database. The first one is a record type (similar to a row) and second is Table type constructed using the record type.

create or replace type REC_TYPE as object(bid number, bdesc varchar2(100));
/ 
create or replace type TAB_TYPE as table of REC_TYPE;
/

下面介绍了表以及用于向表中插入数据的过程.

The following describes the table and procedure used to insert data to the table.

create table BOM (bom_id number, bom_desc varchar2(100));
/
create or replace procedure pBOM (tIn IN TAB_TYPE, res OUT VARCHAR2)
is
begin
  for i in 1..tIn.count loop
      insert into BOM values(tIn(i).bid, tIn(i).bdesc);
  end loop;
end; 
/

这是用于使用JDBC调用过程的Java代码.

Here is the java code used to call the procedure using JDBC.

public  void method()   throws ClassNotFoundException, SQLException {

        Class.forName("oracle.jdbc.driver.OracleDriver");

        try (Connection connection = DriverManager.getConnection("<jdbc_url>", "<user>", "<password>")) {   


            connection.setAutoCommit(false);

            Object[] attributes = new Object[]{1, "test bom"};
            Struct rec= connection.createStruct("REC_TYPE", attributes);
            Struct[] tab =  new Struct[]{ rec };
            Array tabArr = ((OracleConnection)connection).createOracleArray("TAB_TYPE", tab);

            CallableStatement cs = connection.prepareCall("{call pBOM(?,?)}");
            cs.setObject(1, tabArr);
            cs.registerOutParameter(2, Types.VARCHAR);
            cs.executeUpdate();

            connection.commit();
            tabArr.free();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

使用上述方法，最后，当我们在java中运行main方法时，它会插入ID，但令人惊讶的是没有插入描述.

With the above, finally when we run the main method in java, it inserts the ID, but surprisingly not the description.

注意:上面的示例代码段用于说明问题.语法上可能存在一些小问题，但是它确实复制了我们在项目中面临的问题.

NOTE: The above sample code snippets are taken to explain the problem. There might be some minor issues in syntax, however it exactly replicates the issue we are facing in project.

推荐答案

由于我再也看不到答案了，因此我想将问题的原因发布出来.之所以发生这种情况，是因为缺少classpath中的orai18n.jar库，如果没有该库，则文本数据不会更新到数据库中.

Since I couldn't see any more answers, I thought to post the reason behind the issue. It was happening due to missing library in classpath which was orai18n.jar without which the text data was not getting updated to database.

因此，我们需要同时添加驱动程序和语言支持Jar，以避免此类问题.

So we need to add both the driver as well as language support Jars to avoid such issues.

这篇关于无法使用表类型为IN参数的存储过程插入Varchar2数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！