Callable Statement

[ravi janap]

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 ?

[Daniel Dunleavy]

Why use the chr(10) in your statement?
Have you tried to run it from sqlplus instead of from jdbc?
Dan

[Daniel Dunleavy]

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]

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