Java Thinking

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.