Sunday, November 9, 2008

Notes/Thoughts from an accident

1. Life is too short. A minute or even a second could decide if you will see the next moment..
2. Live every moment of the life as if it is your last moment in your life. You never know what might come up in the next moment.
3. Nothing is permanent. You might not live to think about what you could have accomplished with your life. Spend at least 2-5 mins a day thinking what the day has meant to you, if you were given a chance to re-live the same day, what would you have done differently, and then try to implement that difference in the next day
4. Working out, looking good, trying to impress people don't have much to do when your moment arrives. It doesn't matter. Nothing matters actually.
5. If in each day, you could do something that would make you feel better as a person, as a human being, do it, you can find excuses to tell others, but there is no excuse from your conscience.
6. Procrastination could really be dangerous. If there is something that needs to be done, do it now or else have a fixed plan to do it on a particular day by a particular time. If its a big project split it into pieces and have a fixed time for completing each piece.
7. Before complaining about a thousand things that happened to you today, stop for a moment and be thankful for the fact that you are alive, you lived to see another day in your life, you got one more day to prove your essence in the world, whether you utilized it in the right way is a different story.
8. Have a fixed set of things that you really want to do in your life. Give them fixed years. do a bucket list for yourself and assign importance to them. For example, By 30, I want to own a BMW etc. Work out an exact plan how you would do it, day dreaming won't work, have an exact plan within the means..
9. When a moment can decide your life, live it up, live every minute part of a moment..

Friday, November 7, 2008

Oracle SQL group by on a portion of a clob field in the database

I work on an application which writes its internal errors in processing to a DB table. The input data that caused the failure due to a parsing error or missing configuration error is put in a clob column in the database. This input data is an XML message and one of the tags identifies each application that sends data to my application. We had one of the architects who requested us to give a split up on the number of errors that have occurred for each application.

This was significant from Oracle perspective because you cannot do a group by on a clob. In order to do a group by, you need to be able to sort and clob fields do not have a natural sort order. The workaround is to do a to_char function on the clob field but you can get a max of 4000 chars. For me, this suited the requirement because the substring I needed was a small string. But the issue is that I needed to sort a portion of an XML tag inside the clob field and group them based on that. Not only that I needed to find a specific type of error in the XML message and count them.

After a little food for thought, here is the final query that fetched the sweet results


SELECT k.application_name, COUNT (*)
FROM (SELECT TO_CHAR
(SUBSTR (clobfield,
( INSTR (clobfield,
'ns0:appName',
1,
1
)
+ 21
),
( INSTR (clobfield,
'lt;/ns0:appName',
1,
1
)
- INSTR (clobfield,
'ns0:appName',
1,
1
)
- 22
)
)
) AS application_name
FROM tableName
WHERE appID = 1
AND INSTR (clobfield, 'ns0:appName', 1, 1) <> 0
AND INSTR (clobfield, 'ErrorMsg', 1, 1) <> 0
AND INSTR (clobfield, 'Application Not Configured', 1, 1) <> 0) k
GROUP BY k.application_name


The INSTR function selects the index of the occurrence of a string inside another string, while substr gets the substring from a string. The numbers 21 and 22 account for the tag name lengths and the additional characters in the tag name like > etc.

If you need to count based a specific portion of a clob, just modify the above query and you are good to go..