DSXchange

I created a stored procedure (SP), sp_code.sql as:

set serverout on
alter session set plsql_warnings='enbale:all';
drop public synonym sp_code;
create or replace procedure sp_code
as
begin
delete from my_table;
end;
/
create public synonym sp_code for sp_code;
grant execute on sp_code;


I DB is Oracle 9.2
I loaded the SP via SQL Plus.
I imported this SP via the Manager.
I created a job with only 1 stage: Stored Procedure Stage.
I selected the sp_code SP within the Stage.
In SQL Plus, I entered a single row of data into my_table.
I compiled the job and ran.
The job worked. The row was removed.
Then I re-entered a row into my_table.
I recompiled and re-ran the job.
The 2nd log shows identical information as the 1st log.
Under SQL Plus, I checked my_table. The data was still there.
Every re-run thereafter did nothing to the table, and every log looked good.
So, I copied the SP over to sp_code2.sql (changing the name internally also), and followed the same steps.
The first time I ran a new SP, it worked. Every time thereafter, it did nothing, yet showed a perfect log.
I tried again with a 3rd SP. Same results. It always works the first time only.

I also noted under: Director - Cleanup Resources, that the SP's were listed in the window. So, it appears that when we run the first time, the job runs successfully, but upon finishing something happens to hang the SP so that it does not run again. And yet, no mention in the log.

I also tried this using Oracle 10.2, with the similar results.

I have called IBM, and so far, they do not know what the problem is.

Any thoughts, or help on this?
Thanks
-- john herr (Lockheed Martin, Denver)
303-977-3955
john.f.herr@lmco.com

I dont see a commit in your stored proc. Does this stored proc. work properly from command line? Add a commit at the end or instead of delete, do truncate and see what happens.

That was it.
I added a commit and it worked.
Thanks
-- john

Great. Time to mark it "Resolved".