Faster Decimal to Binary Function

There was recently a post on the Oracle forums by a person who needed to convert decimal values to a binary string. They had found the standard library PL/SQL function that you often see on Oracle sites, but its slow performance was giving him headaches. It was an interesting thread as many people offered solutions, and it showed the ingenuity of people, in coming up with alternatives. In this article I’ll show some of my efforts, and my final function which, to my pleasure, was the fastest in the thread.
Read more…

Inheriting Values in SQL

Imagine a situation where you have hierarchical data with various values and you wanted to inherit values from ancestors if a given level in the hierarchy had null values. So if a level had a null value, it could inherit that missing value from the parent and if the parent didn’t have a value, the grandparent etc. etc. updwards until we reach the root. How would you do it?  Well in this article I’ll explore a few approaches.
Read more…

Parameterised Views

Background

Occasionally I see posts on Oracle sites from users asking how to create a view based on parameter(s).  Now, many would ask why would you’d need this, especially when you can simply select from the view and apply a where clause?  Well, there are a few situations which fall outside of that approach, the commonest it probably that the view contains a function that needs parameters or the view requires some “state” in its operation (state means there is memory of the past).  Anyway, assuming you have such a need let’s look at some of the approaches at our disposal.
Read more…