Dynamic Content Region in Oracle APEX 22.2

There's a new version of Oracle Application Express (APEX) in town, and one of the new "features" is a new region type called "Dynamic Content" that allows you to output content using PL/SQL.

What happened to the existing "PL/SQL Dynamic Content" region type that does exactly this, you might ask? It's still there, but now it's marked as "Legacy" and requires an extra mouse click just to be able to select it, and to make you feel bad for using it.

So why a new region type to replace the old one? The stated reason is to allow the region to be refreshed, but as readers of my blog will know, there are already ways of doing that, as demonstrated in this region plugin that I released in 2012.

In order to make the region refreshable, the APEX team had to change the way you use it. You no longer call the "HTP" package to add your content to a global output buffer. Instead, you have to concatenate and return your content as a string (varchar2 or clob). The way it's suggested in the online help is like this:

There are several things I don't like about this:

• You have to declare a local variable
• You have to clutter the code with concatenation
• You cannot split your code into subroutines unless you pass around your local variable holding the final result
  
• You have to rewrite every piece of existing code (if you want to use the new region type)
  

Instead, I want to be able to (continue to) write code like this:

Notice the use of "apex_htp" in the above code; this is a package that does not (yet) exist, but read on!

Or, to give a more complex and real-world example which uses several subprocedures that all write out to the global buffer:

What I think the APEX team should have done

When the APEX team decided that they needed a new region type with a different architecture, instead of just throwing the existing region type into the murky "legacy" territory, they should have made the transition as smooth as possible, by offering a drop-in replacement to the existing HTP package.

I immediately suggested this when I saw the latest APEX version, and as I said on Twitter I think it's a strange decision to hide away the existing method as "legacy" when you are replacing it with a worse dev experience that is not yet fully baked.

To fix this, I've submitted an idea to the APEX Ideas app, to suggest that the APEX team include a new package in the next APEX version that can be used as a drop-in replacement for the HTP package. Using this package with existing code should be as easy as replacing "htp.p" with "apex_htp.p" and adding a single RETURN statement at the end of the region process code.

The spec for such a package could look like the below image. I've already made a working prototype for myself, calling it "xtp", and with a (temporary) synonym I can also call it using "apex_htp", but the point of this blog post (and the submitted idea) is to get this included in APEX itself, where it should have been from day 1 of APEX 22.2. Go and vote today for this idea if you are using PL/SQL Dynamic Content regions!

For those interested in the (just a quick prototype) package body, here it is on GitHub.

Ten years since Mythbusters: Stored Procedures Edition

Today it's exactly ten years since I published my blog post called Mythbusters: Stored Procedures Edition. 

Using "stored procedures" (ie storing and executing code in your database, next to the data, instead of in a middle tier or client) is a practice that is provably both efficient and secure, yet it is often dismissed as "wrong".

My original blog post looks at the usual reasons given against the use of stored procedures for "business logic" (or for anything at all, really) and explains why they tend to be myths (or outright lies), repeated so many times that they are taken as the truth. Take a moment to read the article now.

Publishing the article proved that advocating for the use of stored procedures, which had gone out of fashion already by 2011, was controversial. It caused an immediate spike in my blog traffic, receiving almost 20 000 page views in a single day (at the time, my blog had perhaps a few hundred page views per day on average). The article was picked up by Hacker News and generated a lively discussion there.

So, where are we in 2021, ten years later?

Mainstream development (still) uses imperative, object-oriented 3GL languages (Java, .NET, etc) running in the middle tier or client, dragging data out from the database to process it, with data access usually abstracted through an ORM. Which, predictably, results in suboptimal performance.

The myths about stored procedures are still prevalent. Ask a random Java/.NET developer today, and I bet he will claim that it's impossible to version control stored procedures (myth #1). In that case, please send him a link to that 10-year old article...

 

 

New version of Quick PL/SQL, a code generator for PL/SQL

