Vagrant and Docker Builds : ORDS 20.2 and SQLcl 20.2 Updates

The recent Oracle REST Data Services (ORDS) 20.2 release prompted my usual reaction. I’ve gone through my Vagrant and Docker builds, and updated them to use ORDS 20.2 and SQLcl 20.2.

The Vagrant database builds, which include ORDS, can be found here.

The Docker ORDS builds can be found here.

There were also some small Tomcat mods.

  • Tomcat upgraded to 9.0.37.
  • HTTP/2 enabled.
  • Compression enabled.
  • Cache-Control enabled for images, CSS and Javascript.

All that went pretty well so as soon as I got to work yesterday I rolled ORDS 20.2 to all non-production environments, and a few “not yet production” environments. If you follow the blog you will know we use Docker for ORDS (similar to my Github builds). It makes rolling out a new version really simple. Just throw away all the containers and replace them with the spangly new ones.

If it’s looking OK after a few days we’ll push it out to the remaining production installations.

Cheers

Tim…

Video : SQLCL and Liquibase : Deploying Oracle Application Express (APEX) Applications

In today’s video we’ll give a quick demonstration of deploying an APEX application using the SQLcl implementation of Liquibase.

I Know what you’re thinking. Didn’t I do this video two weeks ago? The answer is yes and no. This video is very similar to the Liquibase video I did two weeks ago, but that was using the Liquibase Pro client. This video uses the SQLcl implementation of Liquibase, and more specifically the runOracleScript tag to achieve the same thing.

The video is based on this article, which has an example of deploying an APEX workspace and an APEX application.

If you are new to Liquibase and SQLcl, you might find it easier to start with these.

The stars of today’s video are the offspring of Jeff Smith. I had been annoying Jeff on Twitter DMs while he was meant to be on holiday, so I agreed to pay him back by turning his children into international megastars. I take no responsibility for how they handle the fame! 😉

Cheers

Tim…

Video : SQLcl : Format Query Results

In today’s video we demonstrate how to format query results in SQLcl using query comments and the SQLFORMAT setting.

For those of you that prefer to read, the video is based on this article.

The star of today’s video is Charles Wilson, who I got chatting with at the OTN lounge at a previous OpenWorld. 🙂

Cheers

Tim…

Video : SQLcl and Liquibase : Automating Your SQL and PL/SQL Deployments

In today‘s video we’ll give a quick demonstration of applying changes to the database using the Liquibase implementation in SQLcl.

The video is based on this article.

You might also find these useful. The secure external password store is a good way to make connections with SQLcl. If you support a variety of database engines, you may prefer to use the regular Liquibase client.

The star of today’s video is Steve Karam from Delphix. 🙂

Cheers

Tim…

Automating SQL and PL/SQL Deployments using Liquibase

You’ll have heard me barking on about automation, but one subject that’s been conspicuous by its absence is the automation of SQL and PL/SQL deployments…

I had heard of some products that might work for me, like Flyway and Liquibase, but couldn’t really make up my mind or find the time to start learning them. Next thing I knew, SQLcl got Liquibase built in, so I figured that was probably the decision made for me in terms of product. This also coincided with discussions about making a deployment pipeline for APEX applications, which kind-of focused me. It’s sometimes hard to find the time to learn something when there is not a pressing demand for it…

Despite thinking I would probably be using the SQLcl implentation, I started playing with the regular Liquibase client first. Kind of like starting at grass roots. If you are working in a mixed environment, you might prefer to use the regular client, as it will work with multiple engines.

Once I had found my feet with that, I essentially rewrote the article to use the SQLcl implementation of Liquibase. If you are focused on Oracle, I think this is better than using the standard client.

Both these articles were written more than 3 months ago, but I was holding them back on publishing them for a couple of reasons.

  1. I’m pretty new to this, and I realise some of the ways I’m suggesting to use them do not fall in line with the way I guess many Liquibase users would want to use them. I’m not trying to make out I know better, but I do know what will suit me. I don’t like defining all objects as XML and the Formatted SQL Changelogs don’t look like a natural way to work. I want the developer to do their job in their normal way as much as possible. That means using DDL, DML and PL/SQL scripts.
  2. I thought there was a bug in one aspect of the SQLcl implementation, but thanks to Jeff Smith, I found out it was a problem between my keyboard and seat. 🙂

With a little cajoling from Jeff, I finally released them last night, then found a bunch of typos that quickly got corrected. Why are those never visible until you hit the publish button? 🙂

The biggest shock for most people will probably be that it’s not magic! I’m semi-joking there, but I figure a lot of people assume these products solve your problems, but they don’t. Both Flyway and Liquibase provide a tool set to help you, but ultimately you are going to need to modify the way you work. If you are doing random-ass stuff with no discipline, automation is never going to work for you, regardless of products. If you are prepared to work with some discipline, then tools like Liquibase can help you build the type of automated deployment pipelines you see all the time with other languages and tech stacks.

The ultimate goal is to be able to progress code through environments in a sensible way, making sure all environments are left in the same state, and allow someone to do that promotion of code without having to give them loads of passwords etc. You would probably want a commit in a branch of your source control to trigger this.

So looking back to the APEX deployments, we might think of something like this.

  • A developer finishes their work and exports the current application using APEXExport. It doesn’t have to be that tool, but humans have a way of screwing things up, so having a guaranteed export mechanism makes sense.
  • Code gets checked into your source control. This includes any DDL, DML, packages, and of course the APEX application script.
  • A new changelog is created for the work which includes any necessary scripts, including DDL and DML, as well as the APEX script, all included in the correct order. That new changelog for this piece of work is included in the master changelog, and these are committed to source control.
  • That commit of the changelog, or more likely a merge into a branch triggers the deployment automation.
  • A build agent pulls down the latest source, which will probably include stuff from multiple repositories, then applies it with Liquibase, using the changelog to tell it what to do.

That sounds pretty simple, but depending on your company and how you work, that might be kind-of hard.

  • The master changelog effectively serialises the application of changes to the database. That has to be managed carefully. If stuff is done out of order, or clashes with another developer, that has to be managed. It’s not always a simple process.
  • You will need something to react to commits and merges in source control. In my company we use TeamCity, and I’ve also used GitLab Pipelines to do this type of thing, but if you don’t have any background in these automation tools, then that part of the automation is going to be a learning curve.
  • We also have to consider how we handle actions from privileged accounts. Not all changes in the database are done using the same user.

Probably the biggest factor is the level of commitment you need as a team. It’s a culture change and everyone has to be on board with this. One person manually pushing their stuff into an environment can break all your hard work.

I’m toying with the idea of doing a series of posts to demonstrate such a pipeline, but it’s kind-of difficult to know how to pitch it without making it too specific, or too long and boring. 🙂

Cheers

Tim…

ORDS, SQLcl, SQL Developer and SQL Developer Data Modeler 19.4 Released

If you follow me, Jeff or Kris on Twitter, you will already know ORDS, SQLcl, SQL Developer and SQL Developer Data Modeler version 19.4 have all been released. They became available for download late Friday US time, and I got a DM about it yesterday morning, so fresh from spending 2 days running Data Guard builds, I moved on to running builds of some of this stuff.

Oracle REST Data Services (ORDS)

It was a long day, but this is what I did.

  • Updated all Docker ORDS builds : here
  • Updated all Vagrant database builds that include ORDS: here

As you might expect, the config changes were minimal, but the time spent waiting for all those builds to complete was hellish.

The new version of ORDS has an additional prompt during the installation. You get this question.

Enter a number to select a feature to enable [1] SQL Developer Web [2] REST Enabled SQL [3] None [1]:1

