• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Category Archives: SQL

Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns

24 Sunday May 2020

Posted by troroblog in Antipattern, ORACLE, PL/SQL, SQL

≈ Leave a comment

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.


Another sign of programmers laziness is the wide usage of “select *”. This antipattern can be found in inline views, database views, cursors …

The only column needed in the following example is the employee_id, so why do we read the whole row?

This is a waste for many reasons:

  • expensive columns like CLOB/BLOB/XMLTYPE
  • chained rows in the table
  • maybe we could benefit of an index-only access if we pared the query down to the minimum
  • storage that is needed to hold the row

Which Columns are really needed?

For this PL/SQL block we would have to return employee_id only from the implicit cursor.

begin
   for EmpRec in (select *
                    from employees
                   where salary < l_minSalary) loop
      raise_salary(in_employee_id => EmpRec.employee_id);
   end loop;
end;

In the following example the only relevant columns needed as result of the inline view on GR_BRCT_STWE (which has 39 columns) are GR_LNR_1 to join GR_STAMM and GR_LNR_2 to filter against a bind variable (which could be done in the inline view anyway) .

select count(*)
  from (select *
          from GR_BRCT_STWE
         where USERBK_NR = :2) verb
  join GR_STAMM mfh on (    verb.GR_LNR_1 = mfh.GR_LNR
                        and mfh.GR_TYP_CD = 3 
                        and mfh.USERBK_NR = :2)
  join GR_GBDE mfh_gbde on (    mfh_gbde.GR_LNR = mfh.GR_LNR
                            and mfh_gbde.USERBK_NR = :2)
 where verb.GR_LNR_2 = :1
  and mfh_gbde.WHG_ANZ is not null;

If we have to use most but not all of the columns of a table it might be cumbersome to write down all the columns, but nowadays we have great support by IDEs like SQL Developer.

SQL Developer may help you…

If you write down a select * from table you will discover a yellow wavy line underneath the asterisk. Hovering your mouse pointer over this line will enable you to apply the complete column list, which may be arranged afterwards.


Efficient coding? Sorry, I call it laziness.

Yes it may be more work to specify exactly which columns you need, but not doing so is not a sign of efficiency but only of laziness.


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 – Substitute NULL with empty string

24 Sunday May 2020

Posted by troroblog in Antipattern, ORACLE, PL/SQL, SQL

≈ Leave a comment

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.


One of the worst things that may be seen often in PL/SQL code is using an empty string as a substitution of a NULL value. There are places where it technically works and therefore is simply ugly and others where it is just wrong.


Ugly NULL substitutions

The ugly usages are those where an empty string is used as an assignment to a variable or a parameter. Even if this is not technically wrong it may be a reason why inexperienced colleagues may end substituting NULL by empty string not only in assignements but also when it comes to conditions.

So ugly NULL substitutions look like this:

...
   L_Variable := '';
   F_GetTableValues('P1' ,'P2' ,'' ,P3 ,'P4');
...
   
select decode(a.Column,'',0,a.OtherColumn)
   from table a;

...
   procedure OpenForm (P_UserId      in varchar2
                      ,P_StartUpArgs in varchar2 default ''
                      ,P_Commit      in number default 0);
...

Wrong NULL substitutions

Substituting NULL by an empty string within a condition is not only ugly, it is just wrong and it will not lead to the expected results. The reason therefore is, that this kind of conditions will not work with the IS / IS NOT keyword (as needed for NULL comparison) but with an equal/unequal operator.

Obviously is is not really unknown, that comparing with an empty is string is kind of problematic, this might be the reason why we can often find things like:

if L_Variable = '' or L_Variable is null then
...
end if;

if L_Variable <> '' or L_Variable is not null then
...
end if;

An example using the HR sample locations table

select city, country_id, state_province
  from locations
 where state_province = '';

no rows selected


select city, country_id, state_province
  from locations
 where state_province is null;

CITY       COUNTRY_ID  STATE_PROVINCE
---------  ----------  --------------
Roma       IT
Venice     IT
Hiroshima  JP
Beijing    CN
Singapore  SG
London     UK


select city, country_id, state_province
  from locations
 where state_province <> '';

no rows selected

Why substitute?

Maybe two single quotes are shorter than the keyword NULL (2 vs 4 letters) but as soon as we think about adding an additional or clause we should really get rid of the NULL substitutions.

And this please not only at the places where the substitutions is wrong but also where it is only ugly…


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 troroblog in Antipattern, ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

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.

Antipattern SQL & PL/SQL – Using the LIKE operator even if searching for equality.

23 Saturday May 2020

Posted by troroblog in Antipattern, ORACLE, PL/SQL, SQL

≈ Leave a comment

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.


I guess that queries like the following may be found throughout every application.

select *
  from emp
 where ename like 'SCOTT';

select table_name, comments
  from dictionary
 where table_name like 'ALL_TABLES';

Those queries are confusing, error-prone and sometimes even wrong.


Confusing

If we find a LIKE expression in a query, the expectations would be to see some wildcards in the condition. Not having any wildcards in the query leads to a certain suspicion.

Error-Prone

When using the like operator instead of an equal sign we have to make sure that every wildcard character that should not be used as a wildcard is escaped. The most common wild card is the percent sign (%) … the one that is often missed is the underscore.

So basically this query is not only searching for an object called “ALL_TABLES” but also for something starting with “ALL” having any charcater at position 4 and ending with “TABLES”.

select table_name, comments
  from dictionary
 where table_name like 'ALL_TABLES';

To correct this query we need to escape the underscore

select table_name, comments
  from dictionary
 where table_name like 'ALL\_TABLES' escape '\';

or even better use an equal instead of the like operator.


Same same but different

Do comparisons without wildcard always return the same result regardless of using the LIKE operator or the equal sign?

Well….no, they dont.
There are not many cases where the CHAR datatype is still used, but nevertheless it is important to know, how this datatype behaves when it comes to equal comparisons.

Blank-Padded comparison semantics (Oracle – SQL Language Reference)

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.

Nonpadded comparison semantics (Oracle – SQL Language Reference)

With nonpadded semantics, Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, then the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2.

Example for blank-padded comparison

As you can see it does not matter how many spaces we add to the search string Hello as the database type is a CHAR and a blank-padded comparison is used.

create table test_char (x char(10));

insert into test_char (x) values ('Hello');

select *
from test_char
where x = 'Hello';

X
----------------
Hello

select *
from test_char
where x = 'Hello ';

X
----------------
Hello

However using LIKE for the comparison changes everything. A like comparison will never use the blank-padding semantic and therefore we are not able to find a row.

select *
from test_char
where x like 'Hello';

no rows selected

What is the datatype of a string literal?

According to the documentation (ORACLE SQL Language Reference) a string (or text or character) literal has the CHAR datatype and therefore also is compared using the blank-padding semantic.


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 troroblog in Antipattern, ORACLE, PL/SQL, SQL, Tipps and Tricks

≈ 2 Comments

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.

ORACLE 18c – TO_UTC_TIMESTAMP_TZ

29 Saturday Feb 2020

Posted by troroblog in BUG, ORACLE, ORACLE 18c, SQL

≈ Leave a comment

Tags

ORACLE, SQL

TO_UTC_TIMESTAMP_TZ takes a ISO8601 formatted date string and converts it to a TIMESTAMP WITH TIMEZONE datatype.

According to documentation this is the ebnf of to_utc_timestamp_tc

to_utc_timestamp_tz

The varchar parameter may be either a date (only) or a date with time combination. The format of the parameter is:

Date only:

YYYY-MM-DD

Date / time:

YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]]][Z|(+|-)hh:mm]

Example

with data (d) as (select '2020-02-28T21:32:52'       from dual union all
                  select '2020-02-28T21:32:52+02:00' from dual union all
                  select '2020-02-28T19:32:52Z'      from dual union all
                  select '2020-02-28T19:32:52+00:00' from dual union all
                  select '2020-02-28T21:32:52.12679' from dual union all
                  select '2020-02-28')
select to_utc_timestamp_tz(d) as converted
  from data;

CONVERTED 
---------------------------------------------
28.02.2020 21:32:52.000000000 GMT
28.02.2020 21:32:52.000000000 +02:00
28.02.2020 19:32:52.000000000 GMT
28.02.2020 19:32:52.000000000 GMT
28.02.2020 21:32:52.126790000 GMT
28.02.2020 00:00:00.000000000 GMT

6 rows selected.

DEFAULT RETURN VALUE ON CONVERSION ERROR

