Manually managing communications with vendors and customers in
today's enterprise environment is a time-consuming and often inefficient
process. Juggling emails, follow-ups, and notifications can quickly become
overwhelming, leading to missed opportunities and delays. To streamline this
critical aspect of business, it's essential to modernize your communication
system. By integrating email directly into your business software, you can
automate these interactions, ensuring timely and consistent communication
without the manual effort.
I have try to demonstrate step by step how to integrate gamil account with application in Oracle APEX. For better understanding, you can follow the email configuration system.
Step 1: Create Access Control List (ACL). Connect your sys user and execute the below command-
DECLARE
l_acl_path VARCHAR2(3000);
BEGIN
SELECT acl
INTO l_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 => l_acl_path
, user => 'APEX_240200' -- Specify your apex_user
, privilege => 'connect'
) IS NULL THEN
dbms_network_acl_admin.add_privilege(
acl => l_acl_path
, principal => 'APEX_240200' -- Specify your apex_user
, is_grant => TRUE
, privilege => 'connect'
);
END IF;
EXCEPTION
WHEN no_data_found THEN
dbms_network_acl_admin.create_acl(
acl => 'SEND_MAIL.xml'
, description => 'Email integration with Oracle APEX and send mail'
, principle => 'APEX_240200'
, is_grant => true
, privilege => 'connect'
);
dbms_network_acl_admin.assign_acl(
acl => 'SEND_MAIL.xml'
, host => '*'
);
END;
BEGIN
dbms_network_acl_admin.append_host_acl(
host => '*'
, ace => xs$ace_type(
privilege_list => xs$name_list('connect')
, principal_name => 'APEX_240200' -- replace your APEX user here.
, principal_type => xs_acl.ptype_db
)
);
END;
Step 2: Connect your gmail account and ensure the two step verification is enabled. Follow the below step to enable -
i. Login your gmail account.
ii. Click on the user icon on top right corner and then click 'Manage your google account'.
iii. Go to the security tab from the left side navigation menu bar.
iv. Scroll down till 'How you sign in to Google'.
a. Click on the '2 Step Verification'.
b. Click on the 'Add phone number' button.
c. Choose your country from the drop down list from the first field and then enter your phone number in the second field. Click next.
d. You will get an OTP from google. Enter your phone number by providing the OTP and click Verify. You will get a successful message. Click OK to finish the process.
e. Type 'app_password' in the search bar and press enter. You will redirect to a new page. Enter your app name here and click 'Create' button.
f. A popup dialog box open with your app password. This is one time password. You can't show this password next time in readable format. So, copy it first and then press 'Done' button.
Step 3: Download SSL certificate to create wallet. Follow the bellow step-
1. Log in your gmail account. Click on the icon immediate before the url.
2. A popup menu open. Click on 'Connection is secure' and then click on 'Certificate is valid'.
3. A new dialog box open. Select 'Details' tab and export all three certificate from the 'Certificate Hierarchy one by one.
a. open cmd/cerminal and execute then below command-
orapki wallet create -wallet 'Your wallet Directory here' -pwd 'Your wallet password' -auto_login
Note: replace Your wallet Directory here' with your physical wallet location and 'Your wallet password'.
b. Add all downloaded certificate to the wallet. Execute the below command to add-
orapki wallet add -wallet 'Your wallet Directory here' -trusted_cert cert 'Your first downloaded certificate path' -pwd 'Your wallet password'.
You need to execute three time the above command by providing the different certificate path.
c. Check, if the certificate is added or not with the wallet. To check, execute the below command-
orapki wallet display -wallet -pwd 'Your wallet password'.
Step 5: Instance Settings. To set the workspace instance setting, follow the step below-
i. Login into internal workspace.
ii. Click on "Manage Instance".
iii. Click on "Instance Settings" under Manage Instance.
iv. Select "Email" tab and set the below properties-
Oracle APEX instance URL: Click on "Default URL"
Oracle APEX Image URL : Click on "Default URL"
SMTP Host Address : smtp.gmail.com
SMTP Host Port : 465
SMTP Authentication Username : "Your Gmail ID Here"
SMTP Password : "Google Provided Password" Which one you get in step 2
Confirm SMTP Password : "Google Provided Password" Which one you get in step 2
Use ssl/tls : Yes
Default Email From Address: "Your Gmail ID Here"
v. Click on wallet tab and set wallet path like "file:\physical directory of your wallet".
Step 6: Loging your application workspace and open the application.
i. Create a blank page and take a region
ii. Create 6 items and rename them as like P2_FROM, P2_TO, P2_CC, P2_BCC, P2_SUBJECT & P2_BODY. Create 2 buttons, one for send email and another for close the page.
iii. Create a page process to send the email.
apex_mail.send(
p_to => :P2_TO
, p_cc => :P2_CC
, p_bcc => :P2_BCC
, p_from => :P2_FROM
, p_subj => :P2_SUBJECT
, p_body => :P2_BODY
);
Note: This process is only for sending text mail. No attachment will send with this process.
Integrating email functionality into your Oracle APEX applications is a game-changer for any enterprise. The steps we've covered—from setting up email credentials to coding the PL/SQL logic—provide a robust foundation for building a more responsive and intelligent application. Embracing this integration not only streamlines your business operations but also significantly enhances the user experience, making your applications more powerful and efficient than ever before.
Happy Coding. lets enjoy!
