Tag: Oracle

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.

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 I didn’t get the feeling that I was missing much. I currently use SubSonic as a means of automating the creation of an abstraction layer between my UI code and the database, and I love it. I’ve been considering it a bridge that will last me at least until LINQ to Entities comes out later this year, at which point I’ll have to evaluate the pros and cons of each. Scott Hanselman’s recent interview with Mike Pizzo provides a great in-depth analysis of the differences between LINQ to Entities and LINQ to SQL. As with any abstraction layer, performance seems to be the question on everyone’s mind. It would be great to see benchmarks of the same queries of the same database for both Linq to SQL and Linq to Entities. Which brings me to my point, I would love to see a Linq to Oracle. I’m sure Oracle is on board to create a provider for the Entity Framework, and I know the EF provides added value (mapping entities to multiple tables, and vice versa), but I would also like to see Oracle provide an implementation of the IQueryable interface that is lean and mean for simple applications. In the mean time, I think I’ll check out the DbLinq Project, which claims to provide LINQ implementations for databases other than SQL Server. I was going to look into this when I first started using SubSonic, but I saw the Nov 07 update that said the Oracle Provider was no longer supported. Still, it’s probably worth downloading and comparing.

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.

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.