Email Configuration in Oracle Apex

-

 1) In Apex

go to apex_admin--> Manage Instance --> Instance Settings --> Email 


SMTP Host Address: localhost

SMTP Host Port: 25


========= ========= ========= ========= =========
2) Email Configuration 
========= ========= ========= ========= =========
Smtp Server Configuration In Window Server 
https://www.vionblog.com/windows-smtp-server-relay-gmail/
========= ========= ========= ========= =========
3) Oracle SQL
========= ========= ========= =========
conn sys/password as sysdba

@$ORACLE_HOME/rdbms/admin/utlmail.sql

@$ORACLE_HOME/rdbms/admin/prvtmail.plb
@$ORACLE_HOME/rdbms/admin/utlsmtp.sql


grant execute on utl_mail TO public;
grant execute on utl_smtp to public;

alter system set smtp_out_server='localhost' or 'ipaddress'

select * from V$PARAMETER where name=‘smtp_out_server'

alter system set job_queue_processes = 10;  

========= ========= ========= =========
4)ACL Acess
========= ========= ========= =========
https://asgor.net/how-to-send-mail-from-oracle-apex/
Granting Connect Privileges Prior to Oracle Database 12c
Demonstrates how to grant connect privileges to any host for the APEX_190200 database user.


========= ========= ========= ========= ========= =========
The following example demonstrates how to grant connect privileges to any host for the APEX_190200 database user. This example assumes you connected to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role.
========= ========= ========= ========= ========= =========
DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_190200
  -- the "connect" privilege if APEX_190200 does not have the privilege yet.
 
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
 
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_190200',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_190200', TRUE, 'connect');
  END IF;
 
EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_190200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
========= ========= ========= ========= ========= ========= ========= ========= =========
The following example demonstrates how to provide less privileged access to local network resources. This example enables access to servers on the local host only, such as email and report servers.
========= ========= ========= ========= ========= ========= ========= ========= =========
DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to 'localhost' and give APEX_190200
  -- the "connect" privilege if APEX_190200 does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
   
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_190200',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_190200', TRUE, 'connect');
  END IF;
  
EXCEPTION
  -- When no ACL has been assigned to 'localhost'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
    'ACL that lets users to connect to localhost',
    'APEX_190200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/COMMIT;
========= ========= ========= ========= ========= =========
Granting Connect Privileges in Oracle Database 12c or Later
========= ========= ========= ========= ========= =========
BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_190200',
                           principal_type => xs_acl.ptype_db));
END;

The following example demonstrates how to provide less privileged access to local network resources. This example enables access to servers on the local host only, such as email and report servers.

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => 'localhost',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_190200',
                           principal_type => xs_acl.ptype_db));
END;
/
========= ========= ========= ========= ========= =========
5)  Query for to check apex mail log file 
========= ========= ========= ========= ========= =========
1) select * from apex_mail_log;

2) select * from apex_mail_queue order by id desc;

3) begin
APEX_MAIL.PUSH_QUEUE;
end;

Now got  Apex Page and Apply Different Message sending Method 

================
Declarative Method 
===============




=================
Send Email - API Proc
=================


    

DECLARE
    l_body      CLOB;
    l_body_html CLOB;
