Interesting Work

1. Easy Level Sudoku Solver in Oracle PL/SQL

I wrote it around the time when I got hooked on to Sudoku puzzles. I was so interested in coding one that I chose pl/sql without giving it a second thought.

Time to decide Data Structure

Figuring out data structures was a difficult part. I was looking at it the same way that I solve Sudoku - Each cell has a value and a list of possible values, so each cell becomes an object in programming with a primitive data type for actual value and a nested table for a list of values.

Lets Make solving and representing Sudoku easy in PL SQL

Before moving forward to algorithm part, I coded few helper functions to easily evaluate cell values. I wrote functions to get block value(3x3), column value(9x1), row(1x9) values in pl sql vararray for a cell (i,j). With the help of this functions, I wrote a function to evaluate possible values for a cell. The logic was simple - if a value is present in its row, column or block then it should not be in the possible value set of (1 to 9). Firstly, I did UNION of all actual values in block, column and row and then did MINUS operation from the set of 1..9.


Now, that I had all the helper functions coded. I devised algorithm with simple rule -

1. For each cell, a checklist of possible values in column, row and block to see if there is any value from the set of possible values which is not possible at any other place.

2. For a cell (2,2), check if any column loner(similarly block and row loner) is there i.e, fetch all possible values from all cells in column 2, union it and minus it from possible value of cell (2,2). I used Oracle's Multiset operations for performing union and minus on nested tables.

Used multiset union distinct for combining all possible values of Column 2 cells other than c(2,2) and then multiset except for checking if there is any particular value which doesn't appear in possible values of any of Column cell's.

For generating Sudoku grids, I used a string with cell values separated by zero's as input.

Check Code

2. Conversion/Interface Program without using cursor or for loops

I have seen a lot of codes written using lots of FOR loops and Cursors. It's one way of doing things in pl sql. The other way is to take advantage of implicit cursor FOR loops in SQL.

I remember reading it on "Ask Tom", he advised using less pl/sql and more sql. After that,  I became interested in writing more [declarative] sql and less [procedural] pl sql for solving problems.
I wrote the whole conversion and interface program using mostly SQL statement's.

How would you solve it?
Take for example one very common task to check loaded file for all mandatory fields. Usually, it will be coded like a for loop to fetch row values and then appending the appropriate error messages for each field.

But by properly using sql statements, this can be done in a single update statement. Cool hh.. Check out the complete blog on how I did it.
Read More

3. Performance Tuning of Oracle Reports and Programs

I was assigned on a 3 months project for performance tuning of Oracle Application. At first I was kind of worried, as I didn't have any deep knowledge about performance tuning. But since there is no say in what you get to work on, I took up performance tuning as a challenge. What I was scared of the most was that -  It requires a lot of patience( for obvious reasons). After tuning my first program, I was pretty confident and quite amazed at how well I improved the report's performance.

From my experience during this time I wrote a post about all that I  learned about performance tuning of Oracle Reports. Do read all the statements in the bold. They really sound wise and interesting now :). Like this one regarding tips - "Most of them will work some of the time, and some of them will work most of the time"

Read More
4. Advanced BI / XML Publisher Report - Fixed number of records per page

The first complex XMLP report that I worked on is this one. Someone had already developed a report, but it was giving a lot of errors, so I had to fix it, instead I chose to rewrite the code from scratch. I learned a lot of new things working on this particular report.

This is the blog that got the most number of views throughout, until, I moved my blog from WordPress to Blogspot. Some attachments were lost and all code rendering was ruined. I should have saved files somewhere else. Anyways, I can still recall spending number of hours debugging report layout and scribbling pages developing logic for it.

Read more

5. Unix Script to dump more than 2 GB data using SQL Plus

This one was actually a workaround. I had written a shell script which should dump data of sal query in a CSV file. It is pretty easy, but the problem came when after each run the query was writing only the 2GB of data. Weird!

How I used Named Pipes and Compress utility to do the Magic?
After doing some research, I realized the problem and modified script by using Named Pipe's and Unix Compress utility. This was the first time I used Named Pipes. It's a really cool thing. It is one of my favorite piece of code.

Check Code

6. Extract Movie Title from Torrent file name
This is one of the spare-time code that I wrote when I am not working on any interesting things at work.

With so many movies sitting on my harddisk, I thought I will categorize it with IMDB rating. I was doing a lot of web scraping using Python at that time.

Lets Organize GB's of unseen movies
What I wanted to achieve was - to extract movie name from a file name. Torrent files usually have movie name as part of the file name, but then it has all other information related to a user who uploaded it, the format of the file, year of release and so. Once I had movie name, I was planning to use another program that I wrote, which fetches rating from IMDB.

Say, a file name is like, the program will extract movie title as countdown to zero.

 I used regular expressions to extract a pattern that I identified by observing some file names. In developing this, I learned that regular expressions always find the longest match and how to use lazy parsing.

7.  A Single Page Website in Bootstrap

Time to Move on to Something New - HTML, CSS - Bootstrap Framework
I have written a lot of code and scripts which run in backend on server which solves some or other problem, but the joy of seeing your code on the web which you can boast about is something different.

I started learning Bootstrap framework for developing a one-page personal website. The website is available online and it looks pretty clean and beautiful.

I have gone through a lot of modern websites which are responsive, interactive with beautiful images and fonts. I have learned some of the tricks that they use and am happy with the output.

8. Chrome Extension - Simplifying Amazon Tax Invoicing Workflow

To simplify tax invoice generation in Amazon, I developed a chrome extension which uses data from current page to generate HTML Tax invoice.

It saves a lot of pain for small vendors who have to otherwise manually prepare an invoice for all orders.

If you enjoy humor, check here for some original geeky humor

No comments:

Post a Comment