Autonomous transaction to the rescue



By Patch ~ August 25th, 2015. Filed under: Oracle, PL/SQL, SQL.

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 (rather complex) procedure. Another problem is this table is emptied for every row, i.e. it will contain only one row at a time. ‘Just build a wrapper table function for this procedure and have that function call the procedure’ was my first idea.

Continue reading »

What’s this ‘WHERE 1=1′?



By Patch ~ March 22nd, 2015. Filed under: Oracle, Plugins, SQL.

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.

Continue reading »

Numeric sorting an alphanumeric column



By Patch ~ March 8th, 2015. Filed under: Oracle, SQL.

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, that can’t be done. Sometimes the column contains alphanumeric data.

Continue reading »

Is it unique?



By Patch ~ October 5th, 2014. Filed under: Oracle, SQL.

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’. This includes multiple columns where any combination is allowed, but only one manager is allowed per department.

Continue reading »

Some things to share…



By Patch ~ May 23rd, 2014. Filed under: APEX, Oracle, PL/SQL, SQL.

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,

Tech14-Ive_SubmittedI have submitted a couple of abstracts for Tech14. Hope at least one of them gets selected. I really like presenting and if it is in a different country, that is just a plus. That way I get to see something of the world while I am still able to do this, considering my illness.

ODTUG_Kscope14On a shorter timeframe, I will be presenting at KScope14 this year. I will be nice to see my friends in the US again. If you are in the neighborhood of Nieuwegein and you want to some of the sessions, but cannot make it to Seattle, Amis-logo-kleinmaybe you can attend the KScope14 preview at the AMIS office on June 12th.

Hope to see you at either one of these events.sftilt80

Oh, and if you have a moment to spare and you know your way around at SQL - PL/SQL - APEX or some other language/technologies, maybe you can sign up at the GivHub and help Steven Feuerstein.