Skip to main content

Email Template Option in Oracle APEX 18.2

An interesting feature on Oracle APEX 18.2 and above is the EMAIL Template, It can be found under 

Shared Components => Other Components


Fig 1: Email Templates

It consists of two major parts,

1.     The Template Details.
2.     PL/SQL Procedure.

1. The Template Details:

It has four sections as below,
  • Header
  • Body
  • Footer
  • HTML Code Content
Get started by loading  "Sample Email Templates"  (option available @ right hand side).
Fig 2: Sample Templates


Fig 3&4: Template Options

The hash tags like #ORDER_NUMBER#, #CUSTOMER_NAME# are substitutions.

Once you create the template, It can be available under the option, 

Shared Components => Other Components => Email Templates

Fig 5: List of Email Templates


Click here to understand the concept of apex_mail.send API.

2. PL/SQL Procedure:

Sample API Usage:

BEGIN
    apex_mail.send (
        p_to                 => email_address_of_user,
        p_template_static_id => 'ORDERDETAILS',
        p_placeholders       => '{' ||
        '    "CUSTOMER_NAME":'           || apex_json.stringify( :BIND_VARIABLE1) ||
        '   ,"ITEMS_ORDERED":'           || apex_json.stringify( :BIND_VARIABLE2 ) ||
        '   ,"MY_APPLICATION_LINK":'     || apex_json.stringify( :BIND_VARIABLE3) ||
        '   ,"ORDER_DATE":'              || apex_json.stringify( :BIND_VARIABLE4) ||
        '   ,"ORDER_NUMBER":'            || apex_json.stringify( :BIND_VARIABLE5) ||
        '   ,"ORDER_TOTAL":'             || apex_json.stringify( :BIND_VARIABLE6) ||
        '   ,"ORDER_URL":'               || apex_json.stringify( :BIND_VARIABLE7) ||
        '   ,"SHIPPING_ADDRESS_LINE_1":' || apex_json.stringify( :BIND_VARIABLE8) ||
        '   ,"SHIPPING_ADDRESS_LINE_2":' || apex_json.stringify( :BIND_VARIABLE9) ||
        '   ,"SHIP_TO":'                 || apex_json.stringify( :BIND_VARIABLE10) ||
        '}' );
END;

To send an nice email to the customer, then you just create PL/SQL process on apex page and call the below procedure with necessary inputs.

Example: Just run it.

BEGIN
    apex_mail.send (p_to                 => 'karkuvelraja.t@xxyyy.com',
                    p_template_static_id => 'ORDERDETAILS',
                    p_placeholders       => '{' ||
                    '    "CUSTOMER_NAME":'           || apex_json.stringify( 'Karkuvelraja Thangamariappan' ) ||
                    '   ,"ITEMS_ORDERED":'           || apex_json.stringify( 5 ) ||
                    '   ,"MY_APPLICATION_LINK":'     || apex_json.stringify( 'https://www.itchotels.in/hotels/chennai/itcgrandchola/offers.html?source=google&medium=cpc&campaign=ITC_EV_INT_UAE_Brand_BMM&gclid=EAIaIQobChMIqt3Q38ON6AIVStreCh2FCApDEAAYASAAEgJ9DfD_BwE' ) ||
                    '   ,"ORDER_DATE":'              || apex_json.stringify( '09-MAR-2020' ) ||
                    '   ,"ORDER_NUMBER":'            || apex_json.stringify( 'ORD090320200015478' ) ||
                    '   ,"ORDER_TOTAL":'             || apex_json.stringify( 2584.50 ) ||
                    '   ,"ORDER_URL":'               || apex_json.stringify( 'https://www.itchotels.in/hotels/chennai/itcgrandchola/offers.html?source=google&medium=cpc&campaign=ITC_EV_INT_UAE_Brand_BMM&gclid=EAIaIQobChMIqt3Q38ON6AIVStreCh2FCApDEAAYASAAEgJ9DfD_BwE' ) ||
                    '   ,"SHIPPING_ADDRESS_LINE_1":' || apex_json.stringify( 'XXX Solutions Pvt LTD, Sholinganallur' ) ||
                    '   ,"SHIPPING_ADDRESS_LINE_2":' || apex_json.stringify( 'Chennai, Tamil Nadu' ) ||
                    '   ,"SHIP_TO":'                 || apex_json.stringify( 'Shanmuga Lakshmi' ) ||
                    '}',
                    p_application_id    => 31586
                   );

     apex_mail.push_queue();      -- To push all the mails from queue
END;

Note: To avoid the below error, you must pass APP ID (When you are executing this procedure from outside the application).


Output: 


That's it. Happy APEXing!!!...

Comments

  1. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. Email templates google workspace

    ReplyDelete
  2. Email template workspace marketplace A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post.

    ReplyDelete
  3. How do I convert email to google sheets Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with extra information? It is extremely helpful for me.

    ReplyDelete
  4. Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative. I can't wait to read lots of your posts. questionnaire builder

    ReplyDelete
  5. There is so much in this article that I would never have thought of on my own. Your content gives readers things to think about in an interesting way. Thank you for your clear information. quiz maker

    ReplyDelete
  6. I am impressed. I don't think Ive met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here. media kit templates The intention of web templates is to design a web site.

    ReplyDelete

Post a Comment

Popular posts from this blog

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template

Generating the report with APEX_DATA_EXPORT

With the APEX_DATA_EXPORT package, you are able to export data from Oracle Application Express in the following file types: PDF, XLSX, HTML, CSV, XML, and JSON. Step 1: Create a table and populate it with some sample records. CREATE TABLE emp   (     empno        NUMBER,     first_name   VARCHAR2(240),     last_name    VARCHAR2(240),     mgr          NUMBER,     deptno       NUMBER,     sal          NUMBER,     created_date TIMESTAMP (6),     comm         NUMBER,     hiredate     DATE,     JOB          VARCHAR2(240),     ename        VARCHAR2(240),     PRIMARY KEY (empno) USING INDEX ENABLE   ); /    INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (1, 'Larry', 'Ellison', ,                  10, 5000, LOCALTIMESTAMP);   INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (2, 'Juan', 'Juan', 1,  

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  manager ,                emp . hiredate ,                 nvl ( emp . sal , 0 )  salary ,                 nvl ( emp . comm , 0 )  commission            FROM  eba_demo_chart_emp emp ,                eba_demo_chart_dept dept ,                eba_demo_chart_emp mgr           WHERE  emp . deptno = dept . deptno             AND  emp . mgr      = mgr . empno  ( + )           ORDER   BY  emp . ename