Category Archives: Tipps and Tricks

Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions

25 Monday May 2020

Posted by in Antipattern, Tipps and Tricks

≈ Leave a comment

Tags

,

During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.

The first time I hit this kind of code I was a little bit confused … it never came to my mind to do something like redefining named exceptions like NO_DATA_FOUND or TOO_MANY_ROWS or even OTHERS.

But after many years I became pretty hardened when it comes to what to expect during a code review.

My own NO_DATA_FOUND Exception

So guess what this peace of code does…

declare
   L_Variable number;
   no_data_found EXCEPTION;
begin
   select empno
     into l_variable
     from scott.emp
    where ename = 'TYRION LENNISTER';
exception
    when no_data_found
       then sys.dbms_output.put_line('Tyrion does not exist');
end;

It ends with raising a no_data_found exception upon the caller…

Error report -
ORA-01403: no data found
ORA-06512: at line 5
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

Hold a minute…why does it raise an exception? There is an exception handler that should take care of no_data_found …
well yes … but …
That is a user defined exception which is different from the named exception that was automatically raised by Oracle. It shares the same name but it is different.

Now how about this?

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      no_data_found EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
    exception
       when no_data_found
          then sys.dbms_output.put_line('Tyrion does not exist');
    end inner;
exception
   when no_data_found
      then sys.dbms_output.put_line('Yes, Tyrion really does not exist');
end outer;

This time we get the message

Yes, Tyrion really does not exist

because in the outer block the no_data_found exception declared in the inner block is not known and therefore we fall back to Oracles named exception NO_DATA_FOUND which is handled in the outer block.

My own OTHERS

How about an user defined others exception?

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      others EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
   exception
      when others 
         then sys.dbms_output.put_line('Tyrion does not exist');
      when no_data_found 
         then sys.dbms_output.put_line('Hello, what did you expect');
   end inner;
exception
   when others 
      then sys.dbms_output.put_line('Yes Tyrion really does not exist');
end outer;

I put the others handler ahead of the no_data_found just to make sure it is “my” others (you cannot do that with the named others) and …

Error report -
ORA-06550: line 14, column 7:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

So obviously that others was not mine but the one of oracle. Next try:

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      others EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
   exception
      when inner.others 
         then sys.dbms_output.put_line('Tyrion does not exist');
      when no_data_found 
         then sys.dbms_output.put_line('Hello, what did you expect');
   end inner;
exception
   when others 
      then sys.dbms_output.put_line('Yes Tyrion really does not exist');
end outer;

This time I got no error 

Hello, what did you expect

So I passed the others exception handler (my own) and fell into the no_data_found handler. The same happens when you write your code like this:

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      others EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
   exception
      when inner.others 
         then sys.dbms_output.put_line('Tyrion does not exist');
      when others
         then sys.dbms_output.put_line('Hello, what did you expect');
   end inner;
exception
   when others 
      then sys.dbms_output.put_line('Yes Tyrion really does not exist');
end outer;

But, honestly …

This is really confusing and in my eyes it is absolutely needless to confuse our colleagues by redefining named exceptions. So get rid of that!

Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.

Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

24 Sunday May 2020

Posted by in Antipattern, Tipps and Tricks

≈ Leave a comment

Tags

,

During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.

Be creative…at least a little bit.

Another antipattern that I see over and over again, is the reuse of table aliases within the same statement … yes syntactically everything is fine, but it is a nightmare to read a query like this – besindes the fact, that table aliases like a,b,c are awful.

select e.first_name, e.last_name
  from employees e
 where e.job_id in (select a.job_id
                      from jobs a
                     where a.min_salary between 5000 and 7500
                        or a.max_salary between 7500 and 9000)
   and e.department_id in (select a.department_id
                             from departments a
                             join locations b on (b.location_id = a.location_id)
                            where b.country_id = 'UK')
   and e.manager_id in (select a.employee_id
                          from employees a
                         where a.hire_date < date '2005-01-01');

To me a table alias is not only a method to clarify the source of a column upon the SQL engine but also a way to ease reading the statement. Therefore the benefit to the reader is much higher than the higher effort it takes for the writer using longer aliases which are more meaningful.

select emp.first_name, emp.last_name
  from employees emp
 where emp.job_id in (select job.job_id
                        from jobs job
                       where job.min_salary between 5000 and 7500
                          or job.max_salary between 7500 and 9000)
   and emp.department_id in (select dpt.department_id
                               from departments dpt
                               join locations loc on (loc.location_id = dpt.location_id)
                              where loc.country_id = 'UK')
   and emp.manager_id in (select mgr.employee_id
                            from employees mgr
                           where mgr.hire_date < date '2005-01-01');

Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.

Antipatterns SQL & PL/SQL – Existance Check

23 Saturday May 2020

Posted by in Antipattern, Tipps and Tricks