BEGIN
    l_body := 'To view the content of this message, please use an HTML enabled mail client.';

    l_body_html := '<html>
        <head>
            <style type="text/css">
                body{font-family: Arial, Helvetica, sans-serif;
                    font-size:10pt;
                    margin:30px;
                    background-color:#ffffff;}

                span.sig{font-style:italic;
                    font-weight:bold;
                    color:#811919;}
             </style>
         </head>
         <body>';
    l_body_html := l_body_html ||'<p>Thank you for your interest in the <strong>APEX_MAIL</strong> package.</p> <br />';
    l_body_html := l_body_html ||'  Sincerely,<br />';
    l_body_html := l_body_html ||'  <span class="sig">The Application Express Dev Team</span><br />';
    l_body_html := l_body_html ||'</body></html>'; 
    apex_mail.send(
    p_to   => :P4_TO,   -- change to your email address
    p_from => 'xxxx@gmail.com', -- change to a real senders email address
    p_body      => l_body,
    p_body_html => l_body_html,
    p_subj      => 'APEX_MAIL Package - HTML formatted message');
    
    APEX_MAIL.PUSH_QUEUE;
END;


====================
Send Email - API Function
====================
DECLARE
    l_body      CLOB;
    l_body_html CLOB;
    l_id NUMBER;
BEGIN
    l_body := 'To view the content of this message, please use an HTML enabled mail client.';

    l_body_html := '<html>
        <head>
            <style type="text/css">
                body{font-family: Arial, Helvetica, sans-serif;
                    font-size:10pt;
                    margin:30px;
                    background-color:#ffffff;}

                span.sig{font-style:italic;
                    font-weight:bold;
                    color:#811919;}
             </style>
         </head>
         <body>';
    l_body_html := l_body_html ||'<p>Thank you for your interest in the <strong>APEX_MAIL</strong> package.</p>';
    l_body_html := l_body_html ||'  Sincerely,<br />';
    l_body_html := l_body_html ||'  <span class="sig">The Application Express Dev Team</span><br />';
    l_body_html := l_body_html ||'</body></html>'; 
    
    l_id        := apex_mail.send(
        p_to        => 'yxz@way2cis.com',   -- change to your email address
        p_from      => 'xxxx@gmail.com', -- change to a real senders email address
      p_replyto     => 'yxz@way2cis.com',
        p_body      => l_body,
        p_body_html => l_body_html,
        p_subj      => 'APEX_MAIL Package - HTML formatted message');
        
    FOR c1 IN (SELECT FILE_NAME filename, DOCIMG blob_content,FILE_MIMETYPE mime_type 
        FROM TRNDOCMAST
        WHERE docno IN (109)) LOOP

        APEX_MAIL.ADD_ATTACHMENT(
            p_mail_id    => l_id,
            p_attachment => c1.blob_content,
            p_filename   => c1.filename,
            p_mime_type  => c1.mime_type);
        END LOOP;
      APEX_MAIL.PUSH_QUEUE;
   END;


=====================
Send Email - From Template
======================


declare
l_purmst_rec purmst%rowtype;
    begin
    
    select * into l_purmst_rec from purmst where docno=170018;
    
    
    apex_mail.send (
        p_to                 => 'yxz@gmail.com',
        p_from               => 'xxx@gmail.com',
        p_replyto            =>'ZYZY@way2cis.com',
        p_template_static_id => 'PURCHASE_ORDER',
        
        p_placeholders       => '{' ||
        '    "CUSTOMER_NAME":'           || apex_json.stringify( l_purmst_rec.vendcd ) ||
        '   ,"ITEMS_ORDERED":'           || apex_json.stringify( l_purmst_rec.totlnes ) ||
        '   ,"MY_APPLICATION_LINK":'     || apex_json.stringify( apex_mail.get_instance_url || apex_page.get_url(:APP_ID, :APP_PAGE_ID)) ||
        '   ,"ORDER_DATE":'              || apex_json.stringify( l_purmst_rec.docdt ) ||
        '   ,"ORDER_NUMBER":'            || apex_json.stringify( l_purmst_rec.docno ) ||
        '   ,"ORDER_TOTAL":'             || apex_json.stringify( l_purmst_rec.netval ) ||
        '   ,"ORDER_URL":'               || apex_json.stringify( 'test' ) ||
        '   ,"SHIPPING_ADDRESS_LINE_1":' || apex_json.stringify( 'ADD1' ) ||
        '   ,"SHIPPING_ADDRESS_LINE_2":' || apex_json.stringify( 'ADD2' ) ||
        '   ,"SHIP_TO":'                 || apex_json.stringify( 'LOC' ) ||
        '}',
        p_application_id => 117
    );
             APEX_MAIL.PUSH_QUEUE;
end;

=======================
APEX_MAIL.PUSH_QUEUE;
=======================



useful link:-https://blogs.oracle.com/apex/creating-email-campaign-app-with-oracle-apex

Comments

Post a Comment

Popular posts from this blog

Telegram Integration with Oracle

-
 How to Integrate Telegram with Oracle Apex 1) create Telegram Account in your Mobile like whatsapp 2) Ones you register in telegram apps serach for BotFather 3) To Create New Bot u need to enter below command  /newbot  Alright, a new bot. How are we going to call it? Please choose a name for your bot. Macsbot Good. Now let's choose a username for your bot. It must end in `bot`. Like this, for example: TetrisBot or tetris_bot. MacsOmanBot Done! Congratulations on your new bot. You will find it at t.me/MacsOmanBot. You can now add a description, about section and profile picture for your bot, see /help for a list of commands. By the way, when you've finished creating your cool bot, ping our Bot Support if you want a better username for it. Just make sure the bot is fully operational before you do this. Use this token to access the HTTP API:<Here you will prompt with your api> 4) /mybots (this command will return the list of bots which you have created) ...
Read more

The username or password for the connection pool named |apex||, are invalid, expired, or the account is locked

-
  1. Execute the following command from SQL Plus as the SYS user:  Conn sys/Pass@xe as sysdba  (connect as container if 12c then  ALTER SESSION SET container = CDB$ROOT;) Alter user ords_public_user identified by xxxx; ALTER USER ORDS_PUBLIC_USER ACCOUNT UNLOCK; Connect as PDB Conn sys/Pass@xepdb1 as sysdba (if 12c  ALTER SESSION SET container = PDB1;) Alter user APEX_PUBLIC_USER identified by xxxx; Alter user APEX_REST_PUBLIC_USER identified by xxxx; Alter user APEX_LISTENER identified by xxxx; 2. Unlock the users: ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK; ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK; ALTER USER APEX_LISTENER ACCOUNT UNLOCK; ALTER USER ORDS_PUBLIC_USER ACCOUNT UNLOCK; 3. Now create a profile for the APEX related users: Create profile apex_public_profile limit password_life_time unlimited; 4. Assign the profile to the APEX users: alter user apex_public_user profile apex_public_profile; alter user apex_rest_public_user profile apex_public_pr...
Read more

Row Initialization event on the IG region (Set Default value for column of Row)- Oracle Apex

-
1)  Create a dynamic action for the Row Initialization event on the IG region. 2) Event :- Row Initialization    Action :- Execute Java Script code     Set Fire on Initialization to No' var val,     model = this.data.model,     rec = this.data.record,     erec =  $v("P404_ITEM_CODE"),     erec1=$v("P404_COMPCD"),     meta = model.getRecordMetadata(this.data.recordId); if ( meta.inserted ) {     val = model.getValue(rec, "ITEM_CODE")     if ( val.v === val.d )       { model.setValue(rec,"ITEM_CODE", {d:apex.item("ITEM_CODE").displayValueFor(erec), v: erec});     }     val = model.getValue(rec, "COMPCD");     if ( val.v === val.d ) {         model.setValue(rec,"COMPCD", {d:apex.item("COMPCD").displayValueFor(erec1), v:erec1});     }     }
Read more