I am trying to call stored procedure which is having user defined input type and I am getting
java.sql.SQLException: invalid name pattern: exception
Below is my stored procedure definition, which is defined on
ipgBalanceTransfer SQL package.
TYPE TransferItemRec_t IS RECORD
(
external_service ipgPaymentTransferDef.External_Service%TYPE,
discount_quantity INTEGER
);
TYPE TransferItems_t IS VARRAY(200) OF TransferItemRec_t;
PROCEDURE BalanceCarryIn(
pCustomerRef IN customer.customer_ref%TYPE,
pSubscriptionPS IN custhasproduct.subs_product_seq%TYPE,
pExternalOfferID IN ipgPaymentTransferDef.External_Offer_Id%TYPE,
pIRBOfferID IN ipgPaymentTransferDef.Irb_Offer_Id%TYPE,
pTransactionDtm IN DATE,
pTransactionID IN ipgtransactionaudit.transaction_id%TYPE,
pTransferItems IN TransferItems_t
);
Below is the code snippet where I am trying to invoke the stored procedure.
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = DriverManager.getConnection(
"jdbc:oracle:thin:@camdl25:1521:VDC1L50", "admin_user",
"admin_user");
oracle.jdbc.OracleCallableStatement callableStatement = null;
callableStatement = (oracle.jdbc.OracleCallableStatement) connection
.prepareCall("BEGIN \"IPGBALANCETRANSFER\".BALANCECARRYIN(:1 ,:2, :3, :4, :5, :6 ); END;");
callableStatement.setString(1, customer_customer_ref);
callableStatement.setInt(2, custhasproduct_subs_product_seq);
callableStatement.setString(3, ipgPaymentTransferDef_External_Offer_Id);
callableStatement.setString(4, ipgPaymentTransferDef_Irb_Offer_Id);
callableStatement.setTimestamp(5, new Timestamp(date));
if(transferItemRec_t!=null && transferItemRec_t.length>0)
{
oracle.sql.ArrayDescriptor descriptor = oracle.sql.ArrayDescriptor.createDescriptor("IPGBALANCETRANSFER.TRANSFERITEMS_T",connection);
oracle.sql.ARRAY transferItemRec_t_Array = new oracle.sql.ARRAY(descriptor, connection, transferItemRec_t);
callableStatement.setArray(6, transferItemRec_t_Array);
}
callableStatement.execute();
callableStatement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
please help me in fixing this issue.