CODEWITHSUNDEEP
javasqloracle-databaseplsqljooq
Simon Martinelli
Simon Martinelli

Reputation: 36173

jOOQ generates a static method to call a function but no method to get it as field

I want to turn this SQL statement into a jOOQ select:

select art_id,
       artnr,
       pa_bez.arttext(art_id, null, pa_param.text(10, 1)) text,
from artikel;

But I cannot use pa_bez.arttext because in the generated package PaBez I can only find a static method to call the function:

public static String arttext(Configuration configuration, Number pArtId, 
                             String pArttextart, String pSprache, 
                             Number pAufart, Boolean pFallbackArttextart) {
...
}

But no method to use it as a field like I got with other functions:

public static String arttextart(Configuration configuration, String pArttextart, 
                                String pSprache) {
...
}

public static Field<String> arttextart(String pArttextart, String pSprache) {
...
}

Is this a bug or a misunderstanding of jOOQ on my side?

This is the function code:

FUNCTION ARTTEXT
(p_art_id     in number
,p_arttextart in varchar2 default 'basis'
,p_sprache    in varchar2 default null
,p_aufart     in number   default null
,p_fallback_arttextart in boolean default true
)
RETURN VARCHAR2

Upvotes: 1

Views: 506

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220987

The BOOLEAN data type is a PL/SQL data type and does not exist in the SQL engine. Unfortunately, there is no direct way to pass the boolean argument to your function in SQL.

However, testing with Oracle 12, and given that you have a default value in your function signature, jOOQ could simply omit the parameter. This should work (but it's not yet supported by jOOQ 3.11):

SELECT pa_bez.arttext(1) FROM dual

If you have to pass a boolean bind variable, the following workaround would work as well:

WITH
  FUNCTION arttext_(
    p_art_id     in number
   ,p_arttextart in varchar2 default 'basis'
   ,p_sprache    in varchar2 default null
   ,p_aufart     in number   default null
   ,p_fallback_arttextart in number default 1
  ) RETURN VARCHAR2 IS BEGIN
    RETURN pa_bez.arttext(
      p_art_id, 
      p_arttextart, 
      p_sprache,
      p_aufart
      CASE p_fallback_arttextart WHEN 1 THEN TRUE WHEN 0 THEN FALSE ELSE NULL END
    );
  END arttext_;
SELECT arttext_(?, ?, ?, ?, ?) FROM dual

I think that jOOQ 3.12 could implement the above workaround for Oracle 12+. I have created an issue for this: https://github.com/jOOQ/jOOQ/issues/8522

I've also blogged about this in more detail here.

In the meantime, you might have to bridge your call manually, by creating an auxiliary function like I've shown, translating between NUMBER and BOOLEAN

Upvotes: 1

Related Questions