Oracle XE 12c becomes Oracle XE 18c

Oracle Database, Express Edition (XE) is a free version of the Oracle Database, currently available as version 11g (11.2) for Windows and Linux.

A planned new version of Oracle XE based on 12c (12.2) was first mentioned back in 2013. Now we are in 2017 and Oracle has changed its version numbering, aiming for yearly releases of the database, which means that after 12c comes 18c (in 2018) and 19c (in 2019) and so on.

According to information coming out at this year's Oracle OpenWorld, the next version of Oracle Database Express Edition (XE) will therefore be Oracle XE 18c.

Below are the details known so far about this upcoming version. Disclaimer: I've collected this information from tweets and blogs, so nothing is official yet. Time will tell as to what is true. UPDATE: Gerald Venzl from Oracle has confirmed the below to be "all true" :-)

• The next version of Oracle Express Edition (XE) will be 18c. (Source: Chris Saxon, Twitter)
• Oracle XE 18c is expected in Q1 of 2018. (Source: AMIS blog). UPDATE: Oracle XE 18c "is currently planned between March and August 2018 and might change". (Source: Gerald Venzl, Twitter)
• There will be yearly releases of Oracle Express Edition (XE), ie Oracle XE 19c in 2019, etc. (Source: Franck Pachot, Twitter).
• There will be simultaneous releases of XE for Linux and Windows. (Source: Gerald Venzl, Twitter)
• Limits for XE 18c will be 2 GB of memory, 12GB of storage (with basic/advanced compression bringing real capacity up to around 40GB), 2 CPUs and 4 pluggable databases. (Source: AMIS blog and Lucas Jellema, Twitter)
• Express Edition (XE) will actually include "nearly all" of the features from Enterprise Edition (EE)! (Source: Franck Pachot, Twitter and Chris Saxon, Twitter).
• Express Edition (XE) will still be free for both development and production. (Source: Chris Saxon, Twitter).
• There will be no support (except through community/forums) for XE, and no bug fixes/patches. Still, with a yearly release cycle that means bugs will be fixed by upgrading to the latest release. (Source: Franck Pachot and Bob Bryla, Twitter)

If most, or even some, of the above is true, this is really great news! I understand we should thank Gerald Venzl at Oracle for this, as he is the guy working on bringing us all this goodness! Thanks in advance, Gerald! :-)

ODC Appreciation Day: The PL/SQL Language

Like last year, Tim Hall of oracle-base.com fame suggested we should all do an "ODC Appreciation Day" in honor of the Oracle Developer Community (ODC), by blogging about our favorite Oracle product or feature.

My personal favorite, after the database itself, is the PL/SQL language that runs inside the database.

Here's what's great about it:

• Simple (and therefore easy to understand and quick to learn)
• Runs everywhere the Oracle database runs (any operating system)
• Seamlessly integrated with SQL
• Great performance, not least because it runs in the database server alongside your data, thus eliminating a lot of mid-tier overhead 

I made a presentation a while back that goes into greater detail about what makes PL/SQL great, take a look at PL/SQL: The Good Parts.


Thanks ODC!

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:

	{
	"version": "2.0.0",
	// Run sqlplus via a batch file
	"windows": {
	"command": "./_run_sqlplus.bat"
	},
	"osx": {
	"command": "./_run_sqlplus.sh"
	},
	"linux": {
	"command": "./_run_sqlplus.sh"
	},
	"presentation": {
	"reveal": "never", // never,silent,always
	"panel": "shared" // new,shared,dedicated
	},
	"tasks": [
	{
	"label": "Build PLSQL code",
	"type": "shell",
	"args": [
	"scott/tiger@//myserver.example.com:1521/xe",
	"@\"${file}\""
	],
	"problemMatcher": {
	"owner": "plsql",
	"fileLocation": [
	"relative",
	"${fileDirname}"
	],
	"pattern": [
	{
	"regexp": "(.*) (\\d*)/(\\d*) (.*?) (.*)",
	"severity": 1,
	"line": 2,
	"column": 3,
	"file": 4,
	"message": 5,
	"loop": true
	}
	]
	},
	"group": {
	"kind": "build",
	"isDefault": true
	}
	}
	]
	}

view raw vscode_tasks_v2.json hosted with ❤ by GitHub

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:

	set pagesize 9999
	set linesize 9999
	set heading off
	set trim on
	select lower(attribute) -- error or warning
	|| ' '
	|| line || '/' || position -- line and column
	|| ' '
	|| lower(name) -- file name
	|| case -- file extension
	when type = 'PACKAGE' then '.pks'
	when type = 'PACKAGE BODY' then '.pkb'
	else '.sql'
	end
	|| ' '
	|| replace(text, chr(10), ' ') -- remove line breaks from error text
	as user_errors
	from user_errors
	where attribute in ('ERROR', 'WARNING')
	order by type, name, line, position;

view raw vscode_show_errors.sql hosted with ❤ by GitHub

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

	#!/bin/sh
	# make sure script uses correct environment settings for sqlplus
	source ~/.profile
	# run sqlplus, execute the script, then get the error list and exit
	sqlplus $1 << EOF
	$2
	@_show_errors.sql
	exit;
	EOF

view raw vscode_run_sqlplus.sh hosted with ❤ by GitHub

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

	rem Setup sqlplus environment for Unicode
	rem Run this before running scripts that contain multibyte characters
	rem See http://www.sqlsnippets.com/en/topic-13434.html for details
	set NLS_LANG=.AL32UTF8
	chcp 65001
	rem show raw error messages from sqlplus
	rem echo exit | echo show errors | sqlplus %1 %2
	rem get errors that can be parsed with problemMatcher
	echo exit | echo @_show_errors.sql | sqlplus %1 %2

view raw vscode_run_sqlplus.bat hosted with ❤ by GitHub

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

	-- enable compiler warnings
	alter session set plsql_warnings = 'enable:all';

view raw sqlplus_login.sql hosted with ❤ by GitHub

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! :-)

APEX Plugin: Calendar Heatmap Region

I was looking at the GitHub "contribution chart", which shows number of commits per day as a heatmap calendar. I thought this type of chart would be cool to have as an APEX plugin, so I went ahead and implemented it.

Here is a video I recorded to walk through how the plugin was made:

Note: For an alternative implementation, see the "Block Calendar" in Oracle JET. As far as I can tell, there is no built-in support for this chart in APEX, so you would have to write your own wrapper to use it as a plugin.


You can download the plugin and PL/SQL code from my GitHub repository.

Enjoy! :-)