Thursday, August 15, 2013

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;
  1. Download and unzip the original PayPal Integration sample application
  2. 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.
  3. Click Edit Application, and then Supporting Objects
  4. Click Prerequisites and remove check next to Create Synonym  and click Apply Changes
  5. Click Installation Scripts, edit paypal_accept.sql and replace all of its contents with code listing 1 and click Apply Changes
  6. Edit paypal_api.plb and replace all of its contents with code listing 2 and click Apply Changes
  7. Click Install Supporting Objects, choose Yes and click Next and then Install
  8. Finally you need to change the substitution values in the application properties, click Edit Application and then Edit Application Properties
  9. 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/
  1. 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;
/

15 comments:

reidster said...

I'm trying to setup PayPal payments through an APEX 4.2 app. I am not using the Oracle cloud. Is there any advantage to using the web service approach detailed in this post versus using the method described in the 6 year old post on OTN that uses UTL_HTTP ?
Thanks for sharing your insight.
Reid

Jason Straub said...

Reid,

I can't think of any real advantage, except this post describes a more "modern" way of doing it.

Regards,

Jason

Unknown said...

Thats a very good tutorial! however, im stuck in one part.. im new into oracle so I can really find the Oracles wallet manager.. how can I get it?

Unknown said...

Hello Jason,
This is exactly what I have been trying to do. This is a very useful tutorial! Thank you for sharing it.
Now, since im new to Oracle stuff, I got stuck on installing the Supporting Objects(step 11) in Apex. Apex tells me that there is a prerequisite check fail with the Object Name: PAYPAL_API. I think its because I havent created a wallet. I cant find the Oracle Wallet Manager to create a wallet. How do I access the Wallet Manager? or is it a application that I download to my computer?

Regards,

Wilson

Unknown said...

Thanks for sharing this! I've been trying to find some info on finding quality payment services in Lufkin TX. Do you have any insights?

Jason Straub said...

Jamal,

Unfortunately I do not, but perhaps someone else will comment.

Regards,

Jason

Unknown said...

Hello,

I tried it on a non maxzpex service, and I got the error:

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation
failure

I run 11g XE on Linux

Any idea what could be wrong?

RIchard (Spire) said...

Great write up. Getting the following when trying to use this though.

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
ORA-06512: at "APEX_040200.WWV_FLOW_WEB_SERVICES", line 543
ORA-06512: at "APEX_040200.WWV_FLOW_WEBSERVICES_API", line 152
ORA-06512: at "ATSIP_PROD.PAYPAL_API", line 62
ORA-06512: at "ATSIP_PROD.PAYPAL_API", line 246
ORA-06512: at line 52

Any ideas on this?

Thanks

Richard

Jason Straub said...

Hi Richard,

Are you using the Oracle Database Cloud Schema as a Service, or your own instance?

If your own instance, it would seem that your wallet does not have the root certificate of the paypal development endpoint imported.

Regards,

Jason

Jason Straub said...

Hi Yannis,

I don't believe you have access to Oracle Wallet manager on XE.

To access wallet manager on Linux, with your ORACLE_HOME env set, enter owm at a prompt.

Regards,

Jason

Jason Straub said...

Hi Wilson,

To access Oracle Wallet manager on Linux, enter owm at a command prompt. Obviously if you are on a hosted instance (such as the Oracle Cloud) then the wallet should be fully configured already for you.

I think the issue you may be having importing the application is that you already have the PAYPAL_API object in your schema. Go to SQL workshop, Object Browser, and choose packages to check. If you want to re-import the application, you may have to drop that object first.

Regards,

Jason

Unknown said...

Hi Jason,

I'm getting the below error on completing all the steps in the tutorial. Verified my PAI keys and credntials from paypal developer account. All looks good
Could you please help me out?

"Contact your application administrator.
Unauthorized URL: https://api-3t.sandbox.paypal.com/nvp"

Jason Straub said...

Hi Suresh,

It seems that your instance is utilizing authorized URLs in instance administration.

See http://docs.oracle.com/cd/E37097_01/doc.42/e35129/adm_mg_service_set.htm#CHDJAABI. The URL https://api-3t.sandbox.paypal.com/nvp needs to be added to the list.

Regards,

Jason

Unknown said...

Thanks for your response Jason.
The problem I'm facing now is that I'm using the hosted apex service from oracle (apex.oracle.com).

Is it possible add authorized URLs?
If so how as I couldn't find the option to add authorised urls?

Regards,
Suresh

Jason Straub said...

Hi Suresh,

Authorized URLs are specified by the instance administrator, and this will not be possible on apex.oracle.com.

Regards,

Jason