I've released a new version of Quick PL/SQL, the code generator for PL/SQL based on the same concept as QuickSQL in APEX, but for PL/SQL code. The initial version was released in August 2018.

The online version is available (as before) here: tinyurl.com/quickplsql2

The source code is available on my GitHub.

This release (version 1.2) has a few improvements. The first is syntax highlighting of the generated code (right hand side of the page). This relies on the CodeMirror library which is bundled with APEX. Unfortunately it only supports SQL, not PL/SQL, so not all code is properly highlighted, but it's better than nothing.

The second improvement is a "Quick CRUD" button which opens a dialog that allows you to specify a table name (and optionally the name of the primary key column and the package name):

Based on this it creates the markup required for a complete "CRUD package" in the Input section, which saves you from remembering the markup syntax, and saves you from typing a lot of boilerplate markup as well.

Then, as before, just click on "Generate" to have the final PL/SQL generated for you.

Enjoy! :-)

Quick PL/SQL, a code generator for PL/SQL based on simple markup

Maybe you've heard about "Quick SQL", a utility (previously a separate application, now part of APEX 18.1 itself) that generates SQL scripts based on a simple markup language. Quick SQL is a real time-saver and allows you to go from idea to prototype to working application in an instant.

Inspired by Quick SQL, I've created a similar utility, called "Quick PL/SQL", that does the same thing, except it generates PL/SQL code (packages with functions and procedures, with standard comment blocks, formatting, etc.) based on a simple markup.

I've recorded a couple of videos to show the tool in action.

Here is an introduction which explains the basic input syntax and the output options:

And here is another video which explains how to create standard CRUD-style (Create, Read, Update, Delete) APIs for your tables:

Try it out for yourself here: https://apex.oracle.com/pls/apex/f?p=QUICKPLSQL:HOME&c=MULEDEV or via this shortcut: https://tinyurl.com/quickplsql2

Of course, I wouldn't mind if this functionality becomes available as part of the QuickSQL utility that is built into APEX! :-)

Thoughts about the APEX_UTIL package

The APEX_UTIL package in Oracle Application Express is a mixed bag of "miscellaneous" stuff.

From the documentation: "You can use the APEX_UTIL package to get and set session state, get files, check authorizations for users, reset different states for users, get and purge cache information and also to get and set preferences for users."

It's clear that, over time, a lot of stuff has been placed here that really belongs elsewhere, or in its own package. By splitting up this package, it should be easier for the developer to find the functions and procedures he is looking for.

Here are my thoughts on how the APEX_UTIL package could be refactored. I'm not saying remove all this from the package in the next APEX release, but move the code to new/other packages and just leave wrappers in the APEX_UTIL package that call the new package, so that old code continues to work. Mark these as deprecated in the docs, and maybe remove after a few more releases.

Move to a new APEX_CACHE package:

  CACHE_GET_DATE_OF_PAGE_CACHE Function
  CACHE_GET_DATE_OF_REGION_CACHE Function
  CACHE_PURGE_BY_APPLICATION Procedure
  CACHE_PURGE_BY_PAGE Procedure
  CACHE_PURGE_STALE Procedure
  CLEAR_APP_CACHE Procedure
  CLEAR_PAGE_CACHE Procedure
  CLEAR_USER_CACHE Procedure
  PURGE_REGIONS_BY_APP Procedure
  PURGE_REGIONS_BY_NAME Procedure
  PURGE_REGIONS_BY_PAGE Procedure
 
