Problem with parameters passed to an Oracle stored procedure from java class

[swetha]

Hello all

I have the following stored procedure in my Oracle XE Express Edition
database :

CREATE OR REPLACE PROCEDURE InsertValue
(
rowID OUT TABLEA.ID%TYPE,
clientID IN TABLEA.CLIENTID%TYPE,
batchNum IN TABLEA.BATHCNUMBER%TYPE,
ticketNum IN TABLEA.TICKETNUMBER%TYPE
)
AS
BEGIN
SELECT ID INTO rowID FROM TABLEA WHERE CLIENTID=clientID AND
BATCHNUMBER=batchNum AND TICKETNUMBER=ticketNum;
END;

The above stored procedure I call from my Java class as follows:

CallableStatement cs = conn.prepareCall("{call
InsertValue(?, ?, ?, ?)}"); //conn is db connection
cs.registerOutParameter(1, Types.INTEGER);
cs.setInt(2, clientID); // clientID contains valid value 1
cs.setInt(3, batchNumber); // batchNumber contains valid value 1
cs.setInt(4, ticket); // ticketNumber contains valid value 235234
boolean result = cs.execute();
int ID = cs.getInt(1);

The ID retrieved above is 0 although when I run the following SQL
command:

SELECT ID FROM TABLEA WHERE CLIENTID=1 AND BATCHNUMBER=1 AND
TICKETNUMBER=235234

I get a valid result. The IN parameters passed to the method seem to
be fine because I modified the stored procedure a few times and
assigned the values of the IN parameters directy to the OUT parameters
and what I retrieved was what I had passed. Also when I substitute the
values of the IN parameters directly in the query, i.e. modify the
stored procedure as

CREATE OR REPLACE PROCEDURE InsertValue
(
rowID OUT TABLEA.ID%TYPE,
)
AS
BEGIN
SELECT ID INTO rowID FROM TABLEA WHERE CLIENTID=1 AND
BATCHNUMBER=1 AND TICKETNUMBER=235234;
END;

I get a valid ID. All my other stored procedures and calls to them in
a similar fashion are working fine. I am not able to figure out where
I am going wrong. Any suggestions will be very helpful.

Thanks
Swetha

 

[Are Nybakk]

Hello all

I have the following stored procedure in my Oracle XE Express Edition
database :

This post would probably fit better in comp.lang.java.databases.

Databases aint one of my strongest sides, but I'll give it a shot.

*snip*
boolean result = cs.execute();
int ID = cs.getInt(1);

Are you sure you want to use getInt? I believe that will only retrieve a
value that you've put in the query (" cs.registerOutParameter(1,
Types.INTEGER); "). Look up PreparedStatement.execute() or
PreparedStatement.executeQuery(). As far as I see, you will need to use
a ResultSet.

 

[Chris ( Val )]

Hello all

I have the following stored procedure in my Oracle XE Express Edition
database :

CREATE OR REPLACE PROCEDURE InsertValue
(
rowID OUT TABLEA.ID%TYPE,
clientID IN TABLEA.CLIENTID%TYPE,
batchNum IN TABLEA.BATHCNUMBER%TYPE,
ticketNum IN TABLEA.TICKETNUMBER%TYPE
)
AS
BEGIN
SELECT ID INTO rowID FROM TABLEA WHERE CLIENTID=clientID AND

[snip]

PL/SQL identifiers are not case sensitive, thus:

CLIENTID == clientID

Fix that and see what happens.

 

[swetha]

PL/SQL identifiers are not case sensitive, thus:

CLIENTID == clientID

Fix that and see what happens.

I changed the IN parameter to clID but still not giving me a valid ID.
Maybe I'll try posting on the comp.lang.java.databases group too.

Swetha