Passionate about data

Data and its implications on software design and development.

Explicitly Rollback When You Encounter a Deadlock.

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.

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

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.

1
2
3
4
5
6
7
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.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning and OLAP options
BDDD@dosa >

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

1
SELECT * FROM USER_OBJECTS WHERE STATUS='INVALID';

Storing Just the Time in Oracle

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:

1
2
CREATE TABLE FOO (PREFERRED_TIME DATE NULL);
INSERT INTO FOO (TO_DATE('11:34','HH24:MI'));

Considerations When Using Stored Procedures for Doing CRUD.

Some environments like to have access to the database tables routed via stored procedures. Instead of using Create/Read/Update/Delete (CRUD) with DML, stored procedures are invoked with the parameters to perform the required operation. I’m not arguing about the benefits/pitfalls of this approach, if you have to do stored procedures, here are some things to look at.

  1. Make the stored procedure handle one object/table only and not multiple objects or tables.
  2. Do not commit open transactions inside the stored procedures.
  3. Do not do business logic in stored procedures.
  4. If they are straight CRUD stored procedures, see if you can generate the stored procedure code using some metadata?
  5. Make sure creation and execution of the stored procedures is part of your Continuous Integration build and developer build.
  6. Make sure stored procedures (or the metadata used to generate them) is under Version Control, have seen many problems when the stored procedure version does not match application code version
  7. Develop against the production stack database.
  8. Make sure exceptions thrown by the database are passed back to the application.

Hibernate Weirdness With Property Names

Consider this Hibernate mapping

1
2
3
4
@Column(name = "qReferenceId")
public Long getQReferenceId() {
return qReferenceId;
}

Where qReferenceId is data provided to our application via a external reference, we do not have a QReference Object or Table for FK references.When trying to query this object using DetachedQuery, this Simple expression was used.

1
2
3
4
5
6
7
public List<Movie> findByQReferenceId(Long id) {
     final SimpleExpression matchesId = Property.forName("qReferenceId").eq(id);
    DetachedCriteria criteria = DetachedCriteria.forClass(Movie.class);
    criteria = criteria.add(matchesId);
    List<Movie> movies = (List<Movie>) getHibernateTemplate().findByCriteria(criteria);
    return movies;
}

When running this method, I kept getting errors shown below.

1
2
3
4
could not resolve property: qReferenceId of: com.example.Movie; 
nested exception is org.hibernate.QueryException: could not resolve property: qReferenceId of: com.example.Movie
....
....

I thought I had a spelling mistake on the property name and also tried many other combinations. Finally I said why not use “QReferenceId” and suddenly everything was hunky dory, this kind of weirdness does not happen when working with property names that do not have consecutive double uppercase characters in the getter/setter for the property. very interesting. So this method worked

1
2
3
4
5
6
7
public List<Movie> findByQReferenceId(Long id) {
    final SimpleExpression matchesId = Property.forName("QReferenceId").eq(id);
    DetachedCriteria criteria = DetachedCriteria.forClass(Movie.class);
    criteria = criteria.add(matchesId);
    List<Movie> movies = (List<Movie>) getHibernateTemplate().findByCriteria(criteria);
    return movies;
}