August 19, 2014

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”
Then follow these steps:

Click [Other Ports]
Click [Add]
Select [User Defined]
Enter Port: “1521”
Select Protocol: “TCP”
Click [Apply]
Click [Reload]

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:

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:

Select “Network”
Click [Port Forwarding]

Then in the Port Forwarding Rules:

Enter a descriptive name: “DB”
Enter the host port: “1521”
Enter the guest port: “1521”

August 12, 2014

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!

December 6, 2013

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

May 17, 2013

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;
/

February 19, 2013

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

June 25, 2012

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:

Connect to your database with the system user
Execute the following command (Note: replace “password” with your password)
```
ALTER USER HR IDENTIFIED BY password ACCOUNT UNLOCK;
```
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.

May 9, 2012

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

April 25, 2012

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! 🙂

January 20, 2012

Select sample WS Callouts straight from your Oracle DB

It’s often nice to have the most recent data when you want to test a web service, so in the best scenario you would want to query existing data with some criteria such as active contracts and/or within a certain range.
This example will show you how to get ready-to-use web service callout requests from SQL Developer (or any other SQL tool) which can be used in a Service Bus Console or SoapUI. We will use the HR example database -which can be found in every Oracle DB- as the source.
This is the request which we’ll use:

select '<v1:IncreaseSalaryRequest xmlns:v1="http://example.joris.visscher/hr/types/v1/">
 <v1:EmployeeId>
 <v1:Type>DEFAULT</v1:Type>
 <v1:Identifier>'||emp.employee_id||'</v1:Identifier>
 </v1:EmployeeId>
 <v1:FirstName>'||emp.first_name||'</v1:FirstName>
 <v1:LastName>'||emp.last_name||'</v1:LastName>
 <v1:Department>
 <v1:Type>DEFAULT</v1:Type>
 <v1:Identifier>'||dep.department_id||'</v1:Identifier>
 </v1:Department>
 </v1:IncreaseSalaryRequest>' WSRequest
from employees emp
, departments dep
where emp.department_id = dep.department_id
-- Let's add some conditions which are needed to finish the process succesfully.
and emp.last_name is not null
and dep.department_name = 'IT' -- return the most valuable employees
-- We only need 5 results.
and rownum <= 5;

This will result in five instantly usable requests from live data so we’ll know for sure that all the criteria that should be met for a functional correct case are indeed met:

<v1:IncreaseSalaryRequest xmlns:v1="http://example.joris.visscher/hr/types/v1/">
  <v1:EmployeeId>
    <v1:Type>DEFAULT</v1:Type>
    <v1:Identifier>103</v1:Identifier>
  </v1:EmployeeId>
  <v1:FirstName>Alexander</v1:FirstName>
  <v1:LastName>Hunold</v1:LastName>
  <v1:Department>
    <v1:Type>DEFAULT</v1:Type>
    <v1:Identifier>60</v1:Identifier>
  </v1:Department>
</v1:IncreaseSalaryRequest>

This query should work on most databases, but it works on an Oracle DB for sure 🙂
Happy testing!

Tag: SQL

Select an XML tag or node using Oracle PL SQL