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);
Re: JDBC program to invoke stored procedure with complex inputs and output
This is the relevant bit of documentation from Oracle.
Actually, the whole page is probably quite handy.
Essentially you want a STRUCT to pass in.
You get a descriptor from the database, using the name of your TYPE, and then build the STRUCT from that and the array of attributes.
Then you'll want to read about arrays.
Re: JDBC program to invoke stored procedure with complex inputs and output
ThanQ for the reply I will try with the documentation provided.
Re: JDBC program to invoke stored procedure with complex inputs and output
if you have a sample code that would be really helpful for me thanQ in advance.
Re: JDBC program to invoke stored procedure with complex inputs and output
That documentation gives examples.
There's not anything else I could really add.
Also, see my comment on OTN re: crossposting.