≈ 2 Comments

Tags

, ,

During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.

The first antipattern is the “check existance by doing a complete count” pattern.

How do we check whether a particular value exists in a table?

E.g. we would like to know whether there is a person in UK or DE which earns at least 80% of the maximum salary defined by the job. If we found that kind of rows, we would have to call a given procedure.

An anti-pattern that can be often found to solve this kind of question, is reading way to many rows to take your decision. 

...
   select count(*)
     into L_Counter
     from EMPLOYEES   e
     join JOBS        j on (j.JOB_ID        = e.JOB_ID)
     join DEPARTMENTS d on (d.DEPARTMENT_ID = e.DEPARTMENT_ID)
     join LOCATIONS   l on (l.LOCATION_ID   = d.LOCATION_ID)
    where e.SALARY > (j.MAX_SALARY * .8)
      and l.COUNTRY_ID in ('DE','UK');

   if L_Counter > 0 then
      procedure1();
   end if;
...

To reduce the number of rows to be read, we should add a rownum filter to stop searching as soon as we found a hit.

...
   select count(*)
     into L_Counter
     from EMPLOYEES   e
     join JOBS        j on (j.JOB_ID        = e.JOB_ID)
     join DEPARTMENTS d on (d.DEPARTMENT_ID = e.DEPARTMENT_ID)
     join LOCATIONS   l on (l.LOCATION_ID   = d.LOCATION_ID)
    where e.SALARY > (j.MAX_SALARY * .8)
      and l.COUNTRY_ID in ('DE','UK')
      and rownum = 1;

if L_Counter > 0 then
   procedure1();
end if;
...

A different approach would be to use a subquery to answer the question connecting it to dual using an EXIST clause. One of the main differences to the count/rownum approach is, that this kind of query may raise a no_data_found exception and we therefore do not have to check a counter variable. We can rely on a “normal continuation” if a row was found. If no row exists an exception will be raised.

...
   begin
      select 1
        into L_Counter
        from dual
       where exists (select 'Employee with > 80% salary in UK/DE found'
                       from EMPLOYEES   e
                       join JOBS        j on (j.JOB_ID        = e.JOB_ID)
                       join DEPARTMENTS d on (d.DEPARTMENT_ID = e.DEPARTMENT_ID)
                       join LOCATIONS   l on (l.LOCATION_ID   = d.LOCATION_ID)
                      where e.SALARY > (j.MAX_SALARY * .8)
                        and l.COUNTRY_ID in ('DE','UK'))

      procedure1();
   exception
      when no_data_found then
         ...
   end;
...

To update or not to update?

Nowadays the question whether to insert or update a row can easily be solved using the merge command. However I came accross situations where the insert had to be done using an API whilst the update was done directly.

Situations like that are another place where you can find the “check existance by count”-antipattern.

...
   select count(*)
     into L_Counter
     from EMPLOYEES   e
    where e.EMPLOYEE_ID = in_emprec.employee_id;

   if L_Counter > 0 then
      update EMPLOYEES e
         set ...
       where e.EMPLOYEE_ID = in_emprec.employee_id;
   else
      InsertEmployee(in_emprec => in_emprec);
   end if;
...

This solution has two problems:

  1. In case of an update we need to process the employees table twice (first for the count, second for the update)
  2. We ignore the fact, that between the count and the follow-up action the situation could change due to data manipulations done by other sessions.

As a solution for the insert or update question (if no merge is possible) my suggestion would be to try the update and do the API call if the update command ended in updating 0 rows.

...
   update EMPLOYEES e
      set ...
    where e.EMPLOYEE_ID = in_emprec.employee_id;
   
   if SQL%ROWCOUNT = 0 then
      InsertEmployee(in_emprec => in_emprec);
   end if;
...

Or (even better) thinking about handling update/insert in the API using a merge command.

Testing nested tables for existence of an element

A way of testing existance of elements I see quite often is the “SQL way”.  The collection is scanned with a SQL doing a count(*) (without stop key). Maybe this has been an evolutionary way a couple of years ago, but today …

...
   select count(*)
     into L_Count
     from table(L_NestedTable)
    where column_value = L_Value;
 
    if (L_COUNT > 0) then
...

Today the easiest way is to use MEMBER OF to check for existens of an certain element in a nested table.

...
   if L_value member of L_NestedTable then
...

Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.

Best way to loop over a dense nested table

24 Monday Jun 2019

Posted by in Tipps and Tricks

≈ Leave a comment

Tags

,

“What is the best way to process a dense nested table?” seems to be a simple question with an quite simple answer…

Looping First to Last

One way to implement this kind of loop is making use of the collection methods first and last to write code like:

begin
   for i in myArray.first() .. myArray.last() loop
      ... do whatever you need to do
   end loop;
end;

This works as long as the array is initialized and not empty.

If the array is not initialized you get a