As most of the other conversion functions documentation says that TO_UTC_TIMESTAMP_TZ supports the on conversion error clause too. But when trying to use it you will get an error.

with data (d) as (select '2018-10-26T21:32:52'       from dual union all
                  select '2018-10-26T21:32:52+02:00' from dual union all
                  select '2018-10-26T19:32:52Z'      from dual union all
                  select '2018-10-26T19:32:52+00:00' from dual union all
                  select '2018-10-26T21:32:52.12679' from dual)
select to_utc_timestamp_tz(d default null on conversion error) converted
  from data;

ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause: 
*Action:
Error at Line: 11 Column: 30

Documentation Bug

I have filed an SR with Oracle Support regarding this error. It has been classified as documentation bug:

DOC 30936692 – TAKE OUT “[ DEFAULT RETURN_VALUE ON CONVERSION ERROR ]” OPTIONAL CLAUSE FROM TO_UTC_TIMESTAMP_TZ

and will be removed from future (>20) documentations.


Conclusion

Somehow I hopped, that Oracle would add the missing functionality concerning the conversion error handling to the TO_UTC_TIMESTAMP_TZ function rather than declaring it as a documentation bug. 

Not having a decent way to handle exceptions (besides building a user defined function with an exception handler) will keep me away from using this function.

ORACLE 19c – LISTAGG Enhancement

07 Tuesday Jan 2020

Posted by troroblog in ORACLE, ORACLE 19c, SQL

≈ Leave a comment

With ORACLE 19c another gap in the listagg feature was filled. Now we are able to do a native distinct operation on the values in the list, so that the list no longer contains duplicates.

In prior releases we had to deduplicate the values before using them in the listagg, which was rather dreadful when doing other group functions in the same query.


LISTAGG Deduplication

Imagine we would have to list all product subcategories of the SH.Products table group by the product category.

select PROD_CATEGORY
     , listagg(PROD_SUBCATEGORY,' | ') as SubCategories
  from SH.PRODUCTS
 group by PROD_CATEGORY;

PROD_CATEGORY                 SUBCATEGORIES
----------------------------  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Photo                         Camcorders | Camera Batteries | Camera Batteries | Camera Batteries | Camera Batteries | Camera Batteries | Camera Media | Camera Media | Camera Media | Cameras
Electronics                   Game Consoles | Home Audio | Home Audio | Y Box Accessories | Y Box Accessories | Y Box Games | Y Box Games | Y Box Games | Y Box Games | Y Box Games | Y Box Games | Y Box Games | Y Box Games                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
Software/Other                Accessories | Accessories | Accessories | Accessories | Accessories | Bulk Pack Diskettes | Bulk Pack Diskettes | Documentation | Documentation | Documentation | Documentation | Documentation | Documentation | Operating Systems | Recordable CDs | Recordable CDs | Recordable CDs | Recordable CDs | Recordable CDs | Recordable CDs | Recordable CDs | Recordable DVD Discs | Recordable DVD Discs | Recordable DVD Discs | Recordable DVD Discs | Recordable DVD Discs                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
Peripherals and Accessories   Accessories | Accessories | Accessories | Accessories | Accessories | CD-ROM | CD-ROM | CD-ROM | CD-ROM | CD-ROM | CD-ROM | Memory | Memory | Modems/Fax | Modems/Fax | Monitors | Monitors | Printer Supplies | Printer Supplies | Printer Supplies | Printer Supplies                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
Hardware                      Desktop PCs | Portable PCs

As we can see, there are duplicates in the subcategory lists. So we had to do a deduplication in the past.

select PROD_CATEGORY
     , listagg(PROD_SUBCATEGORY,' | ') as SubCategories
  from (select distinct PROD_CATEGORY, PROD_SUBCATEGORY
          from SH.PRODUCTS)
 group by PROD_CATEGORY;

PROD_CATEGORY                SUBCATEGORIES
---------------------------  -------------------------------------------------------------------------------------------------------------
Electronics                  Y Box Accessories | Game Consoles | Home Audio | Y Box Games
Software/Other               Bulk Pack Diskettes | Recordable CDs | Recordable DVD Discs | Accessories | Documentation | Operating Systems
Photo                        Camera Batteries | Camera Media | Camcorders | Cameras
Peripherals and Accessories  CD-ROM | Accessories | Modems/Fax | Monitors | Printer Supplies | Memory
Hardware                     Portable PCs | Desktop PCs

