CODEWITHSUNDEEP
javajdbc
user1145770
user1145770

Reputation: 1

JDBC program to invoke stored procedure with complex inputs and output

Can you please help me writing the JDBC program to call the below stored procedure which is having the complex input type.

Below is my stored procedure definition 

CREATE OR REPLACE TYPE inputObjects_t AS OBJECT (
    serviceID        VARCHAR2(7),
    offerID          VARCHAR2(7),
    transactionID    NUMBER
)

CREATE OR REPLACE TYPE inputArray_t IS VARRAY(100) OF inputObjects_t

PROCEDURE CheckObjectInput( pCustomerRef IN VARCHAR2,
                                 pSubscriptionPS IN NUMBER,
                                 pExpiryObjects IN inputArray_t,
                                 pTransactionDtm IN DATE,
                                 pTransactionID IN NUMBER);

PROCEDURE CheckObjectOutput( pCustomerRef IN VARCHAR2,
                                 pSubscriptionPS IN NUMBER,
                                 pExpiryObjects OUT inputArray_t,
                                 pTransactionDtm IN DATE,
                                 pTransactionID IN NUMBER);

Upvotes: 0

Views: 697

Answers (2)

user1389591
user1389591

Reputation:

If you use Oracle, please use the class OracleCallableStatement.
There's a method called setSTRUCT.

Generical Example:

OracleConnection conn = (...)
OracleCallableStatement cs = (OracleCallableStatement) conn.prepareCall(sql);
StructDescriptor sd = StructDescriptor.createDescriptor("MY_TYPENAME", conn);
Object[] attribs = new Datum[3];
attribs[0] = new CHAR("value1",  charSet);
attribs[1] = new CHAR("value2",  charSet);
CLOB clob2 = attribs[2] = new CLOB(conn);
STRUCT st = new STRUCT(sd, conn, attribs);
(...)
cs.setSTRUCT(n, st);
(...)
cs.execute()

Upvotes: 1

Michael Shopsin
Michael Shopsin

Reputation: 2148

You're going to want a pool to store you database connections and then methods to call the stored procs. You'll probably want something like:

public callStoredProc(String serviceID, String offerID, Integer trasnactionID) {
   try {
   myConnection.startTransaction();
   StringBuilder mySQL = new StringBuilder();
   ... build sql statement ...
   myConnection.execute(mySQL.toString());
   } catch (SQLException sqle) {
   ...
   } finally {
      myConnection.endTransaction();
      ... sql cleanup ...
   }
}

Upvotes: 0

Related Questions