Move to a new APEX_USER package:

  CHANGE_CURRENT_USER_PW Procedure
  CHANGE_PASSWORD_ON_FIRST_USE Function
  CREATE_USER Procedure
  CREATE_USER_GROUP Procedure
  CURRENT_USER_IN_GROUP Function
  DELETE_USER_GROUP Procedure Signature 1
  DELETE_USER_GROUP Procedure Signature 2
  EDIT_USER Procedure
  END_USER_ACCOUNT_DAYS_LEFT Function
  EXPIRE_END_USER_ACCOUNT Procedure
  EXPIRE_WORKSPACE_ACCOUNT Procedure
  EXPORT_USERS Procedure
  FETCH_USER Procedure Signature 1
  FETCH_USER Procedure Signature 2
  FETCH_USER Procedure Signature 3
  GET_FIRST_NAME Function
  GET_LAST_NAME Function
  GET_USER_ID Function
  GET_USER_ROLES Function
  GET_USERNAME Function
  GET_EMAIL Function
  SET_USERNAME Procedure
  SET_EMAIL Procedure
  SET_FIRST_NAME Procedure
  SET_LAST_NAME Procedure
  LOCK_ACCOUNT Procedure
  PASSWORD_FIRST_USE_OCCURRED Function
  UNEXPIRE_END_USER_ACCOUNT Procedure
  UNEXPIRE_WORKSPACE_ACCOUNT Procedure
  UNLOCK_ACCOUNT Procedure
  WORKSPACE_ACCOUNT_DAYS_LEFT Function
  STRONG_PASSWORD_CHECK Procedure
  STRONG_PASSWORD_VALIDATION Function
  GET_ACCOUNT_LOCKED_STATUS Function
  REMOVE_USER Procedure
  RESET_AUTHORIZATIONS Procedure [DEPRECATED]
  PUBLIC_CHECK_AUTHORIZATION Function [DEPRECATED]
  RESET_PASSWORD Procedure
  RESET_PW Procedure
  GET_AUTHENTICATION_RESULT Function
  SET_AUTHENTICATION_RESULT Procedure
  SET_CUSTOM_AUTH_STATUS Procedure
  GET_GROUPS_USER_BELONGS_TO Function
  GET_GROUP_ID Function
  GET_GROUP_NAME Function
  SET_GROUP_GROUP_GRANTS Procedure
  SET_GROUP_USER_GRANTS Procedure
  IS_LOGIN_PASSWORD_VALID Function
  IS_USERNAME_UNIQUE Function
  GET_ATTRIBUTE Function
  SET_ATTRIBUTE Procedure
 
Move to a new APEX_PRINT package:

  DOWNLOAD_PRINT_DOCUMENT Procedure Signature 1
  DOWNLOAD_PRINT_DOCUMENT Procedure Signature 2
  DOWNLOAD_PRINT_DOCUMENT Procedure Signature 3
  DOWNLOAD_PRINT_DOCUMENT Procedure Signature 4
  GET_PRINT_DOCUMENT Function Signature 1
  GET_PRINT_DOCUMENT Function Signature 2
  GET_PRINT_DOCUMENT Function Signature 3
  GET_PRINT_DOCUMENT Function Signature 4
 
Move to APEX_IR package (this has already been done):

  IR_CLEAR Procedure [DEPRECATED]
  IR_DELETE_REPORT Procedure [DEPRECATED]
  IR_DELETE_SUBSCRIPTION Procedure [DEPRECATED]
  IR_FILTER Procedure [DEPRECATED]
  IR_RESET Procedure [DEPRECATED]

Move to APEX_SESSION package:

  GET_SESSION_LANG Function
  GET_SESSION_STATE Function
  GET_SESSION_TERRITORY Function
  GET_SESSION_TIME_ZONE Function
  SET_SESSION_HIGH_CONTRAST_OFF Procedure
  SET_SESSION_HIGH_CONTRAST_ON Procedure
  SET_SESSION_LANG Procedure
  SET_SESSION_LIFETIME_SECONDS Procedure
  SET_SESSION_MAX_IDLE_SECONDS Procedure
  SET_SESSION_SCREEN_READER_OFF Procedure
  SET_SESSION_SCREEN_READER_ON Procedure
  SET_SESSION_STATE Procedure
  SET_SESSION_TERRITORY Procedure
  SET_SESSION_TIME_ZONE Procedure
  GET_EDITION Function
  SET_EDITION Procedure
  SET_SECURITY_GROUP_ID Procedure
  FETCH_APP_ITEM Function
  GET_NUMERIC_SESSION_STATE Function
  GET_CURRENT_USER_ID Function
  IS_HIGH_CONTRAST_SESSION Function
  IS_HIGH_CONTRAST_SESSION_YN Function
  IS_SCREEN_READER_SESSION Function
  IS_SCREEN_READER_SESSION_YN Function
  SET_CURRENT_THEME_STYLE Procedure [DEPRECATED]
  GET_DEFAULT_SCHEMA Function
  SET_WORKSPACE_Procedure

