Saad Nayef's blog

CONTINUE Statement in PL/SQL (Oracle 11g):

2010-06-21T01:09:00.000-07:00

We -as developers- know that most programming languages have CONTINUE statement to skip to next iteration of the loop, but unfortunately we didn't have in Oracle, so we used to do workaround code to imitate CONTINUE functionality. Say we have the following loop code:

SET serveroutput ON

DECLARE

BEGIN

FOR i IN 1 .. 10

LOOP

DBMS_OUTPUT.put_line ('The current number is ' || i);

END LOOP;

END;

And if we don't want to print for 4,6 and 8 numbers we would do some of the following workarounds:

Use label:

SET serveroutput ON

DECLARE

BEGIN

FOR i IN 1 .. 10

LOOP

IF (I IN (4,6,8)) THEN

GOTO FOO ;

END IF;

DBMS_OUTPUT.PUT_LINE ('The current number is ' || I);

<<FOO>> NULL;

END LOOP;

END;

Or use IF ELSE:

SET serveroutput ON

DECLARE

BEGIN

FOR i IN 1 .. 10

LOOP

IF (I NOT IN (4,6,8)) THEN

DBMS_OUTPUT.PUT_LINE ('The current number is ' || I);

ELSE

NULL;

END IF;

END LOOP;

END;

etc...

The good news is we don't have to do previous code. In Oracle 11g a new feature is introduced to enable user to enable the developer to explicitly use CONTINUE statement:

SET serveroutput ON

DECLARE

BEGIN

FOR i IN 1 .. 10

LOOP

CONTINUE WHEN i IN (4,6,8);

DBMS_OUTPUT.put_line ('The current number is ' || i);

END LOOP;

END;

Or write:

SET serveroutput ON

DECLARE

BEGIN

FOR i IN 1 .. 10

LOOP

IF I IN (4,6,8) THEN

CONTINUE;

END IF;

DBMS_OUTPUT.put_line ('The current number is ' || i);

END LOOP;

END;

And viola....

Saad Nayef,

Escape special character (Still some people don't know 10g mechanism!):

2010-06-17T01:59:00.000-07:00

To escape single quote in a string we used to add another single quote before -or after :)- the original single quote to consider those two single quote as one single quote within the string and not a string terminator, as the following:

SELECT 'I don''t like my brother''s car'

FROM DUAL

But do we have to do that for every single quote? Well we had to do it, but not anymore. In oracle 10g a new feature is introduced to make it easier to Oracle developers. The previous query would be:

SELECT q'!I don't like my brother's car!'

FROM DUAL

You may notice the exclamation mark "!" which is not the only character you can use, you can use "[" and "]" as start and end respectively, or any character that is found in the original string with a succeeding single quote and it's better to choose a special character like "[ and ]", "{ and }", "!" ....

All of these queries are right:

SELECT q'[I don't like my brother's car]'

FROM DUAL

SELECT q'{I don't like my brother's car}'

FROM DUAL

Even:

SELECT q'ZI don't like my brother's carZ'

FROM DUAL

Saad Nayef,

Converting rows to columns and vice versa:

2010-05-19T10:38:00.000-07:00

Converting rows to columns:

Sometimes we need some reports that somehow require converting rows to columns. Here we are going to put a simple example on how to do so.

We all know emp table in scott schema that contains deptno column, and we know that deptno column has three distinct values: 10, 20 and 30 –no value 40, but any way you can include it to make sure-.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	-	17-Nov-81	5000	-	10
7698	BLAKE	MANAGER	7839	1-May-81	2850	-	30
7782	CLARK	MANAGER	7839	9-Jun-81	2450	-	10
7566	JONES	MANAGER	7839	2-Apr-81	2975	-	20
7788	SCOTT	ANALYST	7566	9-Dec-82	3000	-	20
7902	FORD	ANALYST	7566	3-Dec-81	3000	-	20
7369	SMITH	CLERK	7902	17-Dec-80	800	-	20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7844	TURNER	SALESMAN	7698	8-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100	-	20
7900	JAMES	CLERK	7698	3-Dec-81	950	-	30
7934	MILLER	CLERK	7782	23-Jan-82	1300	-	10

