I know what I said about a post every two days but something has entered my head that seems timely. Things seem to starting up for 2008 and my getting a swathe of post ideas is only one of them. Today, Sun has bought up MySQL, the database that stores these ponderings for posterity, and Oracle has finally got its hands on Bea, the people behind the Weblogic software with which I have had an indirect brush for a lot of 2007.

I tried starting up Oracle’s SQL Developer last night so that I could add a listing if my hillwalking blog posts to my website’s site map with a spot of PHP scripting. However, all that I got was something like that which you see below:

I must confess that this one threw me. The solution, though hard to find (they often are, even with the abilities of Google) was to use a batch file called sqldeveloper.bat than you can find in the [installation directory]\sqldeveloper\bin directory. It does start the thing when all else seems to fail and got me up and running again. I did get that blog post listing added to the site map after all; Having more visibility of the MySQL tables was a definite plus point.

Here’s a gotcha that caught up with me on my journey into the world of Oracle SQL: string quoting. Anything enclosed in double-quotes (") is the name of an Oracle object (variable, table and so on) while values are enclosed in single quotes (’). The reason that this one caught me out is that I have a preference for double quotes because of my SAS programming background; SAS macro variables resolve only when enclosed in double-quotes, hence the convention.

Until SAS 9.2, SAS/Access will not support the Oracle timestamp format. There still is no word on when 9.2 might appear so it’s over to the SQL pass-through facility…

It seems that SAS have put a good deal of effort into making their software work with Oracle. Admittedly, you have got to buy SAS/Access for Oracle in addition to the other components that you already have but it is worth it. The Oracle library engine makes things easy so long as there are no incompatibilities on the database side. For instance, SAS has no plans to support the Oracle timestamp format until the forthcoming SAS 9.2 and this does make things a little interesting. However, this can be resolved with the Oracle SQL pass-through facility where you pass Oracle SQL through to the database itself for processing, avoiding incompatibilities. A more pressing issue is using PROC APPEND to add records to the data tables without updating any sequences that are associated with table ID’s. The SQL pass-through facility is the best way around this so that you can update the sequence with a SELECT statement and use the current value for the ID in the following EXECUTE statement. It may sound far from ideal but you need to process your data row by row; once set up though, everything works well.

Because of my work, I recently have had a bit of exposure to Oracle SQL Developer, which I have been using as part of application development and testing activities. For further investigation, I decided to have a copy at home for further perusal (it’s a free download) and it was with some interest that I found out that it could access MySQL databases. To do this, you need Connector/J for MySQL so that communication can occur between the two. Though you quickly notice the differences in feature sets between Oracle and MySQL, it seems a good tool for exploring MySQL data tables and issuing queries.