Thursday, 15 April 2021

Oracle APEX and Stripe v3 - part 3


Oracle APEX 20.x and Stripe v3 integration

Stripe v3 integration Server side - part 3

If you have taken part in this blog series exploring new Stripe APIs we have already seen how to integrate APEX with new Stripe checkout form on a client side. 
Today we will look into how the same can be done by using a server side processing where payment will be triggered from APEX PL/SQL over doing it directly in Stripe checkout form we have been redirected to so far. 
This post is de-facto an upgraded version of Trent's post we used originally to get Stripe up and running. We will apply the same principles just using latest Stripe APIs and libraries.
First difference to my previous two posts is that our APEX apps will not be redirected for a payment to Stipe integrated checkout.
Pretty much we will be doing the exact opposite we will build a form in APEX and send it using REST API for processing. 
Secondly we will be using amounts instead of Stripe defined products and prices in post 1 and post 2.

Stripe checkout form:

will be replaced with APEX built form:

Of course this is where the freedom and control come into play for us as developers. 
We can build forms as we are used to as in any standard APEX page. 
Let's see how. Create a new app page with Blank with Attributes region with this code as a source. 
On a page level we will add a reference to Stripe JS 
We need to add CSS that will handle the look and feel of the form and Javascript code that will initiate Stripe payment form for us.
Both codes can be found at the link above under CSS and JavaScript sections.

What the JS code will do is among other bits and pieces is initialize Stripe card component, create elements on the form and finally attach an event listener using
Most importantly it will catch the token returned by Stripe which we can use later in our PL/SQL processing to charge the card.
If you run the page now you should see the new form with card fields added to it. 
Great thing is that Stripe injects everything needed for our form to be able to accept payments. This includes all card validations and processing. Isn't this great!
Okay to be fair our job is still not done so we will add few fields to store tokens return by Stripe payments. 
Create two new fields  

Now let's make sure we update the JS lines with public key and with your page item name.
//update this line with your ITEM NAME
Run the page at this stage and click on a Pay button. You should see a token ID returned into PX_STRIPE_TOKEN.

This is the most critical part as without it the next step will not work. Please make sure you get the token back from Stripe.
At this stage it does not creates a charge on a card but only 'initializes it'. 
The rest of the code is pretty much exactly the same as in an old version of the form checkout. What we will do now is create an on change DA on PX_STRIPE_TOKEN that will execute PL/SQL call:
        stripe_api.charge_card (
            p_amount => 2500
          , p_source => :PX_STRIPE_TOKEN
          , p_description => 'Charged from a D.A page X at '|| sysdate
Once we got this in, our form becomes fully operational and is able to send payments to Stripe API. 
If details are entered and payment is successful we will use another DA to notify the user that transaction was successful.

In case you did not get stripe_api.charge_card package so far here is a code for it.

How do we check if payment was successful from Stripe side - you can open Payments link in Dashboard 
also you can check all logs under Developers -> Logs:
Further to this we can easily create a new REST data source in APEX

with simple credentials

and then leverage this in interactive report based on REST data source we just created to help us monitor transactions from our applications. ;)
I am hoping this will be of help. Lots to take in and read so thank you for sticking with me till the end.

Happy APEXing,

Tuesday, 13 April 2021

Oracle APEX and Stripe v3 part 2


Oracle APEX 20.x and latest Stripe v3 integration

Stripe v3 integration using session tokens - part 2

In this blog we will have a quick look at integration of Stripe Checkout form using Session tokens. 
This post is a continuation on a Part 1 post of this series.

If you have a deeper look at Stripe documentation under it guides you on how to migrate your older checkout forms to a new one. You will notice this piece of JS code

