• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

call stored procedure which are having user defined input type

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 "}"
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 5
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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);
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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: https://coderanch.com/t/566654/JDBC/java/Stored-Procedure-Java

Thanks
S Chikki
 
Lookout! Runaway whale! Hide behind this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic