Oracle APEX and Adobe Sign integration - part 1

 

Oracle APEX and Adobe Sign electronic signature integration

Getting your documents signed electronically

 

Back in 2017 I had an opportunity to write about DocuSign integration with Oracle APEX and documents generated with AOP. This time around it is the post about its alternative Adobe Sign. 

 

How do we integrate Adobe Sign and electronic signature with our APEX apps? 

Before we get started first thing we need to do is read/visit an extensive documentation on Adobe website. 

 

After initial intro from Adobe you should have an idea how things work. As usual we typically have to sign up for the service and create an application which will be authorized and configured to interact to your APEX app. To simplify this process I am using here apex.oracle.com where all ACL and configs have been already done for me. For a demo test run I do not need anything else. 

 

Great thing is that Adobe provide us with a developer account to try things out, test it out before moving things forward.

Following the instructions I created an app called APEX_SIGNATURE

 

Here there are two separate parts - one is hidden under View/edit and second one is under Configure OAuth for Application. 

 

If you created one application and click on View/edit here you will find your two most important parameters ClientID and client secret. Write these down somewhere as we will need them later on. 

Under Configure OAuth link you will find a Redirect URL which you will have to change/configure but we come to it shortly. 

 

Adobe OAuth application request permissions

Before we can do anything in Adobe with signatures there is a small process we need to go through. Process requires that the our application requests permissions from the end user before performing any actions on their behalf.  More is described in details here. 

 

If we look closely /public/oauth API you will see that it has an input parameter called redirect_uri which we will need to provide among few other. Why is this?

An example would be:

https://xxxxxxxx.com/public/oauth?
   redirect_uri=YOUR_REDIRECT_URL&
   response_type=code&
   client_id=xxxxxxxxxx&
   state=xxxxxxxxxx&
   scope=user_read

How Adobe Authorization request process works is, we will make the call to Adobe Authorization API and it will redirect all users to sign into the adobe to allow future requests from our APEX apps. 

 

Once you retrieve your Client ID you can initiate this process from a browser. After initial Adobe login screens user will be provided with the screen like bellow.

Okay but thinking about it, how do we handle this from within our APEX apps. 

Imagine I am a logged in end user, I click on a button to authorize Adobe Sign use from this app this redirects me to Adobe sign website where I type in my Adobe login details. Perfect, no issues so far but after I click on 'Allow', it will send me back to my APEX app!?? 

 

This will for sure break my session and kick me out of the screen where I initiated this process. If you didn't come across this scenario before, it may be unusual but we had it on few other REST API integrations too like Xero for example. 

 

Before we go any deeper we need to figure out how to bypass this problem. 

 

I mentioned earlier the config of REDIRECT URI under developer account that we can set as part of our Adobe application configuration. This is the same url we can now use during first authorization call to adobe authorization process.  If you do not configure and align these two redirect URLs, things will not work as it requires us to authorize the URI used in the process. 

 

Since this URI in my Adobe application is not dynamic, meaning I can not provide dynamically my APEX session ID for example, we need a way of doing this somehow differently.

 

In Adobe documentation they say 

When your customer initiates the OAuth process by clicking your app’s Sign link, their browser redirects to the redirect_uri specified in the initial request. Query string parameters are added to indicate whether the request succeed or failed.

This is something we can use for sure. 

 

What is basically telling us is, there is an in/out state parameter that goes in which then will be added/returned unchanged to the end of URI we provided under redirect_uri along side of code as second out parameter. This is if things went well else we will have an additional error parameter returned.

 

The Code value that is returned as an out parameter is the 'first golden' token returned to us, which then can be used to retrieve the Authorization Token needed before anything useful can happen in Adobe.

I know they could not make this any easier, right? 

 

Back to the APEX problem, if I provide my session ID to this state parameter I should be in theory able to redirect my users to the same app not requiring another login.

 

First idea was that I could generate a potential one-time generated token that could be reused to then re-login my end user. But there is a simpler way too. 

 

I could use APEX REST api to redirect my end user to and APEX link which will include the session id on it too. State and Code output parameters would then simply become URI sting based REST parameters which we can and know how to handle.

 

If all is done correctly this should bring the end use back on to the screen where the process was started. Brilliant!

Let's look now how we can build one of these REST APIs that can be used as redirect_uri parameter for calls similar to this one.

Create a REST service like: 

https://apex.oracle.com/pls/apex/YOURREST/adobe/redirect

Resource handler is GET with source type PLSQL. 

That has parameters

If you invoke your REST url from your APEX application now.

 

I created an app with a home page where I created a button that redirects me back to this url: 

 

https://apex.oracle.com/pls/apex/YOURREST/adobe/redirect?code=&CODE.&state=&APP_SESSION.

Why did I use code and state as parameters, this will be added on by Adobe and we are simulating the same scenario. It should redirect you back to the same page you were on without the login.

Once this is working we can look into calling the Adobe URL with all necessary parameters. This is for the next time.

 

Hope it saves you time;

 

