Architecture and Data Blog

Lots of time hand coded SQL is written in the applications for performance sake, getting data using complex SQL out of the tables. As the database structure changes it becomes really hard to find out all the SQL that needs to be changed. In other cases SQL is generated based on certain conditions in the code and its hard to find if the generated SQL is valid after database changes are done.

When accessing the database using stored procedures for basic Create, Read, Update and Delete (CRUD) functions, or when you want to write triggers that capture the before and after values in tables, or when you want to create Plain Old Java Objects (POJO’s) that match the database objects, writing these by hand takes a lot of effort and also since the requirements are changing in an agile projects at a frequent rate, there will be design changes to meet the requirement changes, so the triggers, CRUD stored procedures or Data Access Objects (DAO) are going to be out of data, instead of hand coding, its better to generate the code, using the metadata of the database

Traditionally the data-team is used to sitting in their own area and working for many project teams by handling requests either via a ticketing system or vi email. The hand-over of work or throwing of work over the wall creates knowledge silos and inefficiencies.

Many projects need addition of identical columns to all the tables created by the project. Audit columns are an example of such a requirement. The requirement is to add columns such as created_by, created_date, modified_by and modified_date to all the tables, these columns store, who created the row, when the row was created, who modified the row last and when was it modified. created_by and created_date are required to be present when the row is inserted and thus are required to be not nullable. Adding these columns to each and every table is a lot of work for developers.

In every enterprise and every project we end up having multiple environments, especially the database side of the enterprise tends to stick around for a longer period of time and has much more dependencies or application integration as opposed to application urls etc. Given this, how to name the servers, databases and schemas becomes a very important decision, do these names provide for an easy way to use the application and not make it harder or the developers to access the database.

Some versions back, Oracle would not allow to create database object names with mixed cases, even if we tried to create them, we could not. In newer versions of Oracle we can create tables, columns, indexes etc using mixed case or lower case, when the names are put inside double quotes. For example

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.

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

Lets try to connect with a upper case password

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). Here is some code which shows you what I mean. Run it through SQLPlus.