Accepting PayPal Payments with your Oracle Cloud Database Service

If you haven't signed up for an Oracle Database Service on the Oracle Cloud, you should do so right now.

I wrote a white paper called "Integrating Application Express with PayPal PaymentsPro" about six years ago and also made available a corresponding application for download on OTN. The application still works as long as you configure it properly by supplying the right substitutions in the application properties, most notably your PayPal API developer username, password and signature.

My colleague Rick Greenwald let me know about an Oracle Cloud customer that was trying to integrate PayPal in an application in their database service. They had seen the white paper I had written years ago but there are two requirements of the solution described in the paper that simply won't work on the cloud:

1. You must be able to use UTL_HTTP with a wallet
2. You must define a PL/SQL procedure and grant access to public on it

The reason for the public procedure is that the Classic PayPal API (formerly known as the NVP API) requires a redirect URL, which Pay Pal appends a token parameter and a PayerID parameter. The public procedure had two parameters of exactly the same name. It occurred to me that what might work on the Oracle Cloud would be to create a RESTful service that would accept those parameters with exactly the same names. With help from another colleague, Colm Divilly, I was able to create such a RESTful resource template with a get handler.

It also occurred to me that instead of using UTL_HTTP directly you could simply use the APEX_WEB_SERVICE API, specifically apex_web_service.make_rest_request. This package can be freely used on the Oracle Cloud, and it supports HTTPS calls through an instance wallet.

Below is a cookbook for modifying the existing PayPal integration sample application and using it on the Oracle Cloud. The application uses the Classic API, so look for a white paper by Rick Greenwald on how to use the updated PayPal RESTful API and OAuth2. (A much more modern solution.)

1. Sign up and activate a database service on the Oracle Cloud
2. Sign up for a PayPal developer account
3. Follow the instructions to create sandbox accounts you can use to test transactions
4. Login to your database service, click SQL Workshop, RESTful Services, and Create a new one with the following attributes (see figure 1):

a)      Name: PayPal Accept

b)      URI Template: pay_pal?token={token}&PayerID={payerid}

c)      Method: GET

d)     Source Type: PL/SQL

e)      Source:
begin
    paypal_accept(token => :token,
    PayerId => :payerid);
end;

5. Download and unzip the original PayPal Integration sample application
6. On the Application Builder Home Page, Choose Import, and use paypal_installer.sql, do not choose to install supporting objects, you need to make changes to them and will install them later.
7. Click Edit Application, and then Supporting Objects
8. Click Prerequisites and remove check next to Create Synonym  and click Apply Changes
9. Click Installation Scripts, edit paypal_accept.sql and replace all of its contents with code listing 1 and click Apply Changes
10. Edit paypal_api.plb and replace all of its contents with code listing 2 and click Apply Changes
11. Click Install Supporting Objects, choose Yes and click Next and then Install
12. Finally you need to change the substitution values in the application properties, click Edit Application and then Edit Application Properties
13. Navigate to the Substitutions section and supply values for the following:

a)      API_USERNAME : This is the username you find at developer.paypal.com, click Applications, Sandbox accounts, and then Profile under the account that was initially created when you signed up for the developer account. (It should be Type Business. Upgrade it to Pro so that you can test the direct credit card payment feature.) On the profile window, click API credentials tab. Your username, password, and signature will be listed. Use this to fill out the substitutions of the same name.

b)      API_PASSWORD : See API credentials tab

c)      API_SIGNATURE : See API credentials tab

d)     RETURN_URL : https://database-.db.us1.oraclecloudapps.com/apex/pay_pal

e)      CANCEL_URL : https://database-.db.us1.oraclecloudapps.com/apex/

14. Replace above with your actual identity domain and ensure you don’t have and leading or trailing spaces in the fields, and that you have pasted the entire API signature, it will span multiple lines. Click Apply Changes.

That’s it. The major changes were to create a RESTful service resource template and GET handler to handle the re-direct from PayPal, change the paypal_accept procedure to call f directly instead of redirecting to it, and to change do_post in paypal_api to use apex_web_service instead of UTL_HTTP directly.

