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:
- You must be able to use UTL_HTTP with a wallet
- 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.)
- Sign up and activate a database service on the Oracle Cloud
- Sign up for a PayPal developer account
- Follow the instructions to create sandbox accounts you can use to test transactions
- 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;
begin
paypal_accept(token => :token,
PayerId => :payerid);
end;
- Download and unzip the original PayPal Integration sample application
- 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.
- Click Edit Application, and then Supporting Objects
- Click Prerequisites and remove check next to Create Synonym and click Apply Changes
- Click Installation Scripts, edit paypal_accept.sql and replace all of its contents with code listing 1 and click Apply Changes
- Edit paypal_api.plb and replace all of its contents with code listing 2 and click Apply Changes
- Click Install Supporting Objects, choose Yes and click Next and then Install
- Finally you need to change the substitution values in the application properties, click Edit Application and then Edit Application Properties
- 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/
- 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.
- Click Run Application
- Login with your credentials, you may have to create an Application Express end user account first
- 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
- 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.
- After you log in, you will see a review screen from PayPal, click continue
- 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.
- You should be on a Purchase Confirmed page now with transaction ID, order time and amount. Click Purchase More Tickets.
- Choose Payment Option Credit Card and Check Out
- 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.
- 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;
/
