Forum Stats

  • 3,838,788 Users
  • 2,262,399 Discussions
  • 7,900,756 Comments

Discussions

APEX MAIL on OCI

PJ
PJ Member Posts: 263 Bronze Badge

Hi There

I have set up a database on OCI (NOT Autoomous) and configured the SMTP credentials for a user. I have then set the instance email settings in APEX. I note that an ACL has been created.

However when I try to test it there are no errors - but the mails sit in the APEX mail queue.

Are there any detailed logs that I can look at to figure what the H**l is going on?

It is immensly frustrating trying to do what should be straight forward - when under severe pressure as I am sure most of you know already ...



smtp.email.eu-frankf       587       587 NETWORK_ACL_CB64B6B1AEEF78B0E0530B6E130A2519      0000000080002779 SYS
urt-1.oci.oracleclou
d.com

Thx

Paul

Best Answer

  • PJ
    PJ Member Posts: 263 Bronze Badge
    Answer ✓

    With the help of Oracle support I have managed to resolve this issue and will add a few notes here to assist others.

    In short, when using OCI you must use secure mail (TLS/SSL on port 587) which means you need to load certificates into the wallet and configure the wallet settings in APEX Instance settings. The details are available in this note:

    Database Cloud Service (DBCS / DBaaS) on OCI: How to Set Up APEX_MAIL (Doc ID 2428364.1)

    paul

    InoL

Answers

  • InoL
    InoL Member Posts: 9,987 Gold Crown

     but the mails sit in the APEX mail queue.

    Are they in the queue without an error?

    Is the mail job running? What does this return:

    select state, next_run_date
    from dba_scheduler_jobs
    where job_name = 'ORACLE_APEX_MAIL_QUEUE'
    


  • PJ
    PJ Member Posts: 263 Bronze Badge

    Hi Thx,


    see below ....



    SQL> select state, next_run_date
    from dba_scheduler_jobs
    where job_name = 'ORACLE_APEX_MAIL_QUEUE' 2   3
     4 /
    
    STATE
    ---------------
    NEXT_RUN_DATE
    ---------------------------------------------------------------------------
    SCHEDULED
    09-SEP-21 07.05.00.289926 PM +00:00
    
    SQL> select to_char(sysdate,'DD-Mon-YY HH24:MI:SS') from dual;
    
    TO_CHAR(SYSDATE,'DD-MON-YYH
    ---------------------------
    09-Sep-21 19:05:38
    
    SQL> select state, next_run_date
    from dba_scheduler_jobs
    where job_name = 'ORACLE_APEX_MAIL_QUEUE' 2   3 ;
    
    STATE
    ---------------
    NEXT_RUN_DATE
    ---------------------------------------------------------------------------
    SCHEDULED
    09-SEP-21 07.10.00.521167 PM +00:00
    
    
    SQL> set pages 100
    SQL> select * from apex_mail_log;
    
    no rows selected
    
    SQL> select count(*) from apex_mail_queue;
    
     COUNT(*)
    ----------
           15
    
    


  • InoL
    InoL Member Posts: 9,987 Gold Crown

    Do you see any errors?

    select mail_send_count, mail_send_error, last_updated_on
    from apex_mail_queue
    


  • PJ
    PJ Member Posts: 263 Bronze Badge

    no but I can check - just takes time to get over there ....

    SQL> select mail_send_count, mail_send_error, last_updated_on
    from apex_mail_queue 2 ;
    
    MAIL_SEND_COUNT
    ---------------
    MAIL_SEND_ERROR
    --------------------------------------------------------------------------------
    LAST_UPDA
    ---------
                 0
    
    03-SEP-21
    
                 0
    
    09-SEP-21
    
                 0
    
    09-SEP-21
    
                 0
    
    09-SEP-21
    
                 0
    
    09-SEP-21
    
                 0
    
    09-SEP-21
    
                 0
    
    09-SEP-21
    
                 0
    
    09-SEP-21
    
                 0
    
    03-SEP-21
    
                 0
    
    07-SEP-21
    
                 0
    
    07-SEP-21
    
                 0
    
    07-SEP-21
    
                 0
    
    09-SEP-21
    
                 0
    
    09-SEP-21
    
                 0
    
    07-SEP-21
    
    
    15 rows selected.
    
    SQL>
    
  • InoL
    InoL Member Posts: 9,987 Gold Crown

    Hmm, the background job is running, but it is not picking up any mails.

    Just a thought: do you have multiple APEX schema's from older versions? Maybe the job from one version is running, and you are looking at the queue table from another version.

    Another thought: is APEX installed in a PDB? Are you looking in the same PDB for the job and queue?

  • PJ
    PJ Member Posts: 263 Bronze Badge
    SQL> sho con_name
    
    CON_NAME
    ------------------------------
    TESTDB_PDB1
    SQL> select username from all_users where username like 'APEX%';
    
    USERNAME
    --------------------------------------------------------------------------------
    APEX_210100
    APEX_LISTENER
    APEX_PUBLIC_USER
    APEX_REST_PUBLIC_USER
    
    SQL>
    
  • PJ
    PJ Member Posts: 263 Bronze Badge
    SQL> select count(*) from dba_jobs;
    
     COUNT(*)
    ----------
            0
    
    SQL>
    
  • PJ
    PJ Member Posts: 263 Bronze Badge
    Answer ✓

    With the help of Oracle support I have managed to resolve this issue and will add a few notes here to assist others.

    In short, when using OCI you must use secure mail (TLS/SSL on port 587) which means you need to load certificates into the wallet and configure the wallet settings in APEX Instance settings. The details are available in this note:

    Database Cloud Service (DBCS / DBaaS) on OCI: How to Set Up APEX_MAIL (Doc ID 2428364.1)

    paul

    InoL