Architecture and Data Blog

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.

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. so when you do a select from the FOO table the data would be

Consider this Hibernate mapping

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

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.

Recently when our test databases where upgraded new version of Oracle, we started noticing that the order in which some drop down lists were being displayed was not correct. It turns out that the SELECT statement we had, did not have a ORDER BY clause and the data was being returned in the ORDER of the creation of rows (in the order of ROWID) when the database was upgraded these ROWID’s got changed and hence the ORDER of the data being shown in the drop down lists.

Couple of weeks back I was given a choice to upgrade my work Laptop to a Mac Book Pro or a Windows Laptop. I choose Mac ( I know everyone is into macs nowadays). The transition was pretty good, with the exception of moving my oracle database from windows to mac, since there is no native installation of oracle on mac I had to use VMWare fusion to install oracle.

When creating a Foreign Key constraint on the database as shown below

ALTER TABLE BOOK ADD
    (CONSTRAINT FK_BOOK_AUTHOR FOREIGN KEY (AUTHORID)
     REFERENCES AUTHOR)
/

In the above example we are telling the database to check if the BOOK.AUTHORID is a valid value in the Author.AuthorID. When the Author table is being changed, the database does data verification on the BOOK table using SELECT against the BOOK table for the AUTHORID some thing like this

So we version control/source control everything on our project.. code/data/artifacts/diagrams etc. yesterday I said why not extend it to my writings to everything I have. So I started this long journey of refactoring my folder layout and making a nice folder structure to hold all the things I have written about have other artifacts in the process of writing and moved them all to subversion, now all my example code and writings are all under version control that gets backed up everyday…. feels liberating