Wednesday, July 30, 2008

Book Review: Beginning PL/SQL - From Novice to Professional

If you wanted to pick a book in PL/SQL, I recommend this one- Donald Bales: Beginning PL/SQL : From Novice to Professional.

I remember I told my colleague that for starting as a programmer, we read a lot of syntax and rely on reference book most of the time. Something like a dictionary, where you can find the word for your writing. As time moves on when we started to get familiar with the syntax, getting another reference book will not improve our skill any further. To move forward, it about getting a new concept on how to write better software. More thinking is shift to the design work instead of the labor coding work.

And this book has both of them. It started with brief coding on the first half and the second part is more involve in design and coding concept. It also has a fairy tale chapter, which is my favorite chapter, because in order to use of what he had describe seems to be really a fairy tale. The interface and design in that chapter make sense but I have seen none of them being used so far in my working career.

So to my book review, as usual, it is separate to 3 sections.

1. Who should read it?
Programmer starting to learn PL/SQL for the first time or senior developer who might want to get more insight on design.

2. What is it all about?
SQL and PL/SQL. Beside just syntax reference, there are also chapter in concept for object orientation, debugging, testing and documentation. Object orientation gives me a great insight on how to utilize the object type feature available in PL/SQL. Of course, debugging, testing and documentation also play an important part in programming career. This book provide good example for all of them.

3. What so special about it?
The book is written in non-too-technical way and a bit of humor, therefore it's fun to read. Although some of the joke I can't quite get it but it may put a smile on other reader. Beside that, this book will probably make me read it up again and again because there are more stuff to explore and learn. Fully competent in coding? Then try write better debugging tool. Got a perfect debug tool, then write better unit test. Had a 0% fail rate on your script, then write better documentation. No support call from user because of excellent document? Create a new product and start coding again. By the way, did I mentioned there is a fairy tale chapter?

Overall, the book is enjoyable to read and in the same time it cover all major topic required to write better PL/SQL script.

Monday, July 28, 2008

Clean fire truck

Currently, I'm reading a book from Seth Godin: Small is the new big, and there one statement that caught my eye for programmer. But before that, it mentioned about clean fire truck. The story is about the fireman waiting for the fire alarm to ring, which it seldom happens. What they do in their spare time was to clean up the fire machine and the author suggest they can use the time to spread about the fire prevention information instead.

So, if a organization is labeled as having clean fire truck, it means the company doesn't use its time wisely. This lead to the statement that I wanted to quote,

"Software engineers are often overwhelmed with an endless list of programming fires - and rarely get a chance to think about what they ought to build next."

This statement somehow gives me a deja vu. :)

Simple Data Migration strategies for programmer

The data migration I'm going to talk about is not on the whole process strategy but rather on the actual programming work. Data migration is more than just about transferring data from one place to another and there are couple strategies may consider to be included into the coding process.

1. Using permanent temp table
Import raw data into permanent temp table allow more control on the data. For example, the temp table can have constraint rule created according to destine table or having primary key to avoid duplicate. Furthermore, transferring data within the database would prove to have better performance and provide more convenient way (no need to switch tools/editor) to troubleshoot the raw data. It also allow developer to filter the data and data checking using SQL before import.

Pros: Raw data stays in one place. Probably better data transfer performance and allow raw data checking using SQL.
Cons: Extra space required to store the data. Therefore it depends on how much space is left in the server/machine and how large is raw data for the process.

2. Add extra id/reference field in temp table
This allow to back track the raw data if there are some data inconsistency after the migrated data has been used for couple days. The value for this field is to copy from the destine table primary key/unique value into this reference field. Furthermore, indexes assign to this field might help to improve query performance.

Pros: To help developer to find invalid raw data or to track down bug
Cons: To maintain an extra field might cause minor performance hit and again, more complex script required (Perhaps more fun for those programmer who like challenges).

3. Re-Runnable script
Although is not a must, re-runnable script allows the migrate process to re-import again. Of course, re-run the migration script only picks up raw data that are fixed or troubleshooted or reference field that are blank (not imported). Therefore, this will shorten the time for second run. On top of that, having a re-runnable script will simplify the remote support process by asking the DBA to run the script again after raw data has been fixed.

Pros: If the migration process stop at half way, there's no need to clear the imported data. Just re-run the script will only picks up fixed raw data. Better support for DBA.
Cons: Complex migration script is required and it should be a easy for programmer who wants to try more complex logic.