Move to APEX_EXPORT package:

  GET_SUPPORTING_OBJECT_SCRIPT Function
  GET_SUPPORTING_OBJECT_SCRIPT Procedure
 
Move to APEX_PAGE package: 

  GET_HIGH_CONTRAST_MODE_TOGGLE Function
  GET_SCREEN_READER_MODE_TOGGLE Function
  SHOW_HIGH_CONTRAST_MODE_TOGGLE Procedure
  SHOW_SCREEN_READER_MODE_TOGGLE Procedure

Leave in APEX_UTIL (for now):

  CLOSE_OPEN_DB_LINKS Procedure
  COUNT_CLICK Procedure
  CUSTOM_CALENDAR Procedure
  INCREMENT_CALENDAR Procedure
  SUBMIT_FEEDBACK Procedure
  SUBMIT_FEEDBACK_FOLLOWUP Procedure
  GET_FEEDBACK_FOLLOW_UP Function
  STRING_TO_TABLE Function [DEPRECATED]
  TABLE_TO_STRING Function [DEPRECATED]
  SAVEKEY_NUM Function
  SAVEKEY_VC2 Function
  KEYVAL_NUM Function
  KEYVAL_VC2 Function
  HOST_URL Function
  URL_ENCODE Function
  REDIRECT_URL Procedure
  PREPARE_URL Function
  GET_HASH Function
  GET_SINCE Function
  GET_PREFERENCE Function
  SET_PREFERENCE Procedure
  REMOVE_PREFERENCE Procedure
  REMOVE_SORT_PREFERENCES Procedure
  GET_BUILD_OPTION_STATUS Function Signature 1
  GET_BUILD_OPTION_STATUS Function Signature 2
  SET_BUILD_OPTION_STATUS Procedure
  GET_BLOB_FILE_SRC Function
  GET_FILE Procedure
  GET_FILE_ID Function
  HTML_PCT_GRAPH_MASK Function
  FIND_SECURITY_GROUP_ID Function
  FIND_WORKSPACE Function
  GET_APPLICATION_STATUS Function
  GET_GLOBAL_NOTIFICATION Function
  SET_GLOBAL_NOTIFICATION Procedure
  SET_APP_BUILD_STATUS Procedure
  SET_APPLICATION_STATUS Procedure
 

These are just my suggestions, but I do hope the APEX developer team will do some kind of refactoring (even if not exactly as suggested above) in future releases of APEX.

If you would like to see it done differently, please leave feedback in the comment field below! :-)

Using the Slack webhook API from PL/SQL

Slack is a web-based chat room popular with many companies. Slack also has an API that can be used to post messages to a given "channel" or chat room. The simplest API offered is the "webhook" integration, which is "a simple way to post messages from external sources into Slack. They make use of normal HTTP requests with a JSON payload that includes the message text and some options."

To create a new Slack webhook, login to Slack and go to "Apps" in the left sidebar menu. Click "Manage apps..." and click "Custom Integrations" and then "Incoming Webhooks". Click on "Add Configuration" to create a new webhook. Specify the channel the webhook will post in, and click "Add incoming webhooks integration".

 

Next, take a note of the Webhook URL:

