I think that one more alternative to avoid including SDO functions in
my query... It is declaring a procedure in this way:
CREATE OR REPLACE PROCEDURE MyProcedure
(
param1 IN VARCHAR2,
param2 OUT NUMBER,
param3 OUT NUMBER,
param4 OUT NUMBER,
param5 OUT NUMBER
)
AS
BEGIN
SELECT SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.geometry, m.diminfo, 1) as xmin,
SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.geometry, m.diminfo, 1) as xmax,
SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.geometry, m.diminfo, 2) as ymin,
SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.geometry, m.diminfo, 2) as ymax
INTO param2, param3, param4, param5
FROM TABLE c, user_sdo_geom_metadata m
WHERE m.table_name = 'TABLE' AND m.column_name = 'GEOMETRY'
AND c.key= param1;
EXCEPTION
WHEN OTHERS THEN
param2 := 0;
param3 := 0;
param4 := 0;
param5 := 0;
END;
/
Procedure that I can use from SQLPluss in my command line with the next block:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
v1 NUMBER(30,19);
v2 NUMBER(30,19);
v3 NUMBER(30,19);
v4 NUMBER(30,19);
BEGIN
calBBoxByRC ('000503500DS30G', v1, v2, v3,v4);
DBMS_OUTPUT.PUT_LINE('v1=' || v1);
DBMS_OUTPUT.PUT_LINE('v2=' || v2);
DBMS_OUTPUT.PUT_LINE('v3=' || v3);
DBMS_OUTPUT.PUT_LINE('v4=' || v4);
END/
And getting next output, that means that MyProcedure is working OK:
v1=-15.6650951289033
v2=-15.6649116109827
v3=28.1095441542716
v4=28.1097387002688
Now that i can avoid using SDO functions on my SelectQuery. My question is:
how can i call MyProcedure and get the results from a Java Class using Cayenne?
Thanks
EmeCas
On Thu, Sep 30, 2010 at 6:05 PM, ЄЖЄRSoN CმStმNЄDმ S wrote:
> OK
>
> Thanks Andrus
>
> let's try...
>
> This is the sample quiery of SQLTemplate and Prefetching
>
>
> SQLTemplate q = new SQLTemplate(
> Artist.class,
> "SELECT distinct "
> + "#result('ESTIMATED_PRICE' 'BigDecimal' ''
> 'paintingArray.ESTIMATED_PRICE'), "
> + "#result('PAINTING_TITLE' 'String' ''
> 'paintingArray.PAINTING_TITLE'), "
> + "#result('GALLERY_ID' 'int' ''
> 'paintingArray.GALLERY_ID'), "
> + "#result('PAINTING_ID' 'int' ''
> 'paintingArray.PAINTING_ID'), "
> + "#result('ARTIST_NAME' 'String'), "
> + "#result('DATE_OF_BIRTH' 'java.util.Date'), "
> + "#result('t0.ARTIST_ID' 'int' '' 'ARTIST_ID') "
> + "FROM ARTIST t0, PAINTING t1 "
> + "WHERE t0.ARTIST_ID = t1.ARTIST_ID");
> q.addPrefetch("paintingArray");
> List objects = context.performQuery(query);
>
>
> So, this will be an approximation to my final query, that looks some thing
> like:
>
> SQLTemplate q = new SQLTemplate(
> TABLE.class,
> "SELECT distinct "
> + "#result('XMIN' 'BigDecimal'
> SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.geometry, m.diminfo, 1)), " //?
> + "#result('XMAX' 'BigDecimal'
> SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.geometry, m.diminfo, 1)), " //?
> + "#result('YMIN' 'BigDecimal'
> SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.geometry, m.diminfo, 2)), "//?
> + "#result('YMAX' 'BigDecimal'
> SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.geometry, m.diminfo, 2)) "//?
> + "FROM TABLE c, user_sdo_geom_metadata m "
> + "WHERE m.table_name = 'TABLE' AND m.column_name = 'GEOMETRYCOLUMN' "
> + "AND c.KEY= 'XXXX' ");
> q.addPrefetch("user_sdo_geom_metadataArray"); //?
>
> But i dont know how to use/combine prefeching here in order to include
> the calls to SDO functions in the fields after SELECT distinct
>
> some more idea?
>
>
> EmeCas
>
>
>
> On Tue, Sep 28, 2010 at 5:41 PM, Andrus Adamchik <[email protected]>
> wrote:
>>
>> Not an expert on Oracle Spatial, but I think you should be able to run this
>> via SQLTemplate query:
>>
>> http://cayenne.apache.org/doc30/sqltemplate-query.html
>>
>> Andrus
>>
>> On Sep 28, 2010, at 2:11 PM, ЄЖЄRSoN CმStმNЄDმ S wrote:
>>
>> > Hello everyone,
>> >
>> > I am newby in Cayenne 3.0, I would like to know if it is possible to
>> > structure a query as follow using cayenne (these are SDO fuction of
>> > ORACLE).
>> >
>> > SELECT SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.geometry, m.diminfo, 1) as xmin,
>> > SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.geometry, m.diminfo, 1) as xmax,
>> > SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.geometry, m.diminfo, 2) as ymin,
>> > SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.geometry, m.diminfo, 2) as ymax
>> > FROM TABLE c, user_sdo_geom_metadata m
>> > WHERE m.table_name = 'TABLE' AND m.column_name = 'GEOMETRYCOLUMN';
>> > AND c.KEY= 'XXXX';
>> >
>> > In case that it would not be viable directly from a query, which is the
>> > best
>> > way implementing a query like that?, maybe calling a procedure in PL/sql, i
>> > guess.
>> >
>> > is it there some documentation source about SDO Oracle & Cayenne?
>> >
>> > Thanks for your help
>> >
>> >
>> > EmeCas
>>
>
