CallableStatement Error on Oracle Stored Procedure.

[Kunjal Shah]

Hello,

I am having problems calling a Stored Procedure from Java. This is the relevant portion of my code...

Oracle Stored Procedure.

CREATE OR REPLACE PROCEDURE update_cat_count(in_catid in number)
 
IS
 
latestdate posts.postdate%Type;
latestauthor posts.AUTHOR%Type;
max_post number;
final_id number;
p_id number;
 
BEGIN
 --figure out which id we want to use
 select parent_id into p_id from categories where id = in_catid;
 
 if p_id = 0 then
 final_id := in_catid;
 else
 final_id := p_id;
 end if;
 
 FOR item in (Select id from categories start with id = final_id connect by prior id = parent_id) LOOP
  
 delete from lastcatinfo where lastcatinfo.catid = item.ID;
 
 --get the latest post
  Select max(id) into max_post from posts 
   where cat_id in (Select id from categories start with id = final_id connect by prior id = parent_id);
  
  --if there is one then update the category meta information
  if max_post != 0 then
      
-- get the lastest author and postdate
    select postdate, author into latestdate, latestauthor from posts where id = max_post;
  
     -- insert the values
     insert into lastcatinfo (catid, numberofposts, lastupdated, lastupdateby)
      VALUES (item.ID,
      (Select count(id) from posts posts2
        where cat_id in (Select id from categories start with id = item.ID connect by prior id = parent_id)
   ), latestdate,latestauthor);

end if;

-- end looping through categories
END LOOP;
commit;
END;
/
 
SHOW ERRORS;
 

CREATE OR REPLACE PROCEDURE POSTS_DELETE(in_postid in number)
is
 
 p_id number;
 
BEGIN
 
 select categories.parent_id into p_id from categories, posts 
 where categories.ID = posts.CAT_ID and posts.id = in_postid;
 
   FOR item in (Select id from posts start with id = in_postid connect by prior id = parent_id) LOOP
  
 --delete the post
 delete from posts where id = item.ID;
 
-- end looping through categories
END LOOP;

--update the count
update_cat_count(p_id);
commit;
END;
/
 
SHOW ERRORS;

System.out.println("call " + sql_prefix + "posts_delete(?) " + " id is "  + dtf.getPostID());
            
//Update the category counts
CallableStatement proc = con.prepareCall("{ call " + sql_prefix + "posts_delete(?) }");
proc.setInt(1, Integer.parseInt(dtf.getPostID()));
proc.execute();

I get an error ORA-01403: no data found --
ORA-01403: no data found
ORA-06512: at "TEST_USER.UPDATE_CAT_COUNT", line 13
ORA-06512: at "TEST_USER.POSTS_DELETE", line 20
ORA-06512: at line 1...

The line 13 in UPDATE_CAT_COUNT is

> select parent_id into p_id from categories where id = in_catid;
I know that the error means there is now row returned by the Select Into statement... But somehow when I run the procedure on the SQL*Plus

call " + sql_prefix + "posts_delete(2)

it runs fine !!.. I would appreciate any help/tips to resolve this.
Regards

[Scott Selikoff]

I'm not sure what the problem is but you might consider some debugging practices when trying to diagnose a problem and/or ask for help. You've posted a lot of code that is probably unrelated to the problem. I'd start smaller, cutting out large chunks of code until you find the root cause of the problem. Not many people want to read through entire stored procedures looking for bugs.
[ September 13, 2006: Message edited by: Scott Selikoff ]