Note : The examples i mention here are not intended to execute when you do copy, paste and execute. and please don't say my code is full of bugs and don't label my code as i hate... If you don't have
when others then null;
or its equivalent, I won't hate on your code. If your pseudo code logic above includes that construct I'll ABSOLUTELY be hating on your code.
assume there is a package with 10K lines of source code( procedures, functions).
I've got a problem with that already.
the business logic is like this...now please don't ask why is it like this, why can't it be like some other way.
Again, I refuse that request. If you are doing something obviously wrong, I'm going to point it out. It is what I do. It is what I am. I will not change in that respect.
now, in procedure a1, while creating partitions on some table, if there is error due to non availability of space, the cron job fails throwing oracle error.
now my requirement(client requirement) is,
+> can i start from the point of failure, from the point of creating partitions
+> without executing the insert and delete statements which are prior
in procedure a1 where the job has failed. Why the heck would you need to use a goto? Think about this.
If you need to be restartable, you are going to have to change the code. It will require an extra table so it can restart itself automagically.
create table cron_job_log( step number );
create function new_function return number
as
rec cron_job_log%rowtype;
begin
select * into rec from cron_job_log;
return rec.step;
exception
when no_data_found then return 0;
end;
procedure a1
if (new_function < 1)
then
insert data into a table
insert (1) into cron_job_log;
commit;
end if;
if (new_function < 2)
then
delete from a table;
delete from cron_job_log;
insert (2) into cron_job_log;
commit;
end if;
run query to look at dictionary to see if partitions are ALREADY created
if (partitions are not created)
then
do the ddl to create said partition;
end if;
A2;
end A1;
procedure A2
if (new_function < 3)
then
delete from cron_job_log;
insert into cron_job_lob (3);
truncate some table -- this will commit the insert
end if;
A3;
delete from cron_job_log;
commit; -- we are done
end A2;
Now, I just banged that out on the fly, do a code review with your team after you flesh it out. It is standard "coding 101" sort of stuff - requires no 'gotos', just good old fashioned linear procedural logic to see if you've already done something - and if you have, don't do it again.
And now you see why committing often is a really bad idea in general, you have to write a lot more code to make yourself restartable.