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.

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:

• Available for both Windows and Linux at the OTN website
• Easy to install (InstallShield on Windows, rpm package on Linux), only takes a few minutes
• Lightweight, requires very little resources to run
• Great performance
• Comes preinstalled with APEX on it
• It's free for any usage, including production applications

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

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:

openModal('my_inline_dialog');


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:

closeModal();

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:

• Bruce Clark on (Inline) Dialogs
• Rodrigo Mesquita on Modal Window with Universal Theme

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.

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

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:

• URL Rewrite Module 2.0
• Application Request Routing 3.0

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 127.0.0.1. 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 http://127.0.0.1:8888/myproxy/example.com/foo/bar would be rewritten to https://example.com/foo/bar


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;

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'apex.xml',
    description  => 'Access Control List for APEX',
    principal    => 'APEX_050000',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
  COMMIT;
END;
/

-- for https proxy
-- see http://blog.rhjmartens.nl/2015/07/making-https-webservice-requests-from.html
-- see http://ora-00001.blogspot.com/2016/04/how-to-set-up-iis-as-ssl-proxy-for-utl-http-in-oracle-xe.html

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'apex.xml',
    host        => '127.0.0.1',
    lower_port  => 8888,
    upper_port  => 8888);
  COMMIT;
END;
/

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.

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 

  apxws_row_pk,

  "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",

  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.