Mapping Oracle collection in Java/Hibernate

Discussion in 'Java' started by Hole, Oct 2, 2009.

  1. Hole

    Hole Guest

    Hi there,

    I have a stored procedure returning an Oracle collection (a nested
    table of object and not simple arrays).

    Do you know how to map it in Java or, preferibally, Hibernate?

    The collection type looks like this:

    <code>
    create or replace TYPE SAMPLED_VALUE AS OBJECT
    ( data_timestamp date,
    data_value number(8,2)
    );

    create or replace TYPE SAMPLED_VALUES_ARRAY AS TABLE OF SAMPLED_VALUE;
    </code>

    While the stored procedure is declared as:

    <code>
    create or replace
    PROCEDURE EXTRACTOR_EXTRACT(
    id IN NUMBER, dateFrom IN DATE, dateTo IN DATE, step VARCHAR2,
    valuesMap OUT NOCOPY SAMPLED_VALUES_ARRAY, error_code OUT NOCOPY
    NUMBER, error_description OUT NOCOPY VARCHAR2
    ) AS
    ....
    ....
    </code>

    Can you suggest me a solution?
    Thanks in advance.
     
    Hole, Oct 2, 2009
    #1
    1. Advertising

  2. In article
    <>,
    Hole <> wrote:

    > I have a stored procedure returning an Oracle collection (a nested
    > table of object and not simple arrays).
    >
    > Do you know how to map it in Java or, preferibally, Hibernate?


    I don't know about Hibernate, but calling PL/SQL from Java is discussed
    in the "Java Developer's Guide":

    <http://download.oracle.com/docs/cd/E11882_01/java.112/e10588/
    chseven.htm#CACJDIAC>

    [...]
    --
    John B. Matthews
    trashgod at gmail dot com
    <http://sites.google.com/site/drjohnbmatthews>
     
    John B. Matthews, Oct 2, 2009
    #2
    1. Advertising

  3. Hole

    Hole Guest

    On Oct 2, 1:47 pm, "John B. Matthews" <> wrote:
    > In article
    > <>,
    >
    >  Hole <> wrote:
    > > I have a stored procedure returning an Oracle collection (a nested
    > > table of object and not simple arrays).

    >
    > > Do you know how to map it in Java or, preferibally, Hibernate?

    >
    > I don't know about Hibernate, but calling PL/SQL from Java is discussed
    > in the "Java Developer's Guide":
    >
    > <http://download.oracle.com/docs/cd/E11882_01/java.112/e10588/
    > chseven.htm#CACJDIAC>
    >

    Thanks John,

    in fact I would know how to map an "Oracle collection of objects" to
    the Java side. It seems that there is a lack of documentation on it
    (I've found someone suggesting to use oracle.sql.STRUCT class to map
    an Oracle object in Java but it's not so clear how to map a "nested
    table" oracle type).

    In Hibernate, I found that the right way to map a collection of Oracle
    objects is, first of all, implements the UserType interface to create
    a custom mapping:

    https://www.hibernate.org/261.html
     
    Hole, Oct 2, 2009
    #3
  4. In article
    <>,
    Hole <> wrote:

    > On Oct 2, 1:47 pm, "John B. Matthews" <> wrote:
    > > In article
    > > <>,
    > >
    > >  Hole <> wrote:
    > > > I have a stored procedure returning an Oracle collection (a
    > > > nested table of object and not simple arrays).

    > >
    > > > Do you know how to map it in Java or, preferibally, Hibernate?

    > >
    > > I don't know about Hibernate, but calling PL/SQL from Java is
    > > discussed in the "Java Developer's Guide":
    > >
    > > <http://download.oracle.com/docs/cd/E11882_01/java.112/e10588/chseven.htm#CACJDIAC>
    > >

    > in fact I would know how to map an "Oracle collection of objects" to
    > the Java side. It seems that there is a lack of documentation on it
    > (I've found someone suggesting to use oracle.sql.STRUCT class to map
    > an Oracle object in Java but it's not so clear how to map a "nested
    > table" oracle type).


    I see that mentioned in "JDBC Developer's Guide,"
    "16 Working with Oracle Collections" under
    "Using a Type Map to Map Array Elements":

    <http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/oraarr.htm#i1049179>

    --
    John B. Matthews
    trashgod at gmail dot com
    <http://sites.google.com/site/drjohnbmatthews>
     
    John B. Matthews, Oct 2, 2009
    #4
  5. Hole

    Hole Guest

    > > in fact I would know how to map an "Oracle collection of objects" to
    > > the Java side. It seems that there is a lack of documentation on it
    > > (I've found someone suggesting to use oracle.sql.STRUCT class to map
    > > an Oracle object in Java but it's not so clear how to map a "nested
    > > table" oracle type).

    >
    > I see that mentioned in "JDBC Developer's Guide,"
    > "16 Working with Oracle Collections" under
    > "Using a Type Map to Map Array Elements":
    >
    > <http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/oraarr.h...>
    >


    Great! Thanks again, John.
    It seemed strange that no official documentation was provided for such
    a thing...I tried to search for docs using different key words
    (limitation of non-ontological search engines :p).
     
    Hole, Oct 4, 2009
    #5
  6. Hole

    Hole Guest

    > > I see that mentioned in "JDBC Developer's Guide,"
    > > "16 Working with Oracle Collections" under
    > > "Using a Type Map to Map Array Elements":

    >
    > > <http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/oraarr.h...>

    >
    > Great! Thanks again, John.
    > It seemed strange that no official documentation was provided for such
    > a thing...I tried to search for docs using different key words
    > (limitation of non-ontological search engines :p).


    Hi there,

    only to post working code that solved my question.
    Perhaps, there is room to improve the code either the design solution.

    <code>
    public List<SampledValue> getResults() {
    //int progress = 0;
    List<SampledValue> svList = new ArrayList<SampledValue>();

    try {
    CallableStatement cs = conn.prepareCall(CALL_SP);
    cs.setBigDecimal(1, this.virtualId);
    cs.setTimestamp(2, new Timestamp(dateFrom.getTime()));
    cs.setTimestamp(3, new Timestamp(dateTo.getTime()));
    cs.setString(4, this.dateTimeStep);
    cs.setString(5, this.variables);

    //the third parameter is the SQL_TYPE name of the NESTED
    TABLE
    //as declared in Oracle:
    //create or replace TYPE SAMPLED_VALUES_ARRAY AS TABLE OF
    SAMPLED_VALUE;
    cs.registerOutParameter(6, Types.ARRAY,
    "SAMPLED_VALUES_ARRAY");

    cs.execute();


    ARRAY a = (ARRAY) cs.getObject(6);
    //from the ARRAY object, you need to get a ResultSet...
    ResultSet rs = a.getResultSet();

    while (rs.next()) {
    //the first "column" is the row number while the
    second is the real Oracle object
    //Oracle objects are mapped as java.sql.STRUCT
    STRUCT object = (STRUCT) rs.getObject(2);
    //BigDecimal row = (BigDecimal) rs.getObject(1);

    //get attributes from the Oracle object
    Object[] attrs = object.getAttributes();

    //you need how to map Oracle object into your java
    object
    //
    // create or replace TYPE SAMPLED_VALUE AS OBJECT
    // ( data_timestamp date,
    // data_value number(8,2)
    // );
    SampledValue sv = new SampledValue();
    sv.setDateTimeUtc((Timestamp) attrs[0]);
    sv.setDataValue((BigDecimal) attrs[1]);
    svList.add(sv);

    }
    cs.close();
    } catch (Exception exc) {
    svList = null;
    exc.printStackTrace();
    }
    return svList;

    }

    </code>
     
    Hole, Oct 7, 2009
    #6
  7. Hi!

    Hole wrote:

    > Hi there,
    >
    > only to post working code that solved my question.



    There is an alternative solution:

    Rewrite your PL/SQL code to return the nested table from a function
    (table function or Oracle9++ pipelined table function).

    Then your query would simply be:

    select * from TABLE(myTableFunction(parameter1, ...)

    8i syntax: table(cast( ... as tabletype

    Best regards

    Gunter
     
    Gunter Herrmann, Oct 10, 2009
    #7
  8. Hole

    Hole Guest

    On Oct 10, 9:56 pm, Gunter Herrmann <>
    wrote:
    > Hi!
    >
    > Hole wrote:
    > > Hi there,

    >
    > > only to post working code that solved my question.

    >
    > There is an alternative solution:
    >
    > Rewrite your PL/SQL code to return the nested table from a function
    > (table function or Oracle9++ pipelined table function).
    >
    > Then your query would simply be:
    >
    > select * from TABLE(myTableFunction(parameter1, ...)
    >
    > 8i syntax: table(cast( ... as tabletype
    >
    > Best regards
    >
    > Gunter


    Thanks Gunter,

    I'll take a look at it and see if it helps me to simplify the design
    (if so, I will apply a refactor cycle)...
    At the moment, I call the SP simply by using this:

    public final static String CALL_SP = "{call EXTRACTOR_EXTRACT
    (?,?,?,?,?,?)}";

    Perhaps, using that solution, I would be able to use my Hibernate
    layer...
     
    Hole, Oct 12, 2009
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ducnbyu
    Replies:
    2
    Views:
    6,163
    ducnbyu
    Sep 8, 2006
  2. Øyvind Isaksen
    Replies:
    1
    Views:
    987
    Øyvind Isaksen
    May 18, 2007
  3. Sandy Miller
    Replies:
    0
    Views:
    541
    Sandy Miller
    Jan 28, 2008
  4. Eleanna Skouta
    Replies:
    2
    Views:
    8,442
    Arne Vajhøj
    Dec 9, 2008
  5. Feyruz
    Replies:
    4
    Views:
    2,204
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page