Superhero Ninja

Tag: SQL

  • 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!? 🙂
    (more…)

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

  • 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) 
      ALTER USER HR IDENTIFIED BY password ACCOUNT UNLOCK;
    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,
 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! 🙂

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

    Oracle SQL developer queries an OracleXE11g db on Ubuntu 11.10