Tag: pl/sql

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…

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

Cheers

Tim…

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.

Cheers

Tim…

PS. Remember, you can practice a lot of this 12.2 stuff for free at https://livesql.oracle.com .

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

Cheers

Tim…

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

TABLE Operator with Locally Defined Types

postit-missed-itIn my OUG Ireland 2016 – Summary post I mentioned the Oren Nakdimon session called “Write Less (Code) with More (Oracle 12c New Features)”. One of the things he mentioned was the removal of restrictions associated with the use of the TABLE operator on local table types. If I had read about this or seen it before, it had certainly slipped my mind, so I made a note to write something about it and add a link to it from my PL/SQL new features article. So here it is.

It’s a neat little feature.

Cheers

Tim…

 

PL/SQL Formatting : More pearls of wisdom from Bryn

glasses-272399_1280-smallAnother topic of conversation that came out of Bryn‘s session at Oracle Midlands related to PL/SQL code formatting. I’m not sure I agree 100% with his opinion, but it’s certainly making me think about my current stance.

When I said I was going to blog about a couple of the points he raised, Bryn emailed me some stuff, which I’m going to use here. So this is Bryn’s content, with some of my opinions thrown in. I will no doubt get told off for not doing the subject justice, like I did with the last post. 🙂

Bryn: Look at page 1 (below). This makes PL/SQL look so old-fashioned. I’m sure that this is part of what makes it unappealing to the modern youth. How easy do you find it to read its real meaning?

Me: With a few minor formatting changes, this is pretty much what I do. 🙂 As a result, it looks really normal to me and I like it, but if I try to put myself into the head-space of a young person, it does look overly formal, and as Bryn said in his talk, we are essentially shouting all the keywords and using naming conventions that detract from the true meaning we are trying to convey. I don’t like to admit it to myself, but I kind-of see his point. It does look a little Pipe and Slippers.

Bryn: Now look at Page 4 (below). Identifiers are like proper nouns in English prose. They’re capitalized to let them stand out. The capitals in Object_ID (or DBMS_Output) are an aid to pronunciation.

04-bryn-modern

Me: This is a slightly unfair comparison, since there is no syntax highlighting, which is on by default in all PL/SQL IDEs, but it does look less “old”. I understand Bryn’s point about the capitals potentially aiding pronunciation, but if I’m going to stop “shouting” the keywords, I actually prefer the look of it all in lower case myself. 🙂 I agree that identifiers still need “_” between words. Although camelCase is used by many languages, it doesn’t work so well in Oracle. It looks fine in your scripts, but when you are looking at dictionary views, much of the information is presented in uppercase, so “uniquenessContraintMissing” becomes “UNIQUENESSCONSTRAINTMISSING”, which sucks. The prefix/suffix used on identifiers can be confusing, especially since an alternative was mentioned in the previous post.

Bryn: As promised, here’s my-marked up copy of the Gettysburg Address. It’s considered to be an exemplar of English prose, so having it mangled into an incomprehensible mess by the robotic application of “code formatting best practice” makes my point very vividly. … Feel free to use it. But do make sure that you credit the author, President Abraham Lincoln, and me for having had the idea to use it to make fun of the anally retentive code formatters who jointly conspire to make PL/SQL look so dated.

02-bryn-prose1

03-bryn-prose2

Me: Yeah. Point made! 🙂

I’ve tried to alter my code formatting in the past, which invariably meant I had to go back and reformat it a couple of days later when I couldn’t stand knowing it wasn’t “how I usually do things”. 🙂 Having said that, if you read the last blog post, you would see I tried to stop shouting. 🙂 It’s not what Bryn likes, but I think it’s a step in the right direction… Maybe…

Am I going to redo all my articles, blog posts and videos? No. Am I going to try and change? At the moment I’m thinking yes, but I reserve the right to do a u-turn at any point.

Don’t forget to check out these whitepapers from Bryn.

Cheers

Tim…

Preventing PL/SQL name clashes. You learn something new every day!

glasses-272399_1280-smallI mentioned in yesterday’s Oracle Midlands post, Bryn had an example of some syntax I had not seen in 20+ years of PL/SQL development.

You tend to name PL/SQL parameters and variables in such a way as to prevent name clashes with table columns, but you could just qualify each use of a PL/SQL identifier with the identifier for the name of the block in which the item is declared in this case the procedure name. In the following example, “dummy” is both a parameter name and a table column, but Oracle knows exactly what you mean because you’ve fully qualified it.

create or replace procedure my_proc(dummy varchar2)
as
  amount pls_integer;
begin
  select count(*)
  into   my_proc.amount
  from   dual a
  where  a.dummy = my_proc.dummy;
  dbms_output.put_line('amount=' || my_proc.amount);
end my_proc;
/

set serveroutput on
exec my_proc('Y');
amount=0

PL/SQL procedure successfully completed.

SQL>

You can also do this for parameterised cursors and named/labelled blocks. You learn something new every day! 🙂

This was just one of the points Bryn discussed in his whitepaper called Doing SQL from PL/SQL: Best and Worst Practices.

Bryn: Look for this section: “Name capture, fine grained dependency tracking, and defensive programming”. This discussion leads to this:

