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.

Sunday, June 19, 2016

Blog retrospective

Not long ago I suddenly realized I had published my 100th blog post, so I figure it's time to look back and write down some thoughts about the blog.

I started this blog in 2008, so it has taken me all of 8 years to produce one hundred posts. Not exactly a huge output when compared to many other bloggers, but at least the pace has been reasonably consistent over the years, and hopefully most of the posts have been useful. So why am I bothering with blogging at all? Primarily, it's a good way to document and have easy access to information that I find useful in my own work, but I also want to contribute to the community and help other people be successful in their work.

Back in 2008, I had been working with Oracle and PL/SQL for about 10 years already, but was just getting started with APEX. One of my first posts shows that we are on APEX 3.1 and we are getting to grips with the differences between DBMS_EPG and mod_plsql. It seems that none other than Joel Kallmann himself was the first ever to post a comment on my blog, what a flying start! :-) (And by the way, it was Joel who told me just last year that one is supposed to write "APEX" in uppercase, not "Apex" like I have done for the first 7 years on this blog...! As you can see, I'm now firmly in the uppercase APEX camp... :-)

I have always been a believer in the "fat database" paradigm and in 2009, I wrote some posts about it. The same year, I released my open source project, the Thoth Gateway, a replacement for mod_plsql that is written in C# and runs on Microsoft Internet Information Server (IIS) as an ASP.NET web application. To create it, I had to dive deep into the internals of the PL/SQL Web Toolkit (OWA). It's really interesting to see all the amazing stuff in APEX that has been built on top of the rather humble foundations of OWA.

In 2010, I started looking into the use of JSON from PL/SQL, and I also published some utilities to parse CSV and return it via a pipelined function. I've had lots of use for that in the years after.

2011 was another productive year, as I launched the PL/SQL Utility Library, soon codenamed Alexandria. (With names like Thoth Gateway and Alexandria Library, it's no big secret that I enjoy studying ancient Egyptian history!) Another important milestone in 2011 was the PL/SQL API for Amazon S3, another package that I have had good use of a number of times since then.

In 2012 I released a PL/SQL API for MS Exchange, which was fun to write but I actually haven't had much use for this myself so far.

In 2013 the most popular post was the one about Oracle 12c XE, or rather the first mentions of this as-yet mythical creature. Based on the hints dropped by Oracle, this will be released some time after Oracle 12.2, so I believe it's still a couple of years to wait.

In 2014 I looked into the JSON capabilities of APEX 5.0, and I also updated the Thoth Gateway to use the ODP.NET Managed Driver to simplify installation.

In 2015, I wrote a four-part series on how to install Oracle XE, ORDS and APEX on Linux. As well as learning myself a lot about Linux, I think it helped a lot of people judging by the great feedback I got on this series.

I'll finish this retrospective with a look at some statistics. For some reason, Blogger only has statistics going back to May 2010, even though I started by blog in March 2008. Anyway, below is a chart showing the page views per month. It shows a slow rise from a couple of thousand page views per month, up to the current average which is about 20,000 page views per month.

 But look at the anomaly in the chart: In August 2011, it shows 18,000 page views! And what's more, those 18,000 page views were in a single day, not a total for the month! So what happened back then? At the end of July, I wrote a post called "Mythbusters: Stored Procedures Edition". It was my attempt to refute all the usual arguments for why "stored procedures are bad", which is what every Java/.NET developer tries to convince you (and yet they struggle mightily with their ORMs). My post caused a heated debate on the blog itself, and also on Hacker News and on Reddit. I guess we could have the same discussion all over again today. But instead of arguing, let's just go out there and build some kick-ass web applications with PL/SQL and APEX. "The proof of the pudding is in the eating", after all! :-)

Tuesday, April 26, 2016

How to set up IIS as SSL proxy for utl_http in Oracle XE

If you want to call a web service over HTTPS from the utl_http or apex_web_service packages in PL/SQL, you need to set up an Oracle Wallet that contains the SSL certificates of the server you are connecting to from the database.

Setting up an Oracle Wallet is quite straightforward, but it can be a bit of a hassle to configure a large number of certificates. Also, if you are using Oracle Express Edition (XE) which gets very infrequent updates, you are stuck with whatever SSL protocol support was in the database at the time of release.

One solution is to set up an outgoing proxy in a local web server. The database will communicate with the web server via plain HTTP, without any need for SSL certificates, and the web server translates the requests to HTTPS. This is fine from a security point of view as long as the database and the web server are on the same machine or on the same internal network. Traffic from the web server to the outside world is encrypted using SSL, while the internal traffic between the web server and the database remains unencrypted.

Richard Martens wrote a blog post about this and showed how to set up the proxy on Apache. In this blog post I will show you the equivalent setup on Microsoft Internet Information Server (IIS).

Step 1: Installation

First you need to download and install the following IIS extensions from Microsoft:
Install these extensions as per Microsoft's documentation.

Step 2: Set up proxy website

Create a new website in IIS. The name of the website is not important, but in this example I will call it "MyProxy". You need to link the website to a physical location on disk, I use C:\temp\myproxy but again this does not really matter.

Then edit the website bindings to bind the site to a specific IP address and port. In this example, I choose 8888 as the port number. I also bind the site to the localhost IP address This ensures that the site can only accept connections coming from the local machine, and therefore assumes that my Oracle database is located on the same machine as the web server.

Step 3: Configure URL rewrite from HTTP to HTTPS

Follow the instructions in this blog post to enable Application Request Routing at the server level (short version: Click "Application Request Routing Cache" at the server level, choose "Server Proxy Settings" and then check the "Enable Proxy" checkbox).

Then go to the MyProxy website that you created in step 2, and open the "URL Rewrite" feature. Create a new rule called something like "Forward all requests as HTTPS".

Set up a rewrite rule as follows: Pattern myproxy/(.*?)/(.*) rewrites to https://{R:1}/{R:2}

This rule means that, for example, a request to would be rewritten to

Step 4: Test the proxy setup

You can now try out the proxy rewrite rule from a Powershell command prompt on the server. In the screenshot below you can see two regular requests (the direct HTTP request doesn't work as the remote server does not accept non-HTTPS requests), as well as a request to the local proxy which gets a successful response back from the remote server.

With that in place, we can now turn to the database.

Step 5: Adjust database network ACL

To be able to access the local proxy, we need to open this up using the database network ACL. A typical script to do this would look like this:

-- to be run by user SYS

alter session set nls_language = AMERICAN;
alter session set nls_territory = AMERICA;

    acl          => 'apex.xml',
    description  => 'Access Control List for APEX',
    principal    => 'APEX_050000',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

-- for https proxy
-- see
-- see

    acl         => 'apex.xml',
    host        => '',
    lower_port  => 8888,
    upper_port  => 8888);

Since we can now use this proxy for any HTTPS traffic, we only need this single entry in the network ACL. (If you want to narrow down the list of allowed remote sites, you could adjust the rewrite rule in step 3 so that it only works for specific sites instead of all sites.)

Step 6: Enjoy

You can now use the utl_http and apex_web_service packages from PL/SQL to call any HTTPS site. Just remember that you need to alter the original URL so it hits the proxy instead.

Thursday, January 14, 2016

ORA-01445 in Interactive Report when importing Apex 3.2 app into Apex 5.0

Just a quick note about an error I encountered when importing an old Apex 3.2 (!) app into Apex 5.0.2.

Using Apex 5.0.2, I have imported an application which ran fine under Apex 3.2, that had an interactive report with the following query:

select c001, c002, c003, c004, c005, c006, c007, c008, c009, c010,
  c011, c012, c013, c014, c015, c016, c017, c018, c019, c020,
  c021, c022, c023, c024, c025, c026, c027, c028, c029, c030,
  c031, c032, c033, c034, c035
from apex_collections
where collection_name = 'USER_QUERY'

The collection is populated by PL/SQL code before the query runs.

Now, in Apex 5.0.2, when I run the page, I get the following error:

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table 

Running the page in debug mode shows the following:

...Execute Statement: select 
  count(*) over () as apxws_row_cnt
 from (
select * from (select b.ROWID apxws_row_pk, b.* from (select * from (
select c001, c002, c003, c004, c005, c006, c007, c008, c009, c010,
  c011, c012, c013, c014, c015, c016, c017, c018, c019, c020,
  c021, c022, c023, c024, c025, c026, c027, c028, c029, c030,
  c031, c032, c033, c034, c035
from apex_collections
where collection_name = 'USER_QUERY'
) ) b) r
) r where rownum <= to_number(:APX~

Logging exception:
Sqlerrm: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
Backtrace: ORA-06512: at "SYS.DBMS_SYS_SQL", line 1325
ORA-06512: at "SYS.WWV_DBMS_SQL", line 464
ORA-06512: at "SYS.WWV_DBMS_SQL", line 475
ORA-06512: at "APEX_050000.WWV_FLOW_DYNAMIC_EXEC", line 416

Logging exception:
Sqlerrm: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
Backtrace: ORA-06512: at "SYS.DBMS_SYS_SQL", line 1325
ORA-06512: at "SYS.WWV_DBMS_SQL", line 464
ORA-06512: at "APEX_050000.WWV_FLOW_DYNAMIC_EXEC", line 461
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_STANDARD", line 471

Logging exception:
Sqlerrm: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
Backtrace: ORA-06512: at "SYS.DBMS_SYS_SQL", line 1325
ORA-06512: at "SYS.WWV_DBMS_SQL", line 464
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET", line 4277
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET", line 11471

And indeed, if I just isolate the inner part of the IR query that Apex generates, and run this in the SQL Workshop in Apex:

select b.ROWID apxws_row_pk, b.* from (select *  from (
select c001, c002, c003, c004, c005, c006, c007, c008, c009, c010,
  c011, c012, c013, c014, c015, c016, c017, c018, c019, c020,
  c021, c022, c023, c024, c025, c026, c027, c028, c029, c030,
  c031, c032, c033, c034, c035
from apex_collections
where collection_name = 'USER_QUERY'
) ) b

I get the same error:

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

I thought this was a bug, but then I checked the "Link Column" attribute of the Interactive Report. It was set to "Link to Single Row View" and the "Uniquely Identify Rows by" was set to "ROWID". That must be why Apex wraps my query with an outer query that adds the rowid, which then fails because my original query is not "key-preserved". (Funny that the single row view worked in Apex 3, but I guess the internal implementation of the outer query changed between versions.)

In my case I did not really need the single row view, so I just disabled it (set "Link Column" attribute to "Exclude Link Column"). Your case might be different, so you would have to rewrite the query or specify a unique key column instead of rowid.

Tuesday, January 12, 2016

Working with the Apex 5 treeview

Apex 5 includes a new treeview widget. I haven't been able to find good documentation on how to use the treeview. This blog post is an attempt to summarize what I have gleaned from the help text, forum posts, and experimentation, to make it easier for others.

Adding a treeview to a page

Let's start with the basics. The treeview is a built-in region component called "Tree". Simply drag it from the Regions palette to the content body of the page.

If you intend to manipulate the treeview using Javascript, you should assign the region a static ID (such as my-treeview).

You need to specify a query as the source for the treeview.

The treeview query

This sample query is taken from the help text, and shows you what the treeview query needs to look like in terms of columns. It uses the sample EMP table (usually installed in the sample SCOTT schema in the database, see for example this blog post if you need to recreate it).

Since a tree is used to display a hierarchy, we use the connect by clause in the query to link the item (employee) with the parent item (manager). connect_by_isleaf and level are built-in Oracle functions/pseudocolumns that can be used with queries that use the connect by clause.

 select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
        ename as title,
        'icon-tree-folder' as icon,
        empno as value,
        ename as tooltip,
        null as link
   from emp
  start with mgr is null
connect by prior empno = mgr
  order siblings by ename

For reference, here is a slightly more complex query taken from the "Sample Trees" packaged application.

select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
       label || ': ' || name as title,
         when item_type = 'P' then 'fa-file-text-o'
         when item_type = 'S' then 'fa-caret-square-o-right'
         when item_type = 'T' then 'fa-minus-square-o'
         else null
       end as icon,
       id as value,
         when tooltip is not null then name || ' - ' || tooltip || '% complete'
         else name
       end as tooltip,
       case when item_type = 'P' then
            when item_type = 'T' then
            when item_type = 'S' then
       end as link
 from ...

Custom icons

The fourth column of the treeview query is the icon, specified as a Font Awesome class name (or whatever other icon library you have included in your application). This can either be a static value hardcoded into the query, or you can use a case statement (or a PL/SQL function) to provide different icons depending on your data. The default is no icon.

NOTE: I can't get this to work in my own application. I can see the icon I specify being added to the page markup, but the icon itself does not become visible on the page. And yet I can see it working as expected in the "Sample Trees" packaged application. Obviously something must be different between the two apps, but I have not yet figured out what. Anyone else had any success with this? UPDATE 13.01.2015: For this to work with Font Awesome icons, you have to change the "Icon Type" attribute of the treeview from the default "a-Icon" to "fa".


The sixth column of the treeview query is the tooltip, ie the text that will be displayed when the user hovers over the node with the mouse. To enable tooltips, you also need to set the "Tooltip" attribute of the treeview region from "None" to "Database Column".

Collapsing and expanding all nodes

Create a button, add a dynamic action to it, and set the true action to "Expand Tree", then set the affected element to your treeview region. Create a similar button for the "Collapse Tree" dynamic action.

The link column

The seventh column of the treeview query is the link, ie what should happen when the user clicks on a node in the treeview. If you leave the link column set to null, nothing will happen when the user clicks a node, and you have to use Javascript to retrieve the underlying value of the selected node and do something with it (see below).

If you do construct a link in the query, it can be either an external URL (outside Apex), an Apex URL (in which case you should use either apex_util.prepare_url or apex_page.get_url to construct a valid link, I prefer the latter API since it is the newer of the two), or a Javascript URL (see example below).

See second query above for an example of Apex page links created via the query.

Example of Javascript link: If you don't want to navigate away from the page when the user clicks a node, you can use a Javascript link to call some Javascript function local to the page.

Modify the query above to

select ...,
  'javascript:DoSomethingWithSelectedNode(' || id || ');' as link,

Then add the following function to the "Function and Global Variable Declaration" attribute of your page:

function DoSomethingWithSelectedNode(nodevalue) {
  $s('P1_SELECTED_ID', nodevalue);

Create a hidden page item called P1_SELECTED_ID and set the "Value Protected" attribute to "No" to allow us to manipulate it via Javascript.

You can now add a dynamic action that triggers "on change" of the hidden page item. The hidden item will change when the user clicks a node in the treeview, and this will in turn trigger the dynamic action.

Activate links with single or double click

This is an attribute of the tree region. The default is "Single Click", but you might want to change this to "Double Click" to support more advanced interaction (with a single click being captured by a Javascript event handler and a double click used to navigate to a different page).

Getting the value of the selected node via Javascript

As described in this forum post, there are other ways of getting the selected node value (in addition to the Javascript link described above).

I will quote from the relevant parts of the answer:

"With the Apex 5 tree, you can use two functions to retrieve the current selection. First of all though, you'll want to assign a static region id so you can select the tree correctly. For example, I assigned mine the id "my-treeview".

The difference between the two methods is in what they return. One returns the actual elements in the DOM, the other will get you the datamodel nodes. The datamodel will allow much easier access to the data than the dom nodes would. It depends on what you want to do, obviously."

Code snippets:

// returns the actual elements in the DOM
apex.jQuery("#my-treeview div[role='tree']").treeView("getSelection")

// returns an array of nodes
apex.jQuery("#my-treeview div[role='tree']").treeView("getSelectedNodes")

// returns just the id of the first currently selected node
// multiselection is not something enabled by default, so it is safe to just get the first id
apex.jQuery("#my-treeview div[role='tree']").treeView("getSelectedNodes")[0].id

The screenshot below shows these function calls in action:

It is also possible to set up an event handler to trigger whenever a node is selected ("Thus you can avoid having to generate javascript calls in your SQL"), like this:

// setup event handler to handle selection of nodes
apex.jQuery("#my-treeview div[role='tree']").treeView("option", "selectionChange", function(e){  
  console.log("Selection changed, value of selected node is " + apex.jQuery(this).treeView("getSelectedNodes")[0].id); 

Screenshot below shows this in action:

Remembering the value of the selected node between page views

The "Selected Node Page Item" attribute is used to remember the value of the selected node between page views, so that the tree is always expanded and focused on the previously selected item when the user returns to the page that contains the treeview.

Here is what the help text says: "This item is used to save tree state, by holding the value of the last selected node. The value of the selected node can be saved to the selected item via the node link attribute, or via a page process. When the tree is reloaded, the tree is opened to the selected node value."

In other words, you put in the name of a page item in this attribute, and you need to make sure that the item contains the value of the selected node (this doesn't happen automatically).

One way to do this is as demonstrated in the second treeview query above, which sets the page item on the treeview page (P3_SELECTED_NODE in that example) in the link that navigates away to another page.

Alternatively, if you need to stay on the same page, you can use any of the Javascript methods described above to get the value of the selected node, and then use the $s() function to set the value of the page item. This item value is stored automatically in session state if you submit the page.

Finally, if you need the selected node value to be stored in session state immediately, you can use Ajax. Here's an example of code to put on the "On page load" attribute of the page:

apex.jQuery("#my-treeview div[role='tree']").treeView("option", "selectionChange", function(e){  
  console.log("Selection changed, value of selected node is " + apex.jQuery(this).treeView("getSelectedNodes")[0].id); 
  $s("P4_SELECTED_ID", apex.jQuery(this).treeView("getSelectedNodes")[0].id);

Then create a dynamic action that triggers on change of P4_SELECTED_ID, and the true action to "Execute PL/SQL Code". Set the PL/SQL code to "null;" (do nothing, basically) and put P4_SELECTED_ID in the "Page Items to Submit" attribute.

What this does is set the (server-side) session value of P4_SELECTED_ID via an Ajax call as soon as the value is changed (see screenshot below).

Remember to set P4_SELECTED_ID as the "Selected Node Page Item" attribute of the treeview region. You can now click anywhere in the treeview, then navigate away to another page (via any means), and when you return to the treeview, the previously selected item should be expanded and selected.


Friday, December 18, 2015

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

Wednesday, November 25, 2015

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, unzipped it, and placed it under c:\program files\sqldeveloper-\. 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-\sqldeveloper\jdk\jre\bin\msvcr100.dll.

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

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