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 moreOracle PL/SQL Programming 6th edition by Steven Feuerstein
I have reviewed the latest edition of Oracle PL/SQL Programming. This is not a book, at least for me, that I would read front to back. I use it mostly as a reference, but the great thing about this book is that you can also use this book to learn programming PL/SQL from scratch.
Read moreTypical
After a presentation by Lucas Jellema I decided to try something with types in Oracle. One of the issues posed in this presentation was that the type cannot include self-reference. Neither direct nor indirect.
Read moreKScope14
This year, from June 22 – June 26, the best convention in the world, ODTUG‘s KScope14 will be held in Seattle, Washington. I am already looking forward to meeting some ‘old’ friends again, creating new friends and seeing some of the best content by presenters from all over the world. Some of the presentations I […]
Read moreAPEX 5.0 Early Adopter
The long awaited Early Adopter installation is finally available at http://apexea.oracle.com. You can request a workspace here to play around with the new version. I have just been playing around with it for about an hour an from what I’ve seen it’s a big change to what we’re used to since APEX 4. Not only […]
Read more