ODTUG Kscope 12: Things To Look Out For

With Kscope 12 around the corner (literally) I thought I'd post somethings that I'm really looking forward to at the conference.

Cary Millsap's Keynote at the General Session: I've heard a lot of great things about Cary's talk. Everyone that has seen it has only had rave reviews about it so I'm really excited to see it myself. Besides Cary's talk there will be a lot of other funny and exciting things from ODTUG at the general session.

Red Gate's booth: Red Gate will be doing something I've never seen before at a conference. They'll be having a Rapid Development Lab to develop a new Database Version Control system. I strongly encourage you to check it out and bring some ideas on how to manage changes coming in from multiple developers and how a tool could help with it.

Open Mic Night: Immediately following the general session on Monday there will be the annual APEX Open mic night where anyone can demo anything with APEX. Only rule is no powerpoints and a fixed time limit. I'm always amazed at all the neat things that people are doing in their organization.

Lots of APEX presentations: Obviously I'm biased on this topic, but I think there will be a lot of great presentations on the newly announced 4.2 EA 1 and mobile applications. I'm giving two talks this year that I'm really excited about: 

• APEX 4 + HTML 5 = Awesome II: Mon  11:30 @ Cibolo Canyon 5 - This talk is a sequel to last year's award winning talk on HTML 5. I have some very cool new features to showcase.
• Building a Better Team: Mon  11:30 @ Cibolo Canyon 5 - This talk will cover how to help improve your development team. Though it's not APEX specific it'll cover some very key points.
• In both of my presentations I'll be giving copies of my APEX books away!

I look forward to seeing everyone over the next week and seeing a lot of APEX presentations.

SELECT INTO Techinques

One thing that bothers a lot of PL/SQL developers is when they have to select some data from a table into local variables. The problem is that the data may or may not exist (i.e. return 0..1 rows). They're several ways to write this code, some taking longer than others which is the pain point for developers.

I've listed out various ways to do this below and hopefully it'll help you find some easier ways to solve this issue.

The first example is the classic case of handling the 0..1 rows issue in PL/SQL. They're a few issues (not problems) that I have with this. The first is that it adds some additional lines of code (highlighted) which can make the code harder to follow. The other issue is that instead of using "WHEN no_data_found" some developers tend to use "WHEN OTHERS" which is guaranteed to give you some false positives in the long run.

DECLARE
  l_sal emp.sal%TYPE;
  p_empno emp.empno%TYPE := 123; -- Invalid empno number
BEGIN

  BEGIN
    SELECT sal
    INTO l_sal
    FROM emp
    WHERE empno = p_empno;
  EXCEPTION
    WHEN no_data_found THEN
      l_sal := 0;
  END;
  
END;
/

Another way to get the value from the table is to use the aggregate function MAX. Since you're expecting 0..1 rows this will always return 1 row and eliminates the need to have the "BEGIN ..." block of code which saves 5 lines of code. You can handle what to do when no value is found by using the NVL function.

DECLARE
  l_sal emp.sal%TYPE;
  p_empno emp.empno%TYPE := 123; -- Invalid empno number
BEGIN

  SELECT NVL(MAX(sal), 0)
  INTO l_sal
  FROM emp
  WHERE empno = p_empno;
  
END;
/

The only issue with the above example is when you can have 0..n rows but should only get 0..1. Ideally you should have table constraints in order to guarantee 0..1 rows but it's not always the case in some system. The following example shows how you can use the same technique as above but it will trigger an exception if n rows are found (which is what you want to do):

DECLARE
  l_sal emp.sal%TYPE;
  p_empno emp.empno%TYPE := 123; -- Invalid empno number
BEGIN

  SELECT NVL(MAX(sal), 0)
  INTO l_sal
  FROM emp
  WHERE empno = p_empno
  HAVING count(1) <= 1;
  
END;
/

NTH_VALUE Windowing Clause

In my previous post, which highlighted some analytic functions, I mentioned that the windowing clause must be explicitly defined when using the NTH_VALUE function.

To recap, here's the example I used for NTH_VALUE which lists the 2nd highest salary for each department:

SELECT d.dname, e.ename, e.sal, 
   nth_value(e.sal, 2) OVER (
    PARTITION BY e.deptno ORDER BY e.sal DESC
    -- windowing_clause
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sec_high_sal_dept
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME             SAL SEC_HIGH_SAL_DEPT
-------------- ---------- ---------- -----------------
ACCOUNTING     KING             5000              2450
ACCOUNTING     CLARK            2450              2450
ACCOUNTING     MILLER           1300              2450
RESEARCH       FORD             3000              3000
RESEARCH       SCOTT            3000              3000
RESEARCH       JONES            2975              3000
RESEARCH       ADAMS            1100              3000
RESEARCH       SMITH             800              3000
SALES          BLAKE            2850              1600
SALES          ALLEN            1600              1600
SALES          TURNER           1500              1600
SALES          WARD             1250              1600
SALES          MARTIN           1250              1600
SALES          JAMES             950              1600

What happens if we don't include the windowing clause? Here's the same query, but just focusing on the Accounting department, without the windowing clause:

SELECT d.dname, e.ename, e.sal, 
   nth_value(e.sal, 2) OVER (
    PARTITION BY e.deptno ORDER BY e.sal DESC) sec_high_sal_dept
FROM emp e, dept d
WHERE e.deptno = d.deptno
  AND d.dname = 'ACCOUNTING';

-- Result

DNAME          ENAME             SAL SEC_HIGH_SAL_DEPT
-------------- ---------- ---------- -----------------
ACCOUNTING     KING             5000
ACCOUNTING     CLARK            2450              2450
ACCOUNTING     MILLER           1300              2450

You'll notice that the first row (KING) has a NULL returned for the SEC_HIGH_SAL_DEPT column. That's because when it looks at the first row (KING) it still hasn't had a chance to evaluate at least 2 values. Obviously writing some test queries will identify this "issue" which may or may not be what you're looking for. If it isn't then just add the windowing clause (above).