how to get userdefined sql procedure out parameter from java class

Question

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 data 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?

Show your code and the errors or no one will be able to help — Sean Patrick Floyd
– Sean Patrick Floyd, Commented Apr 7, 2011 at 12:23
Quick googling gave me these links - I believe both Oracle JDBC approach (forum.springsource.org/archive/index.php/t-11805.html) and Spring converter (static.springsource.org/spring-data/data-jdbc/docs/1.0.0.M1/api/…) will work. — Victor Sergienko
– Victor Sergienko, Commented Apr 26, 2011 at 14:13

morisil · Accepted Answer · 2011-05-02 07:51:13Z

2

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.

edited May 2, 2011 at 7:51

answered May 1, 2011 at 18:02

morisil

1,3558 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PainIsAMaster Over a year ago

I have two OUT parameters that are custom Oracle objects (employee and financial objects that have a bunch of fields 40+). I set their type to "OracleTypes.STRUCT" in my Java code and then I call "struct.getObject()" to get the Object after the Stored procedure executes and then I can get an Object[] of all their values by using "structObject.getAttributes()". But I have to set like 40 fields of this manually to the Java POJO (since you can't cast an Object[] array directly to a Java POJO...). Is there not an easier way to Map the Oracle Struct (java.sql.struct ) to a Java POJO????

Lukas Eder · Accepted Answer · 2013-11-20 10:23:53Z

1

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

edited Nov 20, 2013 at 10:23

answered May 2, 2011 at 12:23

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Collectives™ on Stack Overflow

how to get userdefined sql procedure out parameter from java class

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related