Java Thinking

I recently mapped a JPA entity to a view, and encountered this error while using hibernate to update the database schema: ORA-01702: a view is not appropriate here [SchemaUpdate.execute] could not complete schema update I was using the Oracle JDBC drivers 10.1.3.3 - luckily, the solution was to upgrade the drivers - when using 11.2.0.2.0 it works fine. Download ojdbc6.jar from here: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html ojdbc6.jar (2,152,051 bytes) - Classes for use with JDK 1.6. It contains the JDBC driver classes except classes for NLS support in Oracle Object and Collection types.

I'm working on a little sample framework - really only to keep my sanity and practice my chosen craft - that allows you to string together tasks in a pipeline for processing. To exercise that framework - to flush out the pros and cons of the implementation - I'm writing a sample application. The basic idea is that each task is interested in an event. That event could be the arrival of a file, or the completion of another task etc. To string tasks together, I've created a simple database queue system - as a task completes, it writes to the queue and then other tasks which are interested will see that event and then begin. Tasks complete either successfully (by returning no errors) or unsuccessfully (by returning more than one error). An important concept is to be able to add (or remove) tasks from the pipeline programmatically and without changing the database schema. So, quite simply, when a task completes, that event is written to the queue - but how do we find which event...

Sometimes applications need access to data from another application. Here is one approach you could take: Define a view in the database that owns the data - this view should expose the data that the external application needs Grant select permissions so that the external application can select from the view Create a synonym in the external applications database for the view so that it appears as a local resource This has the advantage that the owner of the data defines what is exposed. Handy if the data is sensitive in any way. This solution also assumes that the external application only wants to read the data. If it wanted to write to the database, then the application that owns the data should probably publish an API (eg web services et al).

I've been wanting a way to use playlists on my media center either from within MythTV or with any other player. The reason is I have a bunch of short videos (music and documentaries) that I'd like to be able to play consecutively. It turns out to be a case of just RTFM . You can create playlists for MPlayer from directory listings, and then associate playlists with a particular mplayer command. To generate the playlist from all files in a directory, I use the following: dir * | sed 's/\\//g' > all_videos.pls The sed part of this command removes the \ from escaped spaces... i.e. a file 'Hello world' is output from the dir command as 'Hello\ world' - the space is escaped. We need to generate a file without these escaped spaces for it to work properly. I use the following mplayer command associated with the pls extension: mplayer -shuffle -fs -zoom -quiet -vo xv -playlist %s

I was just using a query which made use of the ST_INTERSECTS function: select * from table1 where st_intersects(st_point( ?, ?, 1),shape)=1 With the data I had, this query took 30 seconds! Before launching into an investigation to find out why, I just decided to swap the parameters - this made all the difference: select * from table1 where st_intersects(shape, st_point( ?, ?, 1))=1 Now the query returns instantly! I'm no database expert, so investigating why the first version of the query took so long would have been a waste of valuable time - when such a simple solution was at hand.

Often there is a need to compare two databases and see the differences. I come across this a lot when releasing a new build into an existing environment - the new code runs on the development database, but the test environment needs a schema upgrade before the code will run. There are tools that will compare schemas for you, but sometimes I just need to quickly find out what columns are missing from the target schema (this is particularly useful when comparing different development schemas and you want to quickly implement changes - production releases need much better quality control than this). The sql below provides one solution - showing the columns that are missing from 'owner1' when compared to 'owner2' for tables that match the prefix: This will ofcourse identify missing tables. It's primative, but its an easy way to see basic structural differences using SQL - without the need for expensive tools. Since it is basic sql, you could include this as part of an e...

I use SQLDeveloper when interacting with Oracle . When viewing tables with date columns it can be frustrating that the default display does not show time (just day, month and year). To change this behaviour, you can set the date format for your current session: alter session set NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS' Now, when you view tables or resultsets, you'll have a more precise view of the data: 04-Sep-2008 09:07:51

It frequently surprises me when seemingly simple things are missing from mature products. For example, Oracle doesn't have a boolean type. Strange but apparently true. Never mind, this article describes how to work around this.

When making support requests it is always helpful to include version information about the product in question. Rather than just stating 'version X' I like to get the software to display the version information and just copy and paste it - this way there can be no confusion, and there may even be extra useful information. So, how to display the oracle version information? I came across this useful post which suggests: select banner from v$version; which on the instance I'm looking at produces: BANNER---------------------------------------------------------------- Oracle10g Enterprise Edition Release 10.2.0.3.0 - Production PL/SQL Release 10.2.0.3.0 - Production CORE    10.2.0.3.0       Production TNS for Solaris: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production Being an SQL query is even more useful than a command line, since you can run this within your applications if you need to display such data.

This information is out of date - please see http://grails.org/plugin/p6spy for the current documentation. I wanted to have a go at creating a Grails plugin so I thought I'd write one to make it easy to add P6Spy to your Grails application. The experience was very good. The structure and distribution mechanism is excellent and the Grails guys have done a great job. Here's the result: grails-p6spy-0.1.zip P6Spy lets you monitor the JDBC queries by proxying your database driver. In addition to logging the prepared statements, it also logs the sql with parameters in place so you can copy and paste the exact sql into your favourite database client to test the results. This Grails plugin makes it easy to utilize P6Spy in your Grails applications. Introduction This plugin contains 2 files - the p6spy jar and spy.properties. After installing the plugin in your Grails application, you can find them in: <project directory>/plugins/p6spy-0.1/grails-app/conf/spy.properties ...

Where has the disk space gone To find out which tables are the biggest you can run this query: SELECT owner, segment_name, segment_type, tablespace_name, SUM (BYTES / 1024 / 1024) sizemb FROM dba_segments GROUP BY owner, segment_name, segment_type, tablespace_name ORDER BY sizemb DESC Recovering disk space Most of the following was learned from this forum post . As tables grow, it can be necessary to compact them to free up disk space. Deleting unnecessary rows won't necessarily free disk space. The space occupied by those rows just becomes available for new rows to consume. To free up space, you need to truncate (or drop and re-create) the table. If there are constraints referencing some of the rows in the table you want to shrink then you'll have to disable those first. So, assuming: you want shrink table FOO you've deleted the rows you don't need from FOO table BAR has constraints referencing FOO -- copy those rows you want to keep into a new table CREATE TABL...

I quite often work with database rows that contain audit information which includes the last modified time. Oracle allows SYSDATE addition so that you can add or subtract a number of days (or fraction of days): select SYSDATE-1 from dual; would return the current time minus 24 hours. So, if you are in the process of debugging and looking at the database rows you could restrict the select statement to show only those modified by yourself in the last hour: select * from foo where last_modified_date > SYSDATE-(1/24) and last_modified_by='bar'; or last half hour: select * from foo where last_modified_date > SYSDATE-(1/48) and last_modified_by='bar';