Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Callable Statement

 
ravi janap
Ranch Hand
Posts: 389
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic