Automatically insert a UUID value into a table with Oracle There are different approaches to inserting a UUID into an Oracle Database table, ranging from Java to various ways of doing it in PL/SQL (as described here). Not many of these seemed appealing to me so I came up with my own approach using a trigger.
Using Maven to create a super SQL file with the merge-maven-plugin If you've ever worked on a PL/SQL project or any project with a good amount of database related work you probably have had to deal with multiple SQL files and their deployment across systems. This is exactly what I've come across recently. Even in the early stages of my PL/SQL project I had 11 separate SQL files that would need to be deployed to system and performance test environments, then propagated to production, production support, etc. To help with managing all this I decided that building a single 'super SQL' file would work best and I ended up using Maven to do it.
Getting the total number of rows with MySQL LIMIT clause The MySQL SELECT statement syntax includes a LIMIT clause that can be used to restrict how much data is returned from the database. This is fantastic for pagination but in most cases you also want to know how many rows there are in total, which typically requires a second query. With MySQL there is a special option that can be passed to SELECT to do this calculation for you automatically.
How to set MySQL connection timezone with PDO For my travelblog.ws project I store all dates as UTC in columns using the DATETIME data type i.e. my code converts dates to UTC before storing them. Because DATETIME is time zone agnostic this worked great, that is until I needed to use the TIMESTAMP data type on a number of new columns that I've added.
Howto: Reset PrestaShop admin password in four easy steps Getting locked out of your PrestaShop admin account can be a real pain, luckily there is a straight forward way to reset your password as long as you have access to the server that the shop is running on. For this you will need access to the server configuration file and the PrestaShop database via phpMyAdmin.
Oracle Database XMLTable vs ExtractValue performance I've been doing SQL optimisation for some of the services at work recently and saw that we've been using EXTRACTVALUE in a number of places. This is not bad in itself, however the Oracle 12c documentation states that EXTRACTVALUE is deprecated and should be replaced with XMLTABLE.
Specifying a default namespace with XMLTABLE I really like the Oracle XMLTABLE feature, which allows you to treat an XML string as if it were a real table. I've been using this feature quite a bit lately and had some difficulties initially around the default namespace so I thought I'd share my findings.
Howto: Force null values to be highlighted in SQLDeveloper query results I've written previously about a neat highlighting feature that SQL Developer has in a post about navigational filtering. There is another feature that is just as handy that allows you to automatically highlight all (null) values in the query results. This of course makes it very easy to see nulls at a glance and definitely eases the task of examining results for specific patterns.
When null == null isn't true and how to get around it I've been writing some tricky SQL recently and came across an odd behaviour, at least I though it was odd at the time because I didn't read the documentation. I had two columns that represented a composite key, the first column was guaranteed to have a value and the second column could be null. Now I had to join this table to itself to get the results I needed and that required comparing this composite key. When I compared just the first column it worked fine, but as soon as I added the second column, the one that could have null values, nothing matched.
Visualising Oracle database partition data distribution with gnuplot We use partitioning in at least one of the databases at work, changing to partitioned tables has allowed us to keep the system running within our imposed SLAs. Recently I've started to wonder however, what happens when we start hammering individual partitions more than others. The data distribution would be skewed and the benefits of partitioning lost. I decided to put together a bit of SQL and then use gnuplot to show me how well our data is distributed.
Using Excel to generate SQL statements from raw data One of my tasks recently has been to analyse erroneous duplicate data in our production system and to come up with a number of data fixes that could be executed using simple SQL update statements. The sanitised data was provided to me as an Excel spreadsheet containing just enough information to work out what should be done with the duplicate, but not enough to identify who the data belongs to. This meant that I could work on it outside of the production environment, but it also meant that I needed to provide a way for the production operations team to reproduce what I did on real data and with relative ease.
Use hightlights in SQLDeveloper to instantly see patterns in data So I've been using SQLDeveloper like mad lately and have been finding some really neat features that it provides. One of the great time savers is its ability to create persistent highlights that you can use to instantly see the patterns in data. Great for debugging that ETL!
The dbms_parallel_execute.create_chunks_by_rowid not creating enough chunks, bound by extents This post is about an Oracle 11gR2 feature. I've been using the dbms_parallel_execute PL/SQL package to parallelise processing of large amounts of data and ran into some issues with chunking of my processing table. What I've noticed is that the number of chunks created seemed to be bound by the number of extents that the table was using, this was not ideal in my case, luckily there is a different solution to create as many chunks as you need.
SQLDeveloper 4.0.2 on OS X with the Mac L&F rendering issues I've updated my SQLDeveloper to 4.0.2 and to my surprise found a major rendering issue with this version. The whole window is using black as the background, including the toolbar. This means none of the text can be read and the whole application is essentially useless then.
Tuning the SQL*Plus fetch size for better performance I've been doing a fair amount of work with data extraction from a database to a flat file recently using SQL*Plus and have been looking at ways to speed up the process. This is where the arraysize setting comes in handy. This setting controls how many rows are fetched from the database in a single round trip. I did some testing with various sizes and found that tweaking this value can have huge performance improvements. This is what I found with the data I was extracting.
Killing runaway SQL sessions in Oracle I've been running a lot of SQL recently that would sometimes get 'stuck' due to the optimiser picking a plan with a full table scan. On a small database this may not be a problem, but when the database has billions of rows and a full table scan that pulls in 100Gb+ of data, the wait is long.