Oracle Foreign Key References

Query to find out which other tables reference a specified table in a foreign key constraint:

SELECT c_src.TABLE_NAME as SRC_TABLE,
c_list.CONSTRAINT_NAME as NAME,
substr(c_src.COLUMN_NAME, 1, 20) as SRC_COLUMN,
c_dest.TABLE_NAME as DEST_TABLE,
substr(c_dest.COLUMN_NAME, 1, 20) as DEST_COLUMN
FROM ALL_CONSTRAINTS c_list, ALL_CONS_COLUMNS c_src, ALL_CONS_COLUMNS c_dest
WHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME
AND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME
AND c_list.CONSTRAINT_TYPE = 'R'
AND c_dest.TABLE_NAME = 'table name'
GROUP BY c_list.CONSTRAINT_NAME, c_src.TABLE_NAME, 
    c_src.COLUMN_NAME, c_dest.TABLE_NAME,    c_dest.COLUMN_NAME;

SSH Tunneling

For single hop:

ssh -L LOCAL_PORT:REMOTE_HOST:REMOTE_PORT USERNAME@REMOTE_HOST

For multiple hops the following is better:

ssh -A -t -l user jump-host -L 8080:localhost:8080
ssh -A -t -l user webserver.dmz -L 8080:localhost:8080

Posting XML to a Service Using Curl on Linux

Linux command line post of XML data to a specified URL.

 curl -k --request POST --header "Content-type: text/xml" --data @request.xml URL > response.xml

Commandline Retrieval of a Remote SSL Certificate

Recently I've had to manage a lot of Java Keystores using keytool. Here's a convenient command to retrieve a remote servers SSL certificate so that you can import it as a trusted cert.

openssl s_client -connect remote.server.net:443 </dev/null | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' >/path/to/certificate.pem

Post Build Notification Using Growl

Although I have Maven integration in my IDE, I often find myself running my build from the terminal. Typically this means kicking off the build, switching to some other application for a few minutes, and frequently checking my terminal for the desired "BUILD SUCCESSFUL" message. The repeated checking is an annoying waste of time that I've recently figured out how to eliminate.

The Growl application for Mac OS X is a simple and unobtrusive notification tool that integrates well with a number of other applications (Entourage, Adium, iTunes, etc). Install Growl by running the Growl.mpkg package on the disk image. A Growl companion tool, growlnotify, is included in the Growl disk image in the Extras folder and allows you to send notifications from the command line to Growl. Install growlnotify by running the install.sh script. The script will install growlnotify in your /usr/local/bin directory. Verify that this is on your path by opening a Terminal and typing

growlnotify -version

you should see output like

growlnotify 1.1.4
Copyright (c) The Growl Project, 2004-2008

Edit your ~/.profile file and include the following line to define a function named "growl" that you can call from you command line.

growl() { growlnotify -m "${1}"; return; }

Restart your Terminal application and try

growl "hello world"

You should see the text in a Growl notification on you desktop.

Now you can post-pend a Growl notification to any command you run from the command line. Try running a lengthy build like

mvn clean install; growl "build done"

While the build is running, be productive doing something else... You will be notified by Growl immediately after your build has finished!

Spice your notifications by downloading some cool icons and modifying your function with

growl() { growlnotify -I /Icons/Blinky.icns -m "${1}"; return; }

Problem with Hibernate

Recently I wanted to execute a fairly straightforward series of SQL calls (column Ids masked) using JPA's EntityManager.createNativeQuery. Running the following sequence of calls:

entityManager.createNativeQuery("set @rank:=0;").executeUpdate();
entityManager.createNativeQuery("select rank from (select @rank:=@rank+1 as rank, XX, YY from ZZ order by YY DESC) as ranks where XX=a;").getResultList();

yielded an unpleasant exception.

java.lang.IllegalArgumentException: org.hibernate.QueryException: Space is not allowed after parameter prefix ':' 'set @rank:=0;'

A quick Google search revealed that this is actually an open issue in Hibernate.
I followed in the footsteps of one of the other posters and tried to create a stored procedure to run my two statements. The procedure worked fine in MySQL Query Browser and yielded the correct results. However, when I executed it as a JPA nativeQuery I got:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 2' at line 1

For some reason, "limit 2" was always being appended to my call to the stored procedure. The final solution was to create a new function instead of a procedure:

DELIMITER $$
DROP FUNCTION IF EXISTS `football`.`getRank` $$
CREATE FUNCTION `getRank`(p1 varchar(256)) RETURNS int(11)
BEGIN
    set @rank:=0;
    RETURN (select rank from (select @rank:=@rank+1 as rank, XX, YY from ZZ order by YY DESC) as ranks where XX=p1);
   END $$
DELIMITER ;

And call it from my DAO with:

Query query = entityManager.createNativeQuery( "select getRank(?1)" ).setParameter( 1, teamId );
return (Integer)query.getSingleResult();

IntelliJ Live Templates - For Each over a Collection

As a recent convert to IntelliJ, I'm doing my best to take full advantage of the IDE's time saving measures. I thought that IntelliJ was missing a Live Template that creates a for each control block over a given collection. Here is how I created this Live Template:

1. Open Settings (Command-,)
2. Select Live Templates and click the "Add" button
3. Complete the dialog fields

4. Click "Edit variables" and complete the dialog

To use this template, simply type Command-J in your editor and begin typing "feco" until the template name is highlighted in your list. Press TAB to accept the template and fill in the values that you need.

--Update--
It turns out that there is already a template to do this identified by "iter" (thanks yole). Fortunately the exercise was useful in getting started with writing my own templates. I've since written one for creating a standard TestNG test method that includes the "groups" parameter for the @Test annotation. My team organizes our tests into groups so that we can execute different tests in different levels of our builds. Tests in the group "unit" are run as part of our commit build, while tests in other groups are run as part of our integration test build.

Here's the template defintion:


And the variable assignments:
.