Then go to the Alexandria PL/SQL Utility Library and install the SLACK_UTIL_PKG package in your database schema. Modify your database Network ACL settings to include "hooks.slack.com" (port 443) to allow connections to the Slack site from the database.

Then use the package like this (see also demo script):

In addition to application-specific messages you could perhaps also set up the database (via a background job) to notify you when you are about to run out of disk space, when the number of APEX page views reach a certain threshold, when a specific user logs in, and so on. Use your imagination! :-)

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:



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

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:

http://return_url?token=EC-60U79048BN7819909&PayerID=7E7KGXCWTTMU2

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:

http://servername/payments/confirm/1234?token=EC-60U79048BN7819909&PayerID=7E7KGXCWTTMU2

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

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": [
                "s3:PutObject"
            ],
            "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.

My Oracle Database Developer Choice Awards 2015

Earlier this week, Santa's little helper (ie DHL) knocked on my door and delivered a package containing my trophies from the Oracle Database Developer Choice Awards 2015. I was nominated in three categories, and won awards in two of them: Application Express and ORDS.




Here's a video from the award ceremony at Oracle OpenWorld 2015:

I'd like to send a big THANK YOU to everyone who voted for me, and also to Oracle for organizing the awards and handing out such cool trophies! :-)

Sending SMS text messages from PL/SQL

Do you need to send SMS (Short Message Service) text messages from your Oracle database using PL/SQL? This is actually quite easy to do, the only hard part is deciding on which SMS gateway to use. You need to sign up with a gateway provider to get a username and password to use the gateway, and you typically pay some cents for each message you send.

Do a google search for "sms gateway api" to find a suitable gateway provider. I won't provide any specific recommendations or endorsements; for this blog post I just picked a random provider to use in the code example below. I have no idea if they offer a good/cheap/reliable service or not. (Leave a comment below if you have any recommendations that you would like to share.)

Most gateways offer an API that takes a HTTP(S) GET or POST request with some parameters in the query string to specify the recipient and the message itself, and then returns some kind of status back in some format (XML, JSON, plain text). The concept is usually the same, but the specifics vary.

I've added a new package called SMS_UTIL_PKG to the Alexandria PL/SQL Utility Library that provides a generic interface to any SMS gateway.

To use the package, you need to check the API documentation of your chosen gateway provider, and set up the URL template accordingly. You can use tags like #username# and #password# and #message#, etc. in the template, which will be replaced with actual values when sending a message.

Here is an example of using the package to set up a gateway and send a message:

There's also support for adding a callback to a custom error handler that you can use to determine if the gateway returns an error message. This should be a function that accepts a clob as an input parameter (this will contain the response message from the gateway) and returns a varchar2 with an error message, or return null if successful.

Remember that you might have to modify your database Network ACL settings to open traffic to the gateway's hostname. And if the SMS gateway requires HTTPS, you need to set up an Oracle wallet with the certificate and call the set_wallet procedure before you send the message.

Happy texting! :-)

My nominations for the Oracle Database Developer Choice Awards 2015

I'm honored to have been nominated for the Oracle Database Developer Choice Awards 2015, in no less than three (!) categories: PL/SQL, Application Express (Apex) and Oracle REST Data Services (ORDS).

Here's a short video that explains what the Oracle Database Developer Choice Awards are all about:

If you are a regular reader of my blog, you are probably familiar with my contributions to the Oracle Database Developer community, but here is a short recap of my work in the relevant categories:

