Steve's Blog Technical Information on Oracle Middleware.

When running a select statement in sqlplus on a nested tables we get the results returned. However the same can not be said when running the same statement through JDBC. He is an example of returning values from a nested table with JDBC.

1. Here's how the sql script looks like to create the type and tables.

drop table person;
drop type nested_addresses;
drop type address;

create or replace type address as object(
 street varchar2(50),
 city varchar2(20),
 postcode varchar2(8),
 state varchar(8))
/

create or replace type nested_addresses as table of address
/

create table person (
 firstname varchar(20),
 lastname varchar(20),
 address_list nested_addresses
 ) nested table address_list store as person_addresses
/

insert into person values ('Fred','Flinstone', nested_addresses(address('345 Cave Stone Road', 'Bedrock', 'BR345','BR'),address('1313 Cobblestone Way', 'Bedville', 'BV1313','BV')))
/

commit
/

scott@orcl> set head on feedback on
scott@orcl> set pages    999
scott@orcl> set linesize 120
scott@orcl> col firsname format a20
scott@orcl> col lastname format a20
scott@orcl> col address_list format a50
scott@orcl> select firstname, lastname, address_list from person;

FIRSTNAME            LASTNAME             ADDRESS_LIST(STREET, CITY, POSTCODE, STATE)
-------------------- -------------------- --------------------------------------------------
Fred                 Flinstone            NESTED_ADDRESSES(ADDRESS('345 Cave Stone Road', 'B
                                          edrock', 'BR345', 'BR'), ADDRESS('1313 Cobblestone
                                           Way', 'Bedville', 'BV1313', 'BV'))


1 row selected.

import java.sql.*;

public class Person {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        // connection parameters
        String dbUrl = "jdbc:oracle:thin:@sracanov-au2.au.oracle.com:1522:orcl";
        String dbUser = "scott";
        String dbPass = "tiger";

        // load the driver
        Class.forName("oracle.jdbc.driver.OracleDriver");

        // create connection
        Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);

        // query the table
        Statement s = con.createStatement();
        String query = "select firstname, lastname, address_list from person";
        ResultSet res = s.executeQuery(query);

        // look at the result set
        res.next();
        System.out.println("First Name: " + res.getString(1));
        System.out.println("Last Name: " + res.getString(2));
        System.out.println("Address: " + res.getString(3));

        // close connection
        con.close();
    }
}

[sracanov@sracanov-au C]$ java -version
java version "1.6.0_16"
Java(TM) SE Runtime Environment (build 1.6.0_16-b01)
Java HotSpot(TM) Client VM (build 14.2-b01, mixed mode, sharing)

[sracanov@sracanov-au C]$ javac -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person.java

[sracanov@sracanov-au C]$ java -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person
First Name: Fred
Last Name: Flinstone
Address: null

import java.sql.*;

public class Person1 {
        public static void main(String[] args) throws SQLException,
                        ClassNotFoundException {

                // connection parameters
                String dbUrl = "jdbc:oracle:thin:@sracanov-au2.au.oracle.com:1522:orcl";
                String dbUser = "scott";
                String dbPass = "tiger";

                // load the driver
                Class.forName("oracle.jdbc.driver.OracleDriver");

                // create connection
                Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);

                // query the table
                Statement s = con.createStatement();
                String query = "select firstname, lastname, address_list from person";
                ResultSet res = s.executeQuery(query);

                // look at the result set
                while (res.next()) {
                        int addresscounter=1;
                        System.out.println("First Name: " + res.getString(1));
                        System.out.println("Last Name: " + res.getString(2));
                        Array array = res.getArray(3);
                        Object[] address_list = (Object[]) array.getArray();
                        for (int i = 0; i < address_list.length; i++) {
                           Struct address = (Struct) address_list[i];
                           Object[] attrib = address.getAttributes();
                           for (int j = 0; j < attrib.length; j++) {
                              System.out.println("Address " + addresscounter + ": " + attrib[j]);
                           }
                           addresscounter++;
                        }
                }

                // close connection
                con.close();
        }
}

[sracanov@sracanov-au C]$ javac -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person1.java

[sracanov@sracanov-au C]$ java -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person1
First Name: Fred
Last Name: Flinstone
Address 1: 345 Cave Stone Road
Address 1: Bedrock
Address 1: BR345
Address 1: BR
Address 2: 1313 Cobblestone Way
Address 2: Bedville
Address 2: BV1313
Address 2: BV