Oracle: Get Month Start and End Dates for a Given Period (i.e. Year)

Took me a few minutes to think this one through, so I thought I’d share. In this example I show the start and end date for each month in the period that contains the fiscal year from 7/1/10 to 6/30/11. select trunc(add_months(’01-JUL-10′, level-1), ‘MM’) start_date, last_day(add_months(’01-JUL-10′, level-1)) end_date from dual connect by level <= ceil(months_between(’30-JUN-11′, ’01-JUL-10′)) […]

T4 Slays Spaghetti Monster

My client wanted a list of all of the tables and their respective columns in a given SQL server database.  My first instinct was to get Visio to create an ER diagram by reverse engineering the database. However, with dozens of tables in this database, the diagram that Visio spit out looked like spaghetti and […]

Storing Passwords in a Database

We all know that you should take some steps to obsfusicate passwords stored in the database so that they are not exposed to users with read access to the table that they are stored in. This Ask Tom post gives straightforward examples of how to obsfusicate passwords stored in an Oracle database either by encryption […]

No shortcircuit for nvl()

This Ask Tom post explains performance differences between nvl() and decode() for null values. In short, decode() will shortcircuit thereby making it much faster if your replacement expression in nvl() is expensive.

Geocoding without GIS Software

I can’t believe this blog has been up for 5 months, and I haven’t posted anything about GIS – since I come from a GIS programming background. It is even more telling that this post is about how to get around using GIS software by using ASP.Net, SQL Server, and the Google API to geocode […]

Write Application Errors to the Oracle Alert Log

My relational database experience is primarily in SQL Server, and when developing applications using PL/SQL stored procedures in Oracle I keep wanting to be able to write errors to the equivalent of the Application Log of the Windows Event Log. Until recently, I have just been publishing errors to a table I set up in […]

Copy a Schema in Oracle

I don’t know why I found it so hard to find a straight answer to the following question: How do you copy all the objects of a given schema to a new schema in the same Oracle database? Or, more simply, how do you make a copy of a schema in an Oracle database? The […]