APEX_SERVER_PROCESS and clob processing

Fetch CLOB size text on page load and submit page

Oracle APEX 32k size limitation workarounds

Since I had to do this few times around already this is for reference and reminders. 

 

Ask is to load and process the same text like JSON data on your APEX page that exceeds typical 32K in size. 

 

Let's first tackle getting the JSON data from a database to be parsed with JavaScript. 

 

In example of processing a payload on HansOnTable example I wrote earlier about. 

In my previous post I used APEX page item to store and process the data which obviously came with limitations.

 

Alternative approach getting the data into the initialization code would be to have  JS code: 

Where not so common method of fetch(url) is used to read the JSON data from application process app_get_clob:

This is then used in JS initialization code. 

 

Notice that we are here downloading the clob as a blob to bypass size issues.

 

Sweet, now that we changed the data we want to update our database table in a similar fashion. 

 

In the save process we will use apex.server.process and p_clob_01 parameter option.

where we then access this ajax passed parameter using

Similarly here we are using g_clob_01 vs g_x01..10 which typically get used.

 

This way we should be more than capable of loading large data loads and process them when data gets submitted back by the end users. 

 

Happy APEXing,
Lino 

 

p.s. example of the code can be found on https://codeshare.io/K8OZ1E

Oracle APEX expire session customization

 

Customize expire session notification window in Oracle APEX

Oracle APEX "Your session has expired"

I noticed a few discussions on the OTN Forum about APEX expire session dialog window and its behavior. Since recently we had a client asking for the same behavior so I decided to write a blog about it as an alternative approach.

We all have seen and know the theory regarding these configuration settings:

They dictate how your applications will handle things when user session is about to expire or is already expired. 

 

What is interesting these settings have been changing over past few version of APEX too but people seem to still have issue related to how to handle it the best possible way. 

Normally if your session is about to expire you would see a message: "Would you like to extend it?"

 

Which allows users either to extend their session or not. So far so good. 

 

If they do not extend they get presented with another dialog screen: "Your session has expired"

 

This is where people tend to take different approaches. 

 

Most common is most people get by with it and do not change things. On the other hand why do we need a Cancel and Close button on this popup at all? Which completely resonates with me. 

 

Sometimes you do not want people to be able to cancel from here and see the data on the screen. If for no other reason maybe from security perspective this makes a lot of sense. 

 

I know you can argue yes they can click on Login Again but still......sometimes you just do not want to have them on at all. Perhaps this would make a nice to-do for APEX team to make this available as configuration option for us?

What can we do about it? 

 

Good thing is that APEX team has already done and gave us everything we need to handle this by ourselves. Let's touch on two most obvious options. 

 

Option A - we do not want to show Cancel and Close button at all

How would we do that? 

Since we are talking about popup dialogs we have a way of listening and catching its events in APEX for example when they are opened. 

On page 0 create on page load DA with execute JS code:

$(document).on("dialogopen", function() {  
  console.log('a dialog opened');
});

This gives us all we need to then influence our UI and probably remove these unwanted elements from the page. I am sure there is a CSS way to do this too but here I will explore only JS way. 

 

If you would to run the page now every dialog window would cause it to trigger this but with little tweak by replacing console.log call above with: 

if( $('button:contains("Login Again")').length > 0 )
  {  
  $( "#apex_session_alert_dlg" ).siblings( ".ui-dialog-buttonpane" ).find("button:first-child").hide();
    $( "#apex_session_alert_dlg" ).parent( ".ui-dialog" ).find(".ui-dialog-titlebar-close").hide();
  }

Now we made it so that it only triggers for session expired popup and only if button in there is called Login Again. 

 

Please bare in mind that sometimes people might change this too due to translations etc so adopt the code to it.

This would result that Expires session popup window would appear without Cancel and Close buttons. Yay one down and one to go....

 

Option B -  force a redirect to a login screen

 

Logic here is pretty much similar to before. This time we will leave both buttons to show. 

 

We will be using DA to listen for on close dialog. Let's create a custom DA with Custom Event: dialogclose

That execute JS code: 

if ( $('button:contains("Login Again")').length > 0 ) {
 apex.navigation.redirect ( "&LOGOUT_URL." );
}

Again here we have to check if the specific dialog contained a required button and proceed to avoid it from triggering on every dialog raised by the app.

You can see that if we have detected that we are on the right dialog we will simply redirect the app to the home page which will then ask users to login again.

Save and run the page. 

If session expires and you click Cancel it should redirect you to login screen.

A few more custom options on how to handle your APEX expired sessions.

 

Happy APEXing,
Lino 

 

Note: above code was tested in APEX 20.1 but with no/minimal changes it should work in other versions too

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

https://js.stripe.com/v3/ 

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

document.querySelector('.payStripe').addEventListener('click',

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
$s('PX_STRIPE_TOKEN',result.token.id);

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:

begin
    stripe_api.set_secret(:STRIPE_SECRET);
    
    :PX_PAYMENT_STATUS :=
        stripe_api.charge_card (
            p_amount => 2500
          , p_source => :PX_STRIPE_TOKEN
          , p_description => 'Charged from a D.A page X at '|| sysdate
        );
end;

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 

https://dashboard.stripe.com/test/payments 

also you can check all logs under Developers -> Logs:  

https://dashboard.stripe.com/test/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,
Lino

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 https://stripe.com/docs/payments/checkout/migration 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:

begin
    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 => 'yourEmail@gmail.com'
               , p_success_url => :PX_PAGE_URL_SUCCESS  
               , p_cancel_url => :PX_PAGE_URL
               );
end;

Code for get_stripe_session_token is here. 

 

 

What this code does is executes a POST request call to STRIPE API https://api.stripe.com/v1/checkout/sessions 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');

stripe.redirectToCheckout({
    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 https://dashboard.stripe.com/test/payments also you can check all logs under Developers-> Logs: https://dashboard.stripe.com/test/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,
Lino

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 https://dashboard.stripe.com. 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}]';

stripe.redirectToCheckout({

    lineItems: JSON.parse(str),    

    mode: 'payment',

    successUrl: 'example.com',

    cancelUrl: 'example.com',

    customerEmail: 'yourEmail@example.com'    

  }).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 

https://js.stripe.com/v3/

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 - https://dashboard.stripe.com/test/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 https://dashboard.stripe.com/settings/checkout

 

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 https://dashboard.stripe.com/test/payments also you can check all logs under Developers-> Logs: https://dashboard.stripe.com/test/logs

 

In the next post we will explore how to do the same New Checkout form using Stripe session token.

 

Happy APEXing,
Lino