I'm successfully using NamedNativeQuery to call stored procedures in legacy database when I only pass 'basic' types to Query.setParameter. However, when I try to call NamedNativeQuery with custom type input types defined in db as arguments I get 'PLS-00306: wrong number or types of arguments in call to: <procedure_name>'.
So for example a call to hypothetical stored procedure like the following would fail because of custom list type expected as input for i_listvals:
Code:
create or replace PROCEDURE MY_STORED_PROC(
o_out OUT sys_refcursor,
i_name VARCHAR, // this parameter is OK
i_listvals [b]custom_list_t[/b] // this parameter causes my problem
)
IS
BEGIN
<... do stuff ...>
END MY_STORED_PROC;
Code:
query.setParameter(name1, val1);//this is OK
query.setParameter(name2, val2);//this will fail even though val2 is formatted per expected string format
To resolve this I'm guessing that I need to provide additional type information to Hibernate so that it knows how to render the appropriate query syntax. As I may target multiple database types, I am seeking a solution that adapts to the current dialect.
I've read and searched on this topic and it appears that implementing a custom UserType might provide a solution. The example at http://community.jboss.org/wiki/UserTypeForPersistingATypesafeEnumerationWithAVARCHARColumn seems conceptually similar to what I'm trying to accomplish however I'd prefer to use annotations only rather than implement mapping files as shown. In practice the ref'd example is also different in that I'm actually trying to treat a list of values as a single input parameter by converting the list into string format expected by the database (... rather than work with enumerated values as is shown in example).
Can anyone confirm whether implementing a UserType would work in this scenario and/or recommend books or articles that cover this topic?
Can anyone comment on how the example ref'd above would need to be changed so that the mapping file is not part of the implementation?
If I'm simply 'headed the wrong way' suggestions as to alternate approaches are of course most appreciated also.
Thanks in advance.
Login
FAQ
Search