I was trying to get a set of records in oracle with a certain date interval. I wanted to make sure that I only get the month, day and year part. Good thing I had an oracle guru officatemate and told me that the TRUNC function strips out the hour, minute, second, etc. portion for me. Since I wasn't...

Note: The code provided will not compile. It should only serve as a guide! We had a performance issue and one of the solutions I came to to prevent excessive rountrip from my Java application to the database is to pass the things I need as an array to a store procedure. I won't cover the Java part...

Note: I'm doing this code inside a Package. I've never written an PL/SQL program outside of a Package. You can declare exception everywhere inside a PL/SQL Stored Procedure or Function. PROCEDURE MY_PROC(V_ARG_SOMETHING IN MY_TBL.MY_FIELD%TYPE) --SOMETHING CAUSE AN EXCEPTION HERE EXCEPTION WHEN...

I've been doing quite a lot of PL/SQL lately. It's funny because I was able to utilize the stored procedures more in Oracle although the product I was able to code my first stored procedure in was SQL Server 2000. Not that I did not do any stored proc in SQL Server profesionally, I just think...

I've been fiddling with Oracle SQLDeveloper lately. What I liked about it is that it's free and second, it's an alternantive to M$ SQLServer. I think you could even use it for other RDBMS other than Oracle like MySQL and SQLServer. It's made in Java(rocks!), and there's an online...

I've been thinking my whole career through what the Levels of Data Normalization means. I know I've been doing it, but I didn't know on which level I am at during this process. I had a discussion with an office colleague and he told me to follow the standards, i.e., up to the 3rd level of...

I just had a talk with one of our senior .Net developers. I asked him if he know about Execution Plan and Indexing in Database. Hmmm... I was shown a diagram in SQLServer which apparently computes the cost of each query in percentage. I guess query optimization is just left with the programmer of the...

Truly, in life... There is no shortcut. And the same rule applies to me. I just came from a very exhausting interview today and I was asked about several things about Java technology tools which I don't have experience with and SQL Performance Tuning. The most frustrating question though was when...

It's funny how the simplest of things could actually fool you. If you want to search for a particular String, let's say "Lamia", but you don't know if that name actually has some trailing numbers or something. You just know that it starts with "Lam". How do you search...

I have been thinking for a good solution on how to avoid the dynamic SQL in stored procedure for a while and I am happy that I got one. In my opinion, dynamic SQL statements are ugly, error-prone, difficult to maintain and hard to read especially if you have tons of conditional statements. For example...