Oracle Identity Column

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);

Installing APEX 20.1 on DB System VM on OCI

This post will show you how to install Oracle APEX on Oracle DB System VM or previously known as DBCS.This an Oracle Database in Oracle Cloud Infrastructure.

Prerequisites

1. Login to your DB System compute instance, navigate to the apps folder:

This image has an empty alt attribute; its file name is image.png

2. Download the latest version on APEX from here

Trick:

 Click on the download, accept the agreement, pause the download then grab the download url. Use wget to download it in your compute instance as root user

This image has an empty alt attribute; its file name is image-2-1024x62.png

Unzip the file in a new directory, assign the owner to ‘oracle

[root@dbcs tmp]# unzip apex_20.1_en.zip?AuthParam=1552035503_zxxxx /u01/app/ -d /u01/app/
[root@dbcs apex]# cd /u01/app
[root@dbcs]# chown -R oracle:oinstall apex/
[root@dbcs# cd apex
[root@dbcs]# su oracle

Login to the Oracle Database 19c.

This image has an empty alt attribute; its file name is image-7.png

The next is to check if there are previous versions of APEX installed both on the CBD and PDB level.

This image has an empty alt attribute; its file name is image-8.png

If APEX exists, uninstall software;

SQL> apxremov.sql

Otherwise install APEX using;

SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

Once installed the next step is to change the password of APEX administrator. (Store it safely, you shall use it later)

SQL> @apxchpwd.sql

Username: ADMIN

Email: youremailaddress

Password: yourpassword

The next thing is to copy APEX images folder

SQL > @apxldimg.sql /u01/app

NB. Note i am copying from the path >> /u01/app, i have not entered the apex folder.

This image has an empty alt attribute; its file name is image-9.png

The next thing is to set the port 8080.

EXEC DBMS_XDB.SETHTTPPORT(8080);

Then unlock the anonymous & XBD accounts. (at the CBD Level)

alter session set container = CDB$ROOT;
ALTER USER ANONYMOUS ACCOUNT UNLOCK;
ALTER USER XDB ACCOUNT UNLOCK;

Tip: Make sure port 8080 is open on the security Lists of your VCN.

Now login to your APEX application;

http://<YourIPAddress>:8080/apex
This image has an empty alt attribute; its file name is image-10-1024x511.png

What Next?

  • Installing APEX using Terraform?

References:

Oracle APEX installation

https://apex.oracle.com/en/learn/documentation/

Sending emails using an APEX application- part 1

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