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′)) [...]

The Missing LINQ (to Oracle)

Since all the .Net applications I am currently working on use Oracle on the back end, I haven’t spent a lot of time looking into LINQ – LINQ to SQL only supports SQL Server. After attending the 2008 launch event and getting a chance to put my hands on some LINQ examples in the labs [...]

EXISTS vs IN

I keep finding myself referring back to this Ask Tom post about perfomance differences between EXISTS and INagain and again.

Posted in Uncategorized | Tagged , | Comments Off

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.

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 [...]