ORA-06531: Reference to uninitialized collection

exception. This because the first/last methods can only be used on an initialized collection. If the collection is initilized but empty, the exception thrown is:

ORA-06502: PL/SQL: numeric or value error

the reason for this error is, that on an empty array first and last return null as the result and this is not feasible for a numeric for loop.

So the correct implemetation using the first/last version of a loop would be:

begin
   if myArray is not null
      and myArray is not empty
   then
      for i in myArray.first() .. myArray.last() loop
         ... do whatever you need to do
      end loop;
   end if;
end;

but…we can do better (and shorter).

Looping 1 to count

A slightly different approach is to use 1 as the lower and count as the upper border. If count is called on an empty collection we get 0 as result which has the effect that the loop body is never executed. But as with last/first we get an error when count is used on a collection that has not been initialized. 

So the solution using 1..count would look like this:

begin
   if myArray is not null
   then
      for i 1 .. myArray.count() loop
         ... do whatever you need to do
      end loop;
   end if;
end;

but again…we can do better (and shorter).

Looping using cardinality

Instead of using count to get the number of entries of a nested table we can also use the cardinality function. The advantage of cardinality is that this function can be called on not initialized collections too. The result of cardinality in this case would be null. Null however is not a valid value for a loop border so the cardinality call has to be surrounded by a nvl/coalesce function.

begin
   for i 1 .. nvl(cardinality(myArray),0) loop
      ... do whatever you need to do
   end loop;
end;

This is the shortes way to loop over a collection and it is a version that handles not initialized and empty collections too.

MERGE – DELETE

29 Friday Dec 2017

Posted by in Tipps and Tricks

≈ Leave a comment

Tags

,

Or:  “why we should read the manual from time to time…and not blindly follow our intuition…”.

Basically I thought that I know the MERGE command by heart. So, no big deal to write a sql statement that handles changes in the source table and moves them to a target table.

Even removing rows from target, when they are logically deleted in the source should not be a problem as there is an update delete clause in the merge command. So everything went fine…but somehow it did not work the way I expected the merge command to work.

Test case:

CREATE TABLE empl (empno     NUMBER
                  ,ename     VARCHAR(30 CHAR)
                  ,hiredate  DATE
                  ,leavedate DATE)
/

CREATE TABLE empl_current (empno  NUMBER
                          ,ename  VARCHAR2(30 CHAR))
/

INSERT INTO empl VALUES (1,'MUELLER',DATE '2017-01-01', NULL);
INSERT INTO empl VALUES (2,'MEIER'  ,DATE '2017-02-01', NULL);
INSERT INTO empl VALUES (3,'HUBER'  ,DATE '2017-03-01', NULL);
INSERT INTO empl VALUES (4,'HOFER'  ,DATE '2017-04-01', NULL);
INSERT INTO empl VALUES (5,'SCHMIDT',DATE '2017-05-01', DATE '2017-05-31');

COMMIT;

Step 1:

Write a merge that transfers all employees from empl to empl_current if they have not left the company

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
              FROM empl
             WHERE leavedate IS NULL
                OR leavedate > SYSDATE) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename;

Checking empl_current should now show employees 1 to 4 but not 5 as 5 has left the company already.

SELECT * FROM empl_current; -- should show 1 - 4

EMPNO  ENAME
------ ---------------------
     1 MUELLER
     2 MEIER
     3 HUBER
     4 HOFER

Ok, all fine so far.

Step 2:

Now doing some updates on the source table and check whether the MERGE does what it should do.

UPDATE empl
   SET ename = 'Meier'
 WHERE empno = 2; 

UPDATE empl
   SET leavedate = DATE '2017-06-30'
 WHERE empno = 3; 

COMMIT;

So the previous merge would now handle the changes on employee 2 but not the one of employee 3 as I do not have a delete clause yet.

So I need to add a delete clause to the merge command.

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,leavedate
              FROM empl
             WHERE leavedate IS NULL
                OR leavedate > SYSDATE) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE leavedate IS NOT NULL
                              AND leavedate <= sysdate;

SELECT * FROM empl_current; 

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier     ---> changed 
     3 HUBER     ---> still here (?)
     4 HOFER

Now, the reason for HUBER (3) still being here is, that it cannot be deleted, as it is not a match as the row is not part of the source dataset due to the filter I applied….

Step 3:

The source data set should also contain rows, that should not be inserted but eventually deleted.

So changing the source filter.

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,leavedate
              FROM empl             
           --  WHERE leavedate IS NULL    /* remove this line */
           --     OR leavedate > SYSDATE  /* and this too */
           ) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE leavedate IS NOT NULL
                               AND leavedate <= sysdate;


SELECT * FROM empl_current; 


EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier
     4 HOFER
     5 SCHMIDT   ---> ???