The documentation hasn’t been released yet, so I had to run a manual installation to spot the new parameter which was necessary to make the ORDS builds silent. If you want SQL Developer Web, you have to enable REST Enabled SQL also, which requires these in your parameter file.

restEnabledSql.active=true
feature.sdw=true

You’ll recognise the first if you’ve used REST Enabled SQL before. The second is the new one.

I spent some of the time waiting for builds knocking up a quick run through the SQL Developer Web feature, which you can see here.

If you’ve used Autonomous Data Warehouse (ADW) or Autonomous Transaction Processing (ATP) on Oracle Cloud, you will already know what SQL Developer Web is. I put out the post and told Jeff to take a look. Next thing Kris put it on Twitter. I was going to wait for the docs to arrive before mentioning it in public. It did give me some quick feedback, so I was able to update it on the fly as I was playing with the tool. Kind-of “live blogging” I guess. 🙂

SQL Developer Web is a neat alternative to poking holes in your database firewall for client connections. It’s not going to satisfy hardcore developers, but I think this is going to be great for some users. The administrator functionality is interesting, and it will be interesting to see how this develops over time.

SQLcl

All my ORDS builds include SQLcl, so the latest version is on everything listed above. I also run this on my desktop. It’s my main client for connecting to the database because I’m a command line kid. Nothing scary happened on my desktop or on my Vagrant and Docker builds.

SQL Developer and SQL Developer Data Modeler

This is now on my desktop. I spend most of my time in SQLcl, but if I need an IDE, this is the one I use.

It’s a couple of weeks until I get back to work, so it’s going to be a while before I get to use ORDS 19.4 in production, and I’ll no doubt forget to update my SQLcl and SQL Developer installations on my work PC, then be horror struck when I notice a few weeks down the line. 🙂

Happy upgrading!

Cheers

Tim…

Video : SQLcl and Oracle REST Data Services (ORDS)

In today’s video we’ll demonstrate the ORDS functionality built into Oracle SQLcl.

This is based on this article.

There are loads of other ORDS articles here.

The star of today’s video is Arman Sharma, captured at Sangam 2015. Seems like yesterday.

Cheers

Tim…

Video : Vagrant : Oracle Database Build (19c on OL8)

Today’s video is an example of using Vagrant to perform an Oracle database build.

In this example I was using Oracle 19c on Oracle Linux 8. It also installs APEX 19.1, ORDS 19.2, SQLcl 19.2, with ORDS running on Tomcat 9 and OpenJDK 12.

If you’re new to Vagrant, there is an introduction video here. There’s also an article if you prefer to read that.

If you want to play around with some of my other Vagrant builds, you can find them here.

If you want to read about some of the individual pieces that make up this build, you can find them here.

The star of today’s video is Noel Portugal. It’s been far too long since I’ve seen you dude!

Cheers

Tim…

ORDS, SQLcl, SQL Developer 19.2 (Vagrant and Docker Builds)

The folks at Oracle dropped some new presents for us today, including version 19.2 of the following.

I’ve updated my Vagrant builds and ORDS Docker builds with the new versions and everything seems to be working fine so far.

Tomorrow I’ll probably try out some of our development ORDS containers with these releases and see how they work out. They are similar to this build, so I’m sure they will be fine…

Cheers

Tim…

Update: I rolled ORDS 19.2 out to all our Dev/Test environments this morning. We run them all on Docker, so it was really quick and easy. 🙂

ORDS, SQLcl and SQL Developer 19.1 Released, and some Vagrant and Docker Stuff

Yesterday Kris Rice put out some tweets to say ORDS, SQLcl and SQL Developer version 19.1 had been released.

As usual I downloaded SQL Developer and put it on my desktop, and I started the process of updating my Vagrant and Docker builds. If you are interested in that stuff, you can find those updates here.

You won’t be surprised that these updates are pretty simple. Just replacing the environment variables with the new version numbers. Such are the joys of automation. 🙂

Happy upgrading!

Cheers

Tim…