Show Leading Spaces in Character String in SQL*PLus

SQL*Plus with its default settings ignores leading spaces when you attempt to output a character string variable using DBMS_OUTPUT.PUT_LINE procedure:

SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
12345

As you can see in the above example, two leading spaces are trimmed.

To make SQL*Plus showing the leading space characters, we need to change the following setting:

SET SERVEROUTPUT ON FORMAT WRAPPED

Now, the spaces will be preserved:

SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
  12345

Alternatively, you can set the format to TRUNCATED:

SQL> SET SERVEROUTPUT ON FORMAT TRUNCATED
SQL>
SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
  12345

PL/SQL procedure successfully completed.

The default setting is WORD WRAPPED:

SQL> SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
SQL>
SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
12345

PL/SQL procedure successfully completed.

SQL> SHOW SERVEROUTPUT
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

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” for instructions.

Advertisements

Prevent Division by Zero with NULLIF function

If you want to avoid division by 0 issue, use NULLIF function to substitute zero in  denominator with NULL.

For example, the following query fails:

SQL> SELECT 100/(SELECT COUNT(*) FROM emp WHERE deptno=40)
 2 FROM dual;
 SELECT 100/(SELECT COUNT(*) FROM emp WHERE deptno=40)
 *
 ERROR at line 1:
 ORA-01476: divisor is equal to zero

To fix it, use NULLIF and the result will be NULL instead of error:

SELECT 100/NULLIF((SELECT COUNT(*) 
                   FROM emp 
                   WHERE deptno=40),0) expr
FROM dual;

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” for instructions.

A few thoughts on parameterized cursors.

In PL/SQL, parameterized cursors offer a great deal of flexibility and clarity of the code.

Let’s illustrate this point with a specific (though artificial) example.

Let say, we need to show all employees who is paid above average in their respective department. In SQL, the solution would be quite simple:

SQL> SELECT deptno, ename, sal
  2  FROM emp e
  3  WHERE sal>(SELECT AVG(sal)
  4             FROM emp
  5             WHERE deptno=e.deptno)
  6  ORDER BY deptno, sal DESC, ename;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 FORD             3000
        20 SCOTT            3000
        20 JONES            2975
        30 BLAKE            2850
        30 ALLEN            1600

We are going to solve the same simple problem in PL/SQL (using anonymous block). The first example will utilize 2 PL/SQL variables instead of cursor parameters:

SET SERVEROUTPUT ON FORMAT WRAPPED

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  v_deptno  NUMBER;
  v_avg_sal NUMBER;
  CURSOR e IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=v_deptno
    AND sal>v_avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    v_deptno:=v1.deptno;
    v_avg_sal:=v1.avg_sal;
    FOR v2 IN e LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

deptno ename       sal
------ -------- ------
    10 KING       5000
    20 FORD       3000
    20 SCOTT      3000
    20 JONES      2975
    30 BLAKE      2850
    30 ALLEN      1600

Note the use of the “SET SERVEROUTPUT ON FORMAT WRAPPED” sqlplus command. You can read more about it here.

Alternatively, we can you a record variable:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  v_dept d%ROWTYPE;
  CURSOR e IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=v_dept.deptno
    AND sal>v_dept.avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    v_dept:=v1;
    FOR v2 IN e LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;

A better way to pass variables to a cursor is to use cursor parameters:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  CURSOR e(c_deptno NUMBER, c_avg_sal NUMBER) IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=c_deptno
    AND sal>c_avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    FOR v2 IN e(v1.deptno, v1.avg_sal) LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

deptno ename       sal
------ -------- ------
    10 KING       5000
    20 FORD       3000
    20 SCOTT      3000
    20 JONES      2975
    30 BLAKE      2850
    30 ALLEN      1600

As you can see, no variable declaration and assignment is needed we; instead, we declare cursor parameters. This gives the code better clarity and readability as both, the cursor parameters and the cursor itself are defined in one place. You don’t need any intermediary variable for opening a nested cursor.

The following example will optimize the last block by using a single cursor parameter:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  CURSOR e(c_dept d%ROWTYPE) IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=c_dept.deptno
    AND sal>c_dept.avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    FOR v2 IN e(v1) LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

