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).

Some Interesting Oracle Analytic Functions

I had to write some reports for a personal application of mine last night and needed to expand beyond my usual set of analytic functions that I normally use. I thought it would be a good idea to do a quick blog post on these analytic functions.

If you've never heard of or used Oracle analytic functions please read this article first. It's by far the best article I've read at explaining what analytic functions are and how to use them. The Oracle analytic function documentation can be found here.

RATIO_TO_REPORT

RATIO_TO_REPORT compares the current value against the sum of the other set of values. The following example shows the percentage of each employees salary when compared to the sum of their department's salary. You'll notice that you can calculate the same value using a different method (sal_dept_ratio2) which may be why I haven't seen RATIO_TO_REPORT used that often.

SELECT d.dname, e.ename, e.sal, 
  round(ratio_to_report(sal) OVER (PARTITION BY e.deptno),2) sal_dept_ratio, 
  round(sal / sum(e.sal) OVER (PARTITION BY e.deptno),2) sal_dept_ratio2
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME             SAL SAL_DEPT_RATIO SAL_DEPT_RATIO2
-------------- ---------- ---------- -------------- ---------------
ACCOUNTING     CLARK            2450            .28             .28
ACCOUNTING     MILLER           1300            .15             .15
ACCOUNTING     KING             5000            .57             .57
RESEARCH       FORD             3000            .28             .28
RESEARCH       SCOTT            3000            .28             .28
RESEARCH       JONES            2975            .27             .27
RESEARCH       SMITH             800            .07             .07
RESEARCH       ADAMS            1100             .1              .1
SALES          WARD             1250            .13             .13
SALES          MARTIN           1250            .13             .13
SALES          TURNER           1500            .16             .16
SALES          JAMES             950             .1              .1
SALES          ALLEN            1600            .17             .17
SALES          BLAKE            2850             .3              .3

NTH_VALUE

NTH_VALUE returns the nth row in the window clause. It's extremely important that you explicitly define the window or your values may not make sense. I'll write another post explaining this later. Update: NTH_VALUE Windowing Clause

The following example shows the 2nd highest salary in each department.