• PL/SQL: I've been programming in PL/SQL for almost 20 years. I've written and released as open source the widely-acclaimed Alexandria PL/SQL Utility Library, which contains around 50 PL/SQL packages that deal with everything from general math and string utilities to Amazon web service integration, NTLM authentication, CSV parsing, MS Exchange integration, and much more. Jeffrey Kemp has a great guided tour of the library which highlights some of the packages. As of February 2015, the library had been downloaded more than 12,000 times. Furthermore, I've created a presentation called PL/SQL: The Good Parts, which in Steven Feuerstein's words is a "fast, entertaining high-level glimpse of what makes PL/SQL so great" (and Steven should know what he's talking about! :-).
• Apex: Since 2008 I've published numerous articles related to Oracle Application Express on this blog. Among other things, I've created popular Apex plugins, including a pivot table plugin and a plugin for generating on-demand dynamic PL/SQL content. I've created and open-sourced the jQGrid Integration Kit for PL/SQL. And I created ApexGen, a framework for generating Apex apps using PL/SQL code (which I may revisit and enhance in the future if the Apex team provides a supported API for it). I've also been active in the Apex community by asking and answering questions on the Apex forum on OTN, actively testing Apex early adopter (EA) releases for Apex 4 and 5, and reporting numerous technical issues and bugs back to the Apex development team. My coworkers know me as "the Apex guy" since I constantly promote the virtues of Apex for new development projects.
• ORDS: I've long been interested in the technology that connects the Oracle Database to the web, including the PL/SQL Web Toolkit (aka OWA) and the forerunners of ORDS such as mod_plsql and the Embedded PL/SQL Gateway. Using this knowledge I implemented and released as open source a mod_plsql alternative for Microsoft Internet Information Server (IIS) called the Thoth Gateway. I've published many articles on the use of JSON from PL/SQL, a key part of REST. Earlier this year I wrote a four-part blog post series with all the nitty-gritty details on installing and running Apex with ORDS that became very popular and widely distributed.

Now go and vote for me! :-)

Click here to vote in the PL/SQL category, here to vote in the Apex category, and here to vote in the ORDS category.

Thank you very much for your vote! :-)

Image credit.

PL/SQL: The Good Parts

Somewhat inspired by Douglas Crockford's Javascript: The Good Parts presentation (and book), I have created a presentation called PL/SQL: The Good Parts.

This is not intended as an introduction to PL/SQL, but rather an overview of features you should consider if you want to maximize your enjoyment of programming in the Oracle Database. Also, this is not an exhaustive list of all features, it's simply the ones I personally use frequently.

Note that unlike Javascript, I don't consider PL/SQL to have too many "bad parts" or bizarre quirks. There's a lot of good stuff, hence the title! :-)

Click here to view the presentation.

ORA-22926 when using getClobVal to convert XMLType to CLOB

I ran into a problem the other day when moving some code from one database to another (both XE 11g). The code in question needs to convert an XMLType to a CLOB to do some (hacky) string manipulation on it, and then turn it back to an XMLType.

Here's the original code:

  l_xml := apex_web_service.make_request(...);
 
  -- little hack to remove bad empty namespace from result
  l_clob := l_xml.getClobVal();
  l_clob := replace (l_clob, '<mytag xmlns="">', '<mytag>');
  -- and then strip out bogus namespace to make parsing easier...
  l_clob := replace (l_clob, ' xmlns="http://tempuri.org/"', '');
 
  l_xml := xmltype (l_clob);

The above had been working before, but now failed with "ORA-22926: specified trim length is greater than current LOB value's length".

Googling turned up a forum post which pointed out that getClobVal is deprecated, and the suggestion is to use XMLSerialize instead.

This can only be used in a SQL statement, not as a PL/SQL expression, so I had to rewrite as follows:

select xmlserialize(document l_xml as clob)
into l_clob
from dual;

