Shriyans Reddy

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);

I was able to resolve this issue, these are the changes I did.

1) Created schema level types.
2) made changes to the JDBC code for setting the user defined types.

Object[] item1 = new Object[] { new String("S1"), new Integer(1) };
Object[] item2 = new Object[] { new String("S2"), new Integer(2) };
Object[] item3 = new Object[] { new String("S3"), new Integer(3) };
Object[] item4 = new Object[] { new String("S4"), new Integer(4) };

oracle.sql.ArrayDescriptor descriptor = oracle.sql.ArrayDescriptor
.createDescriptor("TRANSFERITEMSTABLE", connection);

StructDescriptor itemIdentifierDesc = StructDescriptor
.createDescriptor("TRANSFERITEMOBJECT", connection);
oracle.sql.ARRAY transferItemRec_t_Array = new oracle.sql.ARRAY(
descriptor, connection, new Object[] {
new STRUCT(itemIdentifierDesc, connection, item2),
new STRUCT(itemIdentifierDesc, connection, item1),
new STRUCT(itemIdentifierDesc, connection, item3),
new STRUCT(itemIdentifierDesc, connection, item4) });

callableStatement.setArray(7, transferItemRec_t_Array);

Firstly thank you Martin & Cole Terry for looking into the issue.
I have used OracleCallableStatement in which I have prepared the call using :1 ,:2 etc.

If we use java.sql.CallableStatement we can directly use ? as suggested by you like this.

callableStatement = java.sql.CallableStatement .prepareCall("call IPGBALANCETRANSFER.BALANCECARRYIN(?,?,?,?,?,?,?)");

I have tried in both the ways getting the same exception.
In the mean while I found that the user defined type (TransferItems_t ) which my stored proc is accessing is defined at package level, not at the schema level.

I googled and found that user defined types which are defined at the package level could not be acessed. Is that could be an issue?

Hi,
I was able to generate the webservices for the stored proc's which are having primitive java types using Jdeveloper's create plsql wizard, as described in this site
http://oraclesoa.wordpress.com/2009/07/10/jdeveloper-11g-creating-plsql-webservice-and-deploying-to-weblogic-server/

But I want to expose PL/SQL stored procedures which are having complex input & output structures as Webservice via JDeveloper, complex meaning non-primitive java types.

When I try to generate the web services for the below stored procedures, I am not able to select the stored procedures from the wizard as they were greyed out and when I click on the why not button it showed me the below error message.

The following types used by the program unit do not have an XML Schema mapping and/or serializer specified:
IPGBALANCETRANSFER.TRANSFERITEMS_T


Below is my stored proc definition.

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
);

PROCEDURE BalanceCarryOut(
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 OUT TransferItems_t
);


please help me.
Thanks in advance.

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.