Now, imagine we would like to add the sum of the list_prices to this query … to get the data consistent, we had to do the sum on the list_prices in two steps. First per distinct prod_category/prod_subcategory to get a deduplicated list of subcategories and then on the category level using the pre-calculated sums.

select PROD_CATEGORY
     , listagg(PROD_SUBCATEGORY,' | ') as SubCategories
     , sum(SumProdListPrice)           as SumProdListPrice
  from (select PROD_CATEGORY
             , PROD_SUBCATEGORY
             , sum(PROD_LIST_PRICE) as SumProdListPrice
          from SH.PRODUCTS
         group by PROD_CATEGORY
                , PROD_SUBCATEGORY) p1
 group by PROD_CATEGORY;

Oracle 19c spelling

Now this got much easier with Oracle 19c, the only thing we have to do is adding distinct to the listagg function

select PROD_CATEGORY
     , listagg(distinct PROD_SUBCATEGORY,' | ') as SubCategories
     , sum(PROD_LIST_PRICE)                     as SumProdListPrice
  from SH.PRODUCTS
 group by PROD_CATEGORY;

If you need to have the list ordered, then you have to use the within group syntax.

select PROD_CATEGORY 
     , listagg(distinct PROD_SUBCATEGORY,' | ') within group (order by PROD_SUBCATEGORY) as SubCategories  
     , sum(PROD_LIST_PRICE) as SumProdListPrice
  from SH.PRODUCTS
 group by PROD_CATEGORY;

WITHIN GROUP clause

Starting with version 19c the WITHIN GROUP clause is optional and only needed if the list has to be ordered.


Conclusion

Adding the possibility to remove duplicates from the list using the distinct keyword looks like a tinyness. But as most of the time duplicates in the list are not wanted this tinyness is really useful.

ORACLE 18c – ALTER SEQUENCE RESTART

20 Tuesday Feb 2018

Posted by troroblog in ORACLE, ORACLE 18c, SQL

≈ 3 Comments

Tags

Sequence, SQL

How many times have we seen the request to “set back” or “set forward” a sequence to its original value. Reasons for doing this have been e.g. having a sequence, that start every time period (e.g. month) new or a heavy import has been done and the sequence should now be set to the max-value+1 of the rows inserted to the table.

There have been various ways to solve this kind of issues.

  • To set the sequence back normally we dropped and recreated it (and hopefully did not forget the grants).
  • To set the sequence to a new high value, we just built a loop or changed the stepwidth to get the sequence to the desired value.

All those workarounds are no longer needed with ORACLE 18c as there is a new option that we can use to handle both cases.


Test 1:

After having consumed 500 number from the sequence, we would like to set it back to it’s original value (1).

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501
 
ALTER SEQUENCE MonthlySeq RESTART;

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
      1

So this works as expected.


Test 2:

After having consumed 500 number from the sequence, we would like to set it forward to 1500.

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501
 
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
   1500

Excellent….


Test 3:

I expect the ALTER SEQUENCE statement to commit open transactions…but just to be sure.

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

CREATE TABLE t (x NUMBER);

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501

INSERT INTO t VALUES (1);
 
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

ROLLBACK;

SELECT *
 FROM t;

X
-
1

ok, would have been nice if not, but it’s what was expected.


Conclusion

