Wednesday, September 18, 2019

What Info is Available from my APEX Social Sign-In Provider?

Sorry for the brevity, but I hope this helps someone. If you have configured APEX Social Sign-In with Google, Facebook, Okta, Azure--pick your poison--you may have struggled with figuring out what user information is available to you. If you know the name of the value you are looking for, it is pretty easy to get, but figuring out just what is being returned is not documented anywhere (at least not that I can find). So, here is what you can do...

  • Create an application item A_SOCIAL_INFO
  • Edit your Social Sign-In authentication scheme and add the following pl/sql 
procedure post_authentication is

l_index    varchar2(32767);
l_kind     number;
l_value    varchar2(32767);
begin

  l_index := apex_json.g_values.first;
  
  :A_SOCIAL_INFO := '-- Start -- <br>';
  
  for i in 1..(apex_json.g_values.count) loop
    l_kind  := apex_json.g_values(l_index).kind ;
    if l_kind = 4 then
      l_value := apex_json.g_values(l_index).number_value;
    elsif l_kind = 5 then
      l_value := apex_json.g_values(l_index).varchar2_value;
    else
      l_value := null;
    end if;    
    :A_SOCIAL_INFO := :A_SOCIAL_INFO || l_index || ': ' || l_kind || ':' || l_value || '<br><br>';
    l_index :=  apex_json.g_values.next(l_index);
  end loop;
  
  :A_SOCIAL_INFO :=  :A_SOCIAL_INFO || '-- End -- <br>';
  
exception
  when others then
    :A_SOCIAL_INFO := :A_SOCIAL_INFO  || ' err: ' ||sqlerrm;
  
end;

  • Add post_authentication as your Post-Authentication Procedure Name
  • Create a region on a page with region source  &A_SOCIAL_INFO.

Run the application with a new session. Review the results on your page.

I have noticed that some IdP's will not send information unless you explicitly request it as an additional attribute. In your AuthN scheme, add name,address to the Additional User Attributes.

You can then use apex_json.get_varchar2 to get any values based upon what you see as names in the result.

I realize this is brief and lacks any pictures. If you have questions, feel free to leave a comment.

p.s. I'll try to get specific posts up soon for Azure, Google, Facebook, Okta, etc.

Thursday, June 27, 2019

Oracle APEX Social Sign-On with Microsoft Azure Active Directory (Again)

As you probably know, in our industry nothing stays the same for long. Less than six months ago I posted about this topic and provided screen shots of the Microsoft UI for registering an application. That UI has been deprecated, due to be gone shortly. I also found that it lacked some of the features that can be found in the MS Azure portal. So, here is a revision to my January 31, 2019 post.

My co-worker Adrian Png has written a few great posts on social sign-on with APEX. I'd like to provide a quick cookbook with some interesting details here. If you get a chance, take a look at Adrian's posts:
https://fuzziebrain.com/content/id/1709/
https://fuzziebrain.com/content/id/1711/

Here is the quick cookbook.

On the Microsoft side, you will need a few things. I'll give examples below:

APEX URL: https://myapex.com/ords/
Redirect URI: [APEX URL]/apex_authentication.callback

You may also want a home page url, logo, terms of service and privacy policy urls. Those are optional though.


So, let's assume your APEX url is
https://myapex.com/ords/


Log in to the Azure Portal
https://portal.azure.com

Navigate to Azure Active Directory > App registrations



Click New Registration and fill it in


Name is anything you would like to identify your application.

Read the help on Supported account types and choose accordingly. Note: this is an improvement over the earlier registration option described in my January 2019 post.

Redirect URI is your APEX url followed by /apex_authentication.callback:
https://myapex.com/ords/apex_authentication.callback

Copy your Application (Client) ID 

You will need it later


You can optionally add information under the branding section.

Navigate to Certificates and Secrets




Click New Client Secret and add it




Copy the value of your secret. NOTE: You won't have another opportunity to copy this. Make sure you get it now.


The application will need to be able to read minimal information to work. You need to either consent to this on your users' behalf, or allow them to do so themselves. In order to consent, you can edit the user settings and set the first item below to Yes.



If you want to consent on the users behalf, follow this document:
https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-permissions-and-consent

In particular, you would call the URL below, replacing the appropriate elements.

// Line breaks are for legibility only.

GET https://login.microsoftonline.com/{tenant}/adminconsent?
client_id=6731de76-14a6-49ae-97bc-6eba6914391e
&state=12345
&redirect_uri=http://localhost/myapp/permissions



If you are just doing the Microsoft Azure Active Directory / Office365 portion, you are done. Just give the Application ID and Client Secret to your APEX developers and they will take it from here.


OK, on to the APEX installation portion.


Here's an important item...the client secret (or password) that we got from the application registration is a long string. I'll use this as my example: myMsFtsecret{hasSpecial~Characters. That's a great secret, but it won't work just as you see it. You'll get the following error:

AADSTS50012: Invalid client secret is provided. Trace ID: 830c95a3-9bb7-9de1-8a22dcca1600 Correlation ID: 572ef7ea--4329-9674-9e9e3468982a Timestamp: 2019-01-15 22:05:41Z

The problem is that the secret needs to be url encoded. So, let's do that first.

Log into the APEX builder and navigate to SQL Workshop > SQL Commands and do what needs to be done:

select apex_util.url_encode('myMsFtsecret{hasSpecial~Characters') the_stuff
  from dual

That's the stuff:
myMsFtsecret%7BhasSpecial%7ECharacters

It's reasonable to think that the application ID might need to be encoded as well. I've never seen an application id with special characters, though.


Now navigate to App Builder > Workspace Utilities > Web Credentials. Create credential.

Enter the elements as described. It's worth reading the help.




I put the secret in the comments so you can see it.

Almost there. Now go to your application and create an authentication scheme.




Name: Your Choice

Scheme Type: Social Sign-In

Credential Store: MSAzureCred (the one you created)

Authorization Endpoint URL*: https://login.microsoftonline.com/common/oauth2/v2.0/authorize
   Note: depending on who you are allowing to log into your application, you may need to change /common/ to be /yourCompanyDomain/. For example, it may need to be
https://login.microsoftonline.com/insum.ca/oauth2/v2.0/authorize

Token Endpoint URL*: https://login.microsoftonline.com/common/oauth2/v2.0/token
   Note: depending on who you are allowing to log into your application, you may need to change /common/ to be /yourCompanyDomain/. For example, it may need to be
https://login.microsoftonline.com/insum.ca/oauth2/v2.0/token

User Info Endpoint URL: https://graph.microsoft.com/v1.0/me

Scope: User.Read

Username Attribute: userPrincipalName

Convert User Name to Upper Case: Yes   -- you'll thank me for this if you use APEX ACLs.

* This is the error mentioned above that you get if you use /common/ when you should use /yourDomain/:

==============
Sign in
Sorry, but we’re having trouble signing you in.
AADSTS50194: Application 'your-app-id'(YourAppName) is not configured as a multi-tenant application. Usage of the /common endpoint is not supported for such applications created after '10/15/2018'. Use a tenant-specific endpoint or configure the application to be multi-tenant.
==============


You might be done...if you have the root cert for https://graph.microsoft.com/v1.0/me already in your wallet. If not get your dba to add the certificate from https://graph.microsoft.com/v1.0/me to the Oracle wallet.

The other option is to follow Adrian's post (link at the top) to use a reverse proxy instead.

That does it! Maybe...



Another error you might get is:



Need admin approval
YourAppName
yourDomain
YourAppName needs permission to access resources in your organization that only an admin can grant. Please ask an admin to grant permission to this app before you can use it.

If this happens, it is because your organization does not allow users to consent to the application reading their information. In this case, you will need to have an AD Admin (or someone with privs) log in to AD and either give users the ability to consent to the application reading data, or consent for everyone (see document below).










Wednesday, February 27, 2019

Automatically Navigate to Form from Interactive Report - Oracle APEX

How much is a click worth?

I was recently asked if I could have an APEX form page load automatically when an Interactive Report (IR) resulted in exactly one row returned. I spent about 5 minutes coming up with a solution, followed by about 15 minutes working with Jorge Rimblas to improve my solution.

This assumes you are using an IR with the link built within the IR Attributes. To do this with your own column you need to set a Static ID and use that in place of "LINK" in the code below.


Create a Dynamic Action (DA)
It will be On Refresh of your IR


And it will have the Client-side Condition, Javascript expression

($("td[headers='LINK']").length==1)


Have a True action of Execute Javascript Code

It will have Code

apex.navigation.redirect($("td[headers='LINK'] a").attr("href"));


If the target of the link is a modal window, you may even want to set Fire on Initialization to Yes. If the target is NOT a modal, leave this set to No as you'll have a tough time getting to the report otherwise.





Wednesday, February 20, 2019

Peer Code Review Presentation at RMOUG Training Days 2019

I just finished giving my first presentation at RMOUG Training Days 2019. So far it has been a great event. The venue is very nice and convenient. The presentations cover interesting topics and are presented by experts. The organization has been flawless. A big thank you to the RMOUG team.

During my presentation I mentioned that I would provide a link to the artifacts of my presentation on this blog...so here it is:
https://github.com/ainielse/rando/tree/master/peer_review

There are three files:

I'd love to hear any stories of organizations putting this into practice.

Finally, a plug for my session tomorrow: APEX, ORDS and IoT. I hope to see you there.

Tuesday, February 12, 2019

Oracle APEX Consolidated Help

I recently had the need to show "most" of the application help on a single page in my APEX application. In order to make the procedure generic and easy to use, I didn't want to create any additional database objects--just a procedure to output what I needed. I decided to create a separate APEX Navigation List to create the hierarchy of pages that I want to include in the consolidated help page. The easiest way was to copy my "Desktop Navigation Menu" and then modify it. Of course, I could have just used "Desktop Navigation Menu" if it met my needs. The result looks like this





Others may need to do the same thing so I'm providing the code here. I apologize that I don't have good code formatting on this blog. I'm providing it as a standalone procedure, but you should put it in a package.

https://github.com/ainielse/rando/blob/master/output_consolidated_help.sql

Thursday, February 07, 2019

Oracle APEX Less Social Sign-On with MS Azure and Office 365

After my last blog post a natural question came up: what if I'm a little anti-social?

Specifically, what if I do NOT want to make my APEX application available to everyone on the planet that has (or is willing to get) a Microsoft Azure / Office 365 account? What if I only want people in my company, that uses Azure AD, to be able to log in?

There are several ways you can do this, and I recommend you employ at least two. No, recommend is too lenient, I insist you employ at least two :). The first is to change the way you call the Microsoft OAuth2 provider. Instead of using the values in my last blog post:


Authorization Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/authorize
Token Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/token

Use the following:

Authorization Endpoint URL: https://login.microsoftonline.com/yourCompanyDomain/oauth2/v2.0/authorize
Token Endpoint URL: https://login.microsoftonline.com/yourCompanyDomain/oauth2/v2.0/token

For Insum, this would be insum.ca:

Authorization Endpoint URL: https://login.microsoftonline.com/insum.ca/oauth2/v2.0/authorize
Token Endpoint URL: https://login.microsoftonline.com/insum.ca/oauth2/v2.0/token

The method above does NOT secure your application to just your domain. It just makes it harder for someone to use another domain. A savvy user can bypass that by just typing "common" in the url.

The REAL step to secure your application is to do one or both of the following:

  1. Create an Authentication Scheme sentry function that makes sure the username includes @yourdomain
  2. Create Authorization Scheme that makes sure the username includes @yourdomain and apply it to the application.

I am often logged into multiple Azure AD accounts at the same time. By adding yourCompanyDomain to the Endpoint URLs you have the added bonus that users do not need to select a login each time they go to your application. Microsoft will detect the correct one to use.

Edit:

You can also log into the Azure Portal
https://portal.azure.com
and edit the manifest of your application.

Azure Active Directory > App Registrations > [Your Application] > Manifest

Change

"signInAudience": "AzureADandPersonalMicrosoftAccount",

to

"signInAudience": "AzureADMyOrg",

Thursday, January 31, 2019

Oracle APEX Social Sign-On with Microsoft Azure

My co-worker Adrian Png has written a few great posts on social sign-on with APEX. I'd like to provide a quick cookbook with some interesting details here. If you get a chance, take a look at Adrian's posts:
https://fuzziebrain.com/content/id/1709/
https://fuzziebrain.com/content/id/1711/

Here is the quick cookbook.

Let's assume your APEX url is
https://myapex.com/ords/

Log in to the Azure registration portal
https://apps.dev.microsoft.com/

and click "Add an app"
Enter a name and click "Create"


Click "Generate New Password"






COPY THAT PASSWORD! You won't have another chance!
hbUChicago1990){(upzadLTF3%

While you're at it, grab the application ID:
dbf9c4ac-a7d1-4885-84c4-5b80777703f3

(Careful readers may see that I altered the secret and application ID in the text. I never saved the pic above on Azure, but figured I'd change it in the text anyway.)

Click "Add Platform" and choose Web






Enter
Redirect URLs
https://myapex.com/ords/apex_authentication.callback

and here is the magic!! The logout URL can NOT have a ? in it. I tried several times to use something like this:
https://myapex.com/ords/f?p=logout
and I got a very vague error when trying to save:

The problem is the logout URL but it's not at all obvious. So, you'll need to figure out another way to logout. I used an apache rewrite rule, which allows me to do this:
https://myapex.com/logmeout


Add a home page url if you want:
https://myapex.com/ords/f?p=myapp

Scroll to the bottom and save your changes.

At this point the Microsoft portion is functional but users will get a notice asking them if they consent to share some information with the application. It's not a big deal, but you can avoid that notice by having an admin consent for everyone in the organization. There is certainly a way to do it within the Azure admin...well, I would guess there is, but I couldn't find it. I did discover this method, though. Go to the following URL:


https://login.microsoftonline.com/common/adminconsent?client_id=<APPLICATION_ID>&state=12345&prompt=admin_consent

in our example

https://login.microsoftonline.com/common/adminconsent?client_id=dbf9c4ac-a7d1-4885-84c4-5b80777703f3&state=12345&prompt=admin_consent

log in as an admin and consent.


OK, on to the APEX installation portion.

Here's an important item...the client secrete (or password) that we got from the application registration is hbUChicago1990){(upzadLTF3%. That's a great secret, but it won't work just as you see it. You'll get the following error:

AADSTS50012: Invalid client secret is provided. Trace ID: 830c95a3-9bb7-9de1-8a22dcca1600 Correlation ID: 572ef7ea--4329-9674-9e9e3468982a Timestamp: 2019-01-15 22:05:41Z





The problem is that the secret needs to be url encoded. So, let's do that first.

Log into the APEX builder and navigate to SQL Workshop > SQL Commands and do what needs to be done:

select apex_util.url_encode('hbUChicago1990){(upzadLTF3%') the_stuff
  from dual





That's the stuff:

hbUChicago1990)%7B(upzadLTF3%25



Now navigate to App Builder > Workspace Utilities > Web Credentials. Create credential.



I put the secret in the comments so you can see it.

Almost there. Now go to your application and create an authentication scheme.






Name: Your Choice
Scheme Type: Social Sign-In
Credential Store: MyAppAzure (the one you created)
Authorization Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/authorize
Token Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/token
User Info Endpoint URL: https://graph.microsoft.com/v1.0/me
Scope: User.Read
Username Attribute: userPrincipalName

You might be done...if you have the root cert for https://graph.microsoft.com/v1.0/me already in your wallet. If not get your dba to add the certificate from https://graph.microsoft.com/v1.0/me to the Oracle wallet.

The other option is to follow Adrian's post (link at the top) to use a reverse proxy instead.

That does it!




Tuesday, September 18, 2018

Exactly a Year

Wow, this is awful. It looks like it has been OVER a year since I last made a blog post. That's really inexcusable. OVER a year would be way too long. Fortunately, it turns out that I did not go OVER a year between blog posts. I just switched to the Insum blog. You can read my APEX Sitemap blog there:

https://insum.ca/search-engine-optimization-with-apex-creating-a-google-sitemap/

 As astute readers will note, I did not cross the year boundary between blog posts.

Thursday, September 14, 2017

What is the Most Important Skill for an Oracle APEX Consultant?

I've recently had the opportunity to reflect on what makes a great, not just good, but great Oracle APEX consultant. Michael, a principal at C2, recently announced that he's moving on to another opportunity. Michael has been with C2 for over a decade and we will miss him--though we're certain we'll stay in touch. Over the years I've seen many a client shed a tear, literal salty trembling teardrops, when Michael rolled off a project. It's part of the consulting life cycle--if we do a project correctly, we eventually hand it off and move on. I've seen those same clients rejoice when Michael returned to accomplish something new, just to shed another tear at his inevitable departure. I admit, I anticipate the same will happen with me upon his last day.

As I recalled these moments, wondering how to fill Michael's role on our team, I reflected on what made Michael both so well liked and so effective. Fulfilling the buzz-words of the day, Michael is certainly an accomplished full-stack developer on Oracle's low-code Application Express development platform. He is not our absolute best SQL, PL/SQL, HTML, Java, Javascript,  [pick your tech] developer. He is accomplished in most, though, and knows when and who to ask when he needs the absolute best. This certainly makes him highly effective. It makes him good--really good, as good as can be expected. But it's not what makes him great.

Better than just about anyone, Michael listens. Michael listens because he respects the person who is talking, he understands that the business user's time is valuable, that the client knows what they want--even if they need a little assistance formulating what that is. Michael, hears what someone is saying, processes it, reformulates it, solutions in his mind just enough to know if he needs to ask a follow up question, but not so much as to miss the next point. Michael takes notes, because he values the client's time--he wants to get exactly what was said. Occasionally, Michael checks in to clarify that he understood a statement, or to make sure that the solution he very loosely formulated meets an expectation--or perhaps might meet an expectation enough that it might save significant development time. It's that balance that sets Michael apart as a great APEX consultant. He would be great with any technology. APEX has a unique position in the development world--it's ability to rapidly transform a business requirement into a functioning solution. Michael's ability to sprinkle just a little bit of solutioning into his thought process, while actively listening, while making sure that the client knows he is listening, while taking notes and respecting the value of other peoples' time, makes him a great APEX consultant.

It also makes Michael a great friend. Godspeed.

Thursday, May 18, 2017

Moving your APEX Workspace to a Clone

Last September I posted a list of steps to take when cloning an Oracle database that has APEX installed. The scenario is common for some, especially EBS customers--clone production and turn it into development. You have fresh data that way and are sure that you are developing against the exact configuration of the production environment. I'm not suggesting this as a best practice--I'm just recognizing that it happens.

In addition to the steps I described in September, I recently ran into the need to migrate Workspace users and Team Development content from an existing development instance into the new production clone. If you've followed the best practice of initially exporting your workspace from Dev and migrating it to Test and Production, moving users and other content into the clone is pretty easy. By having the same workspace in Dev, Test and Production you automatically also have it in the Production clone.

I won't say this process is supported, but we have used it successfully.

Step 1: Log into the APEX_ADMIN application (INTERNAL workspace).

Step 2: Export the workspace that has the Team Development content you wish to migrate.

Step 3: Edit the export file and remove the following

The section that begins:  prompt  Creating workspace ABC
Any groups that already exist or that you don't wish to create in the clone: wwv_flow_api.create_user_groups 
Any users that exist or that you don't wish to create: wwv_flow_fnd_user_api.create_fnd_user

Step 4: Run the edited file in the APEX_nnnnnn user of the clone.

That's it. Your Team Dev content should be there.

Thursday, May 04, 2017

The Benefits of an IoT Cloud Infrastructure

I recently presented on the Internet of Things at the Montreal Oracle Developer Day. It was a great day of Oracle tech and I had an engaged and enthusiastic group of participants. The session was less than on hour long, yet during this time a few attendees assembled an IoT thermostat while I created an Oracle REST Data Services (ORDS) JSON service to interact with device. I also created an APEX application to control the device and report on the data it passed to the service. I ended the session with an overview of the benefits of an IoT Cloud Infrastructure--specifically Oracle's IoT Cloud Service. After the session a couple people asked me why I used an Electric Imp instead of a Raspberry Pi. These questions let me know that I didn't do enough to highlight the benefits of the infrastructure services.

I often do this presentation as a hands-on lab during which every participant builds a physical device. I may have 60 or more devices to prepare for a single session. While that is a lot of devices, it pales in comparison to the volume of devices that a typical IoT product might involve. How many Nest and Ecobee thermostats are in the wild? Fitbits? Initially programming the devices, hooking them up to a wifi network, communicating with the devices through firewalls and across wide area networks, pushing patches to the devices are big concerns when dealing with the scale associated with IoT. A few years ago, I chose the Electric Imp as a device because it has a built-in infrastructure for handling these kinds of concerns. A Raspberry Pi is a great device, but it doesn't have the built-in IoT support that comes with an Electric Imp.

Oracle is addressing these requirements through its IoT Cloud Service (IOTCS). The benefits of using an IoT infrastructure include the following:

  • Device Virtualization
  • High Speed Bi-Directional Messaging
  • Device Management
  • Stream Processing, Data Enrichment, Event Store
  • Integration via applications and APIs

If you are looking to build a product, it's time to consider an IoT platform. I'll discuss more about the Oracle IoTCS in a future post.

For now, though, I want to encourage people to play with whatever physical to software interface is fun to work with. Inexpensive and effective sensors are readily available. Electric Imp, Raspberry Pi, Arduino, Zensio, Sparkfun Thing and many other platforms make it easy to build up a device. Have fun.


Tuesday, April 18, 2017

XML Parsing failed with LPX-00261: invalid URL


As promised in my last post, I'll address the parsing error you get when you attempt to convert the text returned by the NCBI efetch utilities into an XMLType. The Oracle XML parser within the database treats the XML comment "<!DOCTYPE TaxaSet PUBLIC "-//NLM//DTD Taxon, 14th January 2002//EN" "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd">" as an indicator to validate the XML against the DTD located at "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd". In many, maybe even most, cases this is fine. If you are generating an XML payload you may want to validate it before sending it out to avoid the embarrassment of sending an invalid message. If you are retrieving it from the source, though, odds are good it will be valid and validating it against the DTD may just be extra overhead. It's also possible that the XML parser doesn't understand the DTD. At least one version of the database has a bug related to exactly how that comment is formatted. In my case there are at least two issues related to validating against the DTD, and I have no need for it. Below is the query and the associated error.

select xmltype(
'<?xml version="1.0" ?>
<!DOCTYPE TaxaSet PUBLIC "-//NLM//DTD Taxon, 14th January 2002//EN" "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd">
<TaxaSet><Taxon>
    <TaxId>33208</TaxId>
    <ScientificName>Metazoa</ScientificName>
    <OtherNames>
        <GenbankCommonName>metazoans</GenbankCommonName>
        <BlastName>animals; animals</BlastName>
        <Synonym>Animalia</Synonym>
        <CommonName>multicellular animals</CommonName>
    </OtherNames>
    <ParentTaxId>33154</ParentTaxId>
    <Rank>kingdom</Rank>
    <Division>Invertebrates</Division>
    <GeneticCode>
        <GCId>1</GCId>
        <GCName>Standard</GCName>
    </GeneticCode>
    <MitoGeneticCode>
        <MGCId>1</MGCId>
        <MGCName>Standard</MGCName>
    </MitoGeneticCode>
    <Lineage>cellular organisms; Eukaryota; Opisthokonta</Lineage>
    <LineageEx>
        <Taxon>
            <TaxId>131567</TaxId>
            <ScientificName>cellular organisms</ScientificName>
            <Rank>no rank</Rank>
        </Taxon>
        <Taxon>
            <TaxId>2759</TaxId>
            <ScientificName>Eukaryota</ScientificName>
            <Rank>superkingdom</Rank>
        </Taxon>
        <Taxon>
            <TaxId>33154</TaxId>
            <ScientificName>Opisthokonta</ScientificName>
            <Rank>no rank</Rank>
        </Taxon>
    </LineageEx>
    <CreateDate>1995/02/27 09:24:00</CreateDate>
    <UpdateDate>2017/02/16 16:52:33</UpdateDate>
    <PubDate>1992/05/26 01:00:00</PubDate>
</Taxon>
</TaxaSet>') the_xml
  from dual;


ORA-31011: XML parsing failed 
ORA-19213: error occurred in XML processing at lines 2 
LPX-00261: invalid URL https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd

Fortunately, you can instruct the database to NOT validate.

alter session set events='31156 trace name context forever, level 2'

will instruct the database to skip the validation. You can then use xmlTable, xmlQuery, etc. without receiving an error.

Note: you may need to "grant alter session" to the user in order for this to work, particularly if using an execute immediate within a package to do the alter session command.




Saturday, April 15, 2017

The Man in the Middle

I recently ran into an Oracle database bug (version 12c). It's definitely a bug, and there may even be a patch for it, maybe. We've all been in this position, though. There's definitely a bug. We've identified something similar in the Oracle support site bug database. There's a patch for it. How long will it take to get the patch installed? Can I even install the patch on an Oracle Database Appliance (or Exadata or whatever other special circumstance)? What happens if the patch doesn't fix the specific issue?

Here is my scenario. I need to interact with the National Institutes of Health taxonomy database. It has REST based services--check them out:

https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=taxonomy&term=Metazoa&usehistory=n

https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?db=taxonomy&id=33208

So, super easy, I can use apex_web_service.make_rest_request to make the request, parse the response and do some science!

Let's see what happens:

select 
  apex_web_service.make_rest_request(
        p_url => 'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?db=taxonomy&id=33208',
        p_http_method => 'GET'
        ) taxonomy_info
        from dual
        ;

ORA-29273: HTTP request failed
ORA-28860: Fatal SSL error
ORA-06512: at "SYS.UTL_HTTP", line 368
ORA-06512: at "SYS.UTL_HTTP", line 1118
ORA-06512: at "APEX_050100.WWV_FLOW_WEB_SERVICES", line 636
ORA-06512: at "APEX_050100.WWV_FLOW_WEB_SERVICES", line 760
ORA-06512: at "APEX_050100.WWV_FLOW_WEBSERVICES_API", line 236
ORA-06512: at line 1
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.

Now, I know that I've used this same kind of call from this same database before, so I do a little more digging and try this:

select 
  apex_web_service.make_rest_request(
        p_url => 'https://oracle.com',
        p_http_method => 'GET'
        )
        from dual
        ;

ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 1258
ORA-06512: at "APEX_050100.WWV_FLOW_WEB_SERVICES", line 717
ORA-06512: at "APEX_050100.WWV_FLOW_WEB_SERVICES", line 760
ORA-06512: at "APEX_050100.WWV_FLOW_WEBSERVICES_API", line 236
ORA-06512: at line 1
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.

It's a different error, but still and error! Now I'm getting concerned. I assume it has to do with using https because both errors reference SSL/TLS. I try a variety of http and https sites. Most work fine, but occasionally I get one of the two errors above. I think they are actually two different errors. The second can probably be fixed by adding some certs to the db wallet, but the first, the one I really care about, appears to need a database patch: Patch 24666032. I'm not sure that would fix it, and for a variety of reasons, I can't get the patch installed and tested in a timely fashion.

But...the scientists have work to do and I don't want to stand in the way of science. So, I developed a hack. Please don't judge me. Of course, the title of this post has already given it away. It's seems so obvious when you already know the answer, but it took some pondering to come up with it: the man in the middle. While I am the man in the middle, between the scientists and the data, I need another man in the middle to solve the problem. That MITM is an Apache Reverse Proxy.

As I mentioned, I know this database can use apex_web_service.make_rest_request, definitely with my own internal development web server. I set up a reverse proxy on our internal apache server to route the request to the NIH REST server.


  1. Added an entry to our internal DNS server
    [internal IP address]   ncbi.concept2completion.com
  2. Created a virtual host entry on our internal Apache web server and restarted
    <VirtualHost *:443>
       ServerName ncbi.concept2completion.com
     
       ProxyRequests on
       SSLProxyEngine on
       ProxyPass / https://eutils.ncbi.nlm.nih.gov/
       ProxyPassReverse / https://eutils.ncbi.nlm.nih.gov/
    </VirtualHost>
  3. Pointed the apex_web_service.make_rest_request to our internal server.

select 
  apex_web_service.make_rest_request(
        p_url => 'https://ncbi.concept2completion.com/entrez/eutils/efetch.fcgi?db=taxonomy&id=33208',
        p_http_method => 'GET'
        ) taxonomy_info
        from dual
        ;


<?xml version="1.0" ?>
<!DOCTYPE TaxaSet PUBLIC "-//NLM//DTD Taxon, 14th January 2002//EN" "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd">
<TaxaSet><Taxon>
    <TaxId>33208</TaxId>
    <ScientificName>Metazoa</ScientificName>
    <OtherNames>
        <GenbankCommonName>metazoans</GenbankCommonName>
        <BlastName>animals; animals</BlastName>
        <Synonym>Animalia</Synonym>
        <CommonName>multicellular animals</CommonName>
    </OtherNames>
    <ParentTaxId>33154</ParentTaxId>
    <Rank>kingdom</Rank>
    <Division>Invertebrates</Division>
    <GeneticCode>
        <GCId>1</GCId>
        <GCName>Standard</GCName>
    </GeneticCode>
    <MitoGeneticCode>
        <MGCId>1</MGCId>
        <MGCName>Standard</MGCName>
    </MitoGeneticCode>
    <Lineage>cellular organisms; Eukaryota; Opisthokonta</Lineage>
    <LineageEx>
        <Taxon>
            <TaxId>131567</TaxId>
            <ScientificName>cellular organisms</ScientificName>
            <Rank>no rank</Rank>
        </Taxon>
        <Taxon>
            <TaxId>2759</TaxId>
            <ScientificName>Eukaryota</ScientificName>
            <Rank>superkingdom</Rank>
        </Taxon>
        <Taxon>
            <TaxId>33154</TaxId>
            <ScientificName>Opisthokonta</ScientificName>
            <Rank>no rank</Rank>
        </Taxon>
    </LineageEx>
    <CreateDate>1995/02/27 09:24:00</CreateDate>
    <UpdateDate>2017/02/16 16:52:33</UpdateDate>
    <PubDate>1992/05/26 01:00:00</PubDate>
</Taxon>

</TaxaSet>


A hack? Yes, but done in the name of science.

Stay tuned for my next post on how to solve the parsing issue with the XML shown above. Also, we now have a pl/sql API for interacting with the NIH efetch service--it's extensive and provides access to the taxonomy lineage, synonyms, etc. Contact me if you are interested in the API.