Now you are ready to test the application.

1. Click Run Application
2. Login with your credentials, you may have to create an Application Express end user account first
3. After you are done laughing at the hugely simplistic and ugly UI (hey, it was like 6 years ago!), choose a quantity, leave PayPal checked and click Check Out
4. You will be redirected to the PayPal sandbox site. You need to login using a Personal sandbox account you should have created way back in step 3 of the cookbook.
5. After you log in, you will see a review screen from PayPal, click continue
6. Clicking continue should have caused you to redirect back to your RESTful services resource template, and you will now be back in your Application Express application on a confirm purchase page. Click Purchase.
7. You should be on a Purchase Confirmed page now with transaction ID, order time and amount. Click Purchase More Tickets.
8. Choose Payment Option Credit Card and Check Out
9. Use the Account type you created when creating the Sandbox personal account. You get the account number and expire date from the Funding tab of the account profile. Enter the expire date in the form MMYYYY no spaces, no dashes.
10. Click Pay Now and you should be back on the Purchase Confirmed page.

You are now ready to accept payments in your database service Oracle Cloud applications. You simply change the API_URL substitution in Application Properties to point to the production PayPal API URL.

Figure 1

Code Listing 1

create or replace procedure paypal_accept
  (token in varchar2,
   PayerId in varchar2)
as
begin
    for c1 in (select session_id, app_id,
                      page_id, payer_id_item
                 from paypal_session_map
                where session_token = token ) loop
        f(p=>c1.app_id||':'||c1.page_id||':'||c1.session_id||'::::'||c1.payer_id_item||':'||PayerId);
        exit;
    end loop;
end paypal_accept;
/

Code Listing 2

set define off

create or replace package body paypal_api
as
function do_post(
    p_api_url               in varchar2,
    p_api_username          in varchar2,
    p_api_password          in varchar2,
    p_signature             in varchar2,
    p_wallet                in varchar2,
    p_wallet_pwd            in varchar2,
    p_method                in varchar2,
    p_parm01                in varchar2,
    p_parm02                in varchar2 default null,
    p_parm03                in varchar2 default null,
    p_parm04                in varchar2 default null,
    p_parm05                in varchar2 default null,
    p_parm06                in varchar2 default null,
    p_parm07                in varchar2 default null,
    p_parm08                in varchar2 default null,
    p_parm09                in varchar2 default null,
    p_parm10                in varchar2 default null )
    return varchar2
is
    l_response       varchar2(4000);
    l_post           varchar2(4000);
begin
    l_post := 'USER='||p_api_username||'&PWD='||p_api_password||'&SIGNATURE='||p_signature||
        '&'||p_parm01;
    if p_parm02 is not null then
        l_post := l_post||'&'||p_parm02;
    end if;
    if p_parm03 is not null then
        l_post := l_post||'&'||p_parm03;
    end if;
    if p_parm04 is not null then
        l_post := l_post||'&'||p_parm04;
    end if;
    if p_parm05 is not null then
        l_post := l_post||'&'||p_parm05;
    end if;
    if p_parm06 is not null then
        l_post := l_post||'&'||p_parm06;
    end if;
    if p_parm07 is not null then
        l_post := l_post||'&'||p_parm07;
    end if;
    if p_parm08 is not null then
        l_post := l_post||'&'||p_parm08;
    end if;
    if p_parm09 is not null then
        l_post := l_post||'&'||p_parm09;
    end if;
    if p_parm10 is not null then
        l_post := l_post||'&'||p_parm10;
    end if;
    l_post := l_post||'&VERSION=2.6&METHOD='||p_method;

    apex_web_service.g_request_headers(1).name := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded; charset=utf-8';
    l_response := apex_web_service.make_rest_request(
        p_url         => p_api_url,
        p_http_method => 'POST',
        p_body        => l_post);
    return wwv_flow_utilities.unescape_url(l_response);
end do_post;
function get_parameter(
    p_response              in varchar2,
    p_parameter             in varchar2 )
    return varchar2
is
    l_start     number;
    l_end       number;
