Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes call stored procedure which are having user defined input type Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "call stored procedure which are having user defined input type" Watch "call stored procedure which are having user defined input type" New topic
Author

call stored procedure which are having user defined input type

Shriyans Reddy
Greenhorn

Joined: Dec 21, 2011
Posts: 5
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(
"jdbcracle: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.
Cole Terry
Ranch Hand

Joined: Nov 23, 2011
Posts: 45
Hi, the problem maybe at this line:



If my memory is good enough, you need to put the name of the stored procedure between "{" and "}"
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I had a problem with user defined type in Oracle, which had to be resolved by including the type's schema name in the createDescriptor method call. Try to add the schema name there, it might help.

Cole Terry wrote:Hi, the problem maybe at this line:



If my memory is good enough, you need to put the name of the stored procedure between "{" and "}"

The curly braces are defined as a JDBC driver escape sequence, which is transformed by the JDBC driver into the destination database syntax for calling stored procedures. In Oracle, it means putting the call between BEGIN and END keywords as done here. I've actually run a few anonymous PL/SQL blocks in Oracle via the JDBC Driver without any problems. I therefore don't think the problem lies there. However, I always use ? to represent a bind variable and don't know how the JDBC driver handles the other (":1") syntax. I don't think this would be the problem, but if everything other fails, I'd try to replace the numerical binds with question marks.

Shriyans Reddy
Greenhorn

Joined: Dec 21, 2011
Posts: 5
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?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Shriyans Reddy wrote: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?

Yes, it could definitely be an issue. Try creating the type using CREATE TYPE and if it is not enough, use fully qualified name.
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
Martin Vajsar wrote:
Yes, it could definitely be an issue. Try creating the type using CREATE TYPE and if it is not enough, use fully qualified name.


+1 for above. I don't think there is anyway to refer to user defined types in the packages via JDBC directly. The access has to be via UDT's created by CREATE TYPE.
Shriyans Reddy
Greenhorn

Joined: Dec 21, 2011
Posts: 5
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);
Su Chikki
Greenhorn

Joined: Feb 06, 2012
Posts: 3
hi Shriyans
I'm trying to do a similar thing and facing almost similar issue. My objects are defined in schema level. My stored procedure takes only one object as input and one object as output. The input object has set of Strings and an array of another DB Obj. I'm able to call the stored procedure if I set null for the array in the INput object. I still haven't figured how to set the array in the INput object. I've almost exhausted the forum discussions on this topic but nobody have talked about my situation. Please note, I'm not setting the array directly to the CallableStatement as you do but I want to set it to a SQLData object and send that to the stored procedure. If anybody can just show how it can be done it would be great. Or atleast a small nudge in that direction is all I need.

P.S: Here's my original post on the above question: http://www.coderanch.com/t/566654/JDBC/java/Stored-Procedure-Java

Thanks
S Chikki
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: call stored procedure which are having user defined input type