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 ?