begin
    if instr(p_response,p_parameter||'=') = 0 then
        return null;
    end if;
    l_start := instr(p_response,p_parameter||'=') + length(p_parameter) + 1;
    l_end := instr(p_response,'&',l_start);
    if l_end != 0 then
        return substr(p_response,l_start,l_end - l_start);
    else
        return substr(p_response,l_start);
    end if;
end get_parameter;
procedure set_express_checkout(
    p_api_url               in varchar2,
    p_api_username          in varchar2,
    p_api_password          in varchar2,
    p_signature             in varchar2,
    p_wallet                in varchar2,
    p_wallet_pwd            in varchar2,
    p_session_id            in varchar2,
    p_return_page           in varchar2,
    p_payerid_item          in varchar2,
    p_redirect_url          in varchar2,
    p_return_url            in varchar2,
    p_cancel_url            in varchar2,
    p_amount                in varchar2,
    p_description           in varchar2,
    p_token_item            out varchar2 )
is
    l_response  varchar2(4000);
    l_token     varchar2(30);
begin
    l_response := do_post(
        p_api_url       => p_api_url,
        p_api_username  => p_api_username,
        p_api_password  => p_api_password,
        p_signature     => p_signature,
        p_wallet        => p_wallet,
        p_wallet_pwd    => p_wallet_pwd,
        p_method        => 'SetExpressCheckout',
        p_parm01        => 'RETURNURL='||p_return_url,
        p_parm02        => 'CANCELURL='||p_cancel_url,
        p_parm03        => 'AMT='||p_amount,
        p_parm04        => 'DESC='||p_description );
    if get_parameter(l_response,'ACK') != 'Success' then
        raise_application_error(-20001,'Error: '||l_response);
    end if;
    l_token := replace(get_parameter(l_response,'TOKEN'),'-','%2D');
    p_token_item := l_token;
    delete from paypal_session_map where session_id = p_session_id;
insert into paypal_session_map values (p_session_id, apex_application.g_flow_id, p_return_page, p_payerid_item, replace(l_token,'%2D','-'));
    apex_application.g_unrecoverable_error := true;
    owa_util.redirect_url(p_redirect_url||l_token);
end set_express_checkout;
procedure get_express_checkout_details(
    p_api_url               in varchar2,
    p_api_username          in varchar2,
    p_api_password          in varchar2,
    p_signature             in varchar2,
    p_wallet                in varchar2,
    p_wallet_pwd            in varchar2,
    p_token                 in varchar2,
    p_email_item            out varchar2,
    p_fname_item            out varchar2,
    p_mname_item            out varchar2,
    p_lname_item            out varchar2,
    p_shiptoname_item       out varchar2,
    p_shiptostreet_item     out varchar2,
    p_shiptostreet2_item    out varchar2,
    p_shiptocity_item       out varchar2,
    p_shiptocc_item         out varchar2,
    p_shiptozip_item        out varchar2,
    p_phonenum_item         out varchar2 )
is
    l_response  varchar2(4000);
begin
    l_response := do_post(
        p_api_url       => p_api_url,
        p_api_username  => p_api_username,
        p_api_password  => p_api_password,
        p_signature     => p_signature,
        p_wallet        => p_wallet,
        p_wallet_pwd    => p_wallet_pwd,
        p_method        => 'GetExpressCheckoutDetails',
        p_parm01        => 'TOKEN='||p_token );
    if get_parameter(l_response,'ACK') != 'Success' then
        raise_application_error(-20001,'Error: '||l_response);
    end if;
    p_email_item := get_parameter(l_response,'EMAIL');
    p_fname_item := get_parameter(l_response,'FIRSTNAME');
    p_mname_item := get_parameter(l_response,'MIDDLENAME');
    p_lname_item := get_parameter(l_response,'LASTNAME');
    p_shiptoname_item := get_parameter(l_response,'SHIPTONAME');
    p_shiptostreet_item := get_parameter(l_response,'SHIPTOSTREET');
    p_shiptostreet2_item := get_parameter(l_response,'SHIPTOSTREET2');
    p_shiptocity_item := get_parameter(l_response,'SHIPTOCITY');
    p_shiptocc_item := get_parameter(l_response,'SHIPTOCOUNTRYCODE');
    p_shiptozip_item := get_parameter(l_response,'SHIPTOZIP');
    p_phonenum_item := get_parameter(l_response,'PHONENUM');
