• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Callable Statement

 
Ranch Hand
Posts: 389
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ?
 
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic