Who or what is nwtraders.msft?

I was searching for this answer and couldn’t find it quickly, thus decided to create this post. I keep running into the nwtraders.msft hostnames because I’m using CentOS images in Vagrant.., to be precise; the london.nwtraders.msft hostname..

NWTraders is a fictional company, created by Microsoft to showcase Microsoft Access.

Continue reading “Who or what is nwtraders.msft?”

Select an XML tag or node using Oracle PL SQL

Hi guys,

I’ve been using this trick for a while and it’s quite useful when querying Oracle Service Bus logs. I found myself trying to explain this one to a colleague and thought it made a nice post :-)

Let’s start with the basic command:

Which translates into:

FROM xml_table

Note: the first argument is being cast from CLOB to XMLTYPE and that you can keep adding namespaces at the end by adding commas.

I’ve added three rows in my table “XML_TABLE” for this example:

<book category="WEB">
  <title lang="en">Learning XML</title>
  <author>Erik T. Ray</author>

<book category="CHILDREN">
  <title lang="en">Harry Potter</title>
  <author>J K. Rowling</author>

<book category="WEB">
  <title lang="en">Learning XML</title>
  <author>Erik T. Ray</author>

Next we’ll query our XML_TABLE:

  EXTRACTVALUE( xmltype(xmlval), '/book/title' )  AS title,
  EXTRACTVALUE( xmltype(xmlval), '/book/author' ) AS author,
  EXTRACTVALUE( xmltype(xmlval), '/book/year' )   AS year,
  EXTRACTVALUE( xmltype(xmlval), '/book/price' )  AS price
FROM xml_table;

Which results in this output:

Workspace 1_034

Awesome, right!? :-)

Continue reading “Select an XML tag or node using Oracle PL SQL”

Oracle SQL Developer 4 does not run on Oracle Java 7 on Ubuntu 14.04

Wow, ain’t this awkward :-). I cannot run Oracle SQL Developer 4 (4.0.2) on Ubuntu with Oracle JDK 7..

To be complete: when running SQL Developer with JDK 7 from Oracle itself, displays the following error;

joris@dipshit:~/programs/sqldeveloper$ ./sqldeveloper.sh
Oracle SQL Developer
Copyright (c) 1997, 2014, Oracle and/or its affiliates. All rights reserved.
LOAD TIME : 968#
# A fatal error has been detected by the Java Runtime Environment:
# SIGSEGV (0xb) at pc=0x6aa69be0, pid=9537, tid=1836366656
# JRE version: Java(TM) SE Runtime Environment (7.0_65-b17) (build 1.7.0_65-b17)
# Java VM: Java HotSpot(TM) Server VM (24.65-b04 mixed mode linux-x86 )
# Problematic frame:
# C 0x6aa69be0
# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again
# An error report file with more information is saved as:
# /home/joris/programs/sqldeveloper/sqldeveloper/bin/hs_err_pid9537.log
# If you would like to submit a bug report, please visit:
# http://bugreport.sun.com/bugreport/crash.jsp
/home/joris/programs/sqldeveloper/sqldeveloper/bin/../../ide/bin/launcher.sh: line 1193: 9537 Aborted (core dumped) ${JAVA} "${APP_VM_OPTS[@]}" ${APP_ENV_VARS} -classpath ${APP_CLASSPATH} ${APP_MAIN_CLASS} "${APP_APP_OPTS[@]}"

Solution: Run Oracle SQL Developer with OpenJDK

First we’ll need to install OpenJDK:

sudo apt-get install openjdk-7-jdk 

Then we’ll need to change the path which SQL Developer uses. This was asked once when you first started it and it is saved in the following path:


The file [[ product.conf ]] contains the value SetJavaHome, we need to change this to the OpenJDK path;

If you're running 32 bit Ubuntu:

SetJavaHome /usr/lib/jvm/java-7-openjdk-i386

Or if you're running 64 bit Ubuntu:

SetJavaHome /usr/lib/jvm/java-7-openjdk-amd64

After saving this change, you can start SQL Developer on Ubuntu 14.04 and it will use OpenJDK 7, without changing your regular Java settings!

