create table DBA_CPLAN.CPLAN_TD_EEI (
ID NUMBER not null,
LIBELLE VARCHAR2(200 char) not null,
ID_SIR NUMBER
);


create table DBA_CPLAN.CPLAN_TD_SIR (
ID NUMBER not null,
LIBELLE VARCHAR2(200 char) not null
);

CREATE OR REPLACE TYPE CPLAN_TYD_SIR AS OBJECT
(
id NUMBER,
libelle VARCHAR2(200 char)
);

CREATE OR REPLACE TYPE CPLAN_TPD_SIR AS TABLE OF CPLAN_TYD_SIR;

CREATE OR REPLACE FORCE VIEW DBA_CPLAN.CPLAN_VP_EEI AS
SELECT eei.ID id
, eei.LIBELLE libelle
, cast ( multiset ( select sir.id id
, sir.libelle libelle
from cplan_td_sir sir
where sir.id = eei.id_sir
) as cplan_tpd_sir
) as liste_sir
FROM CPLAN_TD_EEI eei;

import java.math.BigDecimal;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Struct;

/**
*
*/

/**
* @author vuibert
*
*/
public class test
{

/**
* @param args
*/
public static void main(String[] args)
{
Connection conn = null;

try
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

conn = DriverManager.getConnection("jdbc:oracle:thin:@srv-acs-01:1421:cplan", "dba_cplan", "cplan");

if (conn == null)
return;

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from cplan_vp_eei where id=4");

if (rs != null)
{
while (rs.next())
{
System.out.println("id = " + rs.getInt("id"));
System.out.println("libelle = " + rs.getString("libelle"));

// Recuperation du tableau
Array liste_sir = rs.getArray("liste_sir");

if (liste_sir == null)
break;

// Recuperation des lignes du tableau
Object[] objects = (Object[]) liste_sir.getArray();

System.out.println("liste_sir length = " + objects.length);

if (objects.length <= 0)
break;


Object[] attributes = null;

// Pour chaque ligne du tableau
for (int i=0; i<objects.length; i++)
{
// Recuperation des cellules
attributes = (Object[]) ((Struct)objects).getAttributes();

System.out.println("sir id = " + ((BigDecimal) attributes[0]).intValue());
System.out.println("sir libelle = " + attributes[1]);
}

}
}

}
catch (SQLException e)
{
System.out.println("Caught: " + e);
System.exit(1);
}


}

}

id = 4
libelle = libelle eei 4
liste_sir length = 1
sir id = 30
sir libelle = ???