Prev: Interview
Next: Hudson, Git, and Tomcat
From: Hole on 7 Oct 2009 08:24
> > 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>
From: Gunter Herrmann on 10 Oct 2009 15:56
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
From: Hole on 12 Oct 2009 06:54
On Oct 10, 9:56 pm, Gunter Herrmann <notformail0...(a)earthlink.net>
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...
First  |  Prev  | 
Pages: 1 2
Prev: Interview
Next: Hudson, Git, and Tomcat