Oracle Monday is not always 1

As some of you might have discovered, especially in the non-GB/US countries, in Oracle the result of

to_char(to_date('20160102','yyyymmdd'),'d')

is not always 1. It depends on the settings of your client, not even of your database or server. If the client is set to territory FRANCE, it will be 1 indeed. But if the client is set to territory AMERICA, the same statement will result in 2!

To know what the client has been set to, you can execute this simple query:

select *
from nls_session_parameters;

To switch to another territory, just use these examples:

alter session set nls_territory = AMERICA;

or

alter session set nls_territory = FRANCE;

Compare the nls_session_parameters after each of these.

The nls_session_parameters do not show the first day of the week for that specific territory.

Likewise for the date of the first day of a week that a certain date is in. You have two options:

trunc(testdate, 'day')

results in Monday or Sunday depending on the client’s territory

trunc(testdate, 'iw')

always results in the Monday based on ISO definitions, independent of the client’s territory.

So if the client’s territory is FRANCE or THE NETHERLANDS, then both will result in the same date. But for territory AMERICA these two will differ.

Combining all this knowlegde, it is possible to create a statement that will always result in the same Monday no matter what territory, without using the NLS Session Parameters (as far as we know ūüėČ :

to_char(testdate, 'd') + (trunc(testdate, 'day') - trunc(testdate, 'iw'))

This should always return the weekday number based on the ISO definitions, i.e. Monday=1, Tuesday=2…Sunday=7.

In the next attachment you can find my SQL script with more interesting ways to apply this “territory offset” and even a supposed-to-be-test that runs forever, but I don’t understand why…

mon_is_always_1

Oracle previous_day function

Oracle has a SQL function NEXT_DAY (date, char) which returns the date of the first weekday named by char that is later than the date. But Oracle does not have a PREVIOUS_DAY, for instance to get the Monday of the current week.

Now to get the Monday of the week a certain date is in, you can easily use TRUNC(date,’iw’). And you think perhaps that TRUNC(date,’day’) does the same? Well, that depends on your NLS_TERRITORY setting, because e.g. in America the first day of the week is Sunday, but in The Netherlands it is Monday!

So to get last Monday’s date is not such a great deal. How about Tuesday? Or any other day of the week?

I created next function PREVIOUS_DAY, which is the opposite of Oracle’s NEXT_DAY. It has the same parameters and is independent of the NLS_TERRITORY.

-- The counterpart of the standard Oracle NEXT_DAY function.
create or replace function previous_day
    (pi_date in date := sysdate
    ,pi_day  in varchar2 := to_char(sysdate,'dy','nls_date_language = english')
    )
return date
deterministic
as
    l_date    date;
begin
    select (pi_date - level) as previous_day
    into l_date
    from dual
    where to_char((trunc(pi_date) - level),'dy','nls_date_language = english') = lower(substr(pi_day,1,3))
    connect by level <= 7;
    return l_date;
end previous_day;
/

 

Oracle export schema DDL

You can use the SQL/Developer wizzard for downloading DDL to a file. But you need to remember your preferences, select a file, assign proper name, etc. etc. etc.¬† And it’s not complete since you cannot select jobs to export (version 4.1), as we found out the hard way…

SQL/Plus to the rescue! Attached is a file I compiled based on several information on the internet. You only have to start a SQL/Plus session and run the file (rename it from qa_export_db_ddl.txt to qa_export_db_ddl.sql) with one parameter: The schema you want to be exported. Tadaaaa!

Download qa_export_db_ddl.txt here.

Debug Oracle PL/SQL

As a true developer you realy need a good debugging tool. After many years of DBMS.OUTPUT and logging with autonomous transaction, I finally got the privilege to do it the right way: Debugging PL/SQL in SQL/Developer. Thanks to my current employer and Jeff Smith.

I made a compilation that can help you up-and-debugging in no time. You can download it here (PDF).

Debugging PL/SQL in SQL/Developer

Debugging PL/SQL in SQL/Developer

Oracle Conditional Compilation

Although it’s already there since ages, I never got the time to figure it out. But now I finally have assembled a neat solution for something that I need every now and then: compile pieces of code based on compile-time conditions. Like code that you want in your development database, but certainly not in production. You can find the code here to download:¬†demo_conditional_compilation

It consists of three files:

  • The DDL for a package cntl_cond_comp
  • An example for an anonymous PL/SQL block
  • An example for a stored procedure

It also contains the links to documents that I used to compose this.

Have fun!

Herman Mol is powered by WordPress.  Staypressed theme by Themocracy