SELECT d.dname, e.ename, e.sal, 
   nth_value(e.sal, 2) OVER (
    PARTITION BY e.deptno ORDER BY e.sal DESC 
    -- Don't forget to define the window
    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

LISTAGG

LISTAGG was a highly requested feature that was implemented in 11gR2. Overall LISTAGG allows you to group values in multiple rows and put them in a comma delimited column on one row.

The following example (which is not an analytic function) shows all the employees for each department:

SELECT d.dname,
  listagg(e.ename, ',') WITHIN GROUP (ORDER BY e.ename) dept_emp_list
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;

-- Result

DNAME          DEPT_EMP_LIST
-------------- ----------------------------------------

ACCOUNTING     CLARK,KING,MILLER
RESEARCH       ADAMS,FORD,JONES,SCOTT,SMITH
SALES          ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Using the LISTAGG as an analytic function you can see each employee's colleagues in their department, including themselves.

SELECT d.dname, e.ename,
  listagg(e.ename, ',') WITHIN GROUP (ORDER BY e.ename) 
    OVER (PARTITION BY e.deptno) dept_colleagues
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME      DEPT_COLLEAGUES
-------------- ---------- --------------------------------------

ACCOUNTING     CLARK      CLARK,KING,MILLER
ACCOUNTING     KING       CLARK,KING,MILLER
ACCOUNTING     MILLER     CLARK,KING,MILLER
RESEARCH       ADAMS      ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       FORD       ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       JONES      ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       SCOTT      ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       SMITH      ADAMS,FORD,JONES,SCOTT,SMITH
SALES          ALLEN      ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          BLAKE      ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          JAMES      ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          MARTIN     ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          TURNER     ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          WARD       ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

NTILE

NTILE allows you to divide your values into buckets and see which bucket the current row belongs to. For educational institutions this is a very good function to let students know that they're in the top x% of the class.

Update (12-Apr-2014): NTILE vs WIDTH_BOX describes the different ways to bucket data in Oracle. It also explains why SAL 1250 exists in both buckets 2 and 3.

The following example shows the 3 tiers (or buckets) of salaries across the entire company. It allows you to easily see who's in the top 33% of salaries.

SELECT d.dname, e.ename, e.sal,
  ntile (3) over (order by sal desc) three_tier_sal
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

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

ClariFit From To Date Picker 1.0.1 Update

I've updated the ClariFit From To Date Picker APEX Plugin. There was a small bug in the initial version that caused rendering issues in IE.

The initial post about the plugin can be found here and the demo here.

Thanks to Marc B and Joni V for finding the issue.

Command Line Backups for APEX Applications

Update 21-Apr-2015: This script will no longer be supported. Instead please use the new script (for Mac/Linux) available on Github. New article: http://www.talkapex.com/2015/04/apex-backup-script.html

One thing developers like to do is keep local backup copies of their development work. Sometimes they'll copy files and suffix them with .bak or they'll store them in a version control repository. With APEX you don't copy a file to back it up. Either you have to copy the application (within the workspace) or export the application and save it locally. For one-off backups this can be ok, but as you develop with more and more applications in larger environments it can be tedious work and will slow down your development time.

Thankfully APEX has a tool, that comes as part of the install zip file, which allows for command line exports. It's one of the lesser known tools that is extremely useful.

Below is a simplified copy of a batch file which will automatically backup all the APEX applications on a given database using the APEX backup tool. I usually recommend that organizations run such a script on their development environments to store hourly backups of their development environments. The example is a very simple example which should be modified to tag the backup with the date/time or zip and store the backups in version control. I've also appended an extra script which lists all the APEX applications, workspaces, and IDs and stores this information as part of the backup in a .html file.

To use the script, create the files listed below in the same directory and make the appropriate modifications in apex_backup.bat

apex_backup.bat
Backups all the applications in the database

REM ***** START MODIFICATIONS ******

REM ***** Parameters ******
REM Oracle information
REM To find SID name: select * from v$instance;
set oracleSID=XE
set oracleSystemPass=oracle
set oraclePort=1526
set oracleHost=localhost
REM Root directory where oracle\apex\*class files are stored
REM These is a sub directory from the extracted APEX install zip file
set apexExportJavaDir=C:\temp\apex_4.1.1_en\apex\utilities
REM File that contains names of applications and workspaces
set apexHtmlFileName=apex_info.html
REM Directory where backups will be stored
set backupLocation=C:\temp\apexbackup

REM ****** PATHS *********
REM Note: You may not need to explicitly define these as they may already be set in OS.
set ORACLE_HOME=C:\oracle\product\11.2.0\client_1
set CLASSPATH=%CLASSPATH%;.\;%ORACLE_HOME%\jdbc\lib\ojdbc5.jar;%apexExportJavaDir%
set PATH=%PATH%;.\;C:\Program Files (x86)\Java\jre6\bin

REM ***** END MODIFICATIONS ******

REM ****** Other *********
set startRunLocation=%CD%

REM ****** Directory Setup ******
REM Create temp bacpkup location
mkdir %backupLocation%

REM ****** APEX BACKUP *******
REM Go to backup location to run backups in
cd %backupLocation%

REM Export all applications
java oracle.apex.APEXExport -db %oracleHost%:%oraclePort%:%oracleSID% -user system -password %oracleSystemPass% -instance

REM Export all Workspaces
java oracle.apex.APEXExport -db %oracleHost%:%oraclePort%:%oracleSID% -user system -password %oracleSystemPass% -expWorkspace

REM Generate listing of Workspaces and Applications
sqlplus system/%oracleSystemPass%@%oracleHost%:%oraclePort%/%oracleSID% @%startRunLocation%\apex_backup_info.sql %apexHtmlFileName%

REM Back to start location
cd %startRunLocation%

apex_backup_info.sql
Stores all the application names, workspaces, and IDs

-- Parameters
-- 1 = File Name

-- http://forums.devshed.com/oracle-development-96/sql-plus-column-width-format-185085.html
-- http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1012748
SET NUMFORMAT 9999999999999999999999999999999999999
SET MARKUP HTML ON SPOOL ON HEAD ""

SET ECHO OFF
SPOOL &1

-- Current Date
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') current_date
from dual;

-- Applications
SELECT application_id, workspace, application_name, owner
FROM apex_applications
WHERE workspace != 'INTERNAL'
ORDER BY application_id;

-- Workspaces
SELECT workspace_id, workspace
FROM apex_workspaces
WHERE workspace != 'INTERNAL'
ORDER BY workspace_id;

SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON  

EXIT;