Prev: Interview
Next: Hudson, Git, and Tomcat
From: Hole on 2 Oct 2009 06:19
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.
From: John B. Matthews on 2 Oct 2009 07:47
In article
<043a6449-8a0b-4b95-a453-c11a35b75971(a)b2g2000yqi.googlegroups.com>,
Hole <h0leforfun(a)gmail.com> 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>
From: Hole on 2 Oct 2009 09:06
On Oct 2, 1:47 pm, "John B. Matthews" <nos...(a)nospam.invalid> wrote:
> In article
> <043a6449-8a0b-4b95-a453-c11a35b75...(a)b2g2000yqi.googlegroups.com>,
>
>  Hole <h0lefor...(a)gmail.com> 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


From: John B. Matthews on 2 Oct 2009 10:58
In article
<b3e837e2-f1f0-43a7-b94d-7f4719186b19(a)l13g2000yqb.googlegroups.com>,
Hole <h0leforfun(a)gmail.com> wrote:

> On Oct 2, 1:47 pm, "John B. Matthews" <nos...(a)nospam.invalid> wrote:
> > In article
> > <043a6449-8a0b-4b95-a453-c11a35b75...(a)b2g2000yqi.googlegroups.com>,
> >
> >  Hole <h0lefor...(a)gmail.com> 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>
From: Hole on 4 Oct 2009 05:50
> > 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).
 |  Next  |  Last
Pages: 1 2
Prev: Interview
Next: Hudson, Git, and Tomcat