How to Convert ISO Weeks to Dates in Oracle

Converting a date to an ISO year-week string is easy in Oracle:

>SELECT TO_CHAR(DATE '2012-03-11', 'IYYY-IW') FROM dual;

2012-10

One should think that converting an ISO year-week string back to a date (denoting the first day in this week) should also be easy:

>SELECT TO_DATE('2012-10', 'IYYY-IW') FROM dual;

ORA-01820: format code cannot appear in date input format
01820. 00000 -  "format code cannot appear in date input format"
*Cause:    
*Action:

So, obviously this is not supported yet (speaking of version 11g R2).

Therefore, I decided to write a conversion function myself. Here it is:

CREATE OR REPLACE FUNCTION iso_week_to_date
  (iso_year IN INTEGER, -- full ISO year, e.g., 2012
   iso_week IN INTEGER) -- ISO week
  RETURN DATE
IS
  jan4_of_iso_year DATE;
  first_day_of_iso_year DATE;
  iso_date DATE;
  iso_date_iso_year INTEGER;
BEGIN
  -- Find the first day of iso_year
  -- (= the Monday of the week containing January 4th)
  jan4_of_iso_year := TO_DATE(iso_year || '-01-04', 'YYYY-MM-DD');
  first_day_of_iso_year := TRUNC(jan4_of_iso_year, 'IW');
  
  -- Add the ISO week (in days)
  iso_date := first_day_of_iso_year + 7 * (iso_week - 1);
  
  -- Check whether iso_week is a valid ISO week
  -- (= whether the Thursday of the week containing iso_date is contained in the year iso_year)
  iso_date_iso_year := TO_CHAR(iso_date, 'IYYY');
  IF iso_date_iso_year <> iso_year THEN
    RAISE VALUE_ERROR;
  END IF;
  
  RETURN iso_date;
END;

A quick test:

SELECT iso_week_to_date(2012, 10) FROM dual;

2012-03-05 00.00.00

Another one:

SELECT iso_week_to_date(2012, 1234) FROM dual;

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "JSELKE.ISO_WEEK_TO_DATE", line 23
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

Perfect. 🙂

Have fun!

For those prefering plain SQL:

SELECT TRUNC(TO_DATE(iso_year || '-01-04', 'YYYY-MM-DD'), 'IW') + 7 * (iso_week - 1) FROM dual;

--Example (iso_year = 2012, iso_week = 10):
SELECT TRUNC(TO_DATE(2012 || '-01-04', 'YYYY-MM-DD'), 'IW') + 7 * (10 - 1) FROM dual;

2012-03-05 00.00.00

UPDATE: Changed TRUNC(..., 'D') to TRUNC(..., 'IW') to remove dependency from the database parameter NLS_TERRITORY.

Posted in Oracle | 19 Comments

Stripping (HTML) tags in XSLT

As there doesn’t seem to be any built-in function in XLST for stripping tags from strings (e.g., to remove all markup from a piece of HTML-formatted text), people came up with a recursive template-based solution, which has been posted several times on the web (e.g., here). However, I found this approach hard to use when the string to be cleaned from all tags already is stored in a variable or is created by using a xsl:value-of statement. Therefore, I transformed the existing template-based solution into a function-based one, which is a bit shorter and easier to use. Here it is:

<xsl:function name="util:strip-tags">
  <xsl:param name="text"/>
  <xsl:choose>
    <xsl:when test="contains($text, '&lt;')">
      <xsl:value-of select="concat(substring-before($text, '&lt;'),
        util:strip-tags(substring-after($text, '&gt;')))"/>
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="$text"/>
    </xsl:otherwise>
  </xsl:choose>
</xsl:function>

Note: Don’t forget to declare a namespace for this function (called util in the above code).

UPDATE: From the comments I see that an example might be helpful here. Well, here it is:

example.xsl:

<xsl:stylesheet version="2.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:util="http://whatever">

<xsl:output method="text"/>