Let’s say we need a report that shows each department –as columns- and its employees under it:

SELECT empno, 


       DECODE(deptno, 10, ename) "10", 
       DECODE(deptno, 20, ename) "20", 
       DECODE(deptno, 30, ename) "30" 
FROM   emp 
ORDER  BY 2, 3, 4;

EMPNO	10	20	30
7782	CLARK	-	-
7839	KING	-	-
7934	MILLER	-	-
7876	-	ADAMS	-
7902	-	FORD	-
7566	-	JONES	-
7788	-	SCOTT	-
7369	-	SMITH	-
7499	-	-	ALLEN
7698	-	-	BLAKE
7900	-	-	JAMES
7654	-	-	MARTIN
7844	-	-	TURNER
7521	-	-	WARD

If we want to employees count for each department:

SELECT COUNT(DECODE(deptno, 10, ename)) "10", 


       COUNT(DECODE(deptno, 20, ename)) "20", 
       COUNT(DECODE(deptno, 30, ename)) "30" 
FROM   emp 
GROUP  BY deptno ;

10	20	30
0	0	6
0	5	0
3	0	0

And so on we can convert rows to columns as long as the values we want to make them as columns are not so many.

Note that the key is DECODE function.

- Converting columns to rows:

Create test table:

CREATE TABLE convert_c2r 


  ( 
     id     NUMBER, 
     value1 NUMBER, 
     value2 NUMBER, 
     value3 NUMBER, 
     value4 NUMBER 
  );

Insert random data:

INSERT INTO convert_c2r 


VALUES      (1, 1, 2, 3, 4); 

INSERT INTO convert_c2r 
VALUES      (2, 2, 4, 6, 8); 

INSERT INTO convert_c2r 
VALUES      (3, 3, 6, 9, 12);

INSERT INTO convert_c2r 
VALUES      (4, 4, 8, 12, 16);

View table data to ckeck:

SELECT * 


FROM   convert_c2r;

ID	VALUE1	VALUE2	VALUE3	VALUE4
1	1	2	3	4
2	2	4	6	8
3	3	6	9	12
4	4	8	12	16

and to convert columns to rows:

SELECT id, 


       ser, 
       DECODE(ser, '1', value1, 
                   '2', value2, 
                   '3', value3, 
                   '4', value4) d 
FROM   convert_c2r, 
       (SELECT '1' ser 
        FROM   dual 
        UNION 
        SELECT '2' ser 
        FROM   dual 
        UNION 
        SELECT '3' ser 
        FROM   dual 
        UNION 
        SELECT '4' ser 
        FROM   dual) ;

ID	SER	D
1	1	1
2	1	2
3	1	3
4	1	4
1	2	2
2	2	4
3	2	6
4	2	8
1	3	3
2	3	6
3	3	9
4	3	12
1	4	4
2	4	8
3	4	12
4	4	16

Saad Nayef,

Virtual Columns in Oracle 11g

2010-05-17T10:18:00.000-07:00

Most of us have worked with columns that somehow are based on other columns in the same table. For example full_name column which is a concatenation of two columns first_name and last name. Maybe someone will say OK I’ve never done such a work, I used to use views to do it, or maybe I do the concatenation in the query itself without any need to store it and avoid redundancy. I’ll say it’s good but you need to make this every query and every time you need this ?!...

Other that first_name and last_name, there are a lot of more complicated examples which bring pain to us.

Anyway In Oracle 11g, you don’t need to take care of this anymore because Oracle will take care for you. Virtual columns in Oracle 11g is a perfect solution to our past case.

So if we need to create a full_name column, all we have to do is add a virtual column:

CREATE TABLE emp (
    empno      number,
    first_name varchar2(15),
    last_name  varchar2(15),
    full_name  varchar2(30) generated always AS first_name || last_name virtual
);

Then you can consider the virtual column as normal column with few exception like you can’t manipulate it by DML –crystal clear!-, and virtual columns can’t be based on other virtual columns….

Query to generate incremental numbers in Oracle

2010-01-04T21:02:00.000-08:00

Small tip to generate incremental numbers that developers need it sometimes.
So if I want to generate numbers from 1 to 30, I will write:

SELECT     ROWNUM
      FROM DUAL
CONNECT BY LEVEL <= 30;

Oracle launches "Oracle Application Expresss Developer Certified Expert" certificate "1Z1-450"

2009-12-26T04:00:00.000-08:00

New move from Oracle for "Oracle Application Express" developers to be more recognized and known by launching new certificate Oracle Application Expresss Developer Certified Expert
Oracle Application Express 3.2: Developing Web Applications
Till now the it is beta exam and recommended training is
Oracle Application Express: Developing Web Applications NEW.

Saad,

RETURNING INTO Clause in Oracle

2009-12-15T21:41:00.000-08:00

, Nice new feature introduced in Oracle 10g:
Single-Set Aggregates in DML Returning Clause: -as per Oracle documentation- This feature enables the use of single-set aggregation functions in the RETURNING clause of DML statements. This capability can result in significant performance gains in transactions that process many rows of the same table, a behavior often observed in batch processes.
Simple syntax is:

INSERT INTO {table} (columnn)

{values}
RETURNING {exp} INTO {variables};

where:
can be table, materialized view or single-table view.

The purpose of the RETURNING clause is to return the rows affected by the INSERT statement.

SET serveroutput on

DECLARE
   v_total_salary   NUMBER;
BEGIN
   INSERT INTO emp
      SELECT *
        FROM emp
     RETURNING SUM (sal)
          INTO v_total_salary;

   DBMS_OUTPUT.put_line ('Total Salary is: ' || v_total_salary);
END;
/

RETURNING clause can be used with UPDATE statement:

SET serveroutput on

DECLARE
   v_total_salary   NUMBER;
BEGIN
   UPDATE    emp
         SET sal = sal * 1.1
   RETURNING SUM (sal)
        INTO v_total_salary;

   DBMS_OUTPUT.put_line ('Total Salary is: ' || v_total_salary);
END;
/

RETURNING clause can be used with UPDATE statement also:

SET serveroutput on

DECLARE
   v_total_salary   NUMBER;
BEGIN
   DELETE FROM emp
         WHERE manager IS NOT NULL
     RETURNING SUM (al)
          INTO v_total_salary;

   DBMS_OUTPUT.put_line ('Total Salary is: ');
END;
/

Saad,

NULL value in Oracle

2009-12-07T00:02:00.000-08:00

NULL is the absence of a value or information. NULL can be assigned (to a column or a variable ...) but it can not be equated with anything.
so treating NULL or column which may contain NULL values should be done carefully.
Any equation with NULL value will return NULL, same thing for Adding, subtracting, multiplying and dividing.

Oracle has number of built-in functions to treat NULL values, we will describe some of these functions:

NVL:
NVL ( expr1 , expr2 )
If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
The most frequently used function to replace a NULL value with another value (for example 0 in case of number datatype).

SELECT ename, NVL (comm, 0)
  FROM emp;

NVL2:
NVL2 ( expr1 , expr2 , expr3 )
If expr1 is null, then NVL2 returns expr3. If expr1 is not null, then NVL2 returns expr2.

SELECT ename, NVL2 (comm, 'has commission', 'has no commission')
  FROM emp;

COALESCE:
COALESCE(expr[,expr]…)
Returns the first non-null expr in the expression list.

SELECT COALESCE (NULL, 3, 1)
  FROM DUAL;

NULLIF:
NULLIF( expr1, expr2 )
NULLIF function returns NULL if expr1 and expr2 are equal. Otherwise, it returns expr1.

SELECT NULLIF (ename, ename)
  FROM emp;

SELECT NULLIF (ename, ename || ' ')
  FROM emp;

LNNVL: -New in Oracle 10g-
LNNVL(condition)
Return TRUE if the condition is false or unknown and FALSE if the condition is true.
LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query.

SELECT COUNT (*)
  FROM emp
 WHERE lnnvl (comm < .2);

