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:
extractvalue
Which translates into:
[code language=”sql”]
SELECT EXTRACTVALUE(
xmltype(xml_val),
‘/xml-fragment/tns:product’,
‘xmlns:tns="http://example.org/"’,
‘xmlns:ans="http://anothernamespace.org/"’,
‘xmlns:yans="http://yetanothernamespace.org/"’
)
x
FROM xml_table
[/code]
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:
[code language=”XML”]
ROW1:
<book category="WEB">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
ROW2:
<book category="CHILDREN">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
ROW3:
<book category="WEB">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
[/code]
Next we’ll query our XML_TABLE:
[code language=”sql”]
SELECT
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;
[/code]
Which results in this output:
Workspace 1_034
Awesome, right!? 🙂
Continue reading “Select an XML tag or node using Oracle PL SQL”

Can not connect to Virtualbox Guest Oracle Database 12c Developer Day Database VM due to Oracle Linux firewall

It seems there’s a firewall present on the latest Developer Days Database image which I’ve just downloaded from Oracle.

Description:

I enjoy using these images because it is a complete reference install of Oracle Linux, Oracle Database and Oracle SQL Developer (among others). Besides that, it only takes 10 minutes to setup a base install from the image.
Usually I like to connect from my local SQL Developer instead of the one inside the VM.

Problem:

But with this setup I could not connect when I added the NAT Port Forwarding in Virtualbox, it timed out when trying to connect. I could connect from the SQL Developer inside the VM, just not through the NAT port which was forwarded (important: see the bottom of this post to check the NAT Port Forwarding settings in Virtualbox)

Solution:

It’s fairly easy to add a firewall rule which allows access to port 1521 on Oracle Linux, we can even do it with a GUI:
Select Menu “System” – “Administration” – “Firewall”
Screenshot from 2014-08-19 12:55:14Then follow these steps:

  1. Click [Other Ports]
  2. Click [Add]
  3. Select [User Defined]
  4. Enter Port: “1521”
  5. Select Protocol: “TCP”
  6. Click [Apply]
  7. Click [Reload]

Screenshot from 2014-08-19 12:55:55
 
You’ve just added port 1521 to the iptables which makes it okay to connect to this port from another IP outside the local machine.
We can test from SQL Developer, running on the Host:
Oracle SQL Developer : Local - Sys_009
 
And it works! 🙂
 

Extra: NAT Port Forwarding in Virtualbox

Just to be sure, these are the settings you’ll need to set inside the Virtualbox Manager to setup the port forwarding on port 1521 from the guest to the host:
Select the “Settings” of the Developer Day VM and then:

  1. Select “Network”
  2. Click [Port Forwarding]

OTN Developer Day VM_1 - Settings_012
 
Then in the Port Forwarding Rules:

  1. Enter a descriptive name: “DB”
  2. Enter the host port: “1521”
  3. Enter the guest port: “1521”

OTN Developer Day VM_1 - Settings_011
 

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:

~/.sqldeveloper/4.0.0/product.conf

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:

SELECT XMLTYPE( BLOB_FIELD, 871 ) AS AWESOMEXML FROM YOUR_TABLE;

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

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

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,
 t1.column2,
 t2.column3
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! 🙂