Apex Plugin: Execute PL/SQL code and return content to page

| Nov 6, 2012
Here is a Dynamic Action plugin for Oracle Application Express (Apex) that lets you execute PL/SQL code in the database, and then send output generated on the server (via calls to the "HTP.P" procedure) back to any element on the web page.

A more specific use case would be to respond to the clicking of a button by generating a fragment of HTML using PL/SQL, and then refresh a DIV on the page with that dynamically generated content. See the screenshot below for an example; notice that part of the first region has been updated with content generated on the server (more...)

Database (schema) backup to the cloud with PL/SQL

| Aug 13, 2012
In my last blog post I described using the DBMS_DATAPUMP and DBMS_JOB packages to automate database schema backups from the database itself, rather than using batch files to do so. I also mentioned that "since the files are written to the database server itself, you need some kind of process to move the files to another server or storage location, so the backups don't disappear along with the database if the database server itself is somehow lost or corrupted. [One] option is to upload the backup file from the database to some online ("cloud") storage service, such as Amazon S3. (more...)

Simple database (schema) backup using PL/SQL

| Jul 23, 2012
Usually, database backups are the responsibility of DBAs, and not something that developers really care or think too much about. However, if you are a lone developer, or part of a small team that doesn't have the luxury of having a dedicated DBA, then you might have to deal with backups yourself.

There are several ways to make Oracle backups; what I will be concentrating on here is the "Data Pump Export" method. You may already be familiar with the command-line "expdp" command which allows you to create a dump (.dmp) file containing your database objects (schemas, tables, procedures, (more...)

MS Exchange API for PL/SQL

| May 26, 2012
As mentioned in my earlier post, I've been working on a PL/SQL wrapper for the Microsoft Exchange Web Services (EWS) API. The code is now ready for an initial release!


Using this pure PL/SQL package, you will be able not just to search for and retrieve emails and download attachments, but you will also be able to create emails and upload attachments to existing emails. You can move emails between folders, and delete emails. You can read and create calendar items. You can get the email addresses of the people in a distribution (mailing) list, and more.


A friend in need: Flashback Query

| May 25, 2012

"It is sayd, that at the nede the frende is knowen."
- William Caxton: "Sonnes of Aymon" (1489)
"I say, that when you screw up, Flashback Query is a friend indeed!"
- Morten Braten: "SQLs of Oracle" (2012)

The Flashback Query feature of Oracle (10g) can be really helpful when you run that UPDATE or DELETE statement and then, after the COMMIT, you discover that you made a mistake!

Wouldn't it be nice to be able to look back and see what the data looked like before you messed things up? With a Flashback Query you (more...)

jQGrid Integration Kit for PL/SQL and Apex version 1.2 available

| May 21, 2012
It's been over two years since I released the initial version of the jQGrid Integration Kit for PL/SQL and Apex.

Since then, I've added some additional features and fixed a few bugs:

Version 1.1

  • Procedure include_static_files_once will make sure required JS and CSS files are correctly included
  • Support for "popup_lov" column edit type
  • Procedure render_grid supports p_read_only parameter that can be used to set read-only dynamically
  • Support for some jQGrid built-in formatters based on Oracle column data type
  • Lists of values (select list variety, not Popup LOV) now support bind variables and Static LOVs
  • Date parser handles dates and (more...)

Select * from Inbox

| May 1, 2012
... or how to read your (Microsoft Exchange) email using SQL and PL/SQL.


You are probably familiar with sending email using PL/SQL; there's the UTL_SMTP package, the APEX_MAIL package, and various other utilities that do this.

But what about reading email using PL/SQL? You need to investigate the POP and IMAP protocols. A quick search on Google turns up some sample code and even a feature-rich package, although the latter requires Java in the database (which I try to avoid whenever possible, and which is not available in Oracle XE in any case).

Approaching this from a different angle, (more...)

Interesting PL/SQL and Apex links, April 2012

| Apr 21, 2012
Here's a bunch of interesting stuff related to PL/SQL and Apex that I've come across lately, I'm posting it both as a reminder to myself, and to help spread the word:

  • First mention of PL/SQL enhancements for Oracle Database 12c: In his PL/SQL newsletter for March 2012, Steven Feuerstein revealed that "I've also started beta testing Oracle Database 12c PL/SQL. I can't yet share with you any of the new features, but I can tell you that PL/SQL will certainly be a richer, more usable language when 12c goes production!".
  • On that topic, a couple of things I (more...)