Friday, June 29, 2012

Query Results as CSV in Oracle SQL Developer

I have been using Oracle SQL Developer a lot this week, and have been exporting query data as CSV a lot too: right clicking on the results > selecting Export and going through the dialogs.

Now there is a super cool much easier way. Run your SQL like this:

select /*CSV*/* from MYTABLE;

Then just select all on the results screen and copy/paste the results directly into an Excel spreadsheet! Unfortunately you have to select all twice if you have more than 500 results (you can't override Tools > Preferences > Database > Advanced > SQL Array Fetch Size to be greater than 500).

Thanks to Jeff Smith's blog post: Formatting Query Results to CSV in Oracle SQL Developer. Thanks as well to Ian Carpenter's answer on this StackOverflow question: how to export query result to csv in Oracle SQL Developer?


Actually this isn't working for me in Oracle SQL Developer 3.0.04 - Jeff Smith's solution says you run script (F5) and all I see then is text output of the query i.e. tab delimited, no quotes.. *sigh*

Monday, June 25, 2012

Table names should be upper case with Oracle

I am working with Oracle XE (Oracle Database 10g Express Edition Release 10.2.0.1.0) and the wonderful DB tool DbVisualizer. I am doing some data analysis and am using DbVisualizer to import data from Excel into a new table so that I can run some SQL over it. DbVisualizer even creates the table for you (dropping any existing table first if need be). So very quickly I have a table called PoBoxCompare with all my data in it. Next I do a quick sanity test to make sure the data is there. Using DbVisualizer's auto-complete I have my test SQL:

select * from ROB.PoBoxCompare;

Uh-oh, it doesn't exist!

17:34:49  [SELECT - 0 row(s), 0.000 secs]  [Error Code: 942, SQL State: 42000]  ORA-00942: table or view does not exist
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

After some head scratching and experimentation, I find that table names (in Oracle at least) are case sensitive. If you have a table name with mixed case, you need quotes around the name.

select * from ROB."PoBoxCompare";

Of course, the better solution is just to make sure your table names are all upper-case, which is what I am doing now. :)

Note: the exact same rule applied to column names too!

Monday, June 18, 2012

Use WinMerge as your TortoiseSVN Diff Tool

It is very easy to modify Tortoise for SVN (on Windows) to use the magnificent WinMerge as its diff tool.

Right click on a source managed file > select TortoiseSVN > Settings > drill down to External Programs > Diff Viewer > click External for "Configure the program used for comparing different revisions of files" and enter the path C:\Program Files (x86)\WinMerge\WinMergeU.exe -e -s -x -ub -dl %bname -dr %yname %base %mine.

Note 1. Make sure the path to WinMergeU.exe is correct!

Note 2. I have been using this for ages already, but the "-s" argument is a neat recent addition for me - to force WinMerge to use just a single instance.

Thursday, June 07, 2012

MelbJVM meetup #4

Last night I attended the 4th Melbourne Java & JVM Users Group meeting, and it was fantastic! Here are the highlights.

  • Pizza, beer, over 30 40 developers all talking to each other.
  • JBehave. The first speaker was Aaron Walker who gave a presentation and short demo on JBehave. JBehave is about Behaviour Driven Development - it's a way of writing tests in plain English and is perfect when you need to communicate tests to (or collaborate on tests with) other teams such as BAs or testers. A quick example is below.

    Scenario: User signs up with invalid data
    Given I am on the sign up page
    When I fill in "Email" with "invalid email"
    And I submit the sign up form
    Then I should see error messages

    The scenarios can easily be written by testers or BAs or devs. The Given, When, And and Then clauses get matched to actual Java test code through annotations, e.g.

    @When("I fill in $email with $data")
    public void enterEmailAddress(
          @Named("email) String email,
          @Named("data" String data) {
       // ...
    }
  • Technology Radar by ThoughtWorks. The second speaker was Scott Shaw from ThoughtWorks who presented a register of technologies and techniques that ThoughtWorks maintain called Technology Radar: http://www.thoughtworks.com/radar. It is essentially a survey of a wide variety of tech options ThoughtWorks thinks you should Adopt, Trial, Assess or Hold (avoid)! Important Adopt items included REST, HTML 5, polyglot persistence (relational DBs are not the only option) and polyglot programming (Java is not the only language that runs on the JVM). Interesting Avoid items included Enterprise Service Bus, Java Portal Servers, GWT and Dart.

    I think one of the most important Adopt topics Scott discussed was Infrastructure as Code: there are so many tools now that automate the setting up of a server. You should be able to completely automate the initialisation of a server, it's configuration and deployment of all applications/DBs. These automation scripts etc are infrastructure that should be treated just like code: kept in source control and executed as needed. The consequence of this approach is that you shouldn't need admins to continually log into systems to configure them - human involvement means room for manual error: adjust a script, trash and re-create the server as needed.