• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1003
  • Last Modified:

calling stored procedure from java

hi there I am trying to make a call to stored procedure in java and I am getting error as

SQLException : ORA-06553: PLS-306: wrong number or types of arguments in call to 'CHECK_INV'


Where I am wrong.

Thanks for your help in advance.
PROCEDURE CHECK_INV ( p_inventory_item_id    IN  NUMBER
   			 			 , p_reqd_qty			  IN  NUMBER
   			 			 , p_country	  		  IN  VARCHAR2
			  	        	 , p_state       		          IN  VARCHAR2
			  			 , p_postal_code	  	  IN  VARCHAR2
						 , x_org_id		         OUT NUMBER
						 , x_availability		         OUT NUMBER
						 , x_def_lead_time		 OUT NUMBER
						 , x_min_lead_time		 OUT NUMBER)
 
 
----------------------------------------------------------------------------------------
 
oracleconnection = (OracleConnection)TransactionScope.getConnection();
                oraclecallablestatement = (OracleCallableStatement)oracleconnection.prepareCall("call APPS.MYPACKAGE.CHECK_INV(:1,:2,:3,:4,:5)");
                
                oraclecallablestatement.registerOutParameter(1, Types.INTEGER);
                oraclecallablestatement.registerOutParameter(2, Types.INTEGER);
                oraclecallablestatement.registerOutParameter(3, Types.INTEGER);
                oraclecallablestatement.registerOutParameter(4, Types.INTEGER);
                oraclecallablestatement.setInt(1, inventoryitemid);
                oraclecallablestatement.setInt(2, itemQtyinCart);
                oraclecallablestatement.setString(3, ShiptoCountry);
                oraclecallablestatement.setString(4, ShiptoState);
                oraclecallablestatement.setString(5, ShiptoPostalCode);
                oraclecallablestatement.execute();
                int warehouseorgid = oraclecallablestatement.getInt(1);
                int instock = oraclecallablestatement.getInt(2);

Open in new window

0
techie_ns
Asked:
techie_ns
  • 5
  • 3
1 Solution
 
VenabiliCommented:
I would try with the code below

Basically you need to pass all your parameters, even the out ones because you need a way to register them
And in Java Prepared Calls, a parameter is replaced with ?
So what my code below will do is to first add 5 IN parameters and set their values and then just to register 4 OUT ones.

With these
oraclecallablestatement.registerOutParameter(1, Types.INTEGER);
oraclecallablestatement.setInt(1, inventoryitemid);
you are basically telling that the first ? is a an IN/OUT parameter... which is not what you are trying to do. The counter of the code does not restart for IN and OUT parameters - they all a re parameters

http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/callablestatement.html is a good read on how to use CallableStatement by the way

And you might want to see the Javadoc of OracleCallableStatement. It's online here for example: http://www710.univ-lyon1.fr/~ecoquery//files/oracle-jdbc/javadoc/oracle/jdbc/OracleCallableStatement.html (the usual place is down so I just found a valid link fo you
 oraclecallablestatement = (OracleCallableStatement)oracleconnection.prepareCall("call APPS.MYPACKAGE.CHECK_INV(?,?,?,?,?,?,?,?,?)");
                
                oraclecallablestatement.setInt(1, inventoryitemid);
                oraclecallablestatement.setInt(2, itemQtyinCart);
                oraclecallablestatement.setString(3, ShiptoCountry);
                oraclecallablestatement.setString(4, ShiptoState);
                oraclecallablestatement.setString(5, ShiptoPostalCode);
                oraclecallablestatement.registerOutParameter(6, Types.INTEGER);
                oraclecallablestatement.registerOutParameter(7, Types.INTEGER);
                oraclecallablestatement.registerOutParameter(8, Types.INTEGER);
                oraclecallablestatement.registerOutParameter(9, Types.INTEGER);

Open in new window

0
 
techie_nsAuthor Commented:
I tried your code and now I am getting different error as :

SQLException : Invalid column index


Thanks for your response.
0
 
VenabiliCommented:
On which line?
int instock = oraclecallablestatement.getInt(2); by any chance?
Because you will need to fix this to be the real number -- 7 (5 IN parameters, then the first out is 6 and so on)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
VenabiliCommented:
PS: That's why I gave you the links - the java invocations are kinda different from what you would use in a real DB call.
0
 
VenabiliCommented:
and the same apply for                 int warehouseorgid = oraclecallablestatement.getInt(1); of course
Let me know if this is the case - if not - post your FULL stacktrace (and make sure it is in this code and not somewhere after this)

The good news is that this error is from reading the result so your procedure is called successfully
0
 
techie_nsAuthor Commented:
I have to add the system.out after each line to see which one is throwing the error. I will also read the links you send. WIll update the questions soon.

Thanks for your response.

0
 
techie_nsAuthor Commented:
I found the solution. Thanks for your help. here is my code.



                oraclecallablestatement.setInt(1, inventoryitemid);
                oraclecallablestatement.setInt(2, itemQtyinCart);
                oraclecallablestatement.setString(3, ShiptoCountry);
                oraclecallablestatement.setString(4, ShiptoState);
                oraclecallablestatement.setString(5, ShiptoPostalCode);
                oraclecallablestatement.registerOutParameter(6, java.sql.Types.DECIMAL);
                oraclecallablestatement.registerOutParameter(7, java.sql.Types.DECIMAL);
                oraclecallablestatement.registerOutParameter(8, java.sql.Types.DECIMAL);
                oraclecallablestatement.registerOutParameter(9, java.sql.Types.DECIMAL);
                
                oraclecallablestatement.execute();
                wrhse = oraclecallablestatement.getBigDecimal(6);
                instock123 = oraclecallablestatement.getBigDecimal(7);

Open in new window

0
 
VenabiliCommented:
Which is what I said above - to change the 1 and 2 to 6 and 7 when you get them from the result set.

Great that you got it working :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now