APEX Authentication with Microsoft account

In the upcoming Oracle Application Express (APEX) 5.2 release, support has been added for so-called "Social Sign-In" authentication. This means that your users can login to an APEX application using an external authentication provider. In APEX 5.2, there is built-in support for Google and Facebook accounts. There is also support for "Generic OAuth Providers" and "OpenID Connect Providers". This blog post describes how you can use the "OpenID Connect Provider" to set up authentication with a Microsoft account, such as an Outlook.com account.

Here is how to do it:

A. Register the APEX application with Microsoft

1. Go to Microsoft Application Registration Portal. Note that you need a Microsoft account, such as an Outlook.com email account, to login and use this portal:

https://apps.dev.microsoft.com/

2. Add an app (enter a descriptive name, your users will see this name during the consent prompt in step 20 below). An Application ID gets generated. Click "Generate New Password" to generate a password.

3. Click "Add platform" and choose "Web". Enter the APEX callback URL as the "Redirect URL", for the APEX 5.2 Early Adopter this is:

https://apexea.oracle.com/pls/apex/apex_authentication.callback

4. Add the URL of the APEX app as the "Home Page URL":

https://apexea.oracle.com/pls/apex/f?p=your_app_alias:10

5. Click "Save"

B. Setup APEX Credentials

6. In APEX, go to Shared Components and click on "Credentials"

7. Create a new Credential and give it a name (for example "Azure OpenID Credentials"). Select "OAuth2 Client Credentials Flow" as the credential type.

8. Add the Application ID from step 2 above as the "Client ID", and the password from step 2 above as the "Client Secret".

9. Save the credentials.

C. Setup APEX Authentication Scheme

10. Go to Shared Components and click on "Authentication Schemes"

11. Create a new authentication scheme and give it a name (for example "Azure AD OpenID"). Select "Social Sign-In" as the scheme type.

12. As credential store, select the credentials created in step 9 ("Azure OpenID Credentials").

13. As authentication provider, select "OpenID Connect Provider".

14. As discovery URL, use the following:

https://login.microsoftonline.com/common/v2.0/.well-known/openid-configuration

15. As scope, type "email" (without the quotes)

16. As username attribute, type "email" (without the quotes)

17. Save the authentication scheme.

D. Test the Login

18. Go to the home page of your application:

https://apexea.oracle.com/pls/apex/f?p=your_app_alias:10

19. You should be redirected to a Microsoft login page. Log in with a Microsoft account (such as yourname@outlook.com).

20. You should be prompted to allow the APEX application to log you in and retrieve your email address to identify you. Accept this.

21. You should see the home page of your APEX application, and the value of APP_USER should now be equal to the email address you logged in with at Microsoft.

Switching between authentication schemes in the same APEX session

APEX version 5.2 also includes a new attributte for authentication schemes called "Switch in Session" that can be either Enabled or Disabled. If enabled, the current session's authentication scheme can be changed by passing APEX_AUTHENTICATION=scheme name in a URL's request parameter.

You can use this to present users with multiple login options. For example, you can have the standard APEX Authentication (or your own table-based authentication) set as the current authentication scheme, and then add another button to the login page which switches the authentication scheme to the Microsoft scheme (or Facebook, or Google, etc).

The screenshot above shows an example of a standard APEX login page where I have added an extra button (called "LOGIN_MICROSOFT"), set the icon CSS class to "fa-windows" to get the Windows logo on the button, and set the action attribute of the button to redirect to page 10 (the home page of the application) and also setting the request of the link to "APEX_AUTHENTICATION=name_of_microsoft_auth_scheme". Clicking this button should redirect to a Microsoft page for login, and then redirect the user back to the requested page.

Using VS Code for PL/SQL development

I've been using Sublime Text as my main editor for PL/SQL development for many years, but I'm now in the process of switching to Visual Studio Code (VS Code).

Some good reasons to use VS Code:

• Multi-platform (Windows, OS X, Linux)
• Free, open source
• Lightweight, fast (enough)
• Large ecosystem of extensions
• Built-in Git support
• Can be adapted to PL/SQL coding via a plsql language extension (syntax highlighting, go to/peek definition, go to symbol) and PL/SQL compilation using sqlplus via a Task Runner (see below for PL/SQL specifics)

Installing VS Code and extensions

Download VS Code from https://code.visualstudio.com/ and run the installer.

Start VS Code and click the Extensions icon.

• Search for "plsql" and install the "xyz.plsql-language" extension

 The following are all optional but recommended:

• Search for "git history" and install the "donjayamanne.githistory" extension
• Search for "better merge" and install the "pprice.better-merge" extension (update 24.09.2017: this extension is now part of VS Code itself and does not need to be installed separately)
  
