how to get userdefined sql procedure out parameter from java class

I have one sql procedure having 3 IN and 1 OUT parameter. In which the OUT parameter has user defined datatype that means it is one type of table, so I want to get this table type output from java class. I tried to get this by making one java class which is implemented java.sql.Struct and used it in one backing bean by CallableStatement registerOutParameter as java.sql.Struct type , but got errors.

Actually the below is the situation for which I want the solution.

In my case I want to save 500 or more records saving one by one by Java class. But I want the Oracle process instead in which if we pass some parameter for inserting the d开发者_StackOverflow中文版ata into table then the procedure inserts all data one by one and by doing that if any error occure at any position then that record goes into one userdefined tabletype object using pipe row(). So I want to get the userdefined tabletype object using java class.

Do you have any idea for this type of problem?

You can define several oracle object types. In case of oracle objects, they are mapped to java.sql.Struct. In case of collections (like table type you are referencing, as far as I understand) they are mapped to java.sql.Array. Just register your out parameter as java.sql.Array. In case of older oracle JDBC drivers (10g) remember to use full reference to the type (including schema), otherwise you might get errors like "invalid type" or something:

Example:

stmt.registerOutParameter(4, Types.ARRAY, "SCHEMA.TABLE_TYPE");
stmt.execute();
Array array = stmt.getArray(4);

If you have a lot of code like this, you can benefit from eclipselink's approach:

@Entity
@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(
            name = "Company.getCompanies",
            procedureName = "SQL_PACKAGE.GET_COMPANIES",
            parameters = { 
                    @StoredProcedureParameter(queryParameter = "p_filter", direction = Direction.IN), 
                    @StoredProcedureParameter(queryParameter = "p_result", direction = Direction.OUT_CURSOR),
                    },
            resultClass = Company.class)
})
public class Company {

    @Id
    @Column(name = "COMPANY_NO")
    private Long companyNo;

    @Column(name = "COMPANY_NAME")
    private String companyName;

    public Long getCompanyNo() {
        return companyNo;
    }

    public String getCompanyName() {
        return companyName;
    }

}

And then use such an entity in DAO:

@Transactional(readOnly = true)
public List<Company> getCompanies(String filter) {
    EntityManager em = entityManagerProvider.get();
    Query query = em.createNamedQuery("Company.getCompanies");
    query.setParameter("p_filter", filter);
    return query.getResultList();
}

In such a scenario you don't have to define special oracle collection type for output, just return ref cursor in PL/SQL.

I am the developer of jOOQ, and I created jOOQ exactly for these kinds of purposes. Apart from jOOQ's full SQL support, you can generate Java classes for both your stored procedures and your user-defined-types. Your stored procedure call will then look something like this in Java.

MyUserType result = Procedures.myProcedure(1, 2, 3);

In Oracle, this currently applies to OBJECT and VARRAY types. See the jOOQ manual for more information