Still this is a nice feature available in ORACLE 18c but not in the documentation (SQL 

There you will find:

To restart the sequence at a different number, you must drop and re-create it.

and

You will also miss the RESTART option in the syntax diagram of the ALTER SEQUENCE command…


Update (6.1.2020)

After having filed an SR with ORACLE pointing out that the RESTART option is missing in the documentation, I got the following answer:

It (ALTER SEQUENCE … RESTART) was created for internal operations –
Requested to use (ALTER SEQUENCE) as per documentation.

Which means for me, that the restart option is not supported but as oracle is using it internally …

MERGE – DELETE

29 Friday Dec 2017

Posted by troroblog in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

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.

 

ORACLE 12.2 – External Table Enhancements – Overriding Parameters

26 Wednesday Jul 2017

Posted by troroblog in ORACLE, ORACLE 12c Release 2, SQL

≈ 1 Comment

Another cool feature of 12.2 is the possibility to override defined parametrs of an external table when accessing it. The following properties may be overwritten:

  • default directory
  • location (file-name)
  • access parameters like badfile, logfile and discardfile
  • reject limit

This enables us to change e.g. the name of the file to be read without having to do an ALTER TABLE statement (and therefore doing an impicit commit). Furthermore the modification done this way is effective for the current statement only.

So imagine we have an external table defined like this:

CREATE TABLE employee_import (    
   department_id  NUMBER(2,0)  
  ,first_name     VARCHAR2(20 BYTE)  
  ,last_name      VARCHAR2(20 BYTE)  
  ,email          VARCHAR2(25 BYTE)  
  ,phone_number   VARCHAR2(20 BYTE)  
  ,hire_date      DATE
  ,job_id         VARCHAR2(10 BYTE)
  ,salary         NUMBER(8,2)  
  ,commission_pct NUMBER(2,2)
) 
ORGANIZATION EXTERNAL (    
   TYPE ORACLE_LOADER   
   DEFAULT DIRECTORY LTROT_VM_DIR   
   ACCESS PARAMETERS (       
      RECORDS DELIMITED BY NEWLINE      
      LOGFILE  LTROT_VM_DIR:'emp20_wrong.log'      
      BADFILE  LTROT_VM_DIR:'emp20_wrong.bad'      
      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
      MISSING FIELD VALUES ARE NULL (         
         department_id
        ,first_name
        ,last_name
        ,email
        ,phone_number
        ,hire_date date 'DD.MM.YYYY'
        ,job_id
        ,salary
        ,commission_pct
      )
   )
   LOCATION('emp20.txt')
)
REJECT LIMIT UNLIMITED ;

Now, if we would like to access a different os file (having the same structure) than “emp20.txt” we can easily achieve this with the following query:

SELECT *
 FROM employee_import 
 EXTERNAL MODIFY (LOCATION ('em10.txt'));

The parameters that can be changed this way are:

  • Default Directory
  • Location
  • Access Parameters
  • Reject Limit

If the external is partitioned only table level (not partition level) clauses may be overwritten.


Use-Case

Now, looking at the list of parameters that can be changed it should be possible to change the name of the bad-/log-file when loading the emp-os file and afterwards having an external table where i can read the bad-/log-file passing the names that i used before….

Something like this:

Generic External Table to read log/bad files

CREATE TABLE generic_file (
    message VARCHAR2(4000 BYTE)
) 
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
   DEFAULT DIRECTORY LTROT_VM_DIR
   ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      NOLOGFILE
      NOBADFILE
      NODISCARDFILE
      FIELDS (
         message CHAR(4000)
      )
   )
   LOCATION('emp20.bad')
)
REJECT LIMIT UNLIMITED;

So when I read the emp os-file I alter the name of the bad and the log file using the external modify clause:

SELECT * 
  FROM employee_import
EXTERNAL MODIFY (
   ACCESS PARAMETERS (
      BADFILE 'abadfile.bad'
      LOGFILE 'alogfile.log'));

And after that I should be able to access the bad-/log-file using my generic external table.

SELECT * 
  FROM generic_file 
EXTERNAL MODIFY (
   LOCATION('abadfile.bad'));

Unfortunately I cannot see any data, as the log-/bad-files are locked and empty (Linux Environment) so I need to commit first to release the locks. Afterwards I can read the log-file as well as the bad-file.

SELECT * 
 FROM generic_file 
EXTERNAL MODIFY (
 LOCATION('alogfile.log'))
 WHERE message IS NOT NULL;

MESSAGE
---------------------------------------------------------------------------
 LOG file opened at 07/26/17 07:25:18
Field Definitions for table EMPLOYEE_IMPORT
 Record format DELIMITED BY NEWLINE
 Data in file has same endianness as the platform
 Rows with all null fields are accepted
 Fields in Data Source:
 DEPARTMENT_ID CHAR (255)
 Terminated by ","
 Enclosed by """ and """
 Trim whitespace same as SQL Loader
 FIRST_NAME CHAR (255)
 Terminated by ","
 Enclosed by """ and """
 Trim whitespace same as SQL Loader
 LAST_NAME CHAR (255)
 Terminated by ","
...

Conclusion

Nice!

← Older posts

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.