Principle 1: In embedded SQL, dot-qualify each column name with the from list item alias. Dot-qualify each PL/SQL identifier with the name of the name of the block that declares it.

It is also mentioned in his paper Why use PL/SQL?

Cheers

Tim…

PL/SQL New Features in Oracle Database 12c

I recently put some more PL/SQL new features articles live.

I’ve also posted a top-level new features article.

This contains a number smaller features as well as links to other articles on the site that discuss some of the new features in greater depth.

I’ve got a couple of PL/SQL books I’ve got to read and review, but I’ve been holding back because I wanted to get my take on this subject written before I was influenced by others. I guess I don’t have that excuse any more. 🙂

Cheers

Tim…

The Future of PL/SQL : The People Respond

I put out a post yesterday called The Future of PL/SQL : My Opinion.

For an Oracle-related blog, putting something out at the weekend is a sure fire way to get nobody reading it. If I look at my website, the hit rate at the weekend is about 1/5 of that of a week day. By the time most people get back to work on Monday they have a stack of blog posts to read and yours will probably fall into the “mark as read” pit of their RSS reader. 🙂 In a similar fashion, people’s timelines on social media are generally so crowded, your “look what I’ve just written” tweet will probably be lost amongst the talk of alcohol, bad food and photos of the kids…

So having said that, I chose a Sunday to put out my manifesto for the future of PL/SQL and was surprised by the volume of feedback. I know there are a lot of PL/SQL programmers out there, but it’s easy overlook that when many of the other programming communities are so much more vocal in comparison. It’s good to know there are so many people out there who still care about PL/SQL! You’ve made an old man happy. 🙂

Cheers

Tim…

The Future of PL/SQL : My Opinion

Although a lot of my effort at the moment is focused on DBA features, I have written some articles on PL/SQL enhancements. There are a few neat new features for PL/SQL developers in 12c, but you could be forgiven for thinking it is a little underwhelming. There are two ways to look at this:

  1. OMG. Oracle really don’t care about PL/SQL any more. If they did, there would be loads of new features.
  2. Wow. PL/SQL is so mature and cool that there is really not much more to add.

From a base language perspective, I think option 2 is closer to the mark. PL/SQL is a really stable, fast and mature language. There really isn’t very much that you can’t do with PL/SQL these days. So what is the future of PL/SQL in my opinion?

As part of his role as PL/SQL evangelist, Steven Feuerstein contacted a number of people about their opinions of PL/SQL. When he asked me about my wish list, I suggested all functionality in the Alexandria PL/SQL Utility Library should really be in PL/SQL. A quick look at the Alexandria site shows it includes code that supports a large variety of functionality from a variety of authors. Among other things, this library includes:

  • Generating PDF files.
  • Generating Excel files.
  • Generating RTF files.
  • Microsoft Office Integration (OOXML).
  • Zip and Unzip functionality (separate to UTL_COMPRESS).
  • Parse CSV files.
  • Parse RSS feeds.
  • Generate JSON files.
  • FTP support.
  • Email support (SMTP, POP, IMAP, Exchange).
  • Integration with Google services (Google Maps, Google Calendar, Google Translate).
  • Integration with Amazon Web Services.
  • Integration with PayPal.
  • Integration with Twitter.
  • Consuming and publishing SOAP and REST web services.
  • Logging and debugging APIs and frameworks.

You may see some things in that list that look like duplication of functionality we already have. Oracle 10g introduced UTL_MAIL for sending emails from PL/SQL, but the functionality is so limited, you invariably end up coding your own APIs using UTL_SMTP (like this). Oracle 10g also introduced UTL_DBWS for consuming SOAP web services, but once again, it is often easier to do it yourself directly, or using a simpler SOAP_API based on UTL_HTTP. We don’t even have any reasonable tracing functionality. Instead we have to write our own wrappers for DBMS_OUTPUT, or use someone else’s. So although the PL/SQL language is great, when it comes to integration with other technologies you end up having to do a lot of the heavy listing yourself, or rely on using someone else’s unsupported solution.

So in my opinion, the future for PL/SQL is not in major changes to the language itself, but in bringing these sort of support and integration packages into the database. I think we should avoid forcing overly complex frameworks on people. I’m very much talking about simple utility packages. This could be done in one of two ways:

  1. It is literally baked into PL/SQL. The problem with this approach is you will have to wait for a DB upgrade to get the latest and greatest functionality. That would be a shame since most of the functionality works for multiple DB versions.
  2. Oracle produce their own internal version of the Alexandria PL/SQL Utility Library. So you have an Oracle supplied, supported and maintained library of functionality you can download and use, independent of database version. This means updates are independent of major database version changes.

I think option 2 would make a lot of sense. If you think about it, we almost have a precedent for this in the form of APEX.

  • APEX is built on PL/SQL.
  • It ships separately to the main database releases.
  • Each release supports a variety of DB versions.
  • It brings with it a bunch of utility packages. They are there to support APEX, but there is nothing to stop you using them for your own applications, like this example of using APEX_JSON.

Imagine how exciting it would be if part of the Oracle 12cR2 or Oracle 13c announcement included a huge library of support packages like this! 🙂

Cheers

Tim…