So what is the main advantages of using parameterized cursors over using cursors with [bind] variables?

  • Parameterized cursors support default values for cursor parameters
  • The cursors can be referenced more than once with different parameter values
  • A cursor with parameter(s) encapsulates all information necessary for opening and fetching data which makes it safer for use as you don’t need to trace the assignment of cursor parameters/variables all over the place.

 

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” for instructions.

 

Puzzle of the Week #17: Show hiring years

Puzzle of the Week #17:

Write a single SELECT statement that would show the years of hire in each department. The result should have 3 columns (see below): deptno, year1, and year2. If a department only hired during 1 calendar year, this year should be shown in year1 column (see deptno 30) and year2 column should be blank. If a department hired during 2 calendar years, the first year should be should be shown in year1 column, and the 2nd year should be shown in year2 column (see deptno 10). In all other cases, show 1st year in year1 column and “More (N)” where N is the number of years that department did the hiring (see deptno 20).

Expected Result:

DEPTNO Year 1   Year 2
------ -------- --------
    10 1981     1982
    20 1980     More (3)
    30 1981

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

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” for instructions.

Three Solutions to Puzzle of the Week #16

Puzzle of the Week #16:

With a single SELECT statement find the biggest prime factor of a given integer value (N).

Expected Result:

--For N=100:

Biggest Prime Factor
--------------------
                  5

--For N=52:

Biggest Prime Factor
--------------------
                 13

--For N=21:

Biggest Prime Factor
--------------------
                   7

Solutions

#1: Using CTE (recursive WITH)

