Oracle APEX and Stripe

Oracle APEX and Stripe

Powerful payment gateway in your APEX applications

Full cycle integration

To kick off this post I will reference a cool intro blog series of fellow APEX-er Trent. In my opinion he did a good job giving you a good ground where to start. Thanks Trent!

Before you read the rest of my post these are "nice to read". Of course you can go directly to Stripe.com and check out their checkout examples. This is great add-on to help you understand how checkout form works in APEX.

Why?
Let's start with explaining how we decided to use Stripe. Before you jump into implementation of payment systems you probably had the same doubts.

Do you let your DB run the show or you let external system to does it all for you? Meaning do you go and implement your own system and only call external to do most basic tasks like charge customers while your DB takes care of whole business life-cycle.

There is lots to think about and there is no right or wrong answer here. What ever works for you better is the way to go.

From our perspective we wanted to let all of this being taken care of by external party like Stripe so we do not have to worry about when customers need to get charged, when they subscription expires, cancellations, dummy credit cards and above all we did not want to store customer info like credit cards at our end.

This is why Stripe was an obvious choice. It is really nicely documented, with heaps of examples. It is easy to integrate with your website plus it has all its API exposed that can be easily called from your APEX apps. What more can you ask for.

Note here that I am sure this all is possible with other gateway payment tools like PayPal too, my goal was to give some guidelines for people who decided to go with Stripe. I am not their ambassador or anything like that just have had a chance to work with it and hence the post.

Things to know?
After you went through Trent's basic examples and are able now to set up and run first transactions with Stripe from APEX. This is where we are picking up and continuing building on few more things you might need along the way.

To summarize Stripe in short, once you register and have an account you can log into admin console to configure your payment system. By all means this could probably be the only thing you need as Stripe gives you all you need to run your billings/payments from here manually. But is this what you want? :)

As you can see some of basic entities of this gateway are Customers, Plans, Subscriptions etc..... all of this is available to you through APIs. You can create, update and delete as you please.

In our projects we used two type of plan renewals. Customers can subscribe for monthly payments and annual ones. Another nice feature of Stripe we used, as we did not want to worry about who needs to be charged and when. All of this is done by Stripe.

All we have to do now is start using them.

How?
In example below and also in Trent's blog you see how APEX REST service calls can be used to make basic transactions this is nothing new.
To make calls to an API using apex_web_service package:

...
l_return := apex_web_service.make_rest_request(
p_url => 'https://api.stripe.com/v1/customers/' || l_stripe_customer_id,
p_http_method => 'DELETE',
p_username => l_username,
p_username => l_username,
p_wallet_path => 'your_wallet_path',
p_wallet_pwd => 'wallet_pwd' );
apex_json.parse(l_json, l_return);
...

These are few of REST APIs we used.

https://api.stripe.com/v1/customers -> POST 
https://api.stripe.com/v1/customers/l_stripe_customer_id -> DELETE 
https://api.stripe.com/v1/charges -> POST 
https://api.stripe.com/v1/subscriptions -> GET 
https://api.stripe.com/v1/customers/l_stripe_customer_id/subscriptions/l_stripe_subscription_id -> POST 
https://api.stripe.com/v1/subscriptions/l_stripe_subscription_id -> DELETE 

To find out more about all parameters and what they do please check the API documentation. It is all in there.

Now that we are familiar with all of these it is time to hook it back to your database.

Webhook?

Looking it from above, fine we have a website and a system that can perform transactions but how does your DB know that these transaction are actually happening within your third party software? This is the key component of your payment system.

This is where webhooks come into play. Webhook is a mechanism that enables two systems to notify one another when certain events happen. Event is a driver of a webhook and usually it happens over web service calls between two parties.

Excellent, theory is done. How does that look in practice.
To create a webhook in Stripe go under your Account Settings and notice a Webhooks tab.

 
Here you can create your LIVE and TEST webhooks by simply entering an URL of your APEX RESTful service. For example:

http://www.myserver.com/ords/my_stripe_webhook/ 

Important thing to note is that you can select exact events that will be triggering messages back to your APEX system.

Once you set this up all you have to do now is consume this in APEX by creating a REST method.

