How to enforce email address column to accept only “valid” email values?

The easiest way to solve this problem is to add a check constraint with regular expression validation:

--Let's first add an email column to the emp table:

ALTER TABLE emp ADD email VARCHAR2(100);

Table altered.

--Now let's add constraint using REGEXP_LIKE function:

ALTER TABLE emp ADD CONSTRAINT chk_emp_email 
CHECK (REGEXP_LIKE (EMAIL,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'));

Table altered.

Now let’s see if it works:

UPDATE emp
SET email=ename || '@emp.com'
WHERE ROWNUM=1;

1 row updated.

UPDATE emp
SET email = 'abc@' --invalid email
WHERE ROWNUM=1;
UPDATE emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_EMP_EMAIL) violated

You may want to drop the new column after this exercise:

ALTER TABLE emp DROP COLUMN email;
Table altered.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to create a report showing the number of records in each table of a given schema?

How to create a report showing the number of records in each table of a given schema?
Well, if you have just updated statistics for the entire schema, you can simply query a data dictionary table. We will not consider such case. Let’s assume that statistics is inaccurate, so we need a SQL or a PL/SQL solution.

1) PL/SQL solution
There are many approaches to procedural solution, for ex:
1. Loop over the cursor based on “SELECT table_name FROM user_tables”
2. Count number of rows using “EXECUTE IMMEDIATE” statement.
3. Use dbms_output package to produce the output or populate a collection and select from it to return a cursor to the caller.

2) SQL*Plus script generation
You can write a SQL script that will generate another script that counts number of rows and combines them all together with UNION ALL statement.

Options 1 and 2 have been around for years and are not of any significant interest anymore as Oracle now supports XML with many interesting applications – see the next option for details.

3) Update schema statistics and query user_tables view

SELECT table_name, num_rows 
FROM user_tables

This could be a time consuming process depending on the size of existing tables and number of indexes.

4) Use dbms_xmlgen.getxmltype
dbms_xmlgen package allows to dynamically create and execute numerous SELECT statements and parse the result XML to extract necessary information. Here is the most elegant solution to the problem:

col "Records" for a10

SELECT table_name,                  
       dbms_xmlgen.getxmltype('SELECT COUNT(1) cnt from '|| table_name).extract('/ROWSET/ROW/CNT/text()').getstringval() "Records"
FROM user_tables;

TABLE_NAME                     Records
------------------------------ ---------
DEPT                           4
EMP                            14
BONUS                          0
SALGRADE                       5

Special Note: Sometimes, if your schema has huge tables, this approach may fail if the database does not have enough memory resources available or allocated to process the query. In this case, we may suggest using one of the approaches mentioned above (1-3).

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Combine the power of COALESCE, GREATEST, and NULLIF functions

CASE function is extremely powerful though bulky. It looks and feels like a PL/SQL element even though it is just another SQL function. In some cases, we have an opportunity to use a different, more concise expression avoiding CASE function.

Let’s consider a problem: return a list of all employee names with respective salary and commission columns. If commission is NULL or 0, replace it with 10% of the salary.

A typical solution (with CASE) would look like this:

SELECT ename, sal, CASE WHEN NVL(comm,0)=0 THEN 0.1*sal ELSE comm END AS comm
FROM emp
ORDER BY 1;

Result:

ENAME             SAL       COMM
---------- ---------- ----------
ADAMS            1100        110
ALLEN            1600        300
BLAKE            2850        285
CLARK            2450        245
FORD             3000        300
JAMES             950         95
JONES            2975      297.5
KING             5000        500
MARTIN           1250       1400
MILLER           1300        130
SCOTT            3000        300
SMITH             800         80
TURNER           1500        150
WARD             1250        500

Before presenting a workaround, let’s review the raw data:

SELECT ename, sal, comm
FROM emp
ORDER BY 1;

Result:

ENAME             SAL       COMM
---------- ---------- ----------
ADAMS            1100
ALLEN            1600        300
BLAKE            2850
CLARK            2450
FORD             3000
JAMES             950
JONES            2975
KING             5000
MARTIN           1250       1400
MILLER           1300
SCOTT            3000
SMITH             800
TURNER           1500          0
WARD             1250        500

