How to easily access and gain insights from your Stripe data on Oracle Autonomous Database

May 8, 2023 | 4 minute read
Nilay Panchal
Principal Product Manager
Text Size 100%:

Stripe is a popular platform for online payment processing and credit card processing that I am sure you are aware of. If you use Stripe for your business, you likely need to work with your Stripe data in Oracle Autonomous Database (ADB). Today, we introduced new database views in ADB, based on Stripe's APIs, to get easy insights into your Stripe invoices, subscriptions, customers, and more.

Stripe

What are Stripe views?

Stripe views are predefined views Oracle Autonomous Database provides to access Stripe data using SQL queries. You can use the DBMS_CLOUD package to create and query these views, which are based on Stripe's APIs. The following views cover various aspects of your Stripe account data, as described:

You can find the details about each view and its columns by clicking the links to the documentation above.

Views

 

How to use Stripe views on Oracle Autonomous Database?

To use Stripe views on Oracle Autonomous Database, follow these steps in your ADB instance. Note, if you are starting from scratch and don't have an ADB instance yet, follow this quick helpful LiveLab to provision a database:

1. Update your Network Access Control List (ACL) for accessing Stripe. You need to use the DBMS_NETWORK_ACL_ADMIN package to grant HTTP access to stripe.com for your database user. For example:

BEGIN   

    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

         host => 'stripe.com',

         ace  => xs$ace_type(privilege_list => xs$name_list('http'),

                             principal_name => 'FOO',

                             principal_type => xs_acl.ptype_db)

   );

END;

/

2. Create a credential to access Stripe APIs. Use the DBMS_CLOUD.CREATE_CREDENTIAL procedure to create a credential object that contains your Stripe API token. This token can be found in the Stripe Developers Dashboard. You can either create a credential object with the name STRIPE$CRED, which is the default name used by the views, or create one with a user-defined name and set it as the default credential for your session.

For example:

-- Option 1: Create a credential object with the name STRIPE$CRED

BEGIN

    DBMS_CLOUD.CREATE_CREDENTIAL(

        credential_name => 'STRIPE$CRED',

        username        => 'STRIPE_TOKEN',

        password        => 'bearer_token' );

END;

/


-- Option 2: Create a credential object with a user-defined name and set it as the default credential

BEGIN  

    DBMS_CLOUD.CREATE_CREDENTIAL(

        credential_name => 'MY_STRIPE_CRED',

        username        => 'STRIPE_TOKEN',

        password        => 'bearer_token');

END;

/

ALTER SESSION SET default_credential = 'MY_SCHEMA.MY_STRIPE_CRED';

 

3. You may now query Stripe views! Use simple SQL queries to select data from any of the Stripe views.

For example:

 
-- Query the STRIPE_COUPONS view

SELECT name, percent_off, duration, times_redeemed FROM STRIPE_COUPONS;


-- Query the STRIPE_INVOICES view with a join to the STRIPE_CUSTOMERS view

SELECT i.id, i.amount_due, i.status, c.email

FROM STRIPE_INVOICES i

JOIN STRIPE_CUSTOMERS c ON i.customer = c.id;

 

Note: By default, only a database ADMIN has access to Stripe views; An ADMIN may grant other users access to Stripe views with:

 

 GRANT READ on STRIPE_COUPONS to SCOTT;

 

Conclusion

Stripe views on Oracle Autonomous Database are a convenient way to access and analyze your Stripe data using SQL queries. You can use them to get insights into your online payment transactions, such as how many customers are using your coupons, how much revenue you are generating from your subscriptions, or which invoices are overdue or unpaid.

If you want to learn more about Stripe views on Oracle Autonomous Database, check out the Oracle Autonomous Database documentation.

 

Like what I write? Follow me on the Twitter!

 

Nilay Panchal

Principal Product Manager

Nilay is a principal product manager at Oracle, responsible for adoption and feature development of Oracle's flagship converged cloud database - Autonomous Database. He was previously a developer and data scientist, and has a decade worth of experience in data warehousing, dimensional modeling, search engines and machine learning. A global Carnegie Mellon graduate, he has had the opportunity to work, travel and study in several different countries in various fields. His avocation is music; in his downtime he enjoys playing guitar or piano with a strong cup of chai nearby.

Nilay blogs regularly, and often speaks at cloud and database events. Follow his work on the Twitter @theproductlad


Previous Post

Using the Google Sheets Add-in to Query Autonomous Database

Ashish Jain | 7 min read

Next Post


Announcement: bring Data Catalog objects into Autonomous Database Data Studio

Alexey Filanovskiy | 4 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider