Tag: ORDS

ORDS, SQLcl and SQL Developer 18.3 Updates (VirtualBox, Vagrant, Docker)

A few days ago we got version 18.3 of a bunch of Oracle tools.

Over the weekend I updated some of my VirtualBox and Vagrant builds to include these versions. If you want to play around with them you can see them on GitHub here.

I also updated my ORDS Docker container build, which uses both ORDS and SQLcl. You can find this on GitHub here.

I use this container for live demos of ORDS, as well as a demo for my “DBA Does Docker” talk, which I am doing at Oracle OpenWorld this year.

I put the latest versions of SQL Developer and SQLcl on my laptop. I’m doing an analytic functions talk at Oracle Code One this year. The demos use SQLcl on my laptop connecting to Autonomous Transaction Proccessing (ATP) on Oracle Cloud. I had a little bit of drama with SQLcl on Saturday, which turned out to be PEBCAK. I thought “SET ECHO ON” wasn’t working, but it turned out I had a “login.sql” file in the path that contained “SET TERMOUT OFF”. Once I removed that setting the demos ran fine. 🙂

I’m going to put a freeze on changing my stuff until after OpenWorld and Code One. Honest. 🙂

Cheers

Tim…

Chrome 68, HTTPS , Let’s Encrypt and ORDS

In February Google released a post about Chrome 68, due for release in July, which will increase the pressure to adopt HTTPS for all websites because of this behaviour change.

Basically HTTP sites will be marked as insecure, rather than just getting the (i) symbol.

Recently I’ve seen a bunch of sponsored posts talking about this in an attempt to sell certificates. GoDaddy are pushing the advertising hard. I just wanted to remind people there is a free alternative called Let’s Encrypt you might want to consider.

Let’s Encrypt

I’ve been using HTTPS for a few years now, but over a year ago I switched to using the free Let’s Encrypt service to get my certificates and so far I’ve had no problems. I wrote about this in a blog post here. That links to this article about using CertBot to automate the certificate renewal, which includes the Apache HTTP Server config.

The article also links to this article about configuring HTTPS for Tomcat, which includes an example of using a Let’s Encrypt certificate.

I always run Oracle REST Data Services (ORDS) under Tomcat, so this is how I HTTPS enable ORDS. If you would prefer to run ORDS in standalone mode, but still want to use a real certificate Kris Rice has your back with this article.

Of course, you shouldn’t be having direct traffic to Tomcat servers or standalone ORDS services you care about. They should be sitting behind some form of reverse proxy, or a load balancer acting as a reverse proxy, which is performing the SSL termination. In my company, we have the real certificates on the load balancers, which perform the SSL termination, then re-encrypt to speak to the services below them.

Thoughts

In general I think the push towards HTTPS is a good thing, but I do have a few reservations.

  • There are plenty of sites, like my own, that don’t really do anything that requires encrypted connections. You are just there to read publicly available stuff. Marking them as insecure seems a little stupid to me. Update: As pointed out in the comments, it does make it harder for people to intercept and change the information during transit.
  • A bigger beef is the fact that anything with a valid HTTPS certificate is marked as “Secure”. If you work in IT you understand this just means the connection is secure, but what does it mean to other people? I could understand it if some people thought it meant it was a safe website to visit, when it means nothing of the sort. If HTTPS is the new “normal”, I think the browser should stop marking it as secure, and only flag when it is insecure. Update: It seems this is going to change (here). Thanks to Gary for pointing this out.
  • It worries me that Google can make this decision and the rest of the world has to jump. This all started when they began to alter index ranking based on the presence of HTTPS, which is why I first enabled HTTPS on my website about 4-5 years ago I think. Now the Chrome market share of about 60% is such that they can make big changes like this without having to get buy in from the rest of the world. The motives are good, but I don’t like it.
  • I’m not saying you shouldn’t pay for certificates. My company still does. I’m just saying you have a choice, especially if it is something that you do for fun like this website. In this case the free option is always the good one. 🙂

Happy encrypting…

Cheers

Tim…

ORDS, SQL Developer and SQLcl Version 18.1 Released : Plus Some ORDS Documentation Comments

If you’re active in the Twitter-verse you will have seen a bunch of tweets yesterday about the release of the 18.1.x versions of Oracle REST Data Services (ORDS), SQL Developer (and Data Modeler) and SQLcl.

The first thing I did was edit my ORDS Docker build to use the latest versions of ORDS, SQLcl, Tomcat9 and Java9. If you are interested in playing with that you can find the build on my GitHub here. It was all smooth sailing! 🙂

There are a couple of things I would like to point out about the ORDS 18.1 documentation.

ORDS Installation in Multitenant

The way I read it, the documentation suggests there are two ways of installing ORDS in a multitentant environment, both of which involve installing it into the CDB.

Multitenant is an architecture, so it doesn’t imply the presence of the Multitenant option, or multiple PDBs in the CDB. In fact, I would suggest the vast majority of CDBs will only ever contain a single PDB, as most instances will eventually switch to Lone-PDB (for free) now that non-CDB is deprecated. So what does this have to do with the installations described above?

The advantage of installing ORDS in the CDB is you can have a single connection pool for all PDBs in the instance, which is a big advantage if you have 4096 PDBs in your CDB. If you only have one PDB per CDB, there is no advantage, and actually there is a disadvantage because the PDB has yet another dependency on the CDB, which means ORDS must be installed in the other CDBs before you clone/relocate a PDB to them.

In my opinion, the best way to install ORDS for Lone-PDB, and possibly even for small numbers of PDBs in one instance, is to install directly into the PDB, just like we do with APEX. This means each PDB needs its own connection pool in ORDS, but that’s not a problem. This way the PDB doesn’t have an external ORDS dependency on the container and can be moved between containers without any fuss.

You will see my ORDS installation article installs directly into the PDB, although I will be amending it with some comments about other options.

Database Authentication for PL/SQL Gateway Calls

One of the new features of ORDS 18.1 is you can now use database authentication to provide basic authentication for your calls to PL/SQL. You won’t see that in the documentation though. It’s only present in the examples under the “/path/to/ords/examples/db_auth” directory when you unzip the ORDS media.

Using this type of authentication is not advisable, it’s much better to use OAuth2, but for people like me that have a lot of applications and XML web services still using mod_plsql, this is a really handy feature, and certainly eases the transition from mod_plsql to ORDS.

It would be nice if this feature were put into the main documentation, as it will be a welcome addition for many people out there. It is a feature, so it should be documented as such, and anything that improves the uptake of ORDS has got to be a good thing.

How’s about including the document upload/download functionality from mod_plsql in ORDS also? That would help with the transition too. Yes, I know it’s easy to code this yourself, but that is still an application change, rather than just switching from one gateway to another. Just sayin’. 🙂

I’ll be playing around with ORDS 18 over the coming weeks. I’ll probably amend some of my existing ORDS articles as a result of that, and no doubt put out a new article on database authentication, for the lost souls that don’t look at the media examples, like me before Jeff told me to. 🙂

Cheers

Tim…

Oracle REST Data Services (ORDS) 17.4 Released

Over the weekend I saw Kris Rice tweet about the release of ORDS 17.4. This is the GA release of what was the 17.3 beta. Remember the version names are time-based now.

I’ve already written about the main new features of this release.

I’ve done some installations and upgrades and all was fine. My existing installation and upgrade docs work fine.

I’ve even updated my Docker image to include it. 🙂

Cheers

Tim…

Docker : My First Steps

In a blog post after OpenWorld I mentioned I might not be writing so much for a while as something at work was taking a lot of my “home time”, which might result in some articles, but then again might not… Well, that something was Docker…

After spending a couple of years saying I was going to start looking at Docker, in June I wrote a couple of articles, put them on the website, but didn’t mention them to anyone.  I was finding it quite hard to focus on Docker because of all the fun I was having with ORDS. More recently it became apparent that we have a couple of use-cases for Docker at work, one of which involved ORDS, so it reignited my interest. There’s nothing like actually needing to use something to make you knuckle down and learn it… 🙂

Having gone back to revisit Docker, I realised the two articles I wrote were terrible, which wasn’t surprising considering how little time I had spent using Docker at that point. The more I used Docker, the more I realised I had totally missed the point. I had come to it with too many preconceptions, mostly relating to virtualization, that were leading me astray. I reached out to a few people (Gerald Venzl, Bruno Borges & Avi Miller) for help and advice, which got me back on track…

I’ve been playing around with Docker a lot lately, which has resulted in a few articles, with some more on the way. I’m not trying to make out I’m “the Docker guy” now, because I’m clearly not. I’m not suggesting you use my Docker builds, because there are better ones around, like these. I’m just trying to learn this stuff and I do that by playing and writing. If other people find that useful and want to follow me on the journey, that’s great. If you prefer to go straight to the source (docs.docker.com) that’s probably a better idea. 🙂

I do a lot of rewrites of articles on my website in general. This is especially true of these Docker articles, which seem to be in a permanent state of flux at the moment. Part of me wanted to wait until I was a little more confident about it all, because I didn’t want to make all my mistakes in public, then part of me thought, “sod it!”

If you want to see what I’ve been doing all the articles are on my website and the Dockerfiles on Github.

I’m having a lot of fun playing around with Docker. You could say, I’m having a “whale” of a time! (I’ll get my coat…)

Cheers

Tim…

Oracle REST Data Services (ORDS) 3.0.11 Released

If you’ve been following the right people on Twitter (Kris Rice, Jeff Smith, Colm Divilly, OracleREST) recently you will have seen ORDS 3.0.11 has been released.

This is a really neat release for us as we run ORDS on Tomcat. ORDS version 3.0.10 introduced a bug which broke it on Tomcat 8.5.x, so we had to switch to the Tomcat 8.0.x branch to use it. With ORDS 3.0.11 we have been able to switch back to Tomcat 8.5.x. Happy days! 🙂

Thanks to Colm for fixing this on the plane. 🙂

If you’ve not had a play with ORDS yet, you really should! I’ve written a bunch of stuff about it here.

Cheers

Tim…

PS. It also works on Tomcat 9. 🙂

Oracle REST Data Services (ORDS) 3.0.10 : Going down the rabbit hole!

Oracle REST Data Services (ORDS) version 3.0.10 was released last week. In addition to a bunch of bug fixes it was the first release to include the Auto PL/SQL feature.

Auto PL/SQL is similar to the AutoREST feature available for tables and views, but it allows you to enable PL/SQL objects for Remote Procedure Call (RPC) over HTTP(S). At this point you might be asking yourself what the difference is between REST and RPC over HTTP and I explain that here. Regardless of whether it is REST or not, it’s a nice convenience feature that I’m sure some people will find useful.

Over the weekend I went full on down the rabbit hole, which resulted in this article.

There are a number of issues with the current release and the docs for it, all of which have been fed back to the relevant parties, but on the whole I think it’s a neat first step.

My preference is still to define conventional ORDS RESTful web services rather than use this feature, but Auto PL/SQL may be just what some others are looking for and it’s always good to have options! 🙂

As far as the 3.0.10 release generally, I upgraded 4 non-prod installations to this release, all actively used for fronting APEX and RESTful web services and nobody has had a problem yet. 🙂

If you are yet to experience the joys of ORDS you can read the articles I’ve written about it here, as well as an introduction to JSON here.

Cheers

Tim…

Old Database Sessions and some more ORDS

I spent some time on two totally unrelated things at the weekend.

Old Database Sessions

We’ve been having some problems with old database sessions recently. One of our reporting servers seems to grab loads of sessions and just not let go. In many cases they seem to remain active for a long time, while doing trivial tasks. My first thought was it was a problem with the database, but there doesn’t seem to be any evidence of that. On occasion we’ve restarted the reporting server and everything has been fine for a few days. I have no control over this reporting server, so I took the pragmatic approach of killing old sessions. Yes, I know it’s an ugly solution, but it works.

I was planning on using a profile to do this, but found there were several applications using the same credentials for different purposes (I inherited this, it’s not my fault OK. 🙂 ), so the profile approach was not going to cut it. Instead I wrote some code to identify the dodgy sessions and kill them, then called this from a job. Ugly, but it worked!

On Saturday I received a Facebook message asking me how to handle this exact same issue, so rather than answer directly I decided to write it up as an article.

I didn’t put it on the front page of the website because it is a back-fill article, rather than bringing anything new to the table.

New ORDS Article

After my recent UKOUG Tech16 session, where I talked about how *I* think you should use ORDS, I wanted to give a better example than the simple CRUD examples seen in most articles. One of my points was RESTful web services against Oracle should represent what Mark Farnham would call Logic Units of Work, rather than a CRUD interface over a table. That resulted in this article.

It includes processing the JSON payload with JSON_TABLE and the APEX_JSON package.

Cheers