Essentially, we want to substitute the comm value for all employees except ALLEN, MARTIN, and WARD.

If we did not have to deal with $0 commission (TURNER), we could have used NVL(comm, 0.1*sal) expression, or COALESCE(comm, 0.1*sal) which works identically to NVL function for 2 parameters.

So if we could turn 0 into NULL, we would be able to employ NVL/COALESCE instead of CASE function.

Here comes the turn of NULLIF function. It can do exactly what we need: substitute 0 (or any other value) with NULL. It can be done by the following expression:

NULLIF(comm,0) -- which means: when comm=0 then return NULL.

There is one issue that needs to be resolved before we can use the COALSCE function. We cannot make 2 different expression returing NULL is 2 cases, when the argument is 0 or NULL. However, we can employ GREATEST (or LEAST) function to wrap up multiple arguments that may evaluate to NULL and return just one value – it will be NULL if any of the arguments of GREATEST evaluate to NULL.

So, finally, our workaround will look as follows:

SELECT ename, sal, COALESCE(GREATEST(comm, NULLIF(comm,0)), 0.1*sal) AS comm
FROM emp
ORDER BY 1;

Result:

ENAME             SAL       COMM
---------- ---------- ----------
ADAMS            1100        110
ALLEN            1600        300
BLAKE            2850        285
CLARK            2450        245
FORD             3000        300
JAMES             950         95
JONES            2975      297.5
KING             5000        500
MARTIN           1250       1400
MILLER           1300        130
SCOTT            3000        300
SMITH             800         80
TURNER           1500        150   <-- 0 is replaced with 150 (10%)
WARD             1250        500

COALESCE function comes really handy (combined with NULLIF & GREATEST/LEAST) when we have multiple values of a column that we would like to treat as 0.
For example, if we wanted to treat $0, $300, and $500 as NULLs we could have used the following expression:

COALESCE(GREATEST(comm, NULLIF(comm,0), NULLIF(comm,300), NULLIF(comm,500)), 0.1*sal)

The trick is hidden in the fact that GREATEST returns NULL if one of the parameters is a NULL.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

SQL puzzle: Find unique specialists in every department

Puzzle of the day.
This is a fairly simple problem but from time to time I am being approached by developers who need help with very similar problems.

Find all employees who has a unique job title in their respective department.

Solution #1: Using NOT EXISTS

SELECT ename, deptno, job, sal
FROM emp a
WHERE NOT EXISTS(SELECT 1
                 FROM emp b
                 WHERE a.deptno=b.deptno
                   AND a.job=b.job
                   AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #1.1 – Generic substitution for NOT EXISTS

SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT COUNT(b.empno)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job
           AND a.empno!=b.empno)
ORDER BY deptno, job

Solution #2: Using NOT IN