Ok, Huber has gone but we have Schmidt now in the target table despite the fact that he has left the company…so what we need to add  is a filter on the “NOT MATCHED” insert statement to not add rows that we are not (or no longer) interested in…

Step 4:

Improve the filter on the insert statement in the “MATCHED” case. So no rows get inserted that we are not interested in.

MERGE INTO empl_current tar     
     USING (SELECT empno
                  ,ename
                  ,leavedate
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                       WHERE leavedate IS NULL
                          OR leavedate > SYSDATE
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE leavedate IS NOT NULL 
                              AND leavedate <= SYSDATE;

SELECT * FROM empl_current; 

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier
     4 HOFER

This is, where I placed a trap…

I came this far with my first attempt … but I added an trap by just following my intuition and not reading the manual.

The reason for getting trapped was, that I asked myself the question:

“Why should I update a row, that I am going to delete anyway?” I think UPDATE and DELETE should exclude each other so we are only updating when not deleting and only deleting when not updating….

Thefore my MERGE looked like this:

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,CASE WHEN leavedate <= SYSDATE
                        THEN 'Y'
                        ELSE 'N'
                   END AS delete_flag
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                       WHERE delete_flag = 'N'
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                       WHERE delete_flag = 'N'
                      DELETE WHERE delete_flag = 'Y';

I added a delete flag, which made it easier to insert only unmatched rows that have not yet been deleted and it was also not to complicated to distinguish between update an delete too.

So add another testcase to get rid of  empno 4…

UPDATE empl
   SET leavedate = DATE '2017-08-31'
 WHERE empno = 4; 

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,CASE WHEN leavedate <= SYSDATE
                        THEN 'Y'
                        ELSE 'N'
                   END AS delete_flag
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                      WHERE delete_flag = 'N'
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                       WHERE delete_flag = 'N'
                      DELETE WHERE delete_flag = 'Y';

SELECT * FROM empl_current;                       

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier
     4 HOFER  --- ????

Hmmm…

So, why is employee 4 still in the target table? I checked my statement over and over again and finally had a look to the documentation which says:

ORACLE Database SQL Language Reference

merge_update_clause

The merge_update_clause specifies the new column values of the target table. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.

Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET … WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.

 

That was somehow surprising…but, after removing the WHERE clause on the update, everything worked again…

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,CASE WHEN leavedate <= SYSDATE
                        THEN 'Y'
                        ELSE 'N'
                   END AS delete_flag
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                      WHERE delete_flag = 'N'
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE delete_flag = 'Y';

SELECT * FROM empl_current; 

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier

Conclusion

Testing is everything … from time to time you stumle upon things you thought you know by heart … and then you will be thankful for the great documentation job oracle did.

 

NULLIF – A tiny, powerfull and underrated SQL Function

19 Monday Jun 2017

Posted by in Tipps and Tricks, Underestimated Features

≈ Leave a comment

Tags

,

NULLIF has been introducde quite a while ago (Oracle 9) but still this function is not well known to the community.

SYNTAX : NULLIF(expr1,expr2)

Nullif compares expr1 and expr2. If they are equal, then NULL is returned otherwise expr1. We cannot specify the literal NULL for expr1.

So where can this function be used?

Eliminiate Division by Zero:

WITH sales (prod, thisyear, lastyear)
        AS (SELECT 'Monitor' ,  50, 25 FROM dual UNION ALL
            SELECT 'Keyboard', 125,  0 FROM dual UNION ALL
            SELECT 'Mouse'   ,  35, 40 FROM dual UNION ALL 
            SELECT 'Desktop' ,   0, 25 FROM dual UNION ALL
            SELECT 'Laptop'  ,  10,  3 FROM dual)
SELECT prod, thisYear, lastYear
     , ROUND(thisYear * 100 / NULLIF(lastYear,0),2) AS SalesPerc
  FROM sales
/

PROD       THISYEAR   LASTYEAR  SALESPERC
-------- ---------- ---------- ----------
Monitor          50         25        200
Keyboard        125          0
Mouse            35         40       87.5
Desktop           0         25          0
Laptop           10          3     333.33

 

Remove not needed concatenation seperators:

Add first letter of the middle name followed by a dot if a middle name exists.

WITH pers (firstname, middle, lastname)
       AS (SELECT 'Kurt'  ,'Heinrich','Meier' FROM dual UNION ALL
           SELECT 'Hubert',NULL      ,'Huber' FROM dual)
SELECT firstname || NULLIF(' '||SUBSTR(middle,1,1) || '.',' .') 
    || ' ' || lastname AS fullname
  FROM pers
/    

FULLNAME
---------------
Kurt H. Meier
Hubert Huber

 

Suppress values above (or below) a given margin:

Do not show salaries greater/equal 3000.

SELECT ename, NULLIF(sal,GREATEST(sal,3000)) AS sal
  FROM emp
/    

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT
KING
TURNER           1500
ADAMS            1100
JAMES             950
FORD
MILLER           1300

 

As you can see there are multiple opportunities to make use of this smart little function.

Ignore Nulls with LAST_VALUE

16 Sunday Oct 2016

Posted by in Tipps and Tricks

≈ Leave a comment

Tags

, , , ,

A powerfull feature of the LAST_VALUE/FIRST_VALUE analytic function is the IGNORE NULL clause. This feature allows us to inherit a column value from a prio row with a specific attribute.

Just lately I came accross the following problem on ORACLE SQL & PL/SQL support forum:

The Data:

WITH data (skp_id, code_status, date_app) 
       AS (SELECT 111, 'REJECTED', DATE '2016-01-01' FROM dual UNION ALL
           SELECT 123, 'APPROVED', DATE '2016-02-01' FROM dual UNION ALL
           SELECT 201, 'APPROVED', DATE '2016-03-01' FROM dual UNION ALL
           SELECT 302, 'REJECTED', DATE '2016-04-01' FROM dual UNION ALL
           SELECT 403, 'REJECTED', DATE '2016-05-01' FROM dual UNION ALL
           SELECT 450, 'APPROVED', DATE '2016-06-01' FROM dual UNION ALL
           SELECT 475, 'APPROVED', DATE '2016-07-01' FROM dual UNION ALL
           SELECT 480, 'APPROVED', DATE '2016-08-01' FROM dual UNION ALL
           SELECT 490, 'REJECTED', DATE '2016-09-01' FROM dual) 
SELECT skp_id
     , code_status
     , date_app
 FROM data
/

The task was to show with every line the last rejection date prior to the current row as an additional column.

SKP_ID CODE_STATUS DATE_APP LAST_REJECTED
111 REJECTED 01-JAN-16 –
123 APPROVED 01-FEB-16 01-JAN-16
201 APPROVED 01-MAR-16 01-JAN-16
302 REJECTED 01-APR-16 01-JAN-16
403 REJECTED 01-MAY-16 01-APR-16
450 APPROVED 01-JUN-16 01-MAY-16
475 APPROVED 01-JUL-16 01-MAY-16
480 APPROVED 01-AUG-16 01-MAY-16
490 REJECTED 01-SEP-16 01-MAY-16

To achieve this the LAST_VALUE analytic function with the IGNORE NULLS addition is very handy as it allows to ignore those rows that we are not interested in (the approved ones).

Solution:

In a first step we nullify all rows in the generated last_rejected column that are of no interest:

DECODE(code_status,'REJECTED',date_app)

The next step searches the last entry in the result set where the column value of the generated column is not null.

LAST_VALUE (DECODE(code_status,'REJECTED',date_app) IGNORE NULLS) 
      OVER (ORDER BY skp_id)

And the last step makes sure, that we only evaluate to prior of the current row but not including the current row.

ROWS BETWEEN UNBOUNDED PRECEDING 
                            AND 1 PRECEDING

The whole statement looks like this:

WITH data (skp_id, code_status, date_app) 
       AS (SELECT 111, 'REJECTED', DATE '2016-01-01' FROM dual UNION ALL
           SELECT 123, 'APPROVED', DATE '2016-02-01' FROM dual UNION ALL
           SELECT 201, 'APPROVED', DATE '2016-03-01' FROM dual UNION ALL
           SELECT 302, 'REJECTED', DATE '2016-04-01' FROM dual UNION ALL
           SELECT 403, 'REJECTED', DATE '2016-05-01' FROM dual UNION ALL
           SELECT 450, 'APPROVED', DATE '2016-06-01' FROM dual UNION ALL
           SELECT 475, 'APPROVED', DATE '2016-07-01' FROM dual UNION ALL
           SELECT 480, 'APPROVED', DATE '2016-08-01' FROM dual UNION ALL
           SELECT 490, 'REJECTED', DATE '2016-09-01' FROM dual) 
SELECT skp_id
     , code_status
     , date_app
     , LAST_VALUE (DECODE(code_status,'REJECTED',date_app) IGNORE NULLS) 
             OVER (ORDER BY skp_id 
                   ROWS BETWEEN UNBOUNDED PRECEDING 
                            AND 1 PRECEDING) as last_rejected
 FROM data
/

Analytic functions are not really new – the came with 8.1.6 – but still they do not get the attraction they deserve.

 

ORACLE 12.2 -Data conversion enhancements

30 Friday Sep 2016

Posted by in Tipps and Tricks

≈ Leave a comment

Tags

, , , , , , ,

ORACLE 12.2 has a new option to handle conversion errors when using functions like CAST, TO_NUMBER, TO_CHAR, TO_DATE, etc. Using this option we may avoid conversion errors an return a default value instead.

The examples use TO_DATE but the option is useable in the other conversion functions accordingly

EXAMPLE TO_DATE

In case of a string to date conversion error we would like to return the 1st of January of 2016.

WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
                    SELECT '30.06.2016' FROM dual UNION ALL
                    SELECT '30.02.2016' FROM dual UNION ALL
                    SELECT 'gugus' FROM dual UNION ALL
                    SELECT '07.03.2016' FROM dual UNION ALL
                    SELECT '15.10.2016' FROM dual)
