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.
MySQL always returning 0.999 for DECIMAL type fields I've started to use the DECIMAL type in MySQL to store longitude/latitude coordinates recently and for some reason no matter what value I stored, when queried back I'd always get '0.999'. A little bit of investigation and I found the answer so lets see what was going wrong.
How to get orapki without installing the full Fusion Middleware I was asked recently if there was a way to get the orapki utility without having to install the entire Fusion Middleware. The answer is yes, sort of. So if you need access to this utility but for some reason are not able to use the one that comes with your application server, read on.
Java Mission Control requires GTK but which version? I've recently had one of the DBAs on a project ask me what GTK version is required to run Java Mission Control with JDK 7. I thought easy, the documentation should tell me this, but after searching for several hours I've come up with nothing, apart from 'GTK Libraries Required' as stated in the Oracle Java Mission Control 5.3 Certified System Configurations.
Example JAX-WS WebService built with Oracle WebLogic Maven plugin (wsgen) In the past I've shown how to use Ant and the JWSC task to build a JAX-WS web service, well I've moved onto Maven now and started to use the Oracle WebLogic Maven plugin to build services. This is an example on how to build a JAX-WS web service using that plugin, specifically the wsgen goal.
Returning compressed MTOM data with JAX-WS DataHandler A while back I've shown an example MTOM/XOP enabled web service using DataHandler. Although MTOM is great for optimising the output of a web service, sometimes it's just better to compress these results before sending them.
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.
Use SQLDeveloper navigational filtering to unclutter your workspace SQL Developer is a great tool and it gets better with every version, well in version 4 I noticed that you can filter out what is displayed in the tree underneath a database when you have successfully connected. If you don't exactly know what I mean, this would probably be a familiar sight...
How to redirect SQLDeveloper debug log to a file I've come across an excellent article by Jeff Smith recently that describes how to enable debug mode in SQL Developer. This has been a useful source of information in my recent quest to solve some connectivity issues with certain databases, however it wasn't the final answer I was after.
Maven WebLogic Plugin and the Java 5 source code bug plus how to fix it I've noted previously that the JWSC Ant Task fails to build Java sources above 1.5 source version due to a hardcoded "1.5" value in one of the Jar files. That was happening for the 12.1.2 version of the Ant task, it also appears to be a problem in the 12.1.2 Maven plugin as well as the 12.1.3 Maven plugin.
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.
Change the JRE used for SoapUI on OSX I use SoapUI on a regular basis and today I wanted to change the JDK that it uses. I've noticed that by default the OS X installation of SoapUI 5.1.2 is using JRE 1.7. On my system, I've had the 1.8 JDK set as the default for quite some time, my ~/.profile file pointed to the 1.8 JDK bin directory for the PATH and the JAVA_HOME was set correctly, so it was puzzling to me why 1.7 was being picked up by SoapUI. I dug a little deeper and found the answer.
Workaround for JDeveloper 12c Refactor Rename bug I use JDeveloper on an almost daily basis and when something as basic and fundamental to the product as the rename functionality breaks, it makes you want to tear your hair out. I'm talking about the Refactor Rename feature that has been broken since the 12c release. Luckily there is a workaround.
Making ADF af:table emptyText appear with padding using some CSS I've been doing a fair amount of ADF work recently and especially dealing with tables. What I found was that when a table contained no data, the emptyText string rendering is very ugly by default, so I got exploring. It turns out you can put HTML into this string and customise how it is rendered with some CSS.
NetBeans 7.1.2 does not work with JDK 8 I've recently installed the JDK 8 on my rMBP and all of a sudden NetBeans stopped working for me. I've dug into this a little further and tried several things, none of which worked, in the end I ended up forcing NetBeans to use JDK 7 and everything was working again.
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.
Stand alone CSF (Credential Store Framework) example I've been trying to figure out the Oracle CSF 11g documentation over the last couple of days without getting very far. The documentation is a bit incomplete and has errors in it (referencing classes that do not exist for example). So since I had to implement this for the project I am on, I had no choice by to figure it out myself.
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.