SELECT ename, deptno, job, sal
FROM emp a
WHERE job NOT IN(SELECT job
                 FROM emp b
                 WHERE a.deptno=b.deptno
                   AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #2.1: Using NOT IN with Multi-column subquery – it is pretty much the same as Solution #2.

SELECT ename, deptno, job, sal
FROM emp a
WHERE (job, deptno) NOT IN(SELECT job, deptno
                           FROM emp b
                           WHERE a.empno!=b.empno)
ORDER BY deptno, job

Solution #3.1: Using COUNT in subquery (very similar to Solution #1.1 but has different execution plan)

SELECT ename, deptno, job, sal
FROM emp a
WHERE 1=(SELECT COUNT(b.empno)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #3.2: A mixed version of Solutions #1.1 and #3.1:

SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT SUM(CASE WHEN a.empno=b.empno THEN 0 ELSE 1 END)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job)
ORDER BY deptno, job

Solution #4: Using Analytical function COUNT

WITH x AS (
SELECT ename, deptno, job, sal, COUNT(*) OVER(PARTITION BY deptno, job) cnt
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE cnt=1
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #4.1 – using MIN/MAX analytical functions – essentially, it is the same as solution #4

WITH x AS (
SELECT ename, deptno, job, sal, 
       MAX(empno) OVER(PARTITION BY deptno, job) max_no,
       MIN(empno) OVER(PARTITION BY deptno, job) min_no
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE max_no=min_no
ORDER BY deptno, job

Solution #5: Using In-Line view (WITH)

WITH x AS (
SELECT deptno, job
FROM emp
GROUP BY deptno, job
HAVING COUNT(*)=1
)
SELECT ename, deptno, job, sal
FROM emp JOIN x USING (deptno, job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

A tricky Oracle DDL question from a real job interview

Recently I was asked the following question during the phone interview with one of the large companies:

Consider the following table:
[CREATE] TABLE table1
(
column1 NUMBER PRIMARY KEY,
column2 NUMBER,
column3 VARCHAR2(50),
column4 NUMBER
)

Change the data type of column3 from VARCHAR2(50) to CLOB while keeping the order of columns intact.

The anticipated answer was:

-- Step 1: Create a backup table with PK and last 2 columns
CREATE TABLE table1_bak AS
SELECT column1, column3, column4
FROM table1;

--Step 2: Drop last 2 columns
ALTER TABLE table1 DROP (column3, column4);

--Step 3: Add last 2 columns back:
ALTER TABLE table1 ADD(column3 CLOB, column4 NUMBER);

--Step 4: Restore the data
MERGE INTO table1 a
USING table1_bak b
ON (a.column1=b.column1)
WHEN MATCHED THEN
    UPDATE SET a.column3=b.column3,
               a.column4=b.column4;

--Step 5: Drop backup table
DROP TABLE table1_bak;

This answer was provided. In the spirit of finding workarounds I added another solution:

--Step 1: Add temp column
ALTER TABLE table1 ADD (column5 VARCHAR2(50));

--Step 2: Update column3 with NULLs and column5 with values of column3
UPDATE table1
SET column5=column3,
    column3=NULL;

--Step 3: Change column3 data type to LONG first and CLOB after that:
ALTER TABLE table1 MODIFY (column3 LONG);
ALTER TABLE table1 MODIFY (column3 CLOB);

--Note: trying to change empty column VARCHAR2 to CLOB straight throws the following error:

ALTER TABLE table1 MODIFY (column3 CLOB)
                           *
ERROR at line 1:
ORA-22858: invalid alteration of datatype

--Step 4: Restore the content of column3 from column5:
UPDATE table1
SET column3=column5;

--Step 5: Drop the temp column:
ALTER TABLE table1 DROP COLUMN column5;

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

A tricky alternative to using the TYPE … IS RECORD statement in PL/SQL, by Zahar Hilkevich

This technique is handy when we get a refcursor from a stored procedure, so we cannot use cursor’s FOR LOOP.

We need to define a variable to be used for fetching the cursor.

TYPE dept_info_rt IS RECORD
   (
      dept_name      VARCHAR2(100),
      dept_number    NUMBER,
      emp_count      NUMBER,
      manager_name   VARCHAR2(100),
      budget         NUMBER,
      location       VARCHAR2(100)
   );

   v_rec dept_info_rt;

If we had a view (vw_dept) with all these fields, we could define the record variable using the %ROWTYPE attribute:

   v_rec vw_dept%ROWTYPE;

As a good alternative to a view in this case, we can use a custom cursor:

  CURSOR c IS
  SELECT dname as dept_name, deptno AS dept_number, 1 AS emp_count, ename AS manager_name,
         sal as budget, loc AS location
  FROM emp JOIN dept USING (deptno)
  WHERE 1=2;

  v_rec c%ROWTYPE;

As we can see, the cursor perfectly substitutes the view for our fetching needs.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

SQL Puzzle: Find top management in each department – with multiple workaround solutions, by Zahar Hilkevich

I was recently asked by a colleague at work to help him solving a problem that can be interpreted in scott’s emp table terms as the following:

In each department, list all managers and a president, but in the department where the president works, all managers (if any) should be filtered out.

If we look at all managers/president(s), we will see the following result:

ENAME      JOB           DEPTNO
---------- --------- ----------
JONES      MANAGER           20
BLAKE      MANAGER           30
CLARK      MANAGER           10
KING       PRESIDENT         10

Our desired result should exclude CLARK.

Solution/Workaround #1:

SELECT ename, job, deptno
FROM emp a
WHERE job='PRESIDENT'
  OR (job='MANAGER' AND NOT EXISTS(SELECT 1
                                   FROM emp b
                                   WHERE b.deptno=a.deptno
                                     AND job='PRESIDENT')
     )
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30

This is the most straight-forward solution and it barely requires any explanation.

Workaround #2:

WITH x AS (
SELECT ename, job, deptno, 
       RANK()OVER(PARTITION BY deptno 
                  ORDER BY DECODE(job,'PRESIDENT',1,'MANAGER',2)) rk
FROM emp a
WHERE job IN ('PRESIDENT', 'MANAGER')
)
SELECT ename, job, deptno
FROM x
WHERE rk=1
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30

Analytical functions make the solution very simple. Here, we use custom sorting (in the ORDER BY) with the RANK function.

Workaround #3:

WITH x AS (
SELECT ename, job, deptno,
       COUNT(DISTINCT job)OVER(PARTITION BY deptno) cnt
FROM emp a
WHERE job IN ('MANAGER', 'PRESIDENT')
)
SELECT ename, job, deptno
FROM x
WHERE job='PRESIDENT' OR cnt=1
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30

This solution shows a use of COUNT(DISTINCT …)OVER() analytical function.

Workaround #4:

SELECT ename, job, deptno 
FROM emp 
WHERE (deptno, DECODE(job,'PRESIDENT',1,'MANAGER',2)) IN 
  (SELECT deptno, MIN(DECODE(job,'PRESIDENT',1,'MANAGER',2))
   FROM emp
   GROUP BY deptno)
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30

Another example of the use of custom order hidden in the multi-column subquery.

There are at least 3-5 other workarounds available for this puzzle.

You will have no problems uncovering them after reading my book “Oracle SQL Tricks and Workarounds”.

Use EZConnect Syntax for Oracle connection string when you cannot change the TNS Names file.

In some companies, there are security regulations that disallow users to change their local TNS Names file, so if there is a need to connect to a host that is not already defined in the TNS Names file, a different approach has to be employed.

There are a couple of ways to work around the above mentioned limitation:

Note: To get to a SQL*Plus command prompt without connecting use “/nolog” attribute with sqlplus:

C:\>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 20 14:22:25 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL>

1) TNS-less connection string:

SQL> conn scott/tiger@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.180)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
Connected.

2) EZConnect connection string:

SQL> conn scott/tiger@192.168.1.180:1521/ORCL
Connected.

See http://www.orafaq.com/wiki/EZCONNECT for more information on the syntax and prerequisites.

I was able to use both these approaches with SQL*Plus and SQL Loader.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Oracle’s Equivalent for Networkdays Function in Excel, by Zahar Hilkevich

I was recently approached by a colleague at work with a question if I know an Oracle’s Equivalent for Networkdays Function in Excel. I did not have an immediate answer, but a few minutes later I was able to find a way.

First, let’s define what the Networkdays function does. It should give the number of days between two given dates excluding Sundays and Saturdays.

If we had a calendar table (with a work_day column) the problem would be solved fairly easily:

SELECT COUNT(1)
FROM calendar_table
WHERE TO_CHAR(work_day, 'D') BETWEEN 2 AND 6

To ensure that this code works for different geographic regions, it would be safer to add nls_date_language attribute as follows:

SELECT COUNT(1)
FROM calendar_table
WHERE TO_CHAR(work_day, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6

In absence of the calendar_table, we can mimic it using a standard method for a numeric/date range generator. Let say, we need to generate a range of dates between 15-May-2015 and 20-Aug-2015:

SELECT DATE'2015-05-15'+LEVEL-1 AS day
FROM dual
CONNECT BY DATE'2015-05-15'+LEVEL-1 <= DATE'2015-08-20';

Result (partial):
DAY
---------
15-MAY-15
16-MAY-15
17-MAY-15
18-MAY-15
...
17-AUG-15
18-AUG-15
19-AUG-15
20-AUG-15

So after excluding the weekend days, the counting task becomes trivial:

SELECT COUNT(1)
FROM dual
WHERE TO_CHAR(DATE'2015-05-15'+LEVEL-1, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6
CONNECT BY DATE'2015-05-15'+LEVEL-1 <= DATE'2015-08-20';

Result:
COUNT(1)
--------
      70

Finally, we are ready to package it all together and create a function:

CREATE OR REPLACE FUNCTION NETWORKDAYS(p_date1 DATE, p_date2 DATE) RETURN INTEGER
AS
   v_result INTEGER;
BEGIN
  SELECT COUNT(1) INTO v_result
  FROM dual
  WHERE TO_CHAR(p_date1+LEVEL-1, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6
  CONNECT BY TRUNC(p_date1)+LEVEL-1 <= TRUNC(p_date2);

  RETURN v_result;
END;
/

This is a quite elegant solution, though I had a feeling that it is a bit over-complicated. A few minutes later, I derived a non-SQL approach:

CREATE OR REPLACE FUNCTION NETWORKDAYS(p_date1 DATE, p_date2 DATE) RETURN INTEGER
AS
   v_date1 DATE:=CASE TO_CHAR(p_date1, 'D', 'nls_date_language=AMERICAN') 
		      WHEN 1 THEN TRUNC(p_date1)+1
		      WHEN 6 THEN TRUNC(p_date1)+2
		      ELSE TRUNC(p_date1)
		 END;
   v_date2 DATE:=CASE TO_CHAR(p_date2, 'D', 'nls_date_language=AMERICAN') 
		      WHEN 1 THEN TRUNC(p_date2)+1
		      WHEN 6 THEN TRUNC(p_date2)+2
		      ELSE TRUNC(p_date2)
		 END;
BEGIN
  IF p_date1>p_date2 THEN
	RETURN 0;
  ELSIF p_date1=p_date2 THEN
	RETURN 1;
  ELSE
	RETURN (TRUNC(v_date2, 'D') - TRUNC(v_date1, 'D')) * 5/7 +
 	       TO_CHAR(v_date2, 'D', 'nls_date_language=AMERICAN') -
	       TO_CHAR(v_date1, 'D', 'nls_date_language=AMERICAN');
  END IF;
END;
/

A few comments for better understanding the above code:
1) If one(or both) function parameter values is a week-end day, we change it to the following Monday as it does not change the number of non-weekend days in the given range.
2) TRUNC function with ‘D’ argument returns the first day of the week, so we can expect the number of days between two Sundays (or whatever the first day is) to be a multiple of 7, and so 5/7 gives us the number of non-weekend days.
3) TO_CHAR(v_date2, ‘D’, ‘nls_date_language=AMERICAN’) – TO_CHAR(v_date1, ‘D’, ‘nls_date_language=AMERICAN’) – is an adjustment to the result based on the day of the week of each of the date range parameters.

Quick test for the new function – Find number of work days before the end of the year:

SELECT  SYSDATE+LEVEL-1 AS day,
        TO_CHAR(sysdate+level-1, 'DY') AS day_of_week,
        networkdays(sysdate+level-1, date'2015-12-31') netdays
FROM dual
CONNECT BY LEVEL<=15

Result:
DAY       DAY_OF_WEEK     NETDAYS
--------- ------------ ----------
10-OCT-15 SAT                  58
11-OCT-15 SUN                  58
12-OCT-15 MON                  58
13-OCT-15 TUE                  57
14-OCT-15 WED                  56
15-OCT-15 THU                  55
16-OCT-15 FRI                  54
17-OCT-15 SAT                  53
18-OCT-15 SUN                  53
19-OCT-15 MON                  53
20-OCT-15 TUE                  52
21-OCT-15 WED                  51
22-OCT-15 THU                  50
23-OCT-15 FRI                  49
24-OCT-15 SAT                  48

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.