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 moreSome things to share…
I have been awfully quiet on my blog lately. I think that is because I have been busy with other things, like my garden and stuff like that. There are some ‘techie’ thing I have done in the meantime, though, I have submitted a couple of abstracts for Tech14. Hope at least one of them […]
Read morePL/SQL vs SQL
There is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only […]
Read more