SELECT str
 , TO_DATE(str DEFAULT '01.01.2016' ON CONVERSION ERROR,'DD.MM.YYYY') AS conversion
 FROM data
/

STR CONVERTED 
---------- ----------
15.01.2016 15.01.2016
30.06.2016 30.06.2016
30.02.2016 01.01.2016
gugus      01.01.2016
07.03.2016 07.03.2016
15.10.2016 15.10.2016

6 rows selected.

Using SYSDATE as default

Using SYSDATE as the default value instead of a literal value works as expected.

WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
                    SELECT '30.06.2016' FROM dual UNION ALL
                    SELECT '30.02.2016' FROM dual UNION ALL
                    SELECT 'gugus' FROM dual UNION ALL
                    SELECT '07.03.2016' FROM dual UNION ALL
                    SELECT '15.10.2016' FROM dual)
SELECT str
 , TO_DATE(str DEFAULT SYSDATE ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/ 

STR CONVERTED 
---------- ----------
15.01.2016 15.01.2016
30.06.2016 30.06.2016
30.02.2016 30.09.2016
gugus      30.09.2016
07.03.2016 07.03.2016
15.10.2016 15.10.2016

6 rows selected.

Default to start of the year…

So next step would be to invoke a function on sysdate, to get the first day of the current year as the default value for an occuring conversion error.

WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
                    SELECT '30.06.2016' FROM dual UNION ALL
                    SELECT '30.02.2016' FROM dual UNION ALL
                    SELECT 'gugus' FROM dual UNION ALL
                    SELECT '07.03.2016' FROM dual UNION ALL
                    SELECT '15.10.2016' FROM dual)
 SELECT str
      , TO_DATE(str DEFAULT TRUNC(SYSDATE,'YEAR') ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/ 

Error at Command Line : 8 Column : 28
 Error report -
 SQL Error: ORA-43907: This argument must be a literal or bind variable.

 

So, I am a little bit puzzled…we can use SYSDATE but not having a function around it…

Choosing another column value as the default

Now, how about choosing another column of the same table as the default value?

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual)
 SELECT str
      , dateval
      , TO_DATE(str DEFAULT dateval ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/
 
Error at Command Line : 9 Column : 29
Error report -
SQL Error: ORA-43907: This argument must be a literal or bind variable.

Ok, perhaps it needs to be a string using the same format mask?

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual)
 SELECT str
      , dateval
      , TO_DATE(str DEFAULT TO_CHAR(dateval,'DD.MM.YYYY') ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/
 
Error at Command Line : 9 Column : 29
Error report -
SQL Error: ORA-43907: This argument must be a literal or bind variable.

So, how can this be solved? 

Now, we could return NULL in case of a conversion error and then replace a null value by the value of the second column…like this:

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus'     , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual UNION ALL
                            SELECT NULL        , date '2015-07-21' FROM DUAL)
