Tuesday, March 21, 2017

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


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

Saturday, January 21, 2017

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

Monday, December 5, 2016

APEX plugin files and 404 not found in ORDS

After installing APEX 5.0.4 and ORDS 3.0.8 I had an issue where APEX plugins that have static files bundled with the plugin (files that at runtime get mapped to URLs such as /ords/your_workspace_name/r/your_app_id/files/plugin/123456789/v1/filename) were giving a 404 "not found" error from ORDS. Well, you actually have to open the console in the web browser to see the 404 errors, and then if you try to open the file directly you get a 404 page from ORDS:

I had seen this problem before, and it is typically because you have not run the apex_rest_config.sql script as part of your APEX installation, as explained by Patrick Wolf here and also in the docs.

However, in my case I was fairly certain I had already run the apex_rest_config.sql script. I re-ran it just in case (and restarted Tomcat), but I still got the error.

I then inspected the Tomcat log files (look in /usr/share/tomcat8/latest/logs/catalina.out) and found the following:

SEVERE [ajp-nio-8009-exec-1] . *** The pool named: |apex|pu| is reporting no APEX workspaces are enabled for RESTful Services,
 but the pool named: |apex|al| is reporting 1 workspaces are enabled.
This implies APEX was installed after Oracle REST Data Services. Please run the following command and restart Oracle REST Data Services:
        java -jar ords.war validate --database apex
APEX based RESTful Services, including those used to serve static resources will not function until this has been done.

So I did as instructed and ran this command

java -jar ords.war validate --database apex

and then restarted Tomcat

service tomcat restart

And that resolved this issue.

Monday, October 10, 2016

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:

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

Sunday, September 11, 2016

Using inline dialogs in APEX 5

APEX 5 has built-in support for dialog boxes. These come in two varieties; dialog pages (which show another standard APEX page in a dialog box using an iframe) and inline dialogs (which show a region on the current page as a dialog box). John Snyders goes into great technical detail about APEX dialogs and the differences between the two types.

This post is a quick summary of how to use inline dialogs. As John points out, you should consider inline dialogs if the content is mainly static, don't need validation or submit processing, and must display quickly.

Start by adding a region to the "Inline Dialogs" display position, and set the template to "Inline Dialog". Set the static id of the region to "my_inline_dialog".

When the page is rendered, the region in the "Inline Dialogs" display position will be initially hidden. To display the dialog, create a button and define a dynamic action on it to execute the following Javascript code:


As shown below:

Add a close button to the inline dialog region and create a corresponding dynamic action on it to trigger the following Javascript code:


As shown below:

By the way, these Javascript functions are just short helper functions that are included in the Universal Theme (theme 42), wrappers for jQuery functions:

When you run the page the dialog region is invisible:

When you click on the show button, the dialog appears:

This is a simple example, but remember that you can put anything in the dialog region (reports, charts, lists, PL/SQL dynamic regions, etc), including sub regions. Also remember that whatever content you put in this region will be loaded when the page loads, it will just be hidden until the dialog is displayed. If you need to refresh the content of the dialog, you can use a report region with a dynamic action refresh. To refresh PL/SQL dynamic content, consider using my dynamic action plugin.

Further references on APEX inline dialogs:

Saturday, August 13, 2016

Using the PayPal REST API from PL/SQL

Do you need to accept payments for goods and services via your (APEX) application and would you prefer to handle the payments in the database via PL/SQL? Then this blog post is for you... :-)

Almost a decade ago (in 2007), Oracle released a whitepaper on Integrating Application Express with PayPal Payments Pro which used PayPal's Name Value Pair (NVP) API.

In the years since then, PayPal has made available a new API which is based on REST principles.

I've created a package called PAYPAL_UTIL_PKG to use the PayPal REST API from PL/SQL.

Note: For an alternative to PayPal, check out Trent Schafer's posts on using Stripe from PL/SQL.

Sign up for a PayPal Developer Account

To use the PayPal API you need to sign up for a PayPal account. Go to https://developer.paypal.com/ and sign up. After logging in as a developer, you must register your app to get an API key that you can use for calls to the API. Follow the instructions in the Making your first call article and run the samples using curl to get a feel for how the API works, before you start on the PL/SQL integration.

Note that PayPal provides both a so-called "sandbox" environment for testing, as well as a "live" environment for production. Each environment has its own set of API keys.

See also the PayPal API docs for further reference and troubleshooting.

Installing the PL/SQL package

The PAYPAL_UTIL_PKG is part of the Alexandria Utility Library for PL/SQL. Download the source or clone the repository from GitHub and install the package in your database. (Note: To install with minimal dependencies, run the scripts install_core.sql and install_paypal.sql from the /setup folder.)

A note about the code: To support usage in APEX 4, the PAYPAL_UTIL_PKG package does not currently use the APEX_JSON package provided with APEX 5. For the time being, the package uses its own JSON-parsing routines. In the future, the package might be refactored to use APEX_JSON.