Oracle SQL – BLOB to XML Type ( ORA-06502 PL/SQL : numeric or value error : raw variable length too long )

I am working with a database schema where xml content is being stored in a BLOB and I’m working on querying that XML.

When you cast the BLOB to VARCHAR, you’ll run into the limit of 2000 characters with the following error:

ORA-06502 PL/SQL : numeric or value error : raw variable length too long

So this trick came in handy:


Please be very aware of the character set that you are using, you can enter a world of hurt when you’re using the wrong one. The number 871 is the character set UTF8, which we are using.

For other character sets, check http://www.mydul.net/charsets.html

How to try to drop a table without raising exception ORA-00942: table or view does not exist

With the piece of code below it’s quite easy to first make sure your table exists before dropping it, so you’re not running into SQL Error: ORA-00942: table or view does not exist

Script explanation: It will check if the table is present before trying to drop it.

-- Drop table
 l_count NUMBER;
 INTO l_count
 WHERE table_name = 'TABLE_NAME'
 AND owner = 'USERNAME';
 IF l_count > 0 THEN

Mark or colour NULL values in SQL Developer

By default SQL Developer shows null values in the following way:

Screenshot - SQL Developer - Default Null value
Screenshot – SQL Developer – Default Null value

This is not very notable, so we’ll change it to the following markup:

Screenshot - SQL Developer - What an awesome NULL color!
Screenshot – SQL Developer – What an awesome NULL color!

To do this, we need to go to Preferences – Database – Advanced and change the value for “Display Null Using Background Color”

Screenshot - SQL Developer - Settings for colouring NULL values
Screenshot – SQL Developer – Settings for colouring NULL values

Unlock HR user and schema in Oracle Database

In an Oracle database there is a sample user called “HR”. This user comes with a schema which contains data which is quite convenient for demo purposes.

You can unlock this user using the following steps:

  1. Connect to your database with the system user
  2. Execute the following command (Note: replace “password” with your password)
  3. Log in to the database with the HR user and your specified password

You can use this user for tutorials / demo’s in almost all Oracle databases since the tables are all equal.

Screenshot - Tables, Views and Procedures for the HR user
Screenshot – Tables, Views and Procedures for the HR user

Ps. I’ve posted this small tutorial mainly because it will serve as a prerequisite for other tutorials.

Schema size in Oracle DB

I’ve been running out of disk space on my virtual machine and didn’t want to mount another volume to add more room (because the problem isn’t more room; it’s that I’m sloppy with disk space) :-)

So I was looking around on how to easily query for the schema size to find out which little project of mine was taking up way too much room. I found a nice solution on this blog:

SELECT tablespace_name
 , SUM(bytes)/1024/1024 AS total_size_mb
 FROM dba_segments
 WHERE owner = Upper('&User_Name')
 GROUP BY owner
 , rollup(tablespace_name);

When you run the query it’ll ask you which user you want to check:

Screenshot - SQL Developer - Query for schema size
Screenshot – SQL Developer – Query for schema size

SQL Developer, Comma’s before columns!

I’ve been using SQL Developer a lot lately for data analysis and I’ve been getting used to the auto formatting to easily create well readable code out of my garbled-“I’m in a hurry”-SQL-mess.

This works like a charm, except for the comma’s which kept showing up at the end of the column names after the auto formatting, a small annoyance which is easily solved with this trick.

This is the default auto-formatting which is used by SQL Developer:

SELECT t1.column1,
FROM table1 t1
JOIN table2 t2
ON t1.value1 = t2.value2;

You can change this behaviour by accessing the following settings:

Tools -> Preferences -> Database -> SQL Formatting -> Oracle Formatting -> Press button “Edit”

In the Oracle Formatting window: Line-breaks -> Select “Before comma” and deselect “After comma”


After this trick, the default formatting will have this code as a result:

SELECT t1.column1
, t1.column2
, t2.column3
FROM table1 t1
JOIN table2 t2
ON t1.value1 = t2.value2;

Happy querying! :-)