• Search for "git lens" and install the "eamodio.gitlens" extension
• Search for "tag" and install the "formulahendry.auto-close-tag" extension
• Search for "blackboard" and install the "gerane.Theme-Blackboard" extension
• Search for "material icon" and install the "PKief.material-icon-theme" extension
• Search for "plsql" and install the "apng.orclapex-autocomplete" extension (added 02.01.2018)

Click on "Reload" to restart VS Code with the new extensions loaded.

Your list of extensions should now look something like this:

Click File, Preferences, Color Theme and select the "Blackboard" theme.
Click File, Preferences, File Icon Theme and select the "Material Icon" theme.

Configuring a Task Runner to compile PL/SQL code

Click File, Open Folder and open a folder containing your PL/SQL code.
Click View, Command Palette and enter "task" then select "Configure Task Runner", select "Others".

Copy the following text and paste it into the tasks.json file:



Adjust the connection string as appropriate to your environment.

Copy the following text and save it as _show_errors.sql in the project root folder:



Linux and Mac: Copy the following text and save it as _run_sqlplus.sh in the project root folder (remember to chmod +x the file to make it executable).



Windows: Copy the following text and save it as _run_sqlplus.bat in the project root folder.



Optionally create a login.sql file in project root folder and add:



Editing code

The PL/SQL language extension by xyz provides syntax highlighting for PL/SQL, as well as a couple of very useful code navigation features.

You can go to a "symbol" (ie a function or procedure) inside a package by pressing Shift+Ctrl+O and typing the name of the symbol:

You can go to the definition of a function or procedure by pressing F12 when the cursor is on the function or procedure name (or right-click on the function or procedure name and select either "Go to definition", or "Peek definition" to see the definition in a popup window without leaving the current file).

Snippets

You can define your own snippets for frequently used code; see this how-to article. I recommend that you create snippets for frequently used code blocks such as if/then/else statements, case statements, and larger code blocks such as the skeleton for a package, procedure or function.

Building code

To build (compile) the current file into the database, press Shift+Ctrl+B (or click View, Command Palette, and type "build" to search for the relevant command).

The build task runs sqlplus and passes it the filename of the current file. This creates or replaces the object in the database (you can see the commands being executed in the "Output" pane in VS Code). The build task then queries user_errors to get any errors and warnings from the database, and these are shown in the "Problems" pane in VS Code.

You can click on each problem to jump to the relevant line of code. You'll also see squiggly lines under the errors in the code itself, and you can hover over the text to see a tooltip containing the error message.

Note that since the build task queries the user_errors view without any filters, you get to see all errors in the schema, not just the errors for the current file. I actually like this, because it instantly shows me if there are any other problems in the schema that I might not otherwise be aware of.

Also, the build task assumes that you have one file per database object (ie separate files for package specifications and package bodies), and that the filenames match the database object names. This makes sense to me and I assume that's how most people organize their files, but you can tweak the _show_errors.sql script to generate output appropriate to your setup.


Version control with Git

VS Code has great built-in support for Git. I recommend installing a couple of git-related extensions (see above), but other than that, I'll just refer you to the official tutorial for using Git in VS Code.


That's it, enjoy using VS Code for PL/SQL development! :-)

OTN Appreciation Day: Oracle Express Edition (XE)

Tim Hall of oracle-base.com fame suggested we should all do an "OTN Appreciation" day in honor of the Oracle Technology Network (OTN), by blogging about our favorite Oracle product or feature.


My personal favorite is Oracle Express Edition (XE), the free version of the world's best relational database.

 Here's what's great about it:

• Available for both Windows and Linux at the OTN website
• Easy to install (InstallShield on Windows, rpm package on Linux), only takes a few minutes
• Lightweight, requires very little resources to run
• Great performance
• Comes preinstalled with APEX on it
• It's free for any usage, including production applications

Oracle Express Edition is currently available in version 11gR2 (11.2). Based on hints dropped by various folks at Oracle, there will be a 12c version of Oracle XE, but it is probably a year or two away yet.

The XE documentation is a great starting point for those new to Oracle, with several "2-day guides" to database administration and development tasks.

Enjoy Oracle XE and #ThanksOTN for this great product! :-)

SQL Developer 4.1.2: Missing MSVCR100.dll file

I downloaded the latest version of SQL Developer (4.1.2) to a just-provisioned, pristine server running Windows Server 2012 R2.

I downloaded sqldeveloper-4.1.2.20.64-x64.zip, unzipped it, and placed it under c:\program files\sqldeveloper-4.1.2.20.64-x64\. I double-clicked the main sqldeveloper executable and expected it to "just work" (as it usually does), but this time it didn't.

I got the following error message:

sqldeveloper64W.exe - System Error

The program can't start because MSVCR100.dll is missing from your computer. Try reinstalling the program to fix this problem.
 
