error handling, jobs and sub procedures suggestions

Good Day.

Oracle Noob here. I'm trying to figure a few things out, some help is greatly appreciated!

1. When I submit a procedure with DBMS_JOB.SUBMIT, inside the called procedure is there any way for me to know what job number it's running under?
2. When I submit a procedure as a job, is there any way to get the status and error of the job. I can see if the job is still in the queue under all_jobs, but any way to get detailed error information if it failed without error handling and logging from inside the procedure?
3. Presuming the answer to 2 is NO and forgetting about jobs and trying a dynamic call to a procedure from a calling procedure. What's the best way to return exception and error information back to a calling procedure. Say I have a procedure I will use to launch other procedures. Say, that procedure has a input variable for procedure name and I am doing something like this:

EXECUTE IMMEDIATE 'begin '||procname||';end;';

where proname can change.

On purpose I set procname to a procedure I know will fail. The result is that my calling procedure runs through with no errors or exceptions. What's the best way to return exception information / errors and error messages back to the calling procedure?

Thanks again for any help or information. Received on Mon Feb 05 2007 - 17:51:08 CST