Architecture and Data Blog | Architecture and Data Blog

Testing in data conversion projects

During legacy migration its imperative that the migrated data be tested for logical consistency

When working on projects involving Conversion of data or Migration/Moving of data from a legacy database. The testing effort is enormous and testing takes a lot of time, some test automation can help this effort. Since data is moved/changed from a source database to destination database, we can write sql which should provide results for the types of tests you want to perform, for example: write a sql to give us number of customers, write a sql to give us account balance for a specific account.

Posted by Pramod Sadalage on Wednesday, November 18, 2009

Ruby OCI 2.0 Array binding

How to use stored procedures as interface to the data

We have been doing some data moving lately using Ruby and Ruby-OCI. We started with Ruby OCI 1.0 and did use prepared statements with bind variables (since we are using oracle database and pulling data from an oracle database and pushing data to an oracle database). Later we found this really cool feature in Ruby-OCI8 2.0 where you can bind a whole array and just make one database trip for many database operations.

Posted by Pramod Sadalage on Thursday, October 8, 2009

Create an Index for all FK Columns in the database

Generating indexes for all foreign key constraints in the database

Most of the time I have seen database foreign key constraints on tables without indexes on those columns. Lets say the application is trying to delete a row from the CUSTOMER table DELETE FROM CUSTOMER WHERE CUSTOMERID = 1000; When the database goes about deleting the customerId of 1000, if there are foreign key constraints defined on customerId, then the database is going to try to find if the customerId of 1000 is used in any of those tables.

Posted by Pramod Sadalage on Thursday, September 3, 2009

Materialized views and database links in oracle.

How to utilize database views across other databases

Recently one of my colleague Jeff Norris had a weird error. He was trying to build a materialized view over some tables in his local database and some tables in his remote database using database links the sql to create the view ran fine and provided the results as expected, but when put inside a materialized view statement complained with ORA-00942 errors. Lets say the two databases in question are local and remote, so the sql to create the materialized view to load immediately and refresh everyday is

Posted by Pramod Sadalage on Monday, August 10, 2009

Perfectly good data.. wasted

Okay this is kind of a rant, maybe I’m too picky or just that I hate to see perfectly good data not being used. This is how it goes.. I go regularly to this store to get Horizon organic milk for my family, about 60% of the time I see milk I need NOT in stock, okay I can live with that, may be lots of folks are buying organic milk, but not when it happens frequently, especially when the store knows how much milk was ordered (or supplied from the warehouse) and how much milk was sold, the store should be able to figure out that organic milk gets sold out pretty fast, putting my Business Intelligence (BI) hat on, I think the store should be able to predict when they are going to run out of organic milk ( for that matter any product), its especially frustrating when they have all the data they need to get it done.

Posted by Pramod Sadalage on Wednesday, August 5, 2009

Explicitly rollback when you encounter a deadlock.

Deadlock transactions raise an exception, but don't automatically get reversed

Dead lock is caused in the database when you have resources (connections) waiting for other connections to release locks on the rows that are needed by the session, resulting in all session being blocked. Oracle automatically detects deadlocks are resolves the deadlock by rolling back the statement in the transaction that detected the deadlock. Thing to remember is that last statement is rolled back and not the whole transaction, which means that if you had other modifications, those rows are still locked and the application should make sure that it does a explicit rollback on the connection.

Posted by Pramod Sadalage on Tuesday, May 26, 2009

Oracle for the Mac

Ever since I moved to the Mac, I had to run some other OS inside a VM so that I could run Oracle and use it, since Oracle was not available for the the Mac. Now that is no longer the case. Oracle 10gR2 (10.2.0.4) is now available for Mac here This is especially nice since the Oracle for Mac was the most voted requirement on mix.oracle.com

Posted by Pramod Sadalage on Thursday, May 14, 2009

In Oracle 11g password is case sensitive

In Oracle 10g and before we all know that passwords are not case sensitive, so PASSWORD, Password, password would let you in and everything would be okay. If you upgrade to Oracle 11g (I know lot of you are waiting for 11gR2), you will find that passwords are case sensitive. Here is an example of case sensitive passwords. c:\Software>sqlplus bddd/bddd@dosa SQL*Plus: Release 11.1.0.6.0 - Production on Wed May 6 15:17:43 2009 Copyright (c) 1982, 2007, Oracle.

Posted by Pramod Sadalage on Wednesday, May 6, 2009

Oracle Metadata can be mis-leading

Oracle has metadata about all its objects in various tables/views. One such view is the USER_OBJECTS or ALL_OBJECTS, this view has a column named as STATUS which shows you if the given object is VALID or INVALID. The status applies to DB Code (Stored Procedures, Functions, Triggers etc). To find all the INVALID objects in the schema, issue SELECT * FROM USER_OBJECTS WHERE STATUS='INVALID'; One problem with the way oracle maintains this metadata is, changing the underlying table on which the DB Code depends, oracle marks the objects are INVALID even though the underlying table may have changed in such a way, that it does not affect the DB Code at all (like adding a new column, or making a colum nullable).

Posted by Pramod Sadalage on Tuesday, March 31, 2009

Storing just the time in Oracle

How to store time, without the data component

We came across a need to save just the Time in the database, the requirement is to store time of the day, like say the user likes to have Breakfast at 8.15AM and Lunch at 12.32PM etc. Off course oracle does not have a time only data type. So we ended up using DATE as the data type and just setting the time. for example: CREATE TABLE FOO (PREFERRED_TIME DATE NULL); INSERT INTO FOO (TO_DATE('11:34','HH24:MI')); oracle automatically sets the date to the first day of the current month.

Posted by Pramod Sadalage on Thursday, February 12, 2009