SELECT str
     , dateval
     , NVL(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY'),dateval) AS converted
 FROM data
/

STR        DATEVAL    CONVERTED 
---------- ---------- ----------
15.01.2016 15.07.2015 15.01.2016
30.06.2016 16.07.2015 30.06.2016
30.02.2016 17.07.2015 17.07.2015
gugus      18.07.2015 18.07.2015
07.03.2016 19.07.2015 07.03.2016
15.10.2016 20.07.2015 15.10.2016
           21.07.2015 21.07.2015

7 rows selected.

but…I only wanted to replace values that are not convertable by the second value, not null values…

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual UNION ALL
                            SELECT NULL , date '2015-07-21' FROM DUAL)
SELECT str
     , dateval
     , CASE 
          WHEN str IS NULL THEN NULL
          ELSE NVL(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY'),dateval)
       END AS converted
 FROM data
/

A little bit complicated, but this way I get what I want.

Handling different date formats in one query

Now another test case, having an input stream using different date formats…

WITH data (str) AS (SELECT '15.03.2016' FROM dual UNION ALL
                    SELECT '2005/03/15' FROM dual UNION ALL
                    SELECT '15-MRZ-2016' FROM dual UNION ALL
                    SELECT '15-MAR-2016' FROM dual UNION ALL
                    SELECT '15. MARCH 2016' FROM dual UNION ALL
                    SELECT '15-MAALIS-2016' FROM DUAL UNION ALL
                    SELECT '15-BRE-2016' FROM DUAL)
SELECT str
     , COALESCE(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'YYYY/MM/DD')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=GERMAN')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=FINNISH')
       ) AS converted
 FROM data
