SQL "with"-Statements auf JasperServer

Möchte man in JasperReports with-Statements im Query verwenden, funktioniert das im Studio problemlos, deployed man den Report allerdings auf den Server liefert dieser beim Ausführen eine Security-Exception.

Hintergrund ist das Sicherheitskonzept des JasperServers, welches verhindern soll, dass etwa durch SQL-Injections Schaden an der Datenbank entstehen kann. Leider ist die Validator-Regel zunächst aber etwas zu restriktiv. Zum Glück kann man diese anpassen:

In der Datei


die Zeile


ersetzen durch



Fixing Currency Behaviour in JDeveloper / ADF 12c

As many questions regarding Currency fields in ADF have come to me lately, let me just give you an example of how to tackle these.

It seems, that there is a common error in ADF for German users (or to any other language,where the decimal and grouping delimiter is exactly switched to the English definition).

English (US) Format: $ #,###.##

German Format #.###,##€

As you see, for germans the ',' is changing places wit the '.'

Normally, this would not be much of a deal, but in ADF there seems to be a "hard coded" interpretation somewhere.

So in a simple form, let us type in a salary of 9.999,99 which is a correct entry for the salary field in German.

In case you enter it as the hint provides (that means including the currency symbol) everything seems to work. But for many cases, users are not accustomed to entering the currency symbol.

So let us create our own converter class to fix this issue:

At first, create a java class that is implementing the javax.faces.convert.Converter Interface. This should be annotated by the FacesConverter tag to make it selectable in the UIComponents properties afterwards and removes the requirement to handle the faces-config.xml tracking.

package de.teampb.conv;

import java.math.BigDecimal;

import java.text.DecimalFormatSymbols;
import java.text.NumberFormat;

import java.util.Locale;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.convert.Converter;
import javax.faces.convert.ConverterException;
import javax.faces.convert.FacesConverter;

import oracle.adf.share.logging.ADFLogger;

 * Custom JSF Faces Converter to convert entries in Currency Input Texts in a correct way. Oracle ADF seems to be a bit
 * confused, if the grouping character in a Locale is '.' and the decimal delimiter is '.' (for example in Germany).
public class CurrencyConverter implements Converter {

     * Class logger.
    private static final ADFLogger LOG = ADFLogger.createADFLogger(CurrencyConverter.class.getName());

