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.
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).
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:
Some environments like to have access to the database tables routed via stored procedures. Instead of using Create/Read/Update/Delete aka 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.
Recently I have been thinking a lot about making specification and behavior expected about the database and the code that interfaces with the databases executable. The Behavior Driven Design has a lot of parallels in the database world.
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.
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