Sorting NULL values:
NULL values come last in ascending order and first in descending order but this order can be overridden by using NULLS FIRST/LAST clause.

SELECT   *
    FROM emp
ORDER BY comm DESC NULLS FIRST;

Note:
It is important to remember that any equation with NULL will return NULL, if we did understand this logic we can explain the reason why any query returns no rows if it has NOT IN operator with list of values (or sub-query) contains NULL values.
The following query retuens no rows:

SELECT ename
  FROM emp
 WHERE empno NOT IN (SELECT mgr
                       FROM emp);

To solve this problem we use NVL function:

SELECT ename
  FROM emp
 WHERE empno NOT IN (SELECT NVL (mgr, 0)
                       FROM emp);

OR EXISTS

SELECT ename
  FROM emp e
 WHERE NOT EXISTS (SELECT mgr
                     FROM emp
                    WHERE mgr = e.empno);

Saad,

Compare the performance between two functions in Oracle

2009-11-29T02:52:00.000-08:00

Sometimes when we can do something in Oracle in more than one way, but at that point we have to use the best one which can serves us better.

PERFORMANCE is the most important criteria we have to put into consideration when have more than one solution. Nice tip in Oracle enables us to measure performance -time spent- between more than one function which is get_cpu_time from dbms_utility package which returns the current CPU time in 100th's of a second and we use it before and after each function with loop of relatively big number of iteration.

Example:

DECLARE
   v_c1     NUMBER;
   v_c2     NUMBER;
   v_date   DATE;
   v_char   VARCHAR2 (10);
BEGIN
   v_c1 := DBMS_UTILITY.get_cpu_time;

   FOR c IN 1 .. 100000
   LOOP
      v_date := TRUNC (SYSDATE);
   END LOOP;

   v_c2 := DBMS_UTILITY.get_cpu_time;
   DBMS_OUTPUT.put_line ('For loop using TRUNC');
   DBMS_OUTPUT.put_line ('CPU seconds used: ' || (v_c2 - v_c1));
   v_c1 := DBMS_UTILITY.get_cpu_time;

   FOR c IN 1 .. 100000
   LOOP
      v_char := TO_CHAR (SYSDATE, 'DD-MM-YYYY');
   END LOOP;

   v_c2 := DBMS_UTILITY.get_cpu_time;
   DBMS_OUTPUT.put_line ('For loop using TO_CHAR');
   DBMS_OUTPUT.put_line ('CPU seconds used: ' || (v_c2 - v_c1));
END;

The output is:

For loop using TRUNC
CPU seconds used: 25
For loop using TO_CHAR
CPU seconds used: 68
PL/SQL procedure successfully completed.

The numbers will change depending on CPU speed and tasks assigned, but anyway you can notice the difference.

Saad,

Oracle UNION, UNION ALL, INTERSECT, MINUS

2009-11-23T03:01:00.000-08:00

If you have result sets from two queries or more, and you want to combine those result sets or you want to remove duplicate rows or get only the common rows or not common rows, it's time to learn about UNION, UNION ALL, INTERSECT and MINUS queries:

UNION Query:
Allows you to combine the result sets of two or more queries but it removes the duplicate rows between the queries.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
UNION
SELECT *
  FROM emp
 WHERE deptno = 20;

You can notice that employee with empno = 7566 should come in both select statement but comes once since we used UNION.

UNION ALL Query:
Allows you to combine the result set of two or more queries and returns all rows.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
UNION ALL
SELECT *
  FROM emp
 WHERE deptno = 20;

Since we used UNION ALL, rows from both queries are returned and duplicate rows are not removed.

INTERSECT Query:

It allow you to get only selected rows returned by all queries you are using in INTERSECT Query.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
INTERSECT
SELECT *
  FROM emp
 WHERE deptno = 20;

It returns only one row -returned by both queries-.

MINUS Query:

Retuns rows from first query that are not returned by other queries.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
MINUS
SELECT *
  FROM emp
 WHERE deptno = 20;

Note:
Each SQL statement within the UNION,UNION ALL,INTERSECT and MINUS query must have the same number of fields in the result sets with similar data types.

Saad,