Tag: Database

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'))

START_DATE END_DATE
7/1/2010 7/31/2010
8/1/2010 8/31/2010
9/1/2010 9/30/2010
10/1/2010 10/31/2010
11/1/2010 11/30/2010
12/1/2010 12/31/2010
1/1/2011 1/31/2011
2/1/2011 2/28/2011
3/1/2011 3/31/2011
4/1/2011 4/30/2011
5/1/2011 5/31/2011
6/1/2011 6/30/2011

It should work for periods of any length (i.e. one quarter, 6 months, 2 years, etc) by replacing the period start and end dates.

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 meatballs, and would have printed across 36 8.5″x11″ pages. That said, Visio did a better job laying out the diagram than I would have by hand.

Then I thought, what if I had an automated way to create a document that listed the tables in alphabetical order, showing all of their columns? This list would be something that the client could cross-refrence against the spaghetti monster. Well, thanks to T4, I do have such an automated way. I spent my lunch break hacking away (it’s scary to see how much you rely on Intellisense once you don’t have it) at this simple template that will iterate through all the tables in a database and output all the columns for each as a text file. To get it work,follow these steps:

  1. Download the template file (all_table_columns.tt).
  2. Copy it to any Visual Studio 2005/2008 project.
  3. Open it, replace “[your connection string here]” with a valid SQL Server connection string (ideally to a database with a limited number of tables), and then save the file.

Visual Studio attempts to generate the output whenever you save the template file, so the new text file should already be generated.  To view it, go to the Solution Explorer window and expand the node for all_table_columns.tt and you will see a file named all_table_columns.txt. Open that text document and you should have a list of all tables and columns!

For more on code generation via T4, see Scott Hanselman’s link-fest of a post.

Terra Nova Pools Site Launched!

My most recent project has been to create the first web site for Terra Nova Pools

My most recent project has been to create the first web site for Terra Nova Pools, a swimming pool design and construction company based in Orange County (CA). I launched the site a few days before the client’s deadline of Oct 1st, 2006.

I’ve been really excited about this project for a lot of reasons. Firstly, it’s the only project outside of this site that I have done without partnering with a design team (“Damn it Jim, I’m a programmer not a designer”). It’s also the first time that I’ve been responsible for the search engine optimization, and the result is that it is some of the cleanest XHTML and CSS code I’ve ever written. I’ll add the site to my portfolio soon and include details about the technologies I chose while implenting the site.

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 or by hashing.

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 addresses!

Check out this post about Geocoding with SQL Server on John Sample’s (is that his real name?) blog.

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 Oracle. However, this article shows how you can write to Oracle’s alert log from a stored procedure in Oracle.

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 short answer is that you use the export/import command line utilities EXP and IMP. Most examples show how to copy a schema from one database to another, but in my case, I wanted to make a copy of the schema within the same database. I ended up discovering that the IMP command can take FROMUSER and TOUSER arguments that allow you to specify which schema objects should be copied from, and which schema to copy those objects to. Here’s the procedure I used:

  1. Create the user/schema that you want to copy the objects to
  2. Use the EXP command to export all the objects from your source schema
  3. Use the IMP command with the FROMUSER/TOUSER arguments

One thing to keep in mind is on what tablespace the copied database objects will be created on. In my case, I wanted them created on the new user’s default tablespace as opposed to the tablespace that the source objects were created on. To do this, simply set the import user’s quota on both the original tablespace(s) and the system tablespaces is 0:

alter user [import_user] quota 0 on system;
alter user [import_user] quota 0 on [orig_tablespace];

For more on using the export/import commands, see the Oracle documentation.