Recently I found myself working with timezone translations in a Rails app. However, I had never loaded my local MySQL with the timezone information which led to the conversions’s returning null.

If you haven’t yet loaded your MySQL time zone tables on your Mac, here’s a step by step for doing so from your Mac’s own time zone files.

https://gist.github.com/brianburridge/11298396

I am a very visual person. I like to see graphical representations of large sets of data and complex problems, structures, or processes. This includes application databases, so I was immediately drawn to a new Rails plugin, written by Tom ten Thij, called Schema Browser. It’s as easy as pulling it from git, and running a generate command. The screenshot below is from Tom’s mephisto blog and it illustrates the very nice schema graphic produced from the plugin. Installation instructions on Tom’s blog post, Rails schema browser plugin: proof of concept.

Jasper Reports is unable to call Oracle stored procedures directly, because procedures do not return standard result sets. As a solution, in Oracle, you can use a stored function to retrieve the results of a stored procedure. There are a few more steps to do this than if you were able to use a stored procedure, but it currently is the only option, if the query you need to do can’t be done with a standard SQL query.
Read More »

I have six tables that all use one auto incrementing sequence in Oracle. Problem is, every time the database is redeployed, the next value in the sequence is incorrect because it has to be set statically. So I found this sql on the internet which will update your sequence number according to the next unused ID from your table. I modified the sql because my sequence spanned more than one table. So the modified sql uses a temp table to find the next unused id across multiple tables.

It runs only in sqlplus (due to the use of the variables). I run it with this command:
sqlplus user/[email protected]/path/scriptname

My modified sql.

Original sql.

UPDATE: Thanks to Dan Wilson, Business Analyst where I am currently under contract, for giving me some sql to handle this much better than the sql I had previously found.

SELECT 'DROP SEQUENCE REPORT_LOG_SEQ ; CREATE SEQUENCE REPORT_LOG_SEQ INCREMENT BY 1 START WITH ' || NVL((SELECT TO_CHAR(MAX(LOG_ID)+1) FROM REPORT_LOG),20000) || ' MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE CACHE 20 NOORDER ;' FROM DUAL

This code drops the sequence and then recreates it using the highest id currrently in use in a table, plus one. The only caution about this method, is that, if like my situation, you are using a sequence for multiple tables, then you have to use the table with the highest id in it. So if you are in that situation and you need to automate this process, and you won’t know which table has the highest id, then you may still have to use the previous method I used above. Otherwise, this is much simpler.