/ 

STR            CONVERTED 
-------------- ----------
15.03.2016     15.03.2016
2005/03/15     15.03.2005
15-MRZ-2016    15.03.2016
15-MAR-2016    15.03.2016
15. MARCH 2016 15.03.2016
15-MAALIS-2016 15.03.2016
15-BRE-2016 

7 rows selected.

 

Conclusion

The DEFAULT … ON CONVERSION ERROR is a very powerful feature, which will help us solving conversion errors very elegant. Of course we will still not be able to do conversions when the input value is not unambiguous (A string value of ‘03.05.2016’ could be the 3rd of May as well as the 5th of March) but still a very welcome feature.

 

Grouping and counting continuity

15 Tuesday Dec 2015

Posted by in Tipps and Tricks

≈ Leave a comment

Tags

, , , , ,

A colleague of mine had the problem to group continuous entries of same type to be able to count number of continuos entries.

His Data:

TS        USERNAME RETURNCODE
--------- -------- ----------
05-NOV-15 SCOTT         28000
04-NOV-15 SCOTT         28000
03-NOV-15 SCOTT          1027 
02-NOV-15 SCOTT          1027
01-NOV-15 SCOTT          1027
31-OCT-15 SCOTT          1027
30-OCT-15 SCOTT             0
29-OCT-15 SCOTT          1027
28-OCT-15 SCOTT          1027
27-OCT-15 SCOTT             0
26-OCT-15 SCOTT             0
25-OCT-15 SCOTT          1027

Output needed:

TS        USERNAME RETURNCODE GROUP COUNT
--------  -------- ---------- ----- -----
05-NOV-15 SCOTT         28000     1     2
04-NOV-15 SCOTT         28000     1     2
03-NOV-15 SCOTT          1027     2     4
02-NOV-15 SCOTT          1027     2     4
01-NOV-15 SCOTT          1027     2     4
31-OCT-15 SCOTT          1027     2     4
30-OCT-15 SCOTT             0     3     1
29-OCT-15 SCOTT          1027     4     2
28-OCT-15 SCOTT          1027     4     2
27-OCT-15 SCOTT             0     5     2
26-OCT-15 SCOTT             0     5     2
25-OCT-15 SCOTT          1027     6     1

So basically the problem was to build groups on continuous identical returncodes and increase group number with every change. After those groups have been built the count column can be calculated using a COUNT(*) OVER (PARTITION BY the group-identification).

Solution 1 – ANALYTIC FUNCTION:

This solution compares the returncode value of the previous row (within same username ordered by timestamp) with the current value and returns “1” in case of a difference if the current value is unchanged NULL is returned. In the second step a running summary over this 1/NULL result is built.

WITH data (ts, username, returncode)        
       AS ( SELECT DATE '2015-11-05', 'SCOTT', 28000 FROM dual UNION ALL
            SELECT DATE '2015-11-04', 'SCOTT', 28000 FROM dual UNION ALL
            SELECT DATE '2015-11-03', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-02', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-01', 'SCOTT', 1027  FROM dual UNION ALL 
            SELECT DATE '2015-10-31', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-30', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-29', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-28', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-27', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-26', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-25', 'SCOTT', 1027  FROM dual) 
SELECT ts
      ,username
      ,returncode
      ,SUM(start_entry) OVER (PARTITION BY username ORDER BY ts DESC) AS grp
  FROM (SELECT ts
             , username
             , returncode
             , CASE
                  WHEN returncode = LAG(returncode) OVER (PARTITION BY username
                                                              ORDER BY ts DESC)
                     THEN 0                   
                  ELSE 1
               END AS start_entry 
          FROM data) 
/

TS        USERN RETURNCODE GRP
--------- ----- ---------- ----------
05-NOV-15 SCOTT      28000          1
04-NOV-15 SCOTT      28000          1
03-NOV-15 SCOTT       1027          2
02-NOV-15 SCOTT       1027          2
01-NOV-15 SCOTT       1027          2
31-OCT-15 SCOTT       1027          2
30-OCT-15 SCOTT          0          3
29-OCT-15 SCOTT       1027          4
28-OCT-15 SCOTT       1027          4
27-OCT-15 SCOTT          0          5
26-OCT-15 SCOTT          0          5
25-OCT-15 SCOTT       1027          6         

Solution 2 – MODEL clause:

The model clauses has the possibility to compare the values of different rows and generate a value for a different column based on this comparision. In our case I compare the value of the returncode of the previous row to the value of the returncode of the current row. In case of identical values i reuse the value of the grp column of the previous row to set the grp value of the current row, in case of a difference I increase the previous rows value by 1.

