Registering IN OUT parameter

[Dilip kumar]

Hi All -

I searched couple of books and searched on net but couldn't find the syntax I'm looking for. Hope you guys can help. I'm calling a oracle proc in my java app. The proc returns a cursor. Can you please guide to how to define the IN OUT parameter. Thanks

String temp1 = "{call getValues(?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";
CallableStatement csmt = conn.prepareCall(temp1);
 
csmt.setString(1, "Month");
csmt.setString(2, "All");
csmt.setString(3, "All");
csmt.setString(4, "All");
csmt.setString(5, "All");
csmt.setString(6, "All");
csmt.setString(7, "YYYYMMDD");
csmt.setString(8, "YYYYMMDD");
csmt.setString(9, "All");
csmt.setString(10, "All");
csmt.setString(11, "None");
csmt.setString(12, "None");
csmt.setString(13, "Partial");
// How to define INOUT parameter ?

CREATE OR REPLACE PROCEDURE getValues(
var1 VARCHAR2,
var2 VARCHAR2,
var3 VARCHAR2,
var4 VARCHAR2,
var5 VARCHAR2,
var6 VARCHAR2,
var7 VARCHAR2,
var8 VARCHAR2,
var9 VARCHAR2,
var10 VARCHAR2,
var11 VARCHAR2,
var12 VARCHAR2,
var13 VARCHAR2,
tLineCurs IN OUT temp_LineItemCursPack.lineCurs ) 
AS
:
:

CREATE OR REPLACE PACKAGE temp_LineItemCursPack
AS
TYPE lineItemRec IS RECORD (
line_item VARCHAR2
);
TYPE lineCurs IS REF CURSOR RETURN lineItemRec;
END temp_LineItemCursPack;

[ January 25, 2006: Message edited by: Dilip kumar ]

[Evgeniy Bulanov]

In order to get the results from a stored procedure call, you must register any OUT or IN OUT
parameters before executing the CallableStatement. To do this, call the
CallableStatement object's registerOutParameter( ) method, passing it the position of
the placeholder character in the callable-statement string starting at 1 and moving from left to
right, just as you do for the other accessor methods, and a java.sql.Types constant to specify
the SQL data type that will be returned. There are two applicable signatures for the
registerOutParameter( ) method. For VARCHAR2 and DATE parameters, use the
following signature:
registerOutParameter(
int parameterIndex,
int sqlType)
throws SQLException

(Java Programming with Oracle JDBC ISBN: 0-596-00088-x, 496 pages)

[Dilip kumar]

Thanks for the response.

Looks like still something is wrong in my code. I'm getting error message
PLS-00306: wrong number or types of arguments in call to getValues

    
String temp1 = "{call getValues(?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";
CallableStatement csmt = conn.prepareCall(temp1);
 
csmt.setString(1, "All");
csmt.setString(2, "All");
csmt.setString(3, "All");
csmt.setString(4, "All");
csmt.setString(5, "None");
csmt.setString(6, "All");
csmt.setString(7, "Category");
csmt.setString(8, "State");
csmt.setString(9, "All");
csmt.setString(10, "All");
csmt.setString(11, "All");
csmt.setString(12, "All");
csmt.setString(13, "None");
csmt.registerOutParameter(14, java.sql.Types.VARCHAR);
 
rs = csmt.executeQuery();

[Babji Reddy]

I notice that your OUT param 'TYPE lineCurs IS REF CURSOR RETURN lineItemRec' is of type REF CURSOR.
For Oracle, the param for registerOutParameter() is oracle.jdbc.driver.OracleTypes.CURSOR.
Check the driver JAR file for the apt type code.

[Dilip kumar]

Thanks much.

It worked when I used
csmt.registerOutParameter(14, OracleTypes.CURSOR);