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

EDIT:

The Following Code runs dependently despite pragma autonomous_transaction. It might be due to dynamic SQL. Otherwise, it is as simple as it could be.

DW_Pac

procedure transfer_test( P_user_name Varchar2)
   pragma autonomous_transaction;
as
begin
   dbms_session.sleep(60 * 5);
   commit;
end;

Web_Pac

l_plsql_block := 'BEGIN DW_Pac.transfer_test' || l_db_link || ' (:a); END;';
execute immediate l_plsql_block using in p_nutzer_name;
-- next line will wait 5 Min....

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