Unable to understand how APEX_MAIL's job runs

Question

I'm currently trying to implement a similar version of oracle's APEX_MAIL package. I have everything working, but I can't make the job work unless I modify it.

The job APEX_MAIL uses is called ORACLE_APEX_MAIL_QUEUE

BEGIN
DBMS_SCHEDULER.set_attribute( name => '"APEX_040000"."ORACLE_APEX_MAIL_QUEUE"', attribute => 'job_action', value => 'APEX_040000.WWV_FLOW_MAIL.PUSH_QUEUE');

DBMS_SCHEDULER.set_attribute( name => '"APEX_040000"."ORACLE_APEX_MAIL_QUEUE"', attribute => 'number_of_arguments', value => '2');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( 
         job_name => '"APEX_040000"."ORACLE_APEX_MAIL_QUEUE"', 
         argument_position => 1, 
         argument_value => '');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( 
         job_name => '"APEX_040000"."ORACLE_APEX_MAIL_QUEUE"', 
         argument_position => 2, 
         argument_value => '');

END; 
/

So I go to the package to see what the code does. I'm was assuming push queue would send out emails in the queue. Instead, it calls the same job again!

PROCEDURE PUSH_QUEUE( P_SMTP_HOSTNAME IN VARCHAR2 DEFAULT NULL,
                      P_SMTP_PORTNO   IN VARCHAR2 DEFAULT NULL )
IS
BEGIN
    PUSH_QUEUE_BACKGROUND;
END PUSH_QUEUE;


PROCEDURE PUSH_QUEUE_BACKGROUND
IS
BEGIN
    SYS.DBMS_SCHEDULER.RUN_JOB( JOB_NAME => 'ORACLE_APEX_MAIL_QUEUE', USE_CURRENT_SESSION => FALSE );
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE <> -27478 THEN
            RAISE;
        END IF;
END PUSH_QUEUE_BACKGROUND;

So basically this job does nothing, but I switch it to call PUSH_QUEUE_IMMEDIATE which does what I think it should do.

PROCEDURE PUSH_QUEUE_IMMEDIATE( P_FORCE_YN IN VARCHAR2 DEFAULT 'N')
IS
    L_STATUS            NUMBER;
    L_LOCK_HDL          VARCHAR2(128);
    E_DB_SHUTDOWN       EXCEPTION;
    PRAGMA EXCEPTION_INIT(E_DB_SHUTDOWN, -1089);
BEGIN



    WWV_FLOW_DEBUG.ENABLE_DBMS_OUTPUT;



    SYS.DBMS_LOCK.ALLOCATE_UNIQUE( LOCKNAME => 'APEX_MAIL_QUEUE_LOCK', LOCKHANDLE => L_LOCK_HDL);
    L_STATUS := SYS.DBMS_LOCK.REQUEST( LOCKHANDLE        => L_LOCK_HDL,
                                   LOCKMODE          => SYS.DBMS_LOCK.X_MODE,
                                   TIMEOUT           => 0,
                                   RELEASE_ON_COMMIT => FALSE );
    WWV_FLOW_DEBUG.INFO('APEX Mail Lock status: ' || L_STATUS );




    IF L_STATUS = 0 THEN

        FOR C1 IN ( SELECT   ID, MAIL_SEND_COUNT, LAST_UPDATED_ON
                      FROM WWV_FLOW_MAIL_QUEUE
                     ORDER BY MAIL_SEND_COUNT, LAST_UPDATED_ON) LOOP
            BEGIN
                WWV_FLOW_DEBUG.INFO( 'Pushing email: ' || C1.ID );







                IF  (C1.MAIL_SEND_COUNT = 0) OR (NVL(P_FORCE_YN,'N') = 'Y') OR
                   (C1.MAIL_SEND_COUNT > 0 AND (POWER(2,C1.MAIL_SEND_COUNT)/(60*24) + C1.LAST_UPDATED_ON) < SYSDATE) THEN
                    BACKGROUND( P_ID => C1.ID );
                END IF;
                WWV_FLOW_DEBUG.INFO( 'Pushed email: ' || C1.ID );
            EXCEPTION
                WHEN OTHERS THEN
                    WWV_FLOW_DEBUG.LOG_EXCEPTION;

                    IF L_LOCK_HDL IS NOT NULL THEN
                        L_STATUS := SYS.DBMS_LOCK.RELEASE( L_LOCK_HDL );
                        WWV_FLOW_DEBUG.INFO('APEX Mail released lock' );
                    END IF;
            END;
        END LOOP;
    END IF;



    IF L_LOCK_HDL IS NOT NULL THEN
        L_STATUS := SYS.DBMS_LOCK.RELEASE( L_LOCK_HDL );
        WWV_FLOW_DEBUG.INFO('APEX Mail released lock' );
    END IF;
EXCEPTION WHEN E_DB_SHUTDOWN THEN 
    NULL;
END PUSH_QUEUE_IMMEDIATE;

I'm trying to copy APEX_MAIL to a point, but if I do, I won't have a working job. Can anyone point out if APEX_MAIL changes what the job does after an application setting change or any other change?

Thanks in advance!

score 1 · Accepted Answer · answered Mar 19 '18 at 17:58

1

APEX_MAIL.PUSH_QUEUE is usable in your own code to send your mail (in the queue) out immediate. The job normally calls PUSH_QUEUE_IMMEDIATE. I don't know if your setting ever was a bug in the installation or something wrong on your site. Thus fact, it calls PUSH_QUEUE_IMMEDIATE in a separate session as APEX_040000 job.

Since everyone can request an immediate send of all the jobs in the queue, it makes sure via SYS.DBMS_LOCK.REQUEST only one session will actually do the transmit.

answered Mar 19 '18 at 17:58

D.Vanhaute

348
1
8

It appears to be a bug with the installation. I installed it on a different machine and unwrapped the code to find push did, in fact, do what PUSH_QUEUE_IMMEDIATE did in my original machine. – Matt Mar 26 '18 at 18:29

Unable to understand how APEX_MAIL's job runs

1 Answers1