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! 😉



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

Today’s video is a quick demonstration of deploying an Oracle Application Express (APEX) application using Liquibase.

The video is based on a new article of the same name, which covers the deployment of both APEX workspaces and APEX applications using Liquibase.

Here’s some other content you might find useful.

The star of today’s video is Jorge Rimblas, making a welcome return to the channel, along with some serious reverb. 🙂 Last time we saw Jorge was in a boxing gym, and his daughters have also taken the spotlight for one video.



Video : APEX_DATA_PARSER : Convert simple CSV, JSON, XML and XLSX data to rows and columns

Today’s video is a quick demonstration of using the APEX_DATA_PARSER package to convert simple CSV, JSON, XML and XLSX data into rows and columns.

If you want the copy/paste examples and the test files, you can get them from this article.

Yet another reason why you should always install APEX in your databases.

The star of today’s video is Kosseila.HD, also known as BrokeDBA, complete with sun glasses, basket ball and a rattling watch. 🙂



Video : Returning REF CURSORs from PL/SQL : Functions, Procedures and Implicit Statement Results

Today’s video is a demonstration of returning REF CURSORs from PL/SQL using functions, procedures and implicit statement results.

I was motivated to do this after a conversation with my boss. He’s from a .NET and SQL Server background, and was a bit miffed about not being able to use a SELECT to pass out variable values from a procedure, like you can in T-SQL. So I piped up and said you can using Implicit Statement Results and another myth was busted. I guess most PL/SQL developers don’t use this, and I don’t either, but you should know it exists so you can be a smart arse when situations like this come up. 🙂

The video is based on these articles.

The star of today’s video is Bryn Llewellyn of PL/SQL fame, and more recently Yugabyte.



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. 🙂



Video : Decoupling to Improve Performance

In today’s video we demonstrate how to cheat your way to looking like you’ve improved performance using decoupling.

This was based on the following article.

This came up in conversation a few days ago, so I thought it was worth resurrecting this demo. It doesn’t really matter what tech stack you use, the idea is still the same.

The star of today’s video is Logan Rosenstein, formerly of OTN, and now working for Zignal Labs, and author of Building Towers by Rolling Dice.



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. 🙂



Video : Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL

Today’s video is a brief run through creating RESTful web services using Oracle REST Data Services (ORDS) and PL/SQL.

This is based on the following article, but the article has a load more examples and variations compared to the video.

I don’t mention handling complex payloads or status information, but you can find that here.

You can see all my other ORDS related content here.

The star of today’s video is Alan Arentsen. Finding a clip without him giggling or laughing was kind-of tough… 🙂



PL/SQL Objects for JSON in Oracle 12cR2

I’ve been playing around with some more of the new JSON features in Oracle Database 12c Release 2 (12.2).

The first thing I tried was the new PL/SQL support for the JSON functions and conditions that were introduced for SQL in 12.1. That was all pretty obvious.

Next I moved on to the new PL/SQL objects for JSON. These are essentially a replacement for APEX_JSON as far as generation and parsing of JSON data are concerned. If I’m honest I was kind-of confused by this stuff at first for a couple of reasons.

  • If you are coming to it with an APEX_JSON mindset it’s easy to miss the point. Once you “empty your cup” it’s pretty straight forward.
  • The documentation is pretty terrible at the moment. There are lots of mistakes. I tweeted about this the other day and some folks from the Oracle documentation team got back to me about it. I gave them some examples of the problems, so hopefully it will get cleaned up soon!

I was originally intending to write a single article covering both these JSON new features, but it got clumsy, so I separated them.

The second one isn’t much more than a glorified links page at the moment, but as I cover the remaining functionality it will either expand or contain more links depending on the nature of the new material. Big stuff will go in a separate article. Small stuff will be included in this one.

I also added a new section to this recent ORDS article, giving an example of handling the JSON payload using the new object types.

I’ve only scratched the surface of this stuff, so I’ll probably revisit the articles several times as I become more confident with it.



PS. Remember, you can practice a lot of this 12.2 stuff for free at .

Real Talk : PL/SQL and SQL as your only development skill

notes-514998_640This morning I was asked a question about the job opportunities for a PL/SQL developer these days. I’m talking about someone with good SQL and PL/SQL skills, but limited, or no, knowledge of other development languages.

I think most people know I’m a big fan of PL/SQL. If you have good SQL skills and you know PL/SQL well, you can do pretty much anything with an Oracle database, including all types of web service and web development. Throw in some APEX skills and you can be super productive as a web developer against Oracle databases.

So back to the question, what are the job opportunities for a PL/SQL developer? In the UK at least, that’s not a great place to be right now. When I first started with Oracle technology it was not uncommon for companies to employ developers just to code PL/SQL. There are still jobs like that available, my employer has two PL/SQL contractors right now, but the market for a programmer with just PL/SQL is on the decline. Search for “programming language popularity” and you will see a number of indexes don’t include SQL and PL/SQL in the top 20 lists. Search for “enterprise programming language popularity” and you will see SQL and PL/SQL appear. There may be flaws in the way the information for these lists is gathered, but you get the message.

That’s not to say SQL and PL/SQL skills are not of value, just that those skills alone are no longer enough. They have to be part of a package that includes other development skills.

Most people I talk to work in organisations that use multiple database engines (Oracle, SQL Server, MySQL, several NoSQL engines), so having a person that can only do PL/SQL development means they are of limited use compared to someone that also knows Java, C# or Javascript to a high level. That is, development skills that span database engines.

In a similar way, my current employer won’t commit to APEX as a strategic development platform because it is just for Oracle databases. Using database links to other engines to allow you to continue using APEX against them is not strategic. 🙂 In the same way, we have a lot of PL/SQL right now, but in the future I can see this being of less importance compared to other skills that are multi-engine. Do I like this situation? No, but it seems to be where we are right now.

Of course, this could be a conversation about “Java/C#/Javascript as your only development skill”. Development in todays world requires multiple languages, each serving a different purpose. It could also be a database engine discussion. I can’t imagine ever working for a company again that doesn’t expect me to look after MySQL, SQL Server and other engines, as well as Oracle.

I hope this doesn’t come off as negative. I love SQL and PL/SQL and I would love to be able to tell you these skills alone would set you up for life, but that would be a lie. As a developer, you are forced to follow the market and the market says you need multiple development skills to survive. I hope you pick SQL and PL/SQL as part of your skill set, as they are still very important in enterprise companies, but in the current climate betting your whole development career on a single language is not a safe bet. 🙂



PS. Us old folks will cling on until the bitter end. 🙂