Author: Labanish

An Identity column auto increments on input, introduced from Oracle 12C, it’s a useful for the surrogate primary key column.

Syntax:

GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]

create table FAQS
(id number(10) GENERATED ALWAYS AS IDENTITY START WITH 1, faq varchar (1000));

// add a primary key constraint

ALTER TABLE FAQS
ADD PRIMARY KEY (id);

This article summaries the steps taken to configure APEX application to send emails.

We shall use the email delivery service from Oracle Cloud.

New to Oracle cloud? Get a free trial version of Oracle cloud here.

The assumption is that you have already configured your IAM policies correctly ie. the user we use is in a group (ie. MailGroup ) which has a policy passed to manage approved mail senders.

Allow group MailGroup to manage approved-senders in compartment CompartmentA /tenancy etc.

Learn more about Oracle Email delivery policies here.

Let’s now set up the user SMTP credentials.

To generate SMTP credentials of the user to use in our email delivery, navigate to the user’s profile on Oracle Cloud -> Identity -> Users -> select your user.

On the left side click SMTP Credentials -> Click on generate credentials.

Save the username and password generated. You shall use them later on configuring your APEX application.

Add the user on the Email approved Senders’ List of Oracle Email Delivery.

Navigate to your cloud console to Email Delivery -> Email Approved senders- > create approved sender. (add the user)

Next click on the Email configuration to get the SMTP connection endpoint. (Save it for later use)

ie. smtp.email.eu-frankfurt-1.oci.oraclecloud.com

Set Email Parameters on the Database

Connect to your Database as ADMIN and execute the PL/SQL script below. If you are using Autonomous database, you can connect to your DB using SQL Developer Web.

BEGIN
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'Your SMTP connection endpoint ie. smtp.email.eu-frankfurt-1.oci.oraclecloud.com');
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'smtp username generated');
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'smtp password generated');
COMMIT;
END;
/

Send Email from APEX

Now login to APEX and navigate to SQL workshop to run this command.

BEGIN
apex_mail.send(p_from => 'your approved email sender',
p_to => 'ncve3@gmail.com',
p_subj => 'Email from Autonomous',
p_body => 'It worked!!');
apex_mail.push_queue();
END;
/

Once you run that PL/SQL an email is sent to your client!

Monitoring

You can monitor the mail logs using these commands or using your APEX admin dashboard (Monitor activity\ Mail Log).

Select * from apex_mail_log;

SELECT * FROM APEX_MAIL_QUEUE;

Next

We shall use the now configured email functionality to send emails from APEX application process.

References

Sending Emails from APEX by Sinan

Sending Emails from your APEX by Chaitanya Koratamaddi

select instance_name, con_id, version from v$instance;

select name, con_id, db_unique_name from v$database;

select dbid, con_id, name from v$pdbs;

alter session set container = CDB$ROOT; //You cannot run this command on ATP/ADW shared on Exadata (ADB is a pluggable database)

show parameter db_name;