PLSQL: Subjob as new session?

Question

I know there is pragma autonomous_transaction. But can I get more than that?

I want to start a subroutine (procedure in a package) completely isolated from the calling routine.

I.e.:

• The subroutine shall not end when the calling routine ends.
• The calling routine continues to work without having to wait for the subroutine to finish.

So basically, I want to start a new session and run the subroutine within this session. But how do I archive this?

Best, Peter

Answer 1

If "subroutine" is - actually - a stored procedure (if not, make it so), then one option to do that is to schedule it from the main (calling) procedure; it would run right now and only once. Doing so, main procedure would continue doing what it does, and subroutine would separately do whatever it does.

Use DBMS_SCHEDULER. Basically, you'd

create procedure p_main is
begin
   ... do something here

   -- call subroutine
   dbms_scheduler.create_job(..., p_subroutine, ...)

   ... continue main procedure
end