Another great thing is that Stripe APIs use JSON as file format so parsing the REST responses becomes nothing more than json.parse for us. You will see more in a second.

To know whether or not things have happened there is an Events & Webhook log functionality where you can monitor and see all things triggered in Stripe.

How do you consume Stripe webhook?
Log into APEX then create a RESTful service with POST method.

If all is alright you should be able now to consume your Stripe webhooks and update your DB tables as needed.

Summary
This should give you some great ideas what is possible and how you can achieve it using Stripe.

All I can add is I absolutely had no problems working with it and learning how things work was really easy which is another important factor in your decision.

Thanks,

SLino

p.s. an example of Stripe REST APIs

/******************************/ 
function create_stripe_customer(p_token_id in varchar2, p_email in varchar2) return varchar2
as

  l_code        aop_plan.code%type;
  l_return      clob;   
  l_json        apex_json.t_values;
  l_email       aop_user.email%type;
  l_stripe_customer_id   aop_user.stripe_customer_id%type;
  l_stripe_plan_id       aop_plan.stripe_plan_id%type;
  l_stripe_subscription_id  varchar2(500);
  l_user_id     aop_user.id%type;  
  l_step        number := 0;

begin
      apex_web_service.g_request_headers(apex_web_service.g_request_headers.count + 1).name := 'Content-Type';
      apex_web_service.g_request_headers(apex_web_service.g_request_headers.count ).value := 'application/x-www-form-urlencoded'; 
     
     l_step := 1;    
     -- create customer
     l_return := apex_web_service.make_rest_request(
                    p_url         => 'https://api.stripe.com/v1/customers',
                    p_http_method => 'POST',
                    p_username    => l_username,                
                    p_wallet_path => p_wallet_path,
                    p_wallet_pwd  => p_wallet_password,
                    p_parm_name   => apex_util.string_to_table('email:source'),
                    p_parm_value  => apex_util.string_to_table(p_email||':'||p_token_id)
                  );
     -- returns customer details with stripe id       
     logger.log(l_return); 
     apex_json.parse(l_json, l_return);
         
     --check for errors
     if apex_json.get_members(p_path => '.', p_values => l_json)(1) = 'error'
       then
         logger.log('Step ' || l_step || '. Error processing new customer: ' || l_email || '. Reason: ' || apex_json.get_varchar2(p_path => 'error.message', p_values => l_json));
   --raise_application_error(-20001, 'Error processing new customer (stripe).'); 
       return 'ERROR';    
     else 
         --update AOP user table with new customer token
         l_stripe_customer_id := apex_json.get_varchar2(p_path => 'id', p_values => l_json);
         --logger.log(l_stripe_customer_id);  
         return l_stripe_customer_id;                     
     end if;         
     
 exception when others then return 'ERROR';

end create_stripe_customer;                

Example 2. Charging a credit card

/*******************************/
function charge_creditcard (p_amount in NUMBER, p_currency in varchar2,    p_stripe_customer_id in varchar2, p_description in varchar2, p_wallet_path in varchar2, p_wallet_password in varchar2)
return varchar2
as

l_stripe_resp CLOB;
l_status      varchar2(100) := 'Fail';
l_json        apex_json.t_values;

begin
l_stripe_resp := apex_web_service.make_rest_request(
                p_url         => 'https://api.stripe.com/v1/charges',
                p_http_method => 'POST',
                p_username    => l_username,
                p_wallet_path => p_wallet_path,
                p_wallet_pwd  => p_wallet_password,
  p_parm_name   => apex_util.string_to_table( 'amount:currency:customer:description' ),
                p_parm_value  => apex_util.string_to_table(p_amount || ':' || p_currency || ':' || p_stripe_customer_id || ':' || p_description )
              );
       --logger.log(l_return);      
      apex_json.parse(l_json, l_stripe_resp);
      --check if Errors retrieved
      if apex_json.get_members(p_path => '.', p_values => l_json)(1) = 'error'
        then
         l_status := 'Error';
      else
       --check for success
         l_status := apex_json.get_varchar2(p_path => 'status', p_values => l_json);       
      end if;

      return l_status;

end charge_creditcard;