Which looks very similar to our demo example in Part 1 except that now we have a CHECKOUT_SESSION_ID instead of line_tems we sent earlier to new Stripe checkout form.
Hmh... how does this work then?
Again as before let's create a new page (or you can start by copying the previous example). 
On this page we will have one region and one button that will execute dynamic action to initiate the payment. I named my button again a MAKE_PAYMENT button. 
Something something similar to
On this button we will add DA that executes PL/SQL code:
    stripe_api.set_secret(:STRIPE_SECRET);--Stripe API key
    :PX_SESSION_TOKEN := stripe_api.get_stripe_session_token(
                 p_prices => :PX_SPONSOR_TYPES
               , p_email => ''
               , p_success_url => :PX_PAGE_URL_SUCCESS  
               , p_cancel_url => :PX_PAGE_URL
Code for get_stripe_session_token is here
What this code does is executes a POST request call to STRIPE API forwarding all details it needs to generate a token for us. 
Please do note that again I am using Stipe products and product prices that are sent into this function call as concatenated stings delimited with ":". 
Which we can do by using a APEX checkbox page item. 
Again we need our Stripe API keys but this time we need a private key which you can find under Developers -> API in your Stripe Dashboard.

This will then get stored in PX_SESSION_TOKEN hidden page item we already created.

Now once token is retrieved we are now able to call Stripe checkout form exactly the same as we have done in part one. 

Create a new dynamic action on PX_SESSION_TOKEN changes and when item is not null to execute JavaScript code
var stripe = Stripe('pk_xxxxxxxxxxxxxxxx');

    sessionId: $v('PX_SESSION_TOKEN')  
  }).then(function (result) {
    if (result.error) {
    console.log('Error opening Stripe payment page.');

This should result in Stripe checkout form being loaded

Once details are entered and payment is successful users will be returned back to APEX page using SUCCESS and CANCEL URLs we specified. 
This we can then use for displaying success message.
How do we check if payment was successful from Stripe side - you can open Payments link in Dashboard also you can check all logs under Developers-> Logs:
Of course these checks can be done using REST API too but that is for another day. 

In case you wonder how I am getting query for my check-boxes. 
Since I have defined Product and Prices in Stripe, all application LOV are all based on REST data sources which enable us to tightly integrate the two systems without having to have an admin maintenance on APEX end. 

More about how to do this you can read in the next post where we will explore how to integrate Stripe checkout form directly into APEX app with no redirections and do the card payments all on the server side giving us a little more control over the whole process.
Stay tuned. 

Happy APEXing,

Monday, 12 April 2021

Oracle APEX and Stripe v3 integration - part 1


Oracle APEX 20.x and latest Stripe integration - v3

Quick guide on Stripe v3 integration - part 1

Exactly 4 years ago I had my first encounter with Stripe payment gateway so when I finally got a chance to revisit it I knew there was going to be some good blog material. 
This series of 3 post is about how to integrate the latest version (Stripe /v3/) with your Oracle APEX applications.  

Aside from that there were so many forum questions on this topic and many new payment systems came to the market that were demoed on recent APEX conferences which kept me wondering about how did/does Stripe compares to them nowadays. 
Of course in this time Stripe also accelerated and got even more powerful which takes me back to a previous post done on this topic in February 2017
That was more of a simple overview of things we have done with AOP application to show case this integration. 
Let's start with a good news first and that is that your old "demo" app that integrated with your Stripe payments should continue to work with exceptions of few APIs being updated since. What does that mean? 
Well if you visited the above blog post you would notices that it referenced Trent's series which lead us to first run-able demo of Stripe in APEX. 

That all landed us in a good place for future exploration of Stripe v3 capabilities. The image above is running APEX 20.2 and created using exactly the same steps as before. Only downside so far is this has been deprecated and not sure how long it will stay functional.

To conclude the intro it is sufficient to say that depending on how deep you integrated Stripe in your apps there might be a smaller or bigger upgrade project on a horizon as some responses might not return exact same JSON structure comparing to what they will do in Stripe /v3/. Other than that there is not much big of the difference your PL/SQL API calls will stay pretty much the same. Exception is the new Checkout form too but that is why we are here. 

As you know there are quite a few features in there like web-hooks, invoicing, subscriptions and many more which you have to double check separately assuming your apps used some of them already. Very detailed documentation that is available will surely come in handy.
In this first post we will concentrate on JavaScript integration of the latest Stripe checkout form which is more secure and comes with even more features from version /v2/ or /v1/ (we all have seen before). 
It comes with newer version of built in checkout form with new/updated set of JS modules. So if you are trying to migrate older version of a Stripe checkout form we all seen before (image above) this documentation might come in handy or simply follow this post till the end. ;)
Yes you heard it right! You can now create payments simply by using JavaScript which makes things even more better. Side note here, I am not sure since when this became available but I am sure it wasn't around in 2016/17 when I had my first go at Stripe. 

Further to it there are few ways how we can do this and we will explore two in these blog series.

First thing first - you have to create an account with Stripe so you can log in into You have to do this regardless whether or not you integrated your apps following earlier posts. Once you logged in it should be looking similar to this. 

Perfect! We are now all set to explore this new feature. Please note that you need to have your database ACL configured as explained in previous posts.
Let's create an app (or a page in your existing app) containing one single region and one single button that we will call MAKE_PAYMENT. 
After that we will add dynamic action to the button which will execute true action - JavaScript code:
var stripe = Stripe('STRIPE_PUBLIC_KEY');
var str = '[{"price":"price_1XXXXXXXXXXXXXXXXXXX", "quantity": 1}]';

    lineItems: JSON.parse(str),    
    mode: 'payment',
    successUrl: '',
    cancelUrl: '',
    customerEmail: ''    
  }).then(function (result) {
    if (result.error) {
    console.log('Error opening Stripe payment page.');
As you see before we will be able to use this, there are few configs we need to make but in essence this is all there is to it.
First add reference to latest Stripe JavaScript library in your page header
Next we have to find STRIPE_PUBLIC_KEY which you can find in Stripe dashboard under Developers API keys 

You can make a note of both keys but in this example we only need Publishable key and simply copy it into JS above. 
Great, now step #3 what is this str value and where do we get priceID. 
This has to do with a Stripe products - 

Here I created 4 products my form will be offering to the end users. 
As you create each product you can click on it to see its details among others there will be priceID in there too. 
Similar to

Perfect copy API ID under Pricing for that product you created. You might say why do we need this? Well you do or don't depending on how want to approach your checkout. 
In this demo idea was to utilize most of Stripe ability as we can which is then to have products defined in Stripe which auto creates Prices which we can leverage in our APIs.
Notice that we need a quantity which in our case will be set to 1 for the demo purpose.

Last three inputs sucess_url, cancel_url and customer_email of course can be  APEX generated URLs and email address that we pick from user logged in the app.

In my demo example for this reason I added 4 additional hidden fields: PX_URL_CANCEL, PX_SUCCESS_URL, PX_SUCCESS_MSG and PX_EMAIL which will be passed into this JavaScript call. Of course in SUCCESS_URL you can reference back your page item so once Stripe does it magic it will populate it which then we can use for displaying Success messages to end users.
Returning back into APEX and displaying success message.
Most of these are self-explanatory. 
Important: To be able to do this you need to enable CLIENT-ONLY integration option in Stripe dashboard under
Save your page and let's see what happens.

If we got everything right on a click of the button you should now get the new look and feel Stripe checkout form which will handle all for you. You can fill in details and it should try navigate you back to your SUCCESS_URL.
It will verify cards it will support any kind of payment like Apple and Google pay, bank account payments etc..... all with one click of a button. Very simple and yet very powerful.
This demo of course does not cover all of this but it should give you a head start into exploring the possibilities.
How do we check if payment was successful from Stripe side - you can open Payments link in Dashboard also you can check all logs under Developers-> Logs:
In the next post we will explore how to do the same New Checkout form using Stripe session token.

Happy APEXing,

Tuesday, 16 February 2021

APEX 20.2 - radio item issue

Unusual radio item DA behavior in APEX 20.2 - bug!??

JavaScript Dynamic action on radio item stops working as expected

Yet another post touching on naming standards causing issues in APEX 20.2. I was asked to help by a client that stumbled on this issue and I wanted to share it here. 
Scenario is simple let's say you create a blank page like below.

So all we have on this page is simple region with two radio buttons.

If you run the page and click on your radio items all should work fine. Easy enough to do and expected behavior since we did not add any logic to it yet.
Please notice here the unconventional naming standard we are using here for these items PX_100 and PX_100_1. Keep in mind that this would not be a recommended way to name your items.

All good, let's now add a simple on change Javascript Dynamic Action on PX_100_1 item which will alert some text for us like "Hello world".
We are keeping it simple to make the point here...

Save and run the page. This part now depends on how complex your DA is but expected behavior in this example would be that we get alert messages when we keep clicking and changing values on PX_100_1 item, wouldn't it.
But wait this is not what happens. 
Firstly actual action triggers on my first element which is weird plus it does not do it on every change I make on that same item which we would also expect.
What you can also experience is that your cursor gets refocused as result of your action (or something very similar usually) causing your DA not to act as you would expect.

Why is this and where does it come from? Notice how second item on the page has very similar name to first item and that they both have only numbers in their names which then somehow causes an issue with DA selectors and listeners.

Fixing this issue is simple - simply rename your items for example PX_X100_1 would resolve the problem.

Try it for your self here
As you see this example is running on which should have all the latest patches applied.
Should APEX handle this situation a little bit better is a good question but hey having a good naming standard in place would help you too. :)

This was not happening on APEX 20.1 as far as I know so please keep an eye for it. 
Good thing is that 99.9% of your apps will not hit this use case so there is very little to worry about. :D

Happy APEXing,

Friday, 11 December 2020

APEX 20.2 - On Submit JS Syntax error, unrecognized expression: #PX_{BUTTON_NAME}_DISPLAY


Submit page and button JS error when hidden page item name contains $ sign

Page submit stopped working and you get some strange JavaScript error?

This is another post touching on naming standards
I was asked for an advice by a client that stumbled on this issue and I wanted to share it here. Scenario is simple let's say you create a blank page like below.

So all we have on the page is simple region with one submit page button and one hidden item.

If you run the page and click the button your page would submit and page would be re rendered. Perfect that is easy enough to do and expected behavior.
What if we as a user decide to go with unconventional naming standard and named this hidden item PX_NEW$TEST. Notice $ sign in there. 

Perfect, save and run the page click on the the same button again. Nice JS warning message alert is there with this in console log - Syntax error, unrecognized expression. 

But wait how do we debug this? Imagine if this was a huge page with over 30 items on it. Where do you start?!!! There is nothing in the page called NEW$TEST_DISPLAY on my page not even on the application level..... 
I am hoping you will not lose time on this and that somewhere down the line it will ring the bell that probably naming page items with special signs is probably not a good idea anyway.
For fun of it, try calling your page item something along NEW#TEST and see what APEX will actually save as an item name.  

We could say it would be nice to see APEX handling this situation too but hey having good naming standard in place would help here too. :)

To be clear this may not have been just APEX 20.2 error but potentially can happen in earlier version too so add this odd use case to your memory box somewhere.

Happy APEXing,

Friday, 23 October 2020

Preview Oracle cloud storage images in APEX


 Preview Oracle cloud storage images in APEX

How to show your Oracle Cloud stored images?

Let's say you wanted to store your images on external storage and that your pick was to use always free ATP environment which comes with equally good 20GB of online space included. This makes it a perfect place for all your APEX application files, images etc.....if you choose not to store them in database or on application servers.
First thing first we have to be able to manage images on the Cloud Storage. Here I will point you to an excellent blog by Adrian Png that covers all steps on how to do this part.
It takes you from creating Cloud credentials all the way to creating APEX application that can upload, download and delete images in your buckets. Thanks again Adrian for sharing.
Today's 2 cent will be in how do we now display these images we uploaded in our reports or even better in an image gallery. 
If you have seen my previous post on how to create simple and responsive image gallery in APEX using fotorama.js library today's post will make all the sense. From this point on I will assume you have all these things configured and working.....
Let's first see how would we simply show a preview of an image on page 1 bucket content region which is a classic report based on web source list_objects_in_bucket that we created earlier.
Note here we are assuming that your images are in bucket storage that is not public else you could simply access your images with direct urls. Since private buckets are secured and require authentication we will not be able to use a direct links here.
Firstly lets add a new column to report, there are few ways how we can do this easiest one is to edit the query in Local Post Processing
We are simply rendering a link IMG_URL to page 3 and passing through file name and a bucket name. Page 3 will if you got your download working retrieve the file from the cloud which we will use in our report. 
Currently on page 3 there is a before Header process: 
which is the same process as original on Adrian's post running web
How does this help? We will change column setting HTML expression of IMG_URL to 
Here <img src="#IMG_URL#" /> will do the trick of displaying the image in report column for us. Please note we also need to disable Escape special characters. If you were to have public images this same trick with direct url set in the query above should do the same trick. Difference is that we are using REST calls to authenticate with CI storage.

The result will be that now we have our Cloud images shown in a report. 

OK, a small tweak to this would be to turn this report into an image gallery now based on images coming from Oracle Cloud storage. How? We will use same trick as on my image gallery post.

Create a new region as a copy of your classic report, edit its Local Post Processing to 
select nvl(LISTAGG('<img src="'
       || APEX_PAGE.GET_URL (
          p_page   => 3,
          p_items  => 'P3_BUCKET_NAME,P3_OBJECT_NAME',
          p_values => :P1_BUCKET_NAME || ','|| NAME) || '">', ' ') WITHIN GROUP (ORDER BY TIMECREATED)
      , 'No data found')          
Where NAME like 'test/%' --to filter result set
Save your change and report should only have IMAGES column now. Edit its HTML Expression to be:
<div class="fotorama" data-allowfullscreen="true" data-nav="thumbs" data-fit="contain" data-width="100%"     data-ratio="800/600"  data-minwidth="350" data-maxwidth="350"      data-minheight="300"      data-maxheight="100%" >#IMAGES#</div>
Set Escape special characters to no. All that we are missing now are adding references to our fotorama library. Adding this to page header should do it.

Save and run the page. You should see gallery working.
Leaving you with an app preview. Again all credits go to Adrian as his tips did all the heavy lifting. ;)
Happy APEXing,