7

I have an Oracle function that takes in a table of POINTS%ROWTYPE. I want to call this function from JPA using the CriteriaBuilder class, which has a function for database functions. When I try to build the query, it dies complaining that ArrayLists are not valid query parameters to the function.

How do I pass in an ArrayList from JPA into an Oracle function?

Oracle Function signature:

CREATE OR REPLACE FUNCTION LOCATION_CONTAINS
(
LATITUDE_IN IN DOUBLE PRECISION,
LONGITUDE_IN IN DOUBLE PRECISION,
points IN types_pkg.point_array,
numPoints IN INTEGER
)

Oracle Type:

create or replace package types_pkg
as
type point_array is table of FILTERPOINT%ROWTYPE;
end types_pkg;

JPA Criteria Builder call

List<FilterPoint> points = getPoints(location_name);
int numPoints = points.size();

Expression ex =
           cb.function( "LOCATION_CONTAINS",
           Integer.class,
           entity.get( "latitude" ),
           entity.get( "longitude" ),
           cb.literal( points ),
           cb.literal( numPoints ) );

Exception:

org.apache.openjpa.persistence.ArgumentException: 
The specified parameter of type "class middle.ware.FilterPoint" is not a valid query parameter.

Essentially, I want to grab an array of points outside the function call so I only have to fetch it once (right now I do the select inside the function call, so it's ran every single time the function is called, which is potentially 100,000's of times.) Then I want to pass that array of points back into the function for processing.

I need to use criteria builder for this function is only part of the query.

Thanks for any help.

GuitarStrum
  • 713
  • 8
  • 24
  • First there are no solution to access package-level table type from java.[fetch-oracle-table-type-from-stored-procedure-using-jdbc](http://stackoverflow.com/questions/6410452/fetch-oracle-table-type-from-stored-procedure-using-jdbc). Secound I think there is no chance to access any of User Definde Type(UDT) using standard JPA. (Exlipse link from 2.4 has some extension to achieve this). Solution described by @Andremoniy works only for sql-level types. – Arkadiusz Łukasiewicz Jan 24 '17 at 09:47

1 Answers1

3

This could help. As it described here, appropriate Java type for user-defined TABLE type is java.sql.Array. I suppose you need convert your list into this type. One of the possible ways to do it, just invoke method createArrayOf on your connection (see also this answer):

Session session = (Session)em.getDelegate();
Connection conn = session.connection();
String[] data = points.toArray(new FilterPoint[points.size()]);
java.sql.Array sqlArray = conn.createArrayOf(typeName, data);
Community
  • 1
  • 1
Andremoniy
  • 34,031
  • 20
  • 135
  • 241
  • I get a java.sql.SQLException: Unsupported feature exception. From a little digging, it looks like there's another way to do it in Oracle specifically (OracleConnection.createARRAY()), so I'll have a look into that and see how that works. – GuitarStrum Jan 20 '17 at 21:09
  • 1
    After more testing, I can't seem to get my array from the JPA side to mesh with the Oracle SQL-Level type. I've followed advice from similar questions I found (like capitalization and using a STRUCT), but for some reason it didn't want to work. I've had to put that portion of the project on hold due to deadlines, but seeing as your answer led me in the right path, and no other answer was given, rep for you, and thank you. – GuitarStrum Jan 27 '17 at 12:21