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.

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:

• STRIPE_ACCOUNTS: The Stripe account information for the authenticated caller.
• STRIPE_COUPONS: The coupons issued in the Stripe account.
• STRIPE_CUSTOMERS: The customers defined in the Stripe account.
• STRIPE_INVOICES: The invoices defined in the Stripe account.
• STRIPE_PLANS: The pricing plans defined in the Stripe account.
• STRIPE_PRODUCTS: The products defined in the Stripe account.
• STRIPE_SUBSCRIPTIONS: The subscriptions managed in the Stripe account.

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

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.