Call an Oracle Function from Spring
1 min read

Call an Oracle Function from Spring

Call an Oracle Function from Spring

The easiest way to call a function from Spring is using SELECT. For example, having a function HAS_PANEL_FLAG_OK with two parameters and returning an integer, the function call looks like:

SELECT  panel_id,
  HAS_PANEL_FLAG_OK('17584', panel_id) AS flag
  FROM panels
  WHERE panel_id LIKE '%30'
  ;

This will return all flags for the panels matching the condition.

Java Call

The query string can be something like:

static final String STATEMENT = "select  panel_id, " +
  "HAS_PANEL_FLAG_OK('17584', panel_id) AS flag " +
  "FROM panels" +
  "WHERE panel_id LIKE ?"

and the actual query will be:

jdbcOperations.queryForInt(SELECT_PANEL_IS_LOCKED, new Object[] {
    "%30"
});

The problem I've encountered is the ability to specify an argument which is not part of the query. Something like this:

static final String STATEMENT = "select  panel_id, " +
  "HAS_PANEL_FLAG_OK(?, panel_id) AS flag " + // the first function parameter is parametereised
  "FROM panels" +
  "WHERE panel_id LIKE ?"

jdbcOperations.queryForInt(SELECT_PANEL_IS_LOCKED, new Object[] {
    "17584",
    "%30"
});

I'll investigate this later...

HTH,