<xsl:function name="util:strip-tags">
  <xsl:param name="text"/>
  <xsl:choose>
    <xsl:when test="contains($text, '&lt;')">
      <xsl:value-of select="concat(substring-before($text, '&lt;'),
        util:strip-tags(substring-after($text, '&gt;')))"/>
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="$text"/>
    </xsl:otherwise>
  </xsl:choose>
</xsl:function>

<xsl:template match="/">
<xsl:value-of select="util:strip-tags(/content)"/>
</xsl:template>

</xsl:stylesheet>

input.xml:

<?xml version="1.0" encoding="UTF-8"?>
<content>
test <some><nice><tags>xyz</tags></nice></some> test
</content>

Now I use the SAXON XSLT processor to strip the tags (inside the content tag) from the input file. Note that you might need to change the path to the JAR file to make this example work for you:
java -jar /usr/share/java/saxon.jar input.xml example.xsl

The output:


test xyz test

Posted in XML | 6 Comments

Where to Download the DB2 JDBC driver

When you want to access a DB2 database from Java, you usually need IBM’s JDBC driver for DB2. In the past, I (and some other people I know) always had trouble in finding out where to download the recent version of the driver. To solve this issue once and for all, I just took a deep dive into IBM’s website. There I found the following page, which seems to be ultimate resource to downloading DB2’s JDBC driver: https://www-304.ibm.com/support/docview.wss?uid=swg21363866.

Posted in DB2 | 2 Comments

Steve Jobs’ Macworld 2007 Keynote (iPhone Introduction) in High Quality

For a course about giving good presentations we are currently offering at work, I was looking for a high-quality version of Steve Jobs’ famous iPhone introduction at Macworld 2007. When searching on YouTube, I only got videos in awful resolutions. Even the “official” video stream offered by Apple isn’t too impressive (direct link to the stream). I spent some time on searching for a better version and finally came up with the following (if you want to download these files, you can use tools such as youtube-dl):

Part 1
Part 2
Part 3
Part 4
Part 5
Part 6
Part 7
Part 8
Part 9
Part 10
Part 11

Posted in Uncategorized | 3 Comments

VMware Workstation 7.1.2 and Fedora 14

UPDATE: VMware Workstation 7.1.3 works out of the box with Fedora 14.

As always, VMware has not been able to keep up with recent changes in Linux (kernel 2.6.35 and up). This becomes apparent, when trying to run VMware Workstation in the new Fedora 14. Compiling the VMware kernel module fails with errors like the following:

error: implicit declaration of function ‘iommu_unmap_range’
error: ‘struct sock’ has no member named ‘sk_sleep’

Combining the hints from here and here, I arrived at the following solution, which patches VMware’s kernel module:

cd /tmp
tar xvf /usr/lib/vmware/modules/source/vmmon.tar -C /tmp
perl -pi -e 's,_range,,' vmmon-only/linux/iommu.c
sudo tar cvf /usr/lib/vmware/modules/source/vmmon.tar vmmon-only

cd /tmp
tar xvf /usr/lib/vmware/modules/source/vsock.tar -C /tmp
perl -pi -e 's,sk->compat_sk_sleep,compat_sk_sleep(sk),' vsock-only/linux/af_vsock.c
perl -pi -e 's,listener->compat_sk_sleep,compat_sk_sleep(listener),' vsock-only/linux/af_vsock.c
sudo tar cvf /usr/lib/vmware/modules/source/vsock.tar vsock-only

sudo vmware-modconfig --console --install-all

Posted in VMware | 26 Comments

Running MATLAB scripts on a remote server

I often have to run some experiments with large data sets in MATLAB. Therefore, it comes handy to do this on a machine that is different from my notebook. The following bash script remotely runs a given MATLAB script called matlabbg and writes all console output to some logfile:

#!/bin/bash
nohup matlab -nodisplay -r "run $1" >$2 2>&1 &

To run the MATLAB script myscript.m in the background and write the output to myscript.log, simply do the following:

matlabbg myscript.m myscript.log

Posted in MATLAB | 2 Comments

DB2: How to Add an Identity Column to an Existing Table

Adding generated key columns to existing tables seems to be complicated in DB2. The following code adds a new generated primary key id to the table tablename. Granted, the code is ugly, but it works. I was not able to come up with a better solution in time.


