Environment - JavaSE 6, Oracle 11, WebSphere 7, eclipseLink 2.5.2
Use case We'd like to to use
@NamedPLSQLStoredFunctionQuery
like this:
@NamedPLSQLStoredFunctionQuery
(name = "convertToString",
functionName = "my_schema.my_package.convert_to_string",
parameters =
{ @PLSQLParameter(name = "p_boolean", databaseType = "BOOLEAN") },
returnParameter = @PLSQLParameter(name = "RESULT", databaseType = "VARCHAR_TYPE"))
We execute then the functions like this:
Query query = em.createNamedQuery("convertToString");
query.setParameter("p_boolean", true);
Object result = query.getSingleResult();
System.out.println("result=" + result);
and/or
Query query = em.createNamedQuery("convertToInteger");
query.setParameter("p_boolean", true);
Object result = query.getSingleResult();
System.out.println("result=" + result);
Our Oracle function implementations is as follows:
CREATE OR REPLACE package body my_schema.my_package
as
/**
* Converts the given boolean into an integer (true: 1, false: 0).
* @param p_boolean boolean value to convert to an integer
*/
function convert_to_integer
(
p_boolean in boolean
)
return integer
is
begin
if (p_boolean = true) then
return isac_api.api_boolean_constants_pg.integer_true;
end if;
return isac_api.api_boolean_constants_pg.integer_false;
end;
/**
* Converts the given boolean into a string (true: 'true', false: 'false').
* @param p_boolean boolean value to convert
*/
function convert_to_string
(
p_boolean in boolean
)
return varchar2
is
begin
if (p_boolean = true) then
return isac_api.api_boolean_constants_pg.string_true;
end if;
return isac_api.api_boolean_constants_pg.string_false;
end;
end;
It doesn't really work.
Questions
- What is the problem?
- What can we do if we are not allowed to install 'SYS.SQLJUTL' on the database BUT eclipseLink seems to use it for conversions?
Regards Jan