4. Cleaning/Validate script
If the raw data obtained are all healthy, this section can be skipped. However, there are no such thing as perfect raw data for migration. There are cleaning up and business logic validation required to make sure that the data can be fit into the system without causing any error later on. There are pre cleaning, where invalid data will be either filter out or fixed before import. Then there are post cleaning, which data generate by the triggers that are not required, will be clean up.

Pros: Better data integrity and smoother migration process
Cons: More rules to check means more work required.

5. User friendly screen output
It helps the developer if the user/DBA able to report the error according to what's written on the screen. Hence, having a user friendly message or summary (e.g. no of records processed, successful import and failed) will help the developer to identify the possible cause of error. It also helps remote support since the communication will be much clearer on what's going on.

Pros: Helps the support process by having the user to report error according to the screen, which is what the developer is expected for.
Cons: Sounds unnessary and possibly degrade programmer IQ to think of the user friendly message.

6. Logging
Last but not least, is the logging process. It better to log down any activity performed so when there are unexpected error shows up, the log information will really help to troubleshoot the error. Of course, the error log can be in file or table structure and the data it holds should at least have rowid, date/timestamp, error message/code and recordid. The recordid allow us to trace back the raw data.

Pros: Having an history record of what have happened will sure help the debugging and support process.
Cons: Migration performance will degrade if there are alot of file IO and table access. Therefore, it is best saved for error logging message only.

Risk management should take into consideration on how to minimize the impact on the business process such as backup/restore strategies. Of course, I'll leave that to the DBA to decide. Nevertheless, the above strategies might help minimize the risk and perhaps improve the support process.

Tuesday, July 15, 2008

Use sequence.currval for record count only

Currently I'm doing a data migration and the process is simple. Read the data from text file into temp permanent table in oracle, then transfer the data to the real time table. The temp table then will be updated a id number for reference to keep track which data has imported, so the script/procedure can be run again only for the failed records. A log table also created to log the import process, in case of any error appear for specific transactions the message is recorded.

Again, sequence of the imported data must be followed and therefore it's tempting to use the currval value to update the table. I was told that using currval will going to cause some trouble as environment is in real time and the sequence number might be changed when I use currval. The solution for this problem is simple: assign the sequence.nextval value to a variable and use that variable value instead of currval.

So what's the currval can be used of? Due to the value it holds can be unreliable and I have no idea who has call nextval and cause the value changed, therefore I believe it is best used for record count instead of max() or count() function. The benefit of this approach is maybe some performance improvement but more importantly, a more reliable application.

Basic Element of Oracle SQL

Wednesday, July 9, 2008

Assign sequence value to variable in plsql

To cut the chase, the solution is:
select [sequence] into [variable] from dual;

Update: Oracle 11 allow to assign sequence directly. e.g. [variable] := [sequence].nextval;

Read further to find out my opinion on this syntax.


One thing struck me today while I was preparing a script for data migration project. But before that, let me briefly explain sequence in plsql.

Sequence in plsql is a feature that allow developer to keep track the key count. So, it save you from having a table of storing key value. The basic use of this sequence is simple. For example, I have created a sequence name s_employeeid.

To get the increament value:

To get the current value is:

The syntax for insert a employee record may be look something like this:
Insert into Employee (id, name, age) values (s_employeeid.nextval, 'Alexander', n_age);

Note: n_age is a number viarable.

From the sql statement, it worked rather straight forward. What happened to me today is when I want to assign the sequence to a viarable and in the meantime, I want to pass the sequence to a function. Something like below
n_empid := s_employeeid.currval;
insert_employee(s_employeeid.currval, 'David', n_age);

Both syntax return error. I was surprised and find no reason why both syntax would return error, as sequence and viariable both used exactly the same way. Is the viarable and the sequence has diffrent datatype? Nope, I even defined the variable exactly the same as the sequence.

n_empid s_employeeid%type;

It still won't work after couple of try. Finally it was like this:
select n_employeeid.currval into n_empid from dual;

I only able to pass the sequence's value by using the dual table. Something like a dummy sql.

In conclusion, I don't like it. I thought that a feature that act and behave almost the same as a variable will allow direct variable assignment; but not in plsql. Why?

I rather stop asking.

Update: There are also one mind boggling syntax, which is common on using null in where clause or in if-then-else statement.
Select * from
Employe where name = null;
If = null then
Select * from Employee where name is null;
If is null then

I found null keyword is special and required a "is" word to handle it. :P

Thursday, July 3, 2008

3D user interface

Ever seen a 3D interactive interface before? I believe there are some out there but I've found this one from Nikon website. Click here to check out

Would the future will be moving towards the 3D interface design to enhance the user experience? I'm not sure, but it sure looks cool.