wood burning stoves 2.0*
The moose likes JDBC and the fly likes Callable Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Callable Statement" Watch "Callable Statement" New topic
Author

Callable Statement

ravi janap
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
I am calling a stored procedure in Oracle 8i from a JSP deployed on WebLogic 4.5.1 application server.
This stored procedure is meant to delete data from multiple tables.
Here is the code for the stored procedure
CREATE OR REPLACE PROCEDURE del_bkp_player (v_player_id IN VARCHAR2 , ) IS
SqlStmt varchar2(2000);
TabName varchar2(100);
Cursor C1
is
select table_name from all_tables
where table_name like 'BKP_PLAYER_%';
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO TabName;
EXIT WHEN C1%NOTFOUND;
SqlStmt := 'delete from '| |TabName| |chr(10)| |'where upper(player_id)=upper(:v_player_id)';
--dbms_output.put_line('SqlStmt:'| |SqlStmt);
EXECUTE IMMEDIATE SqlStmt USING v_player_id;
END LOOP;
CLOSE C1;
dbms_output.put_line('After Delete');
--commit;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('sqlerrm'| |sqlerrm);
dbms_output.put_line('ERROR IN THE PROCEDURE');
END del_bkp_player;
/
show errors

My JSP code is as follows :
dbQuery = "{CALL del_bkp_player('"+normalizePlayerID(playerId)+"')}";

callableStatement = con.prepareCall(dbQuery);

if ( callableStatement != null ) {
callableStatement.execute();

}
callableStatement.close();
callableStatement = null;
It doesn't seem to work at all.
Where am I going wrong ?


SCJP, SCJD, SCWCD, SCBCD, SCEA
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
Why use the chr(10) in your statement?
Have you tried to run it from sqlplus instead of from jdbc?
Dan
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
Also, there seems to be a stray comma in your parm list.
Did this actually compile? Has Oracle marked the procedure as valid?
Dan
Vinay Reddy
Greenhorn

Joined: May 08, 2001
Posts: 3
Instead of Using
dbQuery = "{CALL del_bkp_player('"+normalizePlayerID(playerId)+"')}";
USe
callableStatement cs= con.prepareCall("{CALL del_bkp_player(?)}";
cs.setXXX(1,normalizePlayerID(playerId));
cs.execute();
I hope it should work
 
Don't get me started about those stupid light bulbs.
 
subject: Callable Statement
 
Similar Threads
java.sql.SQLException: ORA-06576: not a valid function or procedure name
Stored procedure which returns array
Stored Procedure
Store Procedure Codes Badlu In need of Help!!
Calling stored procedure problems