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