Happy APEXing,

Lino
 

Thank you Garry.

Getting different results in APEX app vs SQL Commands/database - default conversion?

 

Oracle APEX vs SQL Commands/database execution - default conversion

Getting different results?

 

Another one for the books. You have a procedure or a function that someone developed which runs in APEX application without any problems. 

 

Someone gives you to change a smallest piece in it and you are ready to test it. You take the same code and try to run it in SQL commands or in your SQL developer. 

 

All of the sudden some strange database errors come up like typically ORA-01843 not a valid month.

You make sure parameters you pass in are correct? Check. 

You are sure all other things are set correctly as in your APEX app? Check.

 

One in APEX runs fine where this one will not and you are losing your patience/time finding why this error is now occurring. 

 

This is a beauty of implicit conversions which I am sure you heard earlier about. Meaning if you do not follow standards across all of your code, default conversions might be happening between these different environments that you are not expecting and that your code is not handling well but you just didn't notice it.

 

What do I mean? Well if you run your code in APEX and it works there might be some default date format settings that differ from your local database or SQL commands settings. As soon as this is the case there might be issues in your code if implicit conversions are being used over providing explicit formatting especially when dates are involved.

 

Okay but how do we confirm this theory? Easiest possible way would be create a region in APEX and check what it does in this situation

You will get a result in a page like 10-JAN-24 in my example. 

 

Which means your APEX NLS_DATE_FORMAT could be "DD-MON-RR". 

 

This is typically set on application globalization settings. If left empty they might be defaulting to this.

Cool lets see what SQL commands now says about the same thing.

In my case I got 01/10/2024 which indicates MM/DD/YYYY as a default format. What do you think will happen when your data do not match one of the two formats - clearly you would get an error. 

Of course in most cases you would look deep under into where exact problem is and apply appropriate formats to align both sides and problem would go away.

 

Now it may seem obvious but on the other hand you can spend hours debugging and looking for why it works in one where it doesn't work in the other. 

 

It is important to remember these settings can differ and cause you issues. Like it was in my case. Even though it is clear to everyone why it happens it might not ring a bell in the head at the right moment.

 

Problem also was that I wasn't able to change/fix root cause of this error and that all that was needed was to make thing tested and give back to the team. 

 

So how do we make it work to force required date format on SQL commands side too. Before the call to your procedure in your SQL commands session you can execute

and after this it will run without errors; exactly as it does in your APEX session.

 

Hope it saves your time; Thank you Garry.

 

Happy APEXing,

Lino
 

 

Oracle APEX Classic report Card template - card link

 

Oracle APEX Classic report Card template - card link

Making your card be a link?

 

This one is more for my reference as it comes handy on many occasions.

 

You made a classic report with a card template based on a query and you want your card title link to be applied on card level?

Quick cookbook:

1. create region based on a query
   
2. pick one column and make it a link using column settings for example card_title
3. Create After Refresh region Dynamic action and this JS to it

$('.t-Card-title a').each(function(index) {
   lnk = $(this).attr('href');
   
   $(this).closest('.t-Card-wrap')          
          .attr('data-href', lnk)
          .click(function(){
            window.location=$(this).attr('data-href');
          })
          .mouseover(function(){
            $(this).css('cursor', 'pointer');
          })
          .mouseleave(function(){
            $(this).css('cursor', 'default');
          })
});

Similar technique can be applied to IR report with different selectors. 

 

Happy APEXing,

Lino
 

 

Oracle APEX page Item type Number - Format and alignment vs Read Only

 

APEX page Item type Number - Format and alignment vs Read Only

Format is not respected and item changed alert triggers?

 

Here is the situation, you have a simple form with number fields. Imagine you have to make some read only vs some that need to be enter-able but right aligned.

We all know it is easy to do - you would go into the page and change item settings and move onto the next task. 

If you look closely you will notice that read-only field will encounter two issues. It does not format the number any more if you provided the format. 

 

Secondly, APEX also will not respect your Number Alignment setting. For example if you decide to right align your read only number item, it would still stay on the left side.

Fix for this part is easy. 

Formula is: 

1. retrieve read only field from a DB as in required format with to_char()
   
2. apply css style="text-align:end"
   

Of course you can put this in a .css file, apply a css class to your items and handle it in more manageable way.

Last bit we want to address is WarnOnUnsavedChanges message. 

 

In APEX 23.1 and APEX 23.2 (potentially earlier versions too) we noticed that after you make your item of a Number Type and you apply format mask, instantly you will get

 

warning message. Question is Why?

We are talking about a standard item type => Number item. You might think, here we go again, who knows what has been changed on the page and how do I detect which one of my last changes did this to try fixing it.

How do we detect what items have changed on the page so we can address the problem? There have been numerous blogs on this topic from Jeff Kemp, Martin D'Souza (link is broken) and Hayden Hudson on how to detect changes on the APEX page which might come in handy for you.

After playing around a bit you will pick that just because you configured your item to be of the Number type and you set some formatting on it, APEX page will start 'falsely' alerting you of a Unsaved Changes on this page. 