end get_express_checkout_details;
procedure do_express_checkout_payment(
    p_api_url               in varchar2,
    p_api_username          in varchar2,
    p_api_password          in varchar2,
    p_signature             in varchar2,
    p_wallet                in varchar2,
    p_wallet_pwd            in varchar2,
    p_session_id            in varchar2,
    p_token                 in varchar2,
    p_payerid               in varchar2,
    p_amount                in varchar2,
    p_description           in varchar2 )
is
    l_response  varchar2(4000);
begin
    l_response := do_post(
        p_api_url       => p_api_url,
        p_api_username  => p_api_username,
        p_api_password  => p_api_password,
        p_signature     => p_signature,
        p_wallet        => p_wallet,
        p_wallet_pwd    => p_wallet_pwd,
        p_method        => 'DoExpressCheckoutPayment',
        p_parm01        => 'TOKEN='||p_token,
        p_parm02        => 'PAYMENTACTION=Sale',
        p_parm03        => 'AMT='||p_amount,
        p_parm04        => 'PAYERID='||p_payerid,
        p_parm05        => 'DESC='||p_description );
    if get_parameter(l_response,'ACK') != 'Success' then
        raise_application_error(-20001,'Error: '||l_response);
    end if;
    insert into paypal_transactions values (p_session_id, p_token, get_parameter(l_response,'TRANSACTIONID'),
        get_parameter(l_response,'ORDERTIME'), get_parameter(l_response,'AMT'),get_parameter(l_response,'FEEAMT'),
        get_parameter(l_response,'SETTLEAMT'), get_parameter(l_response,'PAYMENTSTATUS'), get_parameter(l_response,'PENDINGREASON'),
        get_parameter(l_response,'REASONCODE'));
end do_express_checkout_payment;
procedure do_direct_payment(
    p_api_url               in varchar2,
    p_api_username          in varchar2,
    p_api_password          in varchar2,
    p_signature             in varchar2,
    p_wallet                in varchar2,
    p_wallet_pwd            in varchar2,
    p_session_id            in varchar2,
    p_ip_address            in varchar2,
    p_amount                in varchar2,
    p_creditcardtype        in varchar2,
    p_account               in varchar2,
    p_expire_date           in varchar2,
    p_first_name            in varchar2,
    p_last_name             in varchar2,
    p_description           in varchar2,
    p_tran_id_item          out varchar2 )
is
    l_response          varchar2(4000);
    l_transaction_id    varchar2(30);
begin
    l_response := do_post(
        p_api_url       => p_api_url,
        p_api_username  => p_api_username,
        p_api_password  => p_api_password,
        p_signature     => p_signature,
        p_wallet        => p_wallet,
        p_wallet_pwd    => p_wallet_pwd,
        p_method        => 'DoDirectPayment',
        p_parm01        => 'PAYMENTACTION=Sale',
        p_parm02        => 'IPADDRESS='||p_ip_address,
        p_parm03        => 'AMT='||p_amount,
        p_parm04        => 'CREDITCARDTYPE='||p_creditcardtype,
        p_parm05        => 'ACCT='||p_account,
        p_parm06        => 'EXPDATE='||p_expire_date,
        p_parm07        => 'FIRSTNAME='||p_first_name,
        p_parm08        => 'LASTNAME='||p_last_name,
        p_parm09        => 'DESC='||p_description );
    if get_parameter(l_response,'ACK') != 'Success' then
        raise_application_error(-20001,'Error: '||l_response);
    end if;
    l_transaction_id := get_parameter(l_response,'TRANSACTIONID');
    insert into paypal_transactions values (p_session_id, null, l_transaction_id,
        null, get_parameter(l_response,'AMT'), null, null, null, null, null);
    p_tran_id_item := l_transaction_id;
end do_direct_payment;
end paypal_api;
/