WITH input AS (
SELECT &N n
FROM dual
), x(num, flag) AS (
SELECT 2, CASE WHEN MOD(n, 2)=0 THEN 1 ELSE 0 END AS flag
FROM input
UNION ALL
SELECT x.num+1, CASE WHEN MOD(i.n, x.num+1)=0 THEN 1 ELSE 0 END
FROM input i, x
WHERE x.num+1<=i.n
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
             FROM dual
             WHERE MOD(x.num,LEVEL)=0
             CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;

Enter value for n: 100
old   2: SELECT &N n
new   2: SELECT 100 n

Biggest Prime Factor
--------------------
                   5

SQL> /
Enter value for n: 52
old   2: SELECT &N n
new   2: SELECT 52 n

Biggest Prime Factor
--------------------
                  13

SQL> /
Enter value for n: 21
old   2: SELECT &N n
new   2: SELECT 21 n

Biggest Prime Factor
--------------------
                   7


#2: Using CONNECT BY clause , version 1

WITH input AS (
SELECT &N n
FROM dual
), x AS (
SELECT LEVEL num
FROM input i
WHERE MOD(i.N, LEVEL)=0
CONNECT BY LEVEL<=i.N
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
             FROM dual
             WHERE MOD(x.num,LEVEL)=0
             CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;

#3: Using CONNECT BY clause, version 2

WITH input AS (
SELECT &N n
FROM dual
), range AS (
SELECT LEVEL num
FROM input i
CONNECT BY LEVEL <= i.N
), x AS(
SELECT r1.num
FROM range r1, range r2, input i
WHERE MOD(i.N, r1.num)=0
GROUP BY r1.num
HAVING COUNT(CASE WHEN MOD(r1.num, r2.num)=0 THEN 1 END)=2
)
SELECT MAX(num) "Biggest Prime Factor"
FROM x;

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” for instructions.

Puzzle of the Week #16: Find the Biggest Prime Factor

Puzzle of the Week #16:

With a single SELECT statement find the biggest prime factor of a given integer value (N).

Expected Result:

--For N=100:

Biggest Prime Factor
--------------------
                  5

--For N=52:

Biggest Prime Factor
--------------------
                 13

--For N=21:

Biggest Prime Factor
--------------------
                   7

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

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” for instructions.

Three Solutions to Puzzle of the Week #15

Puzzle of the Week #15:

Find all the year based intervals from 1975 up to now when the company did not hire employees. Use a single SELECT statement against emp table.

Expected Result:

years
------------
1975 - 1979
1983 - 1986
1988 - 2016

Solutions

#1: Grouping by an expression on ROWNUM (no Analytic functions!)

SQL> col years for a15

SQL> WITH x AS (
  2  SELECT 1975+LEVEL-1 yr
  3  FROM dual
  4  CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
  5  MINUS
  6  SELECT EXTRACT(YEAR FROM hiredate)
  7  FROM emp
  8  )
  9  SELECT MIN(yr) || ' - ' || MAX(yr) "years"
 10  FROM x
 11  GROUP BY yr-ROWNUM
 12  ORDER BY yr-ROWNUM;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

#2: Calculating steps with Analytic function and grouping by a sum of step.

WITH x AS (
SELECT 1975+LEVEL-1 yr
FROM dual
CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
MINUS
SELECT EXTRACT(YEAR FROM hiredate)
FROM emp
), y AS (
SELECT DECODE(yr, LAG(yr,1)OVER(ORDER BY yr)+1, 0, 1) AS step, yr
FROM x
), z AS (
SELECT yr, SUM(step)OVER(ORDER BY yr) grp
FROM y
)
SELECT MIN(yr) || ' - ' || MAX(yr) "years"
FROM z
GROUP BY grp
ORDER BY grp;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

#3: Using Self Outer Join to calculate steps

WITH x AS (
SELECT 1975+LEVEL-1 yr
FROM dual
CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
MINUS
SELECT EXTRACT(YEAR FROM hiredate)
FROM emp
), y AS (
SELECT x1.yr, NVL2(x2.yr, 0, 1) step
FROM x x1 LEFT JOIN x x2 ON x1.yr=x2.yr+1
), z AS (
SELECT yr, SUM(step)OVER(ORDER BY yr) grp
FROM y
)
SELECT MIN(yr) || ' - ' || MAX(yr) "years"
FROM z
GROUP BY grp
ORDER BY grp;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

 

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” for instructions.

Puzzle of the Week #15

Puzzle of the Week #15:

Find all the year based intervals from 1975 up to now when the company did not hire employees. Use a single SELECT statement against emp table.

Expected Result:

years
------------
1975 - 1979
1983 - 1986
1988 - 2016

 

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

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” for instructions.

Three Solutions to Puzzle of the Week #14

Puzzle of the Week #14:

For each department, find its share in the company’s total payroll. The puzzle should be solved with a single SELECT statement that does not utilize sub-queries, WITH clause, in-line views, temporary tables or PL/SQL functions

Expected Result:

DEPTNO   Share, %
------ ----------
    10      30.15
    20      37.47
    30      32.39

Solutions

#1: Using Analytic Functions (SUM)

SELECT DISTINCT deptno, 
                ROUND(100*SUM(sal)OVER(PARTITION BY deptno)/SUM(sal)OVER(),2) AS "Share, %"
FROM emp
ORDER BY 1
/

DEPTNO   Share, %
------ ----------
    10      30.15
    20      37.47
    30      32.39

#2: Using Cartesian Product

SELECT a.deptno, 
       ROUND(100*SUM(a.sal)*COUNT(DISTINCT a.ROWID)/(SUM(b.sal)*COUNT(DISTINCT b.ROWID)), 2) AS "Share, %"
FROM emp a, emp b
GROUP BY a.deptno
ORDER BY 1
/
DEPTNO   Share, %
------ ----------
    10      30.15
    20      37.47
    30      32.39

#3: Using SUM(DISTINCT ..) on a Cartesian Product

SELECT a.deptno,
       ROUND(100*TRUNC(SUM(DISTINCT a.sal+a.empno/1000000)) /
                 TRUNC(SUM(DISTINCT b.sal+b.empno/1000000)), 2) "Share, %"
FROM emp a, emp b
GROUP BY a.deptno
ORDER BY 1;
DEPTNO   Share, %
------ ----------
    10      30.15
    20      37.47
    30      32.39

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” for instructions.

LIKE with ANY in Teradata SQL

Teradata happened to support a very convenient SQL feature that Oracle does not have:

Teradata:

SELECT *
FROM emp
WHERE ename LIKE ANY ('%A%', '%B%');

--Works perfectly fine

Oracle:

SELECT *
FROM emp
WHERE ename LIKE ANY ('%A%', '%B%');

WHERE ename LIKE ANY ('%A%', '%B%')
                 *
ERROR at line 3:
ORA-00936: missing expression

Oracle does not seem to support any combination of LIKE and ANY:

SELECT *
FROM emp
WHERE ename LIKE ANY (SELECT '%A%' FROM dual UNION ALL
                      SELECT '%B%' FROM dual);

WHERE ename LIKE ANY (SELECT '%A%' FROM dual UNION ALL
                 *
ERROR at line 3:
ORA-00936: missing expression