All,
I have 2 tables that are provided by 3rd party software that I'm trying
to query from. There are issues with the way the data is organized, so we
wrapped them in a Pl/SQL function that returns a result set.
I've mapped the 2 tables as a one-to-many, since that's the relationship
between these two tables. I have no problem with calling the function via a
Native Query, but it seems to want to query the second table to get the
associated "many" rows with a query executed per row. I know that if I were
doing this all via OpenJPA and not a proc/function, I could use the " join
fetch " and get around the query-per-row issue. My question to the gurus
is, if my procedure returns all of the columns that would normally have been
in the " join fetch" query, can I get OpenJPA to treat this as a join and
handle the one-to-many relationship without performing a query per row?
Details: OpenJPA 2.0.1, Oracle 11g
Query: Query qry = em.createNativeQuery("select * from table(
FINDBYCRITERIA2(?))", Article.class);
Function: FINDBYCRITERIA2() Currently, returns a join of the ARTICLE
and ARTICLE_DETAIL tables
Tables:
ARTICLE
------------------------------------
ARTICLE_KEY (pk)
LANG_CODE (pk)
TITLE
(etc etc etc)
ARTICLE_DETAIL
------------------------------------
ID (pk)
ARTICLE_KEY
LANG_CODE
KEYWORD
AUTHOR
(etc etc etc)
--
View this message in context:
http://openjpa.208410.n2.nabble.com/OpenJPA-with-Stored-Proc-join-possible-tp5575342p5575342.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
