在Java中使用对象参数调用PL SQL函数 [英] Calling a PL SQL Function with object parameter in java

问题描述

在Oracle SQL中，我有一个类型:

In Oracle SQL I have a type:

CREATE OR REPLACE type address_type
AS
OBJECT
(
Street VARCHAR2(100),
Road   VARCHAR2(100),
Town   VARCHAR2(100),
County VARCHAR2(100) );

这用于一个函数，该函数使用ADDRESS_TYPE将答案作为参数并返回整数:

This is used for a function, the function uses the ADDRESS_TYPE to take an answer as a parameter and returns an integer:

create or replace FUNCTION ADD_ADDRESS_FUNC(
    New_Address IN Address_Type )
  RETURN INTEGER
AS
  AddressID INTEGER;
BEGIN
  AddressID := ADDRESS_SEQ.NEXTVAL;
  INSERT
  INTO Address VALUES
    (
      AddressID,
      New_Address.Street,
      New_Address.Road,
      New_Address.Town,
      New_Address.County
    );
  DBMS_OUTPUT.PUT_LINE(AddressID);
  RETURN AddressID;
END;

在我的Java类中，我具有所有连接，等等.我可以访问其他程序函数，但不包含接受Address对象的函数. 在Java中，我创建了一个包含4个字符串的Address对象，肯定可以将其作为下面的第二个参数传递:

In my Java classes I have all connections etc & I can access other procs & functions but not this one which takes the Address object. In Java I create an Address object a with 4 strings, surely I can just pass this as second argument below:

Address a = new Address("Address 1", "Address 2", "Town", "County");
CallableStatement stmt = conn.prepareCall( "begin ? := ADD_ADDRESS_FUNC(?);end;" );
stmt.registerOutParameter(1, Types.CHAR);
stmt.setObject(2, a);
stmt.execute ();
int memberID = stmt.getInt(1);`

该错误消息显示无效的列索引，并且在setObject调用中已损坏.如果它是字符串，那很好，但是Address对象不起作用. 任何帮助表示赞赏，在此先感谢

The error message says invalid column index and it is breaking down at the setObject call. If it were a string it would be fine but the Address object isn't working. Any help appreciated, thanks in advance

推荐答案

在匿名PL/SQL块(而不是Java)中创建类型，然后传递值:

Create the type in the anonymous PL/SQL block (rather than in Java) and pass in the values:

CallableStatement st = con.prepareCall("BEGIN ? := ADD_ADDRESS_FUNC( Address_Type(?,?,?,?)); END;");

st.registerOutParameter( 1, Types.INTEGER );
st.setString( 2, "Address 1" );
st.setString( 3, "Address 2" );
st.setString( 4, "Town" );
st.setString( 5, "County" );

st.execute();
int id = st.getInt(1);

替代方法是您可以使用 JPublisher 生成Address类，以便可以将其传递到数据库或从数据库传递.

The alternative is that you can use JPublisher to generate the Address class so that it can be passed to/from the database.

这篇关于在Java中使用对象参数调用PL SQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！