How to call a stored procedure asleep?

Question

Hi i have oracle stored procedure

GET_VENDOR_STATUS_COUNT(DOCUMENT_ID IN NUMBER , NOT_INVITED OUT NUMBER,INVITE_WITHDRAWN OUT NUMBER,... 

other parameters are OUT parameters.

In the hbm file I wrote -

 <sql-query name="getVendorStatus" callable="true"> <return-scalar column="NOT_INVITED" type="string"/> <return-scalar column="INVITE_WITHDRAWN" type="string"/> <return-scalar column="INVITED" type="string"/> <return-scalar column="DISQUALIFIED" type="string"/> <return-scalar column="RESPONSE_AWAITED" type="string"/> <return-scalar column="RESPONSE_IN_PROGRESS" type="string"/> <return-scalar column="RESPONSE_RECEIVED" type="string"/> { call GET_VENDOR_STATUS_COUNT(:DOCUMENT_ID , :NOT_INVITED ,:INVITE_WITHDRAWN ,:INVITED ,:DISQUALIFIED ,:RESPONSE_AWAITED ,:RESPONSE_IN_PROGRESS ,:RESPONSE_RECEIVED ) } </sql-query> 

In java I wrote -

  session.getNamedQuery("getVendorStatus").setParameter("DOCUMENT_ID", "DOCUMENT_ID").setParameter("NOT_INVITED", "NOT_INVITED") 

... continue to all parameters.

I get sql exception

  18: 29: 33,056 WARN [JDBCExceptionReporter] SQL Error: 1006, SQLState: 72000
     18: 29: 33,056 ERROR [JDBCExceptionReporter] ORA-01006: bind variable does not exist

Please let me know what the exact process of calling a stored procedure from sleep mode is. I do not want to use the JDBC called statement.

Answer 1

What you are trying (handling INOUT / OUT parameters) is not supported in 4.1. Through 4.1 support, Hibernate called support focuses on returning a ResultSet. There is support for the fact that you are trying already at the place of the leading master and will be part of the next major version of Hibernate (which will be either 4.2 or 5.0); there, the calling functions / procedures are now a first class operation.

Now you need to either directly use JDBC or create Hibernate from master and use this new support. If you choose a later version, it will look like this:

 StoredProcedureCall call = session.createStoredProcedureCall( "GET_VENDOR_STATUS_COUNT" ) .registerStoredProcedureParameter( "DOCUMENT_ID", Long.class, ParameterMode.IN ) .registerStoredProcedureParameter( "NOT_INVITED", String.class, ParameterMode.OUT ) ...; call.getRegisteredParameter( "DOCUMENT_ID" ).bindValue( theDocumentId ); StoredProcedureOutputs outputs = call.getOutputs(); String notInvited = (String) outputs.getOutputParameterValue( "NOT_INVITED" ); ... 

This code is still young and is likely to change. For example, when I write these examples more often, I think that registerStoredProcedureParameter should be renamed as registerParameter or declareParameter and that it should return a typed representation of the declaration / registration; something like:

 interface RegisteredParameter<T> { Class<T> getParameterType(); // only valid for IN or INOUT params void bindValue(T value); } <T> RegisteredParameter<T> registerParameter(String name, Class<T> type, ParameterMode mode); 

which will then allow:

 StoredProcedureCall call = session.createStoredProcedureCall( "GET_VENDOR_STATUS_COUNT" ) call.registerParameter( "DOCUMENT_ID", Long.class, ParameterMode.IN ).bindValue( theDocumentId ); RegisteredParameter<String> notInvitedParam = call.registerParameter( "NOT_INVITED", String.class, ParameterMode.OUT ); ... String notInvited = outputs.getOutputParameterValue( notInvitedParam ); 

As an added bonus, people trying to do this early can help shape what it looks like before it is released (at this point it is much harder to change).

Answer 2

With Hibernate, you must use the result class to convert the SP results, and the returned column names should be smoothed into the correct return fields in your result class. Hibernate wants the stored SQLServer procs to have a single return result, and he wants to know what type of object to create. When I work, we usually return the result of one row of two columns: return_code and message.

For example...

• return_code = 404, message = "Page not found"

• return_code = 200, message = "OK"

The class is displayed like any other POJO, just make sure it is serialized. For example:

 @Entity public class StoredProc implements Serializable { private Integer returnCode; private String message; @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "return_code", nullable = false, unique = true) public Integer getReturnCode() { return returnCode; } public void setReturnCode(Integer returnCode) { this.returnCode = returnCode; } @Column(name = "message") public String getMessage() { return message; } public void setMessage(String message) { this.message = message; } } 

It also seems to me that we recall the general general hawking with respect to the naming convention that Hibernate uses. I think names_with_underscores translate to camelCaseFieldNames, for example.