I say 'falsely' as this is not something we would expect as a result of a simple item setting change. Worst is if your end users pick this up.

Interestingly - if you do the same for example with a Date picker item type and you add formatting to it. WarnOnUnsavedChanges will not be triggered. 

Question is how do we fix this? Well this is still work in progress. 

 

For now you can apply similar logic as we did above for a read only number items. Or you can turn Warn on Unsaved Changes off.

Still it needs to be checked if this happens in 23.x version only or has it been there since number item type was introduced. 

Unfortunately, format mask does not work on Text Field types. 

 

Happy APEXing,

Lino

 

For my reference here  snippet from Hayden's code

var errors = [];

allItems = apex.page.forEachPageItem;
allItems(
    $("#wwvFlowForm"),
    function (el, name) {
      if (
        apex.item(name).isChanged() &&
   !apex.item(name).element[0].classList.value.includes("js-ignoreChange")
      ) {
        errors.push({
          message: "This item has unsaved changes",            location: "inline",
          pageItem: name,
        });
      }
    },
    true
  );

  apex.message.clearErrors();
  apex.message.showErrors(errors);

 

 

ORACLE APEX page item Text Case setting

ORACLE APEX page item Text Case setting

Value Placeholder text gets updated too - How to fix it?

We may argue that this is one of unwanted features of APEX 23.x but it is what it is. 

Here is the situation, you have a simple form with fields and someone asks you to impose a rule on one of the items that as soon as end users enters a value we want it to be upper or lower cased. 

Luckily there is a nice Item setting to help us with it called Text Case situated under Settings section

 

Only issue we found is that except doing what you expected making your input text upper or lower cased, it will also influence how your value place holder works which is maybe not what you wanted. 

To fix this you need to apply this CSS:

.apex-item-text::-webkit-input-placeholder { /* WebKit browsers */ text-transform: none; }

.apex-item-text:-moz-placeholder { /* Mozilla Firefox 4 to 18 */ text-transform: none; }

.apex-item-text::-moz-placeholder { /* Mozilla Firefox 19+ */ text-transform: none; }

What basically happens is we are hitting the default behavior of how :placeholder works on an input. And APEX is simply inheriting it from there. 

Since this was not what we wanted this snipped of CSS code was helpful. 

Thanks to Jeff Kemp and Tim Kimberl for their feedback.

 

Happy APEXing,

Lino

 

 

ORACLE APEX Application item with global scope

 

ORACLE APEX Application item with global scope

Sharing the value between two or more applications - How to?

Simple use case - we have two apps that share authentications and now we want to pass the application items value between these two. 

For a long time now APEX application definition had this feature where we can declare global application items. 

But what does it actually mean and how do we actually do this when there are more apps in place?!! 

Create an app, add an application item to it with a global scope. Have some way of setting this item to a certain value. So far so good. 

Okay now we can create a link to an app #2 where we will try to read this value out. 

Of course first thing we would need to do, is we need to set and align both Authentication Schemes to share their cookies. This way we do not get ask to re-login. We all knew this so far.

In application #2 create a region and simply reference globally set application item from an app 1 using substitution variable syntax. In my example this would be &APP_GLOBAL_ITEM. 

But if you run this.... nothing would happen. Or better said you would not get the expected value back. Question is why? 

In order to get this going the point is that you have to have the same application item created in both of these apps. Else it could leave you wondering what you have done wrong. 

Thanks Mark G. for bringing this to my attention and here is a reference to existing post with the same topic.

https://stackoverflow.com/questions/58065558/oracle-apex-how-to-access-application-items-with-global-scope-in-multiple-applic

Happy APEXing,

SLino

Using this.triggeringElement in apex.item

ORACLE APEX Dynamic action - apex.item and this.triggeringElement

A perfect JavaScript expression usecase

If you ever wanted to trigger your DA based on an item that is a select list here is a quick read.

Scenario here is we want to trigger an action when item we interacted with contains a certain string like NBME in my case. How would we do this? 

To be 100% correct we want to check it against the item display value and not its return value (one hidden from the end users).

I strongly encourage you to visit apex.oracle.com/jsapi for a full reference of apis available to us. 

We all know about apex.item api which can easily give us the value of the item with something like apex.item("PX_TEST_CODE").getValue()

Problem here is that if we were to do this we would have gotten a value of return value from my select list item and not the one we are using as display. 

 

Fair enough we have also a function called displayValueFor() which sounds like our thing. 

If we pack it all together on our DA Client side condition we can do JavaScript expression with following code:

apex.item(this.triggeringElement)

 .displayValueFor(apex.item(this.triggeringElement).getValue())

            .includes('NBME')

What this will do is take our a return value of triggering element then it will get a display value of it in an item that is triggering it. At the end it will check it if it includes NBME string and return true or false as  a result of it all. 

It seemed I was coming back to it a few times so wanted to write it down somewhere.   

Happy APEXing,

SLino