And the error went away... (It seems some people have had problems even with the xmlserialize function, but that's quite an old post, so that particular bug could have been fixed a long time ago.)

Longer names coming to Oracle?

Have you ever, like me, been silently finger-counting the number of characters in a table name, column name or procedure name that you are about to create in the Oracle Database, to make sure it is short enough? It appears those days will soon (?) be at an end...

The current limit on names (identifiers such as table names, column names, procedure names, parameter names, etc) in Oracle SQL and PL/SQL is 30 characters. Here is what you see when you do a describe of the the user_tab_columns dictionary view in Oracle 11g:

But what do we see if we do the same on an Oracle 12c database? The database on apex.oracle.com was upgraded to 12c some time ago, so let's try the same there:

Interesting...! Looks like the new limit will be 128 characters. All the relevant views such as user_arguments, user_identifiers and user_objects have been likewise altered.

Mind you, as of version 12.1.0.2.0, it's still not possible to actually create objects with long names:

But it appears that the preparations have been done to allow longer names, so I'm guessing this will become possible in the next version (12.2?).

The world moves forward... it will be great to stop worrying about short names (but let's not go crazy and turn our PL/SQL into the equivalent of this silliness !).

Alexandria PL/SQL Utility Library moved to GitHub

The PL/SQL Utility Library, codenamed "Alexandria", now has a new home at GitHub. This will make it easier to collaborate on the project. If you want to contribute, just fork and submit a pull request.

The liibrary contains around 50 PL/SQL packages as well as a few types. There are no table dependencies, which reflects the general-purpose nature of the utilities. Jeffrey Kemp has a great guided tour of the library which highlights some of the packages.

Before the move to GitHub, the latest version (1.7.0) had been downloaded almost 7,000 times, and all versions a total of 12,000 times. So I guess it's being pretty widely used...

You can download, clone or fork the library at https://github.com/mortenbra/alexandria-plsql-utils

The APEX_JSON package: Generating JSON from PL/SQL

In my previous blog post, I took a look at the new APEX_JSON package that ships with Apex 5.0 and its capabilities for parsing JSON.

In this blog post, I am going to look at how the APEX_JSON package can be used to generate JSON from data in your database using PL/SQL.

There are multiple ways of creating JSON output using the APEX_JSON package. There are several overloaded write() procedures which can output simple types (like varchars, numbers, and dates), as well as those that print complex types (such as xmltype and ref cursors).

First, a simple example which prints a simple JSON structure with some hard-coded values combined with a value based on user input. The page setup in the Apex Page Designer looks like this:

Note the call to apex_json.initialize_output() which disables the automatic sending of JSON headers in the HTTP response. This is normally desirable when you are sending (just) JSON back to the client, but in my test application I want to output the JSON inside a normal HTML page, so I need to disable the JSON headers. The rest of the code consists of simple calls to open/close_object and write() to write the name/value pairs.

The output looks like this:

Next up is the very useful ability to generate JSON based on a ref cursor. This means that basically any SQL query, dynamic or static, can be transformed into JSON output, with just a single line of code. In my simple test application I've set up a PL/SQL region that opens a ref cursor based on user input, and then passes the cursor to the write() procedure.
NOTE: Using dynamic SQL based on unsanitized user input makes your application vulnerable to SQL injection. If possible, you should always use static SQL instead of dynamic SQL. Also, never trust user input and always validate it (see, for example, the dbms_assert package) before using it in a dynamic SQL statement. The example code below does not follow these security best practices, in order to keep the example simple.

Here's the result in the running application. Note that the output even supports nested queries (via the CURSOR statement), which makes it possible to generate complex/nested JSON from a single SQL statement.

Lastly, the APEX_JSON package also supports output of JSON via XML, by passing an xmltype value to the write() procedure. This is very useful in itself, but can also be combined with other PL/SQL features, such as the ability to convert any database object type to XML via the XMLType constructor.

Consider this example, where I create a database object type called T_CAR, with multiple attributes. The example SQL statement shows I can instantiate a T_CAR object and then convert the object into XML.

I can then pass the resulting XML into the write() procedure of the APEX_JSON package to generate a JSON representation of the T_CAR object type.

Here's the output:

This quick look at the APEX_JSON package from Apex 5.0 shows that this new package gives developers a lot of power and flexibility in terms of parsing and generating JSON from PL/SQL.