Posts tagged: ORACLE

Use SAVEPOINT in a DML loop

Imagine the following situation: You must process a number of records (in a loop or cursor) with the necessary updates / inserts / deletes. But you want that if it goes well that for that record the changes are saved and if it goes wrong for a record that the updates are then reversed. (In short: keep updates that are successful and roll back updates that fail – or possibly give that wrong record an “error status” or notification).

You can find my solution at https://github.com/HermanMol/oracle_scripts/blob/master/rollback_savepoint_in_loop_exceptions.sql

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

Herman Mol is powered by WordPress.  Staypressed theme by Themocracy