how to submit a job with out parameters
Nag S, December 27, 2004 - 5:51 pm UTC
Tom,
Thank you. I submitted as job and its running fine.
DECLARE
v_job_id BINARY_INTEGER;
BEGIN
DBMS_JOB.SUBMIT(v_job_id,'declare
l_errbuf varchar2(4000);
l_retcode number;
begin
geps_load_interface_all( l_errbuf, l_retcode );
if (l_retcode <> 0)
then
raise_application_error( -20001,l_errbuf );
end if;
end;' , SYSDATE, 'sysdate+(5/(24*60))');
COMMIT;
END;
December 27, 2004 - 6:31 pm UTC
(i assumed a NON-ZERO return code is ERROR, you might ask your developers that question)
Return codes vs. exceptions
A reader, December 28, 2004 - 9:27 am UTC
Why yuck?
Whats the real difference between return codes and exceptions? When you come down to it, they are 6 vs. half-dozen, arent they?
Whats the difference between
rc integer;
rc := foo();
case rc
when 1 then ...
when 2 then ...
end;
vs
begin
foo();
exception
when e1 then ...
when e2 then ...
end;
Its just syntactic stuff, whats the big deal?
December 28, 2004 - 10:55 am UTC
exceptions are NON-IGNORABLE.
return codes are not only easily ignored, they frequently are.
what is the difference begin:
begin
rc := foo();
end;
and
begin
bar();
end;
when bar propagates exception and foo returns a return code.
Not syntax at all, it is all about correctness. For the same reason I wish "when others" didn't exist as a catch all exception (people use that to turn exceptions into "return codes" which get silently ignored somewhere up the stack -- it is a bug in the developed code but you try to find it some day )
That is why.
Exceptions -- cannot be ignored
Return codes -- too easy.
Look at the original example!
declare
l_errbuf varchar2(4000);
l_retcode number;
begin
geps_load_interface_all( l_errbuf, l_retcode );
if (l_retcode <> 0)
then
raise_application_error( -20001, l_retcode || ': ' || l_errbuf );
end if;
end;
I had to go out of my way to add all of that code to turn that return code back into an exception, else it would totally get ignored.
I cannot count on the fingers of all of the people on the planet (ok, i exaggerate but not by much) how many times I've seen this "opps, we ignored an error" logically corrupt data, create nasty bugs, go un-noticed for literally months or years -- and then the "you know what" hits the fan and you spend weeks trying to "slueth out" what went wrong.
A reader, December 28, 2004 - 11:29 am UTC
OK I see your point, but PL/SQL has its roots in Ada and Ada just happened to have exceptions so PL/SQL kept them.
Take C, for example, that doesnt have exceptions, it has return codes. Heck, Oracle itself is written in C, right? Doesnt seem to be hurting it?
A good programmer can use return codes exclusively in PL/SQL and still write good code.
I guess the point is, sloppy programmers will be sloppy programers, and good programmers will be good programmers, no matter what constructs the language provides.
But yes, I agree that the "exceptions" concept is a sound one. It avoid ugly nested code like
if proc1
then if proc2
then if proc3
...
where each proc returns a true/false to indicate success/failure.
Thanks
December 28, 2004 - 11:43 am UTC
I don't understand the logic in your first paragraph.
C++ has exceptions.
C is a slightly older language, some might call is "less refined". In any case, C takes a really rigorous developer to develop right in it. Pointers? Unfortunately I'm afraid to say that many C coders I've met don't fully "get them".
Your 3rd paragraph is 100% true -- "A GOOD PROGRAMMER". However, I'm finding things seem to be getting worse, not better. Tell me, what does it take to be a "programmer". What degree do you need? What training must you have gone thru? (caution, close to ranting here....)
Too many programmers fall into "not sloppy" perhaps but more likely "i didn't understand the far reaching ramifications", "i hadn't seen that before personally", "i never thought about that"......
I think I was really lucky. When I got my first programming job (ad in the washington post was "programmer wanted, no experiece required" -- i met all of the requirements, I wanted to be a programmer and I had no experience) -- I was teamed with a much more experienced person (he was about as old then as I am now). He taught me programming -- the art thereof. Defensive coding, heavy heavy, HEAVY instrumentation (whereby if less than 50% of the code wasn't there for debug/tuning purposes, your code just ain't done yet). Thinking about 5 ways to do something and trying them out (where have i heard that before...).
That is why I call return codes a bad idea when the language supports exceptions, they are less easy to ignore. And when I see a "when others" not followed by RAISE; -- I raise bloody heck, 99.9999999999999% of the time, that is a bug, plain and simple.
Training
A reader, December 28, 2004 - 1:49 pm UTC
Regarding your rant above about "what does it take to be a programmer", I think the state of affairs in IT is mainly because correctness, excellence, competence, etc is very very subjective. So it is impossible to have meaningful certifications, degrees, etc that gauge someone's proficiency.
Heck, my favorite (non-IT) rant is about...what does it take to be a parent?! It is, arguably, one of the most difficult "jobs" out there, and just about anyone of legal age can become a parent. Again, judging someone's proficiency as a parent is impossible...
Anyway, sorry for the off-topic rant. Thanks for the clarification about exceptions and return codes