Oracle character set conversion downgrade checkup with utl_i18n

Doing an export/import or a ctas or an oci/jdbc client action into a database that has smaller sized character set, multi- to single-byte for example, will raise the problem of information loss in terms of character data. The information loss, however, is not limited to loosing one or the other character. Some characters may also become replaced by a default or a best guess replacement character in the target character set automatically.
Profound analysis of the outcome of the character set conversion might also cause difficulties when only the source database is already available and just an estimation of the information loss is what you need. You may come up with good old convert() to execute an input/output compare but aside from using convert() is discouraged with the latest releases of Oracle, convert() will also fracture the strings on input as soon as the first multibyte character appears. convert() will alert you of some problem but it will not tell you why or even better: what character is (first) the stumbling block.


The 11g pivot query and the group by clause

There has been this long awaited pivot query feature available since 11g that saves us time in writing those infamous decode / group by queries (see expert one-on-one from Tom Kyte or the web for examples) to flip (grouped by) leading row values to column names.

Searching the web for application examples, however, does only reveal this emp-table stuff, e.g. on, that still contains the well known group by with the base query.

  FROM (SELECT job, deptno, sum(sal) sal FROM emp GROUP BY job, deptno)
         PIVOT ( sum(sal) FOR deptno IN (10, 20, 30, 40) );

JOB               10         20         30         40
--------- ---------- ---------- ---------- ----------
CLERK           1300       1900        950
SALESMAN                              5600
PRESIDENT       5000
MANAGER         2450       2975       2850
ANALYST                    6000

Jep so, what I wanted to achive was a simple flip of rows of status values and counts into columns of status names and count values based on some data like the following.


Just another discussion of unicode character conversion for oracle

Running an oracle in multibyte unicode storage like AL32UTF8, disregarding the char and byte column length topic, is actually no different from the old days single byte storage, e.g. in WE8MSWIN1252. However, any job that includes sort of character conversion in terms of character, decimal and hex reprasentations, does require at least a basic understanding of available unicode storage options and sql functions with oracle. To me, the main reason of common problems is the mismatch being imposed by oracle’s impure layout of the sql functions ascii, asciistr, chr, nchr and unistr concerning the database and the national characterset.

The following has been executed on a 11gR2 on win64 using these database and the national characterset storage options.

SQL> select * from NLS_database_PARAMETERS where parameter like '%CHARACTERSET%';
PARAMETER                VALUE
---------                -----


Implementing bottom-up path traversal for hierarchical tables

Update: There is a newer and imho better approach available with Bottom-up hierarchical root path query recipe in oracle .

oracle supports hierarchical queries on tables by the start with and connect by clauses. this usually preassumes that the rows of some table are chained by a value in column dad_id of some row pointing to a value in column son_id in a parent row, where son_id also denotes the primary key in the table. the root rows of the tree being set up that way, furthermore have a dedicated value in column dad_id. for positive, evolving numbers (id’s ;-), one may just choose 0 here. so far, a typical hierarchical query for a (male) family tree may look like this:


Getting the utc and unix time in milliseconds from oracle

It is quite necessary these days, especially in the globalization/24*7-worlds to pass around (unix-)timestamps in a form being defined as:

The difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.

Indeed there is a dedicated method for this in the Java editions since long being declared as public static long currentTimeMillis() of class System. Also, Web-environments based on Javascript and the like offer native logic to transform a given UTC-timestamp to the some clients timezone and timestamp, respectively.

Delivering that msecs-utc-value from Oracle may seem straightforward at first, but may become more and more a jungle of options and proposed possibilities of conversion, extraction and so on. It is especially puzzling iff one actually has no timestamp with time zone instance at hand and needs to start off from existent date or timestamp w/o time zone column data. That is, data having had no time zone stored upon inserting or updating, such that calculating back to UTC is impossible without an edicated guess.

Hence, the only option for original date or timestamp w/o time zone data is using the Oracle from_tz function (save yourself from checking out new_time, it supports a very limited number of time zones only), passing in a timestamp w/o time zone and a time zone region known to Oracle. Do not just pass in a derived time zone value, e.g. sessiontimestamp, because Oracle takes this for granted without actually looking back in time, regarding daylight saving and things. For example, in 1978 daylight saving has not yet been introduced in Germany but in 1988 it was in use:

-- what's in sessiontimezone currently ?
select sessiontimezone from dual;

-- that's ok, an educated guess on the original time zone
-- actually the only option you have
select from_tz(cast(to_date('01.08.1978 12', ' hh24') as timestamp),
    'Europe/Berlin') as xyz1,
  from_tz(cast(to_date('01.08.1988 12', ' hh24') as timestamp),
    'Europe/Berlin') as xyz1
from dual;
01/08/1978 12:00:00,000000 +01:00	01/08/1988 12:00:00,000000 +02:00

-- possible and likely given in the oracle docs but wrong, oracle just follows
--   your specification without actually looking back in time
-- iff you do this in winter, the result may become wrong again in another
--   way because sessiontimezone will now read +01:00
select from_tz(cast(to_date('01.08.1978 12', ' hh24') as timestamp),
    sessiontimezone) as xyz1,
  from_tz(cast(to_date('01.08.1988 12', ' hh24') as timestamp),
    sessiontimezone) as xyz1
from dual;
01/08/1978 12:00:00,000000 +02:00	01/08/1988 12:00:00,000000 +02:00

Once you have derived this time zone aware value, retieving the UTC time is just employing a supplied function and a cast like this:

select cast(sys_extract_utc(
  from_tz(cast(to_date('01.08.1978 12', ' hh24') as timestamp),
    'Europe/Berlin')) as date) as xyz1
from dual;

Finally, substract any time being passed before january 1st 1970 and calculate the msecs-value, preassuming that one Oracle day is, in numbers, exactly = 1.

select to_number(cast(sys_extract_utc(
  from_tz(cast(to_date('01.08.1978 12', ' hh24') as timestamp),
    'Europe/Berlin')) as date) - to_date('01.01.1970',''))
    * (24 * 60 * 60 * 1000) as xyz1
from dual;

have fun.