Using the PayPal PL/SQL API

The following diagram (made with websequencediagrams.com) illustrates a typical process flow for accepting a payment. The process is further explained below.

First, the user somehow initiates the payment process, typically by clicking a button in your APEX application that runs a PL/SQL process. It is recommended that you create your own package for your application-specific payment logic (in other words, don't put the PL/SQL code inline in the APEX process, just call a procedure in your package and maintain all the logic in your package instead of in the APEX user interface).

Note: While developing and testing, you may want to use PayPal's sandbox environment, in that case call the switch_to_sandbox procedure before any other call to the API.

Next, we call get_access_token with your API key (which actually consists of both a client_id and a secret) to get a valid OAuth token to use for subsequent calls to the API.

Then, we call create_payment with the details of the transaction (amount, currency, description) as well as the URLs that we want PayPal to return the user to after he has confirmed the payment (return_url) or cancelled the transaction (cancel_url). These URLs will typically be REST endpoints that you have created using ORDS (more about this later).

The create_payment function will return a t_payment record. You should save the returned fields in a database table associated with the user's purchase, so you can retrieve the information later.

The approval_url field is a PayPal page. We need to redirect the user from our APEX application to this PayPal URL so that the user can login to PayPal and confirm the payment. To do the redirect, add owa_util.redirect_url(approval_url) as the final statement in your procedure.

The browser redirects the user to PayPal, which shows the details of the transaction to the user. If the user approves the transaction, PayPal redirects the user to the URL that you specified as the return_url parameter in the call to create_payment() above. If the user instead decides to cancel the transaction, PayPal will redirect the user to the cancel_url that you specified.

Let's assume that the user approves the payment, and PayPal redirects the user to the return_url that was specified when the payment was created. A payer ID is appended to the return URL, as PayerID. The URL looks like this:


To execute the payment after the user's approval, make a call to execute_payment and pass the payer_id received via the return URL. You also need to pass the payment_id, which was returned from the previous call to create_payment (and which you stored in a database table for use later, right?). But how do you know which payment_id is identified with this specific transaction? You would typically generate a unique URL which includes some kind of identifier (such as https://servername/payments/confirm/1234 where 1234 is some internal ID that you use to keep track of your user's different purchases). Using this example, PayPal would redirect the user to the following URL:


To handle requests to this URL, you would set up a GET handler in ORDS so you can parse out the various parts of the request URL, including your own id (1234) which you can use to look up the payment_id, as well as the payer_id.

Confused? Perhaps PayPal's description here will clarify the process.

 When you execute the payment, the user's PayPal account is charged, and you get a t_payment record returned.

You can then check the value of the state field in the payment record.

If the state is equal to "approved", then the payment is OK and you can provide whatever goods or services the user has purchased (or at least show a confirmation page to notify the user that the transaction has succeeded).

The money should already be in your PayPal account! :-) 

Saturday, July 23, 2016

Minimal privileges for Amazon S3 backup user

This is a follow-up to an old post I did about how to backup Oracle database schemas to Amazon S3 using PL/SQL.

In short, the packages provided in the Alexandria Utility Library for PL/SQL allow you to set up a schema-level backup of files from your database to Amazon's Simple Storage Service (S3).

At the end of that article I mentioned that you should use AWS Identity and Access Management (IAM) to create a separate backup user that has minimal privileges, to reduce the risk if the password ("Secret Access Key" in S3 terminology) is ever exposed. By creating a separate user which only has upload ("PutObject") permissions on your S3 folder, and no privileges to list file contents, delete or download files, you can limit the damage that someone with a stolen password can do.

Here's how to set this up:

  1. Go to your AWS admin console.
  2. Go to S3 service and create a new bucket (your-bucket-name).
  3. Create a folder called "backup".
  4. Optionally, create subfolders "backup/schemas" and "backup/apps".
  5. Go to IAM service.
  6. Go to Groups and create a new group ("MySiteBackupUsers"). Do not add any of the default policies.
  7. Go to Permissions for the group and under "Inline Policies", click on "Create Group Policy". Choose "Custom Policy".
  8. Policy Name: "UploadFilesToBackup".
  9. Add the following policy definition:
    "Statement": [
            "Action": [
            "Effect": "Allow",
            "Resource": "arn:aws:s3:::your-bucket-name/backup/*"

Here is a screenshot:

Then go to Users and click "Create New Users". Enter a user name ("MySiteBackupUser"). After the user has been created, click on "Show User Security Credentials" and copy the values (Access Key ID and Secret Access Key) into your backup script settings.
Finally, add the user to the newly created user group (add user "MySiteBackupUser" to group "MySiteBackupUsers").

That's it, you now have a minimally privileged user account that you can use for your S3 backup scripts from PL/SQL.