WITH DATA (ts, username, returncode)
        AS( SELECT DATE '2015-11-05', 'SCOTT', 28000 FROM dual UNION ALL
            SELECT DATE '2015-11-04', 'SCOTT', 28000 FROM dual UNION ALL 
            SELECT DATE '2015-11-03', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-02', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-01', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-31', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-30', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-29', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-28', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-27', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-26', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-25', 'SCOTT', 1027  FROM dual)
SELECT ts, username, returncode, grp
  FROM data
      MODEL PARTITION BY (username)
            DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY username 
                                                  ORDER BY ts) AS rn)
            MEASURES (1 as grp,returncode,ts)
            RULES AUTOMATIC ORDER
            (grp[rn > 1]  = CASE WHEN returncode[CV()] = returncode[cv()-1] 
                                 THEN grp[CV()-1]
                                 ELSE grp[CV()-1] + 1
                            END)
/

Solution 3 “MATCH_RECOGNIZE (12c)”:

With the MATCH_RECOGNIZE clause we can do a count of the group members simultaneously to the group assignement.

WITH DATA (ts, username, returncode)
        AS(SELECT DATE '2015-11-05', 'SCOTT', 28000 FROM dual UNION ALL
           SELECT DATE '2015-11-04', 'SCOTT', 28000 FROM dual UNION ALL 
           SELECT DATE '2015-11-03', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-11-02', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-11-01', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-31', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-30', 'SCOTT', 0     FROM dual UNION ALL
           SELECT DATE '2015-10-29', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-28', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-27', 'SCOTT', 0     FROM dual UNION ALL
           SELECT DATE '2015-10-26', 'SCOTT', 0     FROM dual UNION ALL
           SELECT DATE '2015-10-25', 'SCOTT', 1027  FROM dual)
SELECT ts, username, returncode, grp, continuous_rows
  FROM DATA
  MATCH_RECOGNIZE (PARTITION BY username
                   ORDER BY ts DESC                    
                   MEASURES FINAL COUNT(*) AS continuous_rows
                           ,MATCH_NUMBER() AS grp
                   ALL ROWS PER MATCH
                   AFTER MATCH SKIP PAST LAST ROW
                   PATTERN (strt cont*)
                   DEFINE cont AS cont.returncode = PREV(cont.returncode)) x
/

Gap Detection – Tabibitosan Method

01 Wednesday Jul 2015

Posted by in Tipps and Tricks

≈ Leave a comment

Tags

, ,

Another question from the ORACLE SQL & PL/SQL Forum.

Problem:

The original poster (OP) needed to find the first “missing” entry in a series of entries.

Entries are like:

  • Set 1: 0001, 0004, 0006  First missing entry: 0002
  • Set 2: 0001, 0002, 0006  First missing entry: 0003
  • Set 3: 0001, 0002, 0003   First missing entry: 0004
  • Set 4: 0002, 0003, 0004  First missing entry: 0001

Test Data:

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, entry
  FROM data
/

        ID ENTRY
---------- -----
         1 0001 
         1 0004 
         1 0006 
         2 0001 
         2 0002 
         2 0006 
         3 0001 
         3 0002 
         3 0003 
         4 0002 
         4 0003 
         4 0004 

 12 rows selected

Solution:

So first step would be to calculate the effective entry and the entry – “value an entry had if everything was gapless” (the column is called distance).

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, entry
     , TO_NUMBER(entry) - ROW_NUMBER() OVER (PARTITION BY id 
                                             ORDER BY entry) AS distance
  FROM data
/

        ID ENTRY   DISTANCE
---------- ----- ----------
         1 0001           0
         1 0004           2
         1 0006           3
         2 0001           0
         2 0002           0
         2 0006           3
         3 0001           0
         3 0002           0
         3 0003           0
         4 0002           1
         4 0003           1
         4 0004           1

12 rows selected

As long as the distance is 0 the entry belongs to a gapless series. To find the first missing entry, we are looking for the last entry with a distance of 0.

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, MAX(DECODE(distance,0,id,NULL)) AS last_in_serie
  FROM (SELECT id, entry
             , TO_NUMBER(entry) - ROW_NUMBER() OVER (PARTITION BY id
                                                         ORDER BY entry) AS distance
          FROM data)
  GROUP BY id
/

        ID LAST_IN_SERIE
---------- -------------
         1             1
         2             2
         3             3
         4     

By increasing this last_in_series value by 1 we get the first missing entry.

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, TO_CHAR(NVL(MAX(DECODE(distance,0,id,NULL)),0) + 1,'FM0000') as next_entry
  FROM (SELECT id, entry
             , TO_NUMBER(entry) - ROW_NUMBER() OVER (PARTITION BY id
                                                         ORDER BY entry) AS distance
          FROM data)
  GROUP BY id
/

        ID NEXT_ENTRY
---------- ----------
         1 0002      
         2 0003      
         3 0004      
         4 0001