I searched the disk and found a copy of msvcr100.dll at C:\Program Files\sqldeveloper-4.1.2.20.64-x64\sqldeveloper\jdk\jre\bin\msvcr100.dll.

I then copied the above file to C:\Program Files\sqldeveloper-4.1.2.20.64-x64\sqldeveloper\sqldeveloper\bin\msvcr100.dll

I then double-clicked the main sqldeveloper executable again, and this time it started without errors.

Thoth Gateway moved to GitHub, now supports Apex 5.0

As with the Alexandria PL/SQL Utility Library, the source code for the Thoth Gateway, a mod_plsql and Apex Listener/ORDS equivalent for Microsoft IIS, now has a new home on GitHub. This should make it easy for anyone to look at and perhaps contribute to the code.

If you don't care about the source code, you can head straight to the releases page to download just the binaries. Updated installation instructions for IIS 8 will be forthcoming, but deploying the Thoth Gateway is really as easy as just dropping the binaries into the wwwroot and using IIS Manager to turn the folder into an application.

Also, there were a few minor tweaks needed to support Apex 5.0. As far as I can tell from my own testing, everything now works, but do let me know if you have any issues.

Download the lastest version of the Thoth Gateway with Apex 5.0 support here.

Oracle XE 11g Win64 on Microsoft Azure cloud

(Or: "How to build your own Oracle Cloud in 25 minutes"... ! )

After downloading the recently released Oracle Express Edition for 64-bit Windows, I decided to test it out on a Windows Server 2008 R2 running in the Azure cloud (recently renamed from "Windows Azure" to "Microsoft Azure", I guess because you can run Linux on it, as well).

Here are the high-level steps needed to get this up and running:

1. Sign up for Azure and login to the management portal.
2. Create a new Virtual Machine, select Windows Server 2008 R2. The provisioning of the VM takes around 5 minutes. (I chose to use an A1 instance type, which costs around USD 50 per month, and has 1 core and 1,75GB of memory. Note that XE is limited to use only 1GB of memory, leaving some extra memory for the OS itself and for IIS).
3. When you create the VM, you specify a DNS name for it, for example "myxetest". This becomes the subdomain of Microsoft's "cloudapp.net" domain, for example "myxetest.cloudapp.net". If you want, you can use your own domain name by adding a CNAME record to your domain and pointing it towards the cloudapp.net subdomain.
4. Use Remote Desktop to login to the new server.
5. Download Oracle XE for 64-bit Windows. The file is around 300MB, so this takes less than a minute to download.
6. Unzip the XE file and run setup.exe. Specify a password for the SYS and SYSTEM users, and let the installer do its work. The whole setup process took about 10-15 minutes on my server.
7. Check that the database is running using sqlplus on the command line on the server.
8. Oracle XE 11g comes with Apex 4.0 preinstalled, and running via the Embedded PL/SQL Gateway (DBMS_EPG) on port 8080 (localhost only). You can verify that it's up and running by going to http://127.0.0.1:8080/apex/apex on the server. If you want EPG to work for remote hosts, you have to execute the dbms_xdb.setlistenerlocalaccess procedure and pass FALSE to enable non-local access. (You can also change the port used by EPG via the dbms_xdb.sethttpport procedure.)
9. You need to modify the Windows Firewall settings on the server to allow outbound traffic on port 8080. Finally, you also need to open up port 8080 for the VM in the Azure management portal (you'll find this setting under "Endpoints" for the VM.)

Here's a screenshot after the database has been installed:

The Apex homepage on localhost:

The Apex builder in action, accessed via the default cloudapp.net domain:

A note on production deployments

The Embedded PL/SQL Gateway (DBMS_EPG) is intended for development environments, and is not recommended for production. Since you are running on a Windows server, you probably want to leverage the excellent built-in Internet Information Server (IIS), with the Thoth Gateway which is an open source ASP.NET implementation of the OWA interface which is what the EPG, mod_plsql and the Apex Listener (now called Oracle REST Data Services, or ORDS) use to communicate with the database. You'll need to enable/install IIS first, as this is not enabled by default on a Windows 2008 server. Then download the Thoth Gateway and follow the installation instructions in the downloaded zip file.

Oracle Express Edition (XE) 11g for Windows 64 bit

As announced by Kris Rice (who is the product manager for SQL Developer, Apex Listener and Oracle XE), the Oracle Express Edition (XE) Database version 11g is now (finally!) available for 64-bit Windows.

It's been a looong wait; the 64-bit version for Windows was expected to be released around the same time as the 32-bit version, some 3 years ago.

Anyway, it's here now, which is great! Thanks Kris! :-)

Oracle Express Edition 11g can be downloaded here, it now comes in three flavors (Win 32 bit, Win 64 bit, and Linux 64 bit).