How to force exit a function from another one in PL/SQL ORACLE?

Question

Let's say I have a function that do a lot of work creating files and reports. I need an option to abort the execution and rollback changes by using a savepoint.

What I still don't know, can I pass a value to the Function1 from Function2, which can be used to EXIT Function1?

Execuse me if I am mistaking something it's a maintenance work here for an application from 2008. I am not used to PL/SQL.

Any suggestions or corrections are welcomed. The function looks like that already ( I removed critical info ) :

FUNCTION LAUNCH_BATCH(p_batch_code IN batch_details.batch_code%TYPE,
                      -- other parameters omitted
                     )
  RETURN PLS_INTEGER
IS
  v_counter       PLS_INTEGER := 0.;
  Response        PLS_INTEGER;
  v_commande      VARCHAR2(1000);
  v_format_date   VARCHAR2(16);
  v_oracle_job_id NUMBER;

  BEGIN

    Response := GET_BATCH_DETAILS(p_batch_code);

    IF Response <> 0 THEN
      IF Response = -2 THEN
        p_error_batch := ERR_EXCEPTION_ORA;
      ELSE
        p_error_batch := ERR_BATCH_NOT_FOUND;
      END IF;

      RETURN (Response);
    END IF;

    UPDATE batch_details
       SET business_date = p_business_date,
     WHERE batch_code = v_batch_details_record.batch_code;

    COMMIT;

    dbms_job.SUBMIT(v_oracle_job_id,
                    v_batch_details_record.procedure_name_to_call
                    || '(''' || p_business_date
                    || ''','
                    || ''''
                    || v_batch_details_record.batch_code
                    || ''','
                    || ''''
                    || v_batch_details_record.bank_code || ''');');
    COMMIT;

    RETURN (0.);

    EXCEPTION WHEN OTHERS THEN
    RETURN (- 1.);
  END LAUNCH_BATCH;

Answer 1

You can't really influence your submitted job directly, as you can with dbms_scheduler; but you could modify the procedure it runs to make it check whether it should be terminated - maybe after each file or report is created.

You're passing the batch code as an argument to the procedure already, so if you made get_batch_details return -3 when you wanted it to terminate then you could intersperse checks into the procedure:

...
create_file_1; -- whatever code you currently have to do work
if get_batch_details(p_batch_code) = -3 then
  rollback work;
  return;
end if;
create_report_2; -- whatever code you currently have to do work
if get_batch_details(p_batch_code) = -3 then
  rollback work;
  return;
end if;

etc. You may not want to check that often. And you could raise an exception instead, either to pass back through the scheduler, or that the procedure handles itself - something like:

procedure some_proc_run_as_job(p_business_date date,
  p_batch_code varchar2, p_bank_code)
is
  self_destruct exception;
  procedure check_for_self_destruct is
  begin
    if get_batch_details(p_batch_code) = -3 then
      raise self_destruct;
    end if;
  end check_for_self_destruct;
  ...
begin
  create_file_1;
  check_for_self_destruct;
  create_report_2;
  check_for_self_destruct;
  ....
exception
  when check_for_self_destruct then
    rollback;
    return;
end some_proc_run_as_job;

You don't have to use get_batch_details of course; you can have another function, or just look up a flag on a table directly. You need to consider the overhead of that check too.

To cause the job to terminate you'd change the flag on the table; or do whatever is needed to make get_batch_details to return -3 instead of zero. Setting that flag/data would be all your second function would need to do.