     * Converter method from UI Entry to data value. Takes an Input String from the UI Component and converts it to a
     * BigDecimal value for data changes.
     * @param facesContext current JSF Context
     * @param uIComponent Component that has a new value
     * @param string Entered String value (may contain groupings, delimiter or currency symbol)
     * @return correctly converted BigDecimal object for the given input
    public Object getAsObject(FacesContext facesContext, UIComponent uIComponent, String string) {
        LOG.entering("CurrencyConverter", "getAsObject", new Object[] { uIComponent, string });
        BigDecimal result;

        final Locale locale = facesContext.getViewRoot().getLocale();
        LOG.finest("Locale for Conversion: " + locale.getLanguage());

        if (string != null && !string.isEmpty()) {
            LOG.finest("Parsing numeric sanity of string...");
            Pattern regex = Pattern.compile("[&:;=?@#|]|[a-zA-Z]");
            Matcher matcher = regex.matcher(string);
            if (matcher.find()) {
                NumberFormat f = NumberFormat.getCurrencyInstance(locale);
                throw new ConverterException(f.format(123456.78));

            String res = string;
            DecimalFormatSymbols symbols = new DecimalFormatSymbols(locale);

            // get Locale specific grouping and decimal seperators
            char sep = symbols.getDecimalSeparator();
            LOG.finest("Decimal Separator used: " + sep);
            char grp = symbols.getGroupingSeparator();
            LOG.finest("Grouping Separator used: " + grp);

            // remove groupings
            String valueConverted = string.replace(grp, Character.MIN_VALUE);
            LOG.finest("String removed of groups:" + valueConverted);

            // change decimal seperator to "US" standards
            valueConverted = valueConverted.replace(sep, ".".charAt(0));
            LOG.finest("String with changed decimal separator:" + valueConverted);

            // throw away any non numeric stuff
            res = valueConverted.replaceAll("[^\\d.]+", "");

            LOG.finest("Expected result:" + res);
            result = new BigDecimal(res);
        } else {
            LOG.finest("Input was empty, so create a zero object");
            // this is of course project specific, can also return null etc.
            result = new BigDecimal(0);
        LOG.exiting("CurrencyConverter", "getAsObject", result);
        return result;

     * Converter method to create a correct currency String for a given data object.
     * @param facesContext current JSF Context
     * @param uIComponent UI Component that will get the String value
     * @param object data value that shall be converted
     * @return correct String representation of data to a set Locale
    public String getAsString(FacesContext facesContext, UIComponent uIComponent, Object object) {
        LOG.entering("CurrencyConverter", "getAsString", object);

        final Locale locale = facesContext.getViewRoot().getLocale();

        LOG.finest("Locale for Conversion: " + locale.getLanguage());
        NumberFormat f = NumberFormat.getCurrencyInstance(locale);
        String res = f.format(object);

        LOG.exiting("CurrencyConverter", "getAsString", res);
        return res;

Hint: As you can see from the source code, the converter uses the Locale's specific grouping and decimal seperators, so this converter should be working for any given locale and currency representation.

Next, add this converter to a converter component inside the UI Field that should use this converter:

That's it, we can now just enter the values in simple fashion.

If we enter the value 9999,99 once again, we will see the estimated behaviour.

This entry shows, how easy it is to create a converter to a RichUIInput component, another nice use case could be, that you enter a value with a currency and then use the converter to calculate the currency exchange rate and thus convert it to the databases default currency.

Feel free to check out an example project from the German ADF Community (a subgroup of the German Enterprise Application Development Group) github:


Thanks for the read, cheers!


TAKE Four - Amazon Echo (Alexa) mit PL/SQL

Es ist wieder soweit, TEAM lädt zum vierten TAKE (TEAM After Work Knowledge). Wer sich schon immer Oracle Datenbank-orientiert mit dem Thema "Sprachgesteuerte Anwendungen mit Amazon Echo" auseinandersetzen oder generell einen Einblick in das Erstellen von Amazon Anwendungen (Skills) erhalten wollte ist hier genau richtig. Das Beste daran? Es ist vollkommen kostenfrei und sie dürfen selbst Hand an legen.

Was erwartet Sie genauer?

Lassen Sie sich schon vorab mit diesem Video von Wolf G. Beckmann in das Thema Amazon Echo (Alexa) mit PL/SQL einführen.
Mit Amazon Echo steuern Sie über die Spracherkennung Alexa Ihr Smart Home und alle damit verbundenen Geräte. Amazon Echo lässt sich aber auch für das eigene Unternehmen einsetzen – speziell mit der Oracle Datenbank-Welt.
Bei TAKE four schreiben wir einen Skill in PL/SQL, da Amazon Echo hervorragend und direkt mit der Oracle-Datenbank zusammen arbeitet.
Lassen Sie sich die Chance für Ihr Business nicht entgehen: Zur Agenda und Anmeldung
TAKE four – „Amazon Echo (Alexa) mit PL/SQL"
Dienstag, 28.03.17 bei TEAM in Paderborn

Beginn: 17:00 Uhr | Ende: 19:30 Uhr
Bitte bringen Sie zur Veranstaltung Ihren eigenen Laptop mit.
Wir freuen uns auf Ihr Kommen zu TAKE four!


Delaying Attribute Validations in ADF for LOV-based fields until commit

I think many ADF developers have encountered the unlazy validation of JSF components during their UI implementations. Especially when creating a new record, the auto submit / partial trigger reactions are quite unpleasant for users. Take this for example:

In this, the user inserted the fields in tab-order. After leaving the field salary (doing a auto submit, triggering the job field), the job validator executes and marks a red border to inform the user of the issue. As the user has never inserted the field up to this point, this is not very nice. One valid change is to remove the mandatory flag from the attribute and delegate the validation towards entity level. But then, you will lose the "red border at attribute" response and get a FacesMessage leaving the user in question, which attribute to change to solve the invalid entity.

Andrejus Baranovskis has written a nice article, how to solve this issue when encountering attributes on the same entity, the validation is occuring on:


So let us extend the usecase a bit.


For many List of Value components, users do not want to see the key (for example JobId) in the input text, but the looked up value (JobTitle in this case).

To implement this, there are many options to take. To combine the lov requirement and the delayed attribute validation, let me show you one implementation, that works and is quite declarative.

Define the following use cases:

  • Users want to be able to change the Job of an employee using a list of values showing the title, not the id of the job
  • When changing the salary of an employee, the job will be nullified
  • An empty job leads to a validation, but this should only occur on save button or navigation

The second use case defines the usage of partial triggers / auto submit on JSF side later on.


To start, we need the base Entity (Employees), a View Object on that entitity (EmployeesView) and a Lookup View Object (JobsLookupView).

Next create a Transient Attribute on Employees Entity, representing the JobName. This will be the target Attribute for the delayed validation and is the base attribute for the lov.

This Attribute uses an expression referring a ViewAccessor on JobsLookupView:


JobId != null ? JobsLookupView.getAllRowsInRange().find{it['jobId']==JobId}['jobTitle'] : null

Next, we will add the entity validation (referring Andrejus' blog entry):

(opt. add failure message)

Add dependencies on Entity attributes:

Additionally remove the mandatory flag from the JobId Attribute (to allow entity validation to do the not null check)

Add LOV to EmployeesView JobName Attribute:

(rem: We can use the Employee-level view accessor in this case because we do not use dynamic query components in this example, so no additional view object instances needed).

Create UI

Finally just create a simple Form Layout on a page, removing the JobId Attribute and ensuring the InputListOfValues Component for JobName.

Then update the following attributes for Salary resp. JobName Components:

Additionally, drag the CreateInsert Operation onto the page, to create a new record.

When running the app, and creating a new employee again tabbing through the form, we will see, there is no validation when tabbing out of the salary field (because the showRequired=true instead of mandatory=true).

When commit or navigation is triggered, the entity validation will fire, but because of the special validation attribute, we will see this on the single attribute instead of a FacesMessage.

This is especially helpful, if you are editing data inside a popup.

TL;DR / Result:

Please feel free to checkout a demo application implementing the shown at the german ADFCommunity github repository


Any questions regarding this topic or want to see a feature/idea implemented in ADF? Just send me a message ;)

mail:     mke@team-pb.de
twitter:  @MarkusKlenke



Individual Frame Layout in ADF 12c via custom RichRenderer Class

In one of our Forms to ADF modernization projects, one of our customers wanted to keep the UI structure more or less the same. For many components, there are best practices to map one Oracle Forms UI Component to ADF Structures, but in our case, we had to provide a solution to the following UI-Container Pattern:

The idea of this pattern is to provide help for the user to see a categorization of UI Items. In general ADF, this might be done via UI Categorization via UI Hints at View Object Level. Unfortunately, this resolves into af:group containers on UI, which are interpreted by the parent container to make sense out of the items. So all in all, we are limited by the frameworks default implementations.

To solve the problem, we thought about the general solution in free html/css context. Here the problem is easy to solve. See


for a sample solution. To reach the explicit HTML code you need out of JSF components is (in most cases) not possible.

Again, ADF supports the creation of own declarative components, but typically we just join other JSF compontents to our liking.

So we have to go one step further. The next steps show you how to create a "component/renderer" mashup to achieve the NamedFramePattern in ADF.

1. Create a declarative container component that has only a panel group layout and a content facetRef. Be sure to add a ComponentClass to your Declarative component for easier access and uniqueness afterwards.

(The Group layout inside is necessary, since all declarative components are not rendered per definition of the UIXBaseComponent-Renderer because it is only a reference for a JSF include).

2. Create a RichRenderer that is based on the renderer for ADF Group Layouts and add additional HTML code to the rendered feature. This can be done by overriding the encodeAll(...) method of the parent class.

package de.teampb.ir.templates.nf;

import java.io.IOException;

import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;

import javax.faces.context.ResponseWriter;

import oracle.adf.view.rich.component.rich.fragment.RichDeclarativeComponent;
import oracle.adf.view.rich.component.rich.layout.RichPanelFormLayout;
import oracle.adf.view.rich.render.ClientComponent;
import oracle.adf.view.rich.render.RichRenderer;

import oracle.adfinternal.view.faces.renderkit.rich.DeclarativeComponentRenderer;

import org.apache.myfaces.trinidad.bean.FacesBean;
import org.apache.myfaces.trinidad.context.RenderingContext;
import org.apache.myfaces.trinidad.render.CoreRenderer;

import oracle.adfinternal.view.faces.renderkit.rich.PanelGroupLayoutRenderer;

public class NamedFrameRenderer extends PanelGroupLayoutRenderer {
    public NamedFrameRenderer() {

    private static final String DEFAULT_COLOR = "#AAAAAA";

    private static final String IMPORTANT_COLOR = "#FFAAAA";

    protected void encodeAll(FacesContext facesContext, RenderingContext renderingContext, UIComponent uIComponent,
                             ClientComponent clientComponent, FacesBean facesBean) throws IOException {
        ResponseWriter rw = facesContext.getResponseWriter();
        if (uIComponent.getParent() instanceof NamedFrame) {
            NamedFrame nf = (NamedFrame) uIComponent.getParent();
            rw.startElement("div", null);
            rw.writeAttribute("style", "border: 2px solid " + getColor(nf) + "; margin: 5px; padding: 5px", null);
            rw.startElement("h2", null);
                              "text-indent: 30px;\n" + "    margin-top: -10px;\n" + "    height: 10px;\n" +
                              "    line-height: 20px;\n" + "    font-size: 15px;", null);
            rw.startElement("span", null);
            rw.writeAttribute("style", "background-color:#FFFFFF; color:#000000;", null);
            rw.writeText(nf.getTitle(), null);
            super.encodeAll(facesContext, renderingContext, uIComponent, clientComponent, facesBean);
        } else
            super.encodeAll(facesContext, renderingContext, uIComponent, clientComponent, facesBean);

    private String getColor(NamedFrame nf) {
        if ("important".equals(nf.getType())) {
            return IMPORTANT_COLOR;
        } else
            return DEFAULT_COLOR;


The idea is that the renderer overrides the default Renderer for Panel Group layouts. So we have to keep the default implementation (the inside super.encodeAll line).

(The HTML code could be beautified by using classes instead of inline style, but I think you get the idea)

3. Add renderer to faces-Config.xml in the consuming ViewController Project.

4. Use declarative component as usual to achieve the layout addition by the renderer.

As you can see from the last image, since the JDeveloper design view uses the internal renderer classes we also get the advantage of what-you-see-is-what-you-get development. The resulting ADF application then looks as follows:

Please feel free to download the sources at


and pull the sources. To run the application, you need access to an HR schema. Then Run the hr-main.xml Task-Flow.