unable to call main procedure from wrapper procedure asynchronously

Question

I want to call main procedure from the wrapper procedure. Wrapper procedure I'm able to output with dbms output print line but I'm unable to print within the main proc, looks like the main proc is not called from wrapper procedure. Please help me

Below is the table script

CREATE TABLE ASYNC_SAMPLE_TAB
( attribute1 varchar2(50),
  attribute2 varchar2(50)
);

Below is my package specification

CREATE OR REPLACE PACKAGE XX_ASYNC_DEMO_PKG
IS
    PROCEDURE MAIN_PROC( 
        attribute1          IN      VARCHAR2,
        attribute2          IN      VARCHAR2
    );
    
    PROCEDURE WRAPPER_MAIN_PROC( 
        attribute1          IN      VARCHAR2,
        attribute2          IN      VARCHAR2
    );
  
END XX_ASYNC_DEMO_PKG;

Below is my package bofy

CREATE OR REPLACE PACKAGE BODY XX_ASYNC_DEMO_PKG
IS
    PROCEDURE WRAPPER_MAIN_PROC( 
            attribute1          IN      VARCHAR2,
            attribute2          IN      VARCHAR2
        )
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('-- START OF WRAPPER PROC---' || SYSTIMESTAMP);
        dbms_scheduler.create_job(
            job_name   => 'ASYNC_MAIN_PROC' || '_' || attribute1, 
            job_type   => 'PLSQL_BLOCK', 
            job_action => 'BEGIN 
                              MAIN_PROC(''' || attribute1 || ''', ''' || attribute2 || ''');
                           END;', 
            start_date => systimestamp , 
            auto_drop  => true,
            enabled    => true
        );
        DBMS_OUTPUT.PUT_LINE('-- END OF WRAPPER PROC---' || SYSTIMESTAMP);
    END;
    

PROCEDURE MAIN_PROC( 
        attribute1          IN      VARCHAR2,
        attribute2          IN      VARCHAR2
    )
    AS
        sql_stmt    VARCHAR2(200);
    BEGIN
        DBMS_OUTPUT.PUT_LINE('-- START OF MAIN PROC---' || SYSTIMESTAMP);
        DBMS_SESSION.sleep(10);
        sql_stmt := 'INSERT INTO ASYNC_SAMPLE_TAB VALUES (:1, :2)';
        EXECUTE IMMEDIATE sql_stmt USING attribute1, attribute2;
        DBMS_OUTPUT.PUT_LINE('-- END OF MAIN PROC---' || SYSTIMESTAMP);
    END;
END XX_ASYNC_DEMO_PKG;

Below is the command with which I'm trying to test above solution

exec XX_ASYNC_DEMO_PKG.WRAPPER_MAIN_PROC('Test101_1', 'Test101_2');

Answer 1

As described in the documentation, the main purpose of DBMS_OUTPUT is debug messages to trace execution flow. It's also said there that it doesn't immediately return anything to the caller, but text is placed into the buffer and may be retrieved from there by the caller or the program itself (though, you cannot "get into" any other program flow and get anything from the executing code until the called unit is complete).

Scheduler doesn't retrieve the content of the buffer after it completes the job, so DBMS_OUTPUT.PUT* called inside a background job has no effect and you cannot see any result.

You may use some logging table (with separate logging procedure executed in autonomous transaction not to have an influence or be influenced by the main transaction) or external file and UTL_FILE to send results here. Or use a general table that should be processed by the job and check results after it finishes.

UPD: Below is a complete setup to demonstrate how to pass parameters to a callable and see its results.

• We'll log results into this table:

create table log_table (
    ts timestamp,
    val number,
    job_name varchar2(30)
)

• Then this procedure will be used to perform an action:

create or replace procedure proc_insert(
    p_val in number,
    p_job_name in varchar2
) as
begin
    insert into log_table(ts, val, job_name)
    values (systimestamp, p_val, p_job_name);
    commit;
end;

• This procedure will call our action procedure in a background job. We need to specify a number of parameters to override default values in a callable when creating a job. And then use DBMS_SCHEDULER.SET_JOB_[ARGUMENT|ANYDATA]_VALUE depending on the parameter type.

create or replace procedure proc_insert_async(
  p_call_id varchar2,
  p_val number,
  r_job_name out varchar2
) as
  l_job_prefix constant varchar2(20) := 'LOG_TABLE_';
  l_job_name varchar2(100) := l_job_prefix || p_call_id;
begin
  dbms_scheduler.create_job(
    job_name => l_job_name,
    job_type => 'STORED_PROCEDURE',
    /*Callable unit - out API procedure*/
    job_action => 'TEST_EAS.PROC_INSERT',
    /*Number of args to be passed to the proc*/
    number_of_arguments => 2,
    /*Run immediately*/
    start_date => sysdate,
    enabled => false,
    auto_drop => true
  );

  /*Pass parameters to the callable*/
  dbms_scheduler.set_job_anydata_value (
    job_name => l_job_name,
    argument_position => 1,
    argument_value => sys.anydata.convertNumber(p_val)
  );
  dbms_scheduler.set_job_argument_value(
    job_name => l_job_name,
    argument_position => 2,
    argument_value => l_job_name
  );

  /*Start job*/
  dbms_scheduler.enable(
    name => l_job_name
  );
  
  r_job_name := l_job_name;
end;

• Finally, test this in action (sleep is added due to async nature of inserts to wait for results).

declare
    l_job_name varchar2(100);
begin
    proc_insert_async(
        p_call_id => 'test1',
        p_val => 1,
        r_job_name => l_job_name
    );

    dbms_output.put_line(l_job_name);

    proc_insert_async(
        p_call_id => 'test2',
        p_val => 10,
        r_job_name => l_job_name
    );

    dbms_output.put_line(l_job_name);
    dbms_session.sleep(3);
end;
/
LOG_TABLE_test1
LOG_TABLE_test2


PL/SQL procedure successfully completed.

select *
from log_table

TS	VAL	JOB_NAME
25.11.22 10:52:37,310403000	10	LOG_TABLE_test2
25.11.22 10:52:37,302992000	1	LOG_TABLE_test1