Dear Patrick, I have got a website with a search form. I want to display a limited number of results to the user and have him/her navigate through different pages. Is this possible using plain SQL? Kindest regards, Mitchell Ian
Read moreVirtual Private Database…
Some time ago I was asked to assist in fixing or at least finding the cause of a performance problem. The application ran fine until the Virtual Private Database (VPD) policy was applied. Oracle claims there should be near zero impact on your application when you implement VPD, then how is this possible?
Read moreDot Qualify Everything?
There is talk about Dot Qualifying Everything in your PL/SQL Code. But what are the pros and cons in this approach?
Read moreUse the database to do the heavy lifting
Suppose you have data in your PL/SQL program stored in a collection. I am using the EMP table (14 records) but you should imagine you have many, many more records in your collection. If you want to order (sort) your collection in a different manner somewhere in your code you can of course write your […]
Read moreHow an Oracle error can send you the wrong way…
At my current assignment I needed to create an update statement to copy data from one table to another. Quite a simple task, I would say, but an error or actually a constraint violation sent me the wrong way in finding my solution.
Read moreUpdate multiple columns
This is something I knew somewhere in the back of my head, but had forgotten about until now. When you want to update multiple columns in a single SQL statement based on a sub query you can of course duplicate this query for every column you want to update. But this violates the SPOD (Single […]
Read moreAutonomous transaction to the rescue
Today, at my current project, I came across an issue where autonomous transactions came in handy. The situation: I need to create a query to perform an export. A couple of the fields to be selected come from a global temporary table, nothing fancy so far except this global temporary table is filled by a […]
Read moreWhat’s this ‘WHERE 1=1’?
Since some time I have been adding WHERE 1=1 to all my queries. I get queries like this: SELECT * FROM emp e WHERE 1=1 AND e.ename LIKE ‘A%’ AND e.deptno = 20 Lots of people ask me what’s the use of this WHERE 1=1.
Read moreNumeric sorting an alphanumeric column
The other day a customer came up to me and said: I have this column that holds numeric data, usually. But when I sort it it gets all messed up, because is sorts it alphanumerically. That is 10 is listed before 2 etc. My first suggestion was: well, sort by TO_NUMBER(column) then. Well, he replied, […]
Read moreIs it unique?
The other day one of my colleagues said something like: ‘Hey, this is strange. Oracle is not working correctly’. My first thought is then: ‘well, you probably didn’t specify you requirements correctly’. What was the problem? A unique index was created on a table to implement a business rule which can be rephrased into ‘One-Manager-Per-Department’. […]
Read more