Oracle SQL: Aggregate List – LISTAGG

Know this command?

I think it’s very useful, but not so often I see that in use… More about it:

It was introduced in Oracle 11g Release 2, as an analytic function to make the life easier fot the PLSQL Dev, specially when working to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.

In case WM_CONCAT is in use for this end, be aware that WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). Also, WM_CONCAT has been removed from 12c onward, once it was completely replaced by LISTAGG.

More info and examples can be found here:

Hope you enjoy it. Cheers!

Managing database changes with Sqitch

Nowadays everybody talks about continuous integration, automated test tools, and stuff like that. But almost every time it’s about testing applications. Let’s talk a little about Sqitch, “a database change manager” as they use to call themselves.

Let’s say you’re starting a new project and want to ensure every database change are managed and secured with tests? Just like with an app, you can write tests to ensure everything works and you can have a “revert” method for the changes too. Suppose you are changing a field type and start creating a backup column (fallback ;)), but something goes wrong on the meantime and for some reason, you can’t do a simple rollback… revert it and be happy.

What if your CI says that the application it’s fine and can be released to the production server but “a wild bug appears” and you NEED to revert to a previous version of your service, easy huh? With Docker and one command line, the service is up and running again. But what about the database changes your team did? Just call the revert and be happy :).

Have I mentioned that you can have multiples connection URI at the project and have different environments to apply the changes with a single command?!. Let’s see a few commands to create a simple MySQL database…

Create a folder, initialize GIT in it and initialize the Sqitch project.

mkdir awesomeapp
cd awesomeapp
git init .
sqitch init flipr --uri --engine mysql

Now you have a ready to go sqitch structure. Now let’s do some basic creates.

sqitch add createcustomeruser -n 'Creation of Customer table'

Add the create table script to the file inside the “deploy” dir.

CREATE TABLE customer (
name VARCHAR(50)

Add the verify script, can be a simple select. Add the revert script, guess what’s the command, and after all, you can run the commands below and see the result.

sqitch deploy db:mysql://root@/awesomeapp
sqitch verify db:mysql://root@/awesomeapp
sqitch revert db:mysql://root@/awesomeapp

This was a quick explanation about Sqitch so go ahead and read more at their docs and tutorials, they are pretty good.

Automating your Github Project’s build and deploy with Heroku and Travis

Hey fellas!
My name is Jéferson Sartor Decker, I’m a Software Developer in Thoughtworks Brasil and live in Porto Alegre! Matheus invited me to make a post here, so I hope you enjoy it!

What we’re going to use?

Github: A web-based service for version control, that uses git and is free to open source projects <3 !

Heroku: As they said in their site:

Heroku is a cloud platform that lets companies build, deliver, monitor and scale apps — we’re the fastest way to go from idea to URL, bypassing all those infrastructure headaches.

And is also free with some restrictions. Remember that Heroku support for default the languages below:

  • Javascript
  • Ruby
  • PHP
  • Python
  • Go
  • Scala
  • Clojure

Heroku CLI: Heroku Command Line Interface.

Travis-CI: A continuous integration service, that builds and tests code in Github and also is free to open source projects <3 !

Travis-CI CLI: Travis CI Command Line Interface.

Let’s get started!

So, first of all we need to have a project on Github, I’ll create a sample Java + Spring project, just to have something to build and deploy. You can do this to any project that you already have in your Github! My project is the ‘sample-java-project’, as you can see in the next image:
Continue reading

RabbitMQ on a Micro-service Architecture

Microservices ain’t a boom word right now, at least not as it was before… But we still can see a few things about it, and that’s what I’ll talk about today.

When we talk about microservices and how they work and scale, we think how the services will exchange data between them. We have a few options to do it, we can send the data to a reverse proxy that will handle the load balance for us, for example. There is also the option to send directly to a docker service name and let swarm handle it. We can use Kubernets and the service discovery that it provides to us (or use Consul for that).

All these options are fine, but what if we need a huge task to be done, and the service that was dealing with that job crashes? Did we lose our job? Do we need to manage that crash on the communication protocol our selves? How’s the load on the service? Is the service handling the request or they’re slowing? Too many questions right?

The point here is that even a simple and fast service can fail/crash and throw some error to our user or to the consuming service and that’s bad. Here’s where RabbitMQ comes in! it’ll handle the request between our services and ensure every single request has an answer.

Rabbit handle queues and delivery they to the right consumer. Once a job is “delivered” to a consumer, it has two main options:
1) Automatic acknowledge
2) The consumer sends the acknowledge.

The second one is the better way to say that some job was achieved. After the job, the consumer may give an answer back to the producer with some data.

Wanna know the load avg of the services?
Every service has it’s own queue and rabbit can show you how every single queue is doing. With that, you can see if you need more consumers from the same service or can scale down any of them.

You have another approach to solve those questions? Let me know 😉

Embeded server for Java Web App and a New author

Hi, my name is Maurício and it’s the first time I’m posting here. I’m a Senior Software Engineer and I live in Brazil. I have six years working with web development and love Java and Javascript. I’ll be posting here from time to time development stuff. Hope you enjoy it!

Our subject for today: Once in a while, I do a small Java Web project and every time I think on which application server will host the app, Tomcat, GlassFish, WebLogic, WildFly, Payara and there are plenty more servers. Nowadays almost everybody uses a Docker container to host app’s, and that’s nothing wrong with that, but why load a container if I can just embed the server in a jar file?

If you have a Maven project, there’s almost no work to be done, just add a plugin that targets the package goal and there you go. In the end, you will have a jar file with your app-name-swarm.jar (i know, it’s a WildFly swarm, not a fully WildFly, but it weights just 64Mb). Run it like a normal jar and you will have a running web app.


After all, if you want, that jar can be loaded in a Docker container and launched at a production server. There are other options like Tomcat embedded, Payara microserver and a few others. Go ahead and give a try to any one of them and share your experience, I’m sure you have something to say about them.

But remember, these options aren’t a full application server, they’re engineered to be small and focused on specific features so read their docs to see what they can and can’t do before using. 😉


Application Named Variables Returning ORA-01008: not all variables bound

Hi all,
I saw it a long time ago and last week I was involved in a discussion for this same error. So, this deserve a post for future reference. 🙂

Imagine the situation: You are building an application or a module to perform queries on database. You want to use variables in the query and fill the values using text fields on application. Sounds easy and works fine for SQLServer and others, but Oracle database is returning:

ORA-01008: not all variables bound

What to do?

Fist let’s clear the issue: this is not related to database layer or oracle interpreter/parser. This error happens when a bind variable being used on SQL have no value. The official reasoning is:

Cause: A SQL statement containing substitution variables was executed without all variables bound. All substitution variables must have a substituted value before the SQL statement is executed.

In my case ODP.Net with C# (but apply to other languages). Interesting fact:
“ODP.Net provider from oracle uses bind by position as default. To change the behavior to bind by name, set property BindByName to true.”

This means Oracle may be waiting for “:1”, “:2” as bind variables and this can also not being set correctly by application.
In this case, please try to set BindByName to true in application code for Oracle command. Example below:

using(OracleCommand cmd = con.CreateCommand()) {
    cmd.BindByName = true;

Now, try again. 🙂

Hope this helps you!