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:

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

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:

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>

Next we’ll query our XML_TABLE:

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;

Which results in this output:

Workspace 1_034

Awesome, right!? :-)

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

Advertisements

Dynamic HTTP endpoint in Oracle Service Bus 12c based on values in a database routing table

This article outlines how to set a dynamic endpoint in an OSB HTTP Business Service. The endpoint is retrieved from a routing table which resides in an Oracle 12c database.

Components used for this solution:

  1. Ubuntu Linux 14.04 64bit
    1. JDeveloper, running the Quick Start Oracle Fusion Middleware suite
      1. Oracle Service Bus 12c
      2. Oracle Weblogic 12c
      3. OSB Project location:
        1. https://github.com/visscher/Fusion/tree/master/DBRouting
    2. Oracle Virtualbox Developer Days image for DB 12c, running:
      1. Oracle Database 12c
    3. Oracle SQL Developer 4

This picture shows the running solution in the OSB test console:

Oracle Service Bus Console 12c : Pipeline Testing - DBRouting_v1Pipeline - Google Chrome_019

Database table preparation

We need a routing table in our schema, I’m using this table setup:

CREATE TABLE "C##JORIS"."ROUTINGTABLE"
  (
    "ROUTE" VARCHAR2(50),
    "ENDPOINT" VARCHAR2(100)
  );
Where ROUTE stands for the identifier and ENDPOINT is the HTTP endpoint we try to reach.
I’ve inserted two rows:
Insert into ROUTINGTABLE (ROUTE,ENDPOINT) values ('SalesOrder','http://localhost:7101/salesEndpoint');
Insert into ROUTINGTABLE (ROUTE,ENDPOINT) values ('FinanceReceipt','http://localhost:7101/financeEndpoint');
These two endpoints will point to very simple OSB services which we will create in a moment.
Selection_022

Weblogic configuration: JDBC Data Source

We need to configure a JDBC data source in our Weblogic server, this data source is used by the XQuery function to execute SQL.
Start JDEVeloper, select your integrated Weblogic Server and start it up.
When your domain is started, open the WLS Console:
http://127.0.0.1:7101/console/
Login and open the Data Sources summary:
Selection_020
Navigation in Console: DefaultDomain - Services - Data Sources
Create a new datasource, in my example I use the JNDI name “LocalDB
When you’re done with the configuration, test the datasource to make sure all is well:
Selection_021
The status message will be green and show a check mark if you’ve configured your data source correctly.

JDeveloper: Oracle Service Bus project

If you import the DBRouting project from here, you should have all the necessary services.
I will only discuss the assign steps which are needed in the DBRouting_v1Pipeline.pipeline.
Selection_023
There are three assign actions:
1.) Assign $route: node-name($body/*[1])

This assign determines our routing key. It is the same key as 
the first column in the routing table.
The XPath here is used to select the name of the first node 
but you can change this to what you want to route on.
2.) Assign $query: 
fn:concat("select ENDPOINT from ROUTINGTABLE where ROUTE = '", $route, "'")

This assign determines the query which will be executed in 
the next step. We want to select the ENDPOINT which belongs 
to the ROUTE which was assigned in step 1.
3.) Assign $query: 
(fn-bea:execute-sql(
 xs:string("LocalDB"), 
 xs:string("ENDPOINT"), 
 $query
 )/*:ENDPOINT)[1]

This assign actually executes the SQL query to our database, 
which is the first argument.
The second argument names the re-occurring rows, in this 
case "ENDPOINT".
The thirst argument is the query to execute.
The XPath after the execute-sql statement is to make sure 
we only get one endpoint.
4.) After those assigns, we use place a task “Routing Options” in the HTTP Route node:
We only use the “URI” Routing Option:
$endpoint/text()
Selection_024
This ends the article, if you execute the pipeline you will see the endpoint has become dynamic, it is retreived from the routing table:
Oracle Service Bus Console 12c : Pipeline Testing - DBRouting_v1Pipeline - Google Chrome_019

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

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
DECLARE
 l_count NUMBER;
BEGIN
 SELECT COUNT(1)
 INTO l_count
 FROM ALL_TABLES
 WHERE table_name = 'TABLE_NAME'
 AND owner = 'USERNAME';
 IF l_count > 0 THEN
 EXECUTE IMMEDIATE 'Drop table USERNAME.TABLE_NAME CASCADE CONSTRAINTS';
END IF;
END;
/

Oracle Database 11gR2, quick and easy

It’s been a while since the last article and I just found myself being enthousiastic about a neat Oracle hands on again. So while it’s downloading I might as well tell you guys about it.

Last year I have been working with Oracle XE a lot, but some features are not available in the express edition (XE), so some queries might not run as expected or simply don’t run at all. Oracle has a VM ware image, which provides a really nice solution for this problem:

They have created a database application environment inside a Virtualbox VM image, with the following specifications:

  • Oracle Linux 5
  • Oracle Database 11g Release 2 Enterprise Edition
  • Oracle TimesTen In-Memory Database Cache
  • Oracle XML DB
  • Oracle SQL Developer
  • Oracle SQL Developer Data Modeler
  • Oracle Application Express
  • Oracle JDeveloper
  • Hands-On-Labs (accessed via the Toolbar Menu in Firefox)

So if you’re looking for a nimble way to quickly run a OraDB on your laptop, without the hassle of installing all these components, this might just be what you’re looking for.

You can download the Oracle Developer Days 11g DB image here:

http://download.oracle.com/otn/other/virtualbox/dd/Oracle_Developer_Day.ova

The only prerequisite is that you have Oracle Virtualbox installed on your machine;
But this is easily installed via this link

 

In the coming days I will post another article, which will describe a neat way of connecting to this database!

oracle-11g-express-edition

The original article can be found here: http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html