Tim…

Playing with Oracle REST Data Services (ORDS)

ordsI think it’s been about 18 months since I first wrote about installing ORDS 3, but since then I’ve done little more than dabble. For historical reasons, we are fronting APEX and exposing data as web services using Oracle HTTP Server and mod_plsql, which comes with its own set of pros and cons.

During the OTN Cloud Developer Day we were supposed to be using ORDS to expose our tables as web services, and my lack of ORDS experience was evident. 🙂 Since then I’ve been determined to take a proper look at ORDS. If you’ve been looking at the website homepage recently, you will know that process has started in earnest.

It’s by no means complete and the more I play with this stuff, the more rewriting I find myself doing, but I’m well on my way to understanding this stuff now.

For someone used to using mod_plsql and Data Access Descriptors (DADs), the authentication model and configuration of database connections in ORDS seems a little weird at first. I tied myself up in knots a few times before I understood it. While you’re getting to grips with this stuff, I would advise regularly flipping back to a clean VM snapshot, so you can approach things with a clean slate. I kept jumping to conclusions, only to find some of my results/issues were due to remnants of previous tests.

The official ORDS documentation is not the best. It’s like you need a decent grasp of the situation, or the documentation won’t make sense. Not that helpful when you’re coming to it fresh. 🙂 The documentation for the OAUTH and ORDS packages is pretty poor. I found myself just using trial and error to figure stuff out because stuff was just plain missing from the docs. I swear this is one of the examples.

Examples

The following example creates a .

EXECUTE ORDS.DEFINE_MODULE(

There are also things like this, which don’t really help.

Examples

The following example ...

EXECUTE ORDS.ENABLE_OBJECT((
   p_enabled      => ...,
   p_schema       => '...',
   p_object       => '...',
   p_object_type  => '...',
   p_object_slias => '...',
   p_auto_rest_auth => ...);

One of the last articles I did was the SQL Developer one, which in hindsight was a mistake. Some, but not all, of the SQL Developer screens allow you to see the package calls it is using to get the job done, which would have saved me a lot of time. Having said that, I probably learned a lot more because of the rough ride. 🙂

Like I said, I’m still learning so don’t give me a hard time if I’ve goofed up, but by all means drop me a line if you spot any mistakes and I’ll correct them. 🙂

Cheers

Tim…

PS. Jeff Smith just reminded me that SQLcl can export ORDS module definitions, so I’ve added it to the SQL Developer article here.

PPS. Added in the SODA functionality also.

Oracle REST Data Services (ORDS) 3.0 on Tomcat 7

What started out being a quick dabble with Oracle REST Data Services (ORDS) 3.0 spawned a bunch of different things…

First, I needed a fresh APEX installation to play around with. Most of the APEX stuff I’ve been using recently has been against Oracle 11.2 databases and I follow this kind of method. Standard stuff…

That’s fine, but the default installation of APEX in Oracle 12c multitenant environments splits APEX between the CDB and the PDBs. On the surface that sounds like a good thing. Installing APEX multiple times sounds like a waste right? In practice, this “shared APEX” situation is a complete pain in the ass and I hate it. So the first thing I now do on a new installation is to remove the shared APEX stuff. That spawned this note.

It’s actually a pretty important decision, because Oracle don’t support removal of APEX from the CDB once it has PDBs, so you will be faced with a bunch of unplugs and administration if you change your mind later. Having to decide up front if you may want to do something later is a total fail in my book. I hope Oracle scrap this shared APEX setup. I would prefer to see people create a new PDB from the seed, install APEX into it and use that as a seed for new PDBs. Effectively building their own gold-image seed… That’s my little rant over. 🙂

Next thing I needed was a Tomcat installation. Despite the ORDS 3.0 documentation suggesting Tomcat 7, I started with Tomcat 8. Word to the wise, it doesn’t work, at least not by just following the ORDS install docs anyway.

Since I was having problems with the ORDS installation on Tomcat 8, I switched back to the recommended Tomcat 7. This wasn’t so much a case of RTFM as a case of BTFM. The “B” is for Believe. 🙂

Once I switched to Tomcat 7, the ORDS installation went just fine. Here is a note about that…

So now we are four articles in and I’m just about able to start the thing I wanted to actually test. 🙂 Like I said, a quick dabble spawned a bunch of different things… 🙂

Cheers

Tim…