ALTER TABLE tablename ADD COLUMN id INTEGER NOT NULL WITH DEFAULT 0
ALTER TABLE tablename ALTER COLUMN id GENERATED BY DEFAULT AS IDENTITY
REORG TABLE tablename
UPDATE tablename SET id = DEFAULT
ALTER TABLE tablename ALTER COLUMN id SET PRIMARY KEY

EDIT: I tested this code with DB2 9.7, but it should work on earlier versions of DB2.

Posted in DB2 | 5 Comments

Windows 7: Persistent Storage of Login Credentials for Samba Shares

I have a Windows 7 installation running in a VMware virtual machine. Within Windows, I mounted a network drive pointing to a Samba share within our local network, which is secured by username/password. However, although I told Windows several times to remember my login credentials (username/password) when it asked for them, Windows stored them only for the current session. This can be seen using Windows’ credential manager (Control Panel\User Accounts and Family Safety\Credential Manager). Therefore, the data was lost after each reboot and I had to enter them again.

There is a simple (but strange) solution to this problem: Instead of entering just your username when asked for, enter SERVERNAME\USERNAME. Adding the server name as “remote domain” makes Windows think that you are located within a “trustworthy” enterprise environment. Then, your login credentials are stored persistently, thus surviving reboots. Thanks to Microsoft for this intuitive mechanism.

Posted in Uncategorized | 3 Comments

How to look up SQL error codes quickly in DB2

Quite regularly DB2 complains about my SQL statements and usually returns a cryptic error message including an even more cryptic SQL error code. What I normally do in those situations is looking up the error code in the DB2 Information Center, which takes some time. I just found out that there is an easier way to get at least a short explanation of what an error code means. For example, to look up the error code -161, you can use the following SQL query:

VALUES SQLERRM(-161)

Result:

1
------------------------------------------------------------------------------------------------------
SQL0161N  The resulting row of the insert or update operation does not conform to the view definition.

Nice.

Posted in DB2 | 2 Comments

What SQL statements are currently running on my DB2 database server?

To see some detailed info about which users are currently connected to your DB2 database server (including the SQL statements they are running), you can use the following query (tested with DB2 9.7, fixpack 3a):

    WITH a AS (SELECT a.application_handle, a.system_auth_id, a.application_name, a.client_wrkstnname, a.workload_name, a.workload_occurrence_state, b.activity_id, b.uow_id, b.local_start_time, b.activity_state, b.activity_type, b.total_cpu_time, b.rows_read, b.rows_returned, b.query_cost_estimate, b.direct_reads, b.direct_writes
                 FROM TABLE(wlm_get_service_class_workload_occurrences_v97(NULL, NULL, NULL)) a
                      LEFT JOIN TABLE(wlm_get_workload_occurrence_activities_v97(NULL, NULL)) b ON a.application_handle = b.application_handle),
         b AS (SELECT t.*
                 FROM TABLE(mon_get_activity_details(FOR EACH ROW OF (SELECT application_handle, uow_id, activity_id, NULL FROM a WHERE activity_id > 0))) t),
         c AS (SELECT x."activity_id" activity_id, x."uow_id" uow_id, x."stmt_text" stmt_text
                 FROM b,
                      XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'), '$m/db2_activity_details' PASSING XMLPARSE(DOCUMENT b.details) AS "m" COLUMNS
                                                                                                             "activity_id" INTEGER PATH 'activity_id',
                                                                                                             "uow_id" INTEGER PATH 'uow_id',
                                                                                                             "stmt_text" VARCHAR(1024) PATH 'stmt_text') x)
  SELECT a.*, TRANSLATE(c.stmt_text, ' ', CHR(10)) stmt_text
    FROM a
         LEFT JOIN c ON a.activity_id = c.activity_id AND a.uow_id = c.uow_id
   WHERE stmt_text IS NULL OR SUBSTR(stmt_text, 1, 40) <> 'WITH a AS (SELECT a.application_handle, '
ORDER BY application_handle, a.activity_id

Posted in DB2 | 6 Comments