JasperReports und REF_CURSOR

In vielen Reports zu JasperReports Migrationsprojekten werden die Daten per REF_CURSOR dem Report zur verfügung gestellt.

Wie diese Cursor in JasperReports verwendet werden möchte ich hier zeigen:

1. Die Beispiel-Routine in der Datenbank:

CREATE OR REPLACE PROCEDURE emps_holen(emp_cursor OUT sys_refcursor,anzahl in number) IS
BEGIN 
  OPEN emp_cursor FOR 
    SELECT first_name,last_name,email 
    FROM employees 
    WHERE rownum < anzahl; 
END; 

2. Report anlegen

als Platzhalter ein select * from dual

und speichern (Finish)

3. REF_CURSOR Aufruf einfügen

Wieder den Query-Editor öffnen 

Language auf plsql umstellen und folgenden Code hinzufügen:

{
  call emps_holen($P{ORACLE_REF_CURSOR},$P{anzahl})
}

Danach wieder den Dialog schließen

und neu öffnen. Durch das Schließen wird der Parameter automatisch angelegt. 

Hinweis: Wenn man den Parameter von Hand anlegt, kommt beim Schließen eine  Meldung, dass man einen eindeutigen Parameternamen eingeben soll. Um die Meldung zu vermeiden das Schließen und wieder Öffnen.

Nun können über Read Fields die Felder eingelesen werden.

Zur Demonstration wie man mit Parametern für die PL/SQL-Funktion umgeht legen wir den Zusätzlichen Parameter anzahl an, der an die PL/SQL-Prozedur übergeben wird.

Dann schließen wir den Query-Editor.

4. Report erstellen

Hier die Felder

(Wunderschönen) Report erstellen

Ausführen

Fertig!

Solving Integrated Weblogic Coherence Issue in JDeveloper 12.2.1.3

Recently after a java update (Oracle JDK 1.8.0_144) and reinstallation of a oracle virtual box version (5.2.18 r), I stumbled across as severe issue regarding my integrated weblogic server as it would not start anymore. Instead it would repeatedly print the following lines:

<03.12.2018 14:51 Uhr MEZ> <Warning> <com.oracle.coherence> <BEA-000000> <2018-12-03 14:51:03.621/41.438 Oracle Coherence GE 12.2.1.3.0 <Warning> (thread=Cluster, member=n/a): This Member(Id=0, Timestamp=2018-12-03 14:50:32.801, Address=xxx.xxx.xxx.xxx:61846, MachineId=27972, Location=site:team-pb.de,machine:pcteam114,process:7564,member:DefaultServer, Role=WeblogicServer) has been attempting to join the cluster using WKA list [/xxx.xxx.xxx.xxx:7574] for 30 seconds without success; this could indicate a mis-configured WKA, or it may simply be the result of a busy cluster or active failover.>

<03.12.2018 14:53 Uhr MEZ> <Warning> <com.oracle.coherence> <BEA-000000> <2018-12-03 14:53:26.720/184.537 Oracle Coherence GE 12.2.1.3.0 <Warning> (thread=Cluster, member=n/a): Delaying formation of a new cluster; unicast networking appears to be inoperable on interface xxx.xxx.xxx.xxx as this process isn't receiving even its own transmissions; consider switching network interfaces>





which stated that the technologically mapped coherence cluster (which consists of only the DefaultServer) was not getting any responses (and thus could not connect to the cluster itself). As there is only one server in our coherence cluster, it will be always the unicast-master and thus needs to check initial pings on itself (as others would ping it in this scenario).

To fix this issue, you could do the following:

1. Remove the coherence cluster as a whole (as this is the integrated WLS, it seems irrelevant for most cases anyway)
2. Set the coherence cluster to be in multicast mode. This would lead to n:m pinging in the cluster, but as there is only one server, it does not bring performance issues as you might get in a real Worldwide clustered environment

Solution 1:

To remove the coherence cluster awareness of the default server, you just have to remove the coherence-cluster-system-resource tag from the domains config.xml file (which resides in your $DOMAIN_HOME/config folder).

<server>   
  <name>DefaultServer</name>
     …  
  <coherence-cluster-system-resource>defaultCoherenceCluster</coherence-cluster-system-resource>
</server>

Just remove the highlighted entry and the server should start (without any awareness of coherence).

Solution 2:

To change the type of coherence the cluster is using, you have to add a property inside the CoherenceSystemResource config file (which is located in the $DOMAIN_HOME/config/coherence folder). The correct *-coherence.xml is the one with the same name as the coherence systemResource as seen in Solution 1 (by default: defaultCoherenceCluster-coherence.xml).

Just add the following highlighted line in the coherence-cluster-params element-tag:

  <coherence-cluster-params>

    <cluster-listen-port>7574</cluster-listen-port>

    <clustering-mode>multicast</clustering-mode>

  </coherence-cluster-params>

This also should lead to the server starting again.

Cheers!

Solving the "file too large" issue for oracle 12c db docker image

Prerequisites:

The challenge was to install a docker image with Oracle 12.2.0.1 Database Standard Edition to a OEL 7 server. The following prerequisites were set:

• OEL 7.4
• DOCKER 17.12
• Openshift Origin v3-latest

For the Openshift cluster, the docker storage-type was set to 'overlay2' (which is the recommended setting). Construction of the image has been done via README.md given by

Oracle official Docker-Images

Issue:

Running through the issue, the following exception occured on step 7/17 of the Dockerfile:

chown: changing ownership of '/opt/oracle/install/linuxx64_12201_database.zip': File too large
Removing intermediate container d581497c401d
The command '/bin/sh -c chmod ug+x $INSTALL_DIR/*.sh && sync && 
$INSTALL_DIR/$CHECK_SPACE_FILE && $INSTALL_DIR/$SETUP_LINUX_FILE' returned a non-zero code: 1

Solution:

There seems to be an issue with the storage-type overlay2 and large files on OEL. Thus the first idea was to change the storage-type from 'overlay2' to 'devicemapper'. But this is not recommended for production-like environments. A more serious issue was that the openshift cluster was not able to start up anymore after changing the docker storage type.

Thus we tried another approach. Sometimes Docker locks up files (for chowning) that were introduced via COPY task in previous steps of the Dockerfile. A common workaround for this issue is to make a copy of an issued file, removing the original, doing the chown and moving the copy back to the original. This approach worked in this case. The problematic command can be found in the file

<Dockerfile-Home>/<Version_of_DB>/setupLinuxEnv.sh
change the line

chown -R oracle:dba $ORACLE_BASE

to 

cp $ORACLE_BASE/install/linuxx64_12201_database.zip $ORACLE_BASE/install/rename.zip
rm $ORACLE_BASE/install/linuxx64_12201_database.zip
chown -R oracle:dba $ORACLE_BASE
mv $ORACLE_BASE/install/rename.zip $ORACLE_BASE/install/linuxx64_12201_database.zip

This solves the issue and the docker build should run through.
Cheers!

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

...\jasperserver\WEB-INF\classes\esapi\validation.properties

die Zeile

Validator.ValidSQL=(?is)^\\s*(select|call)\\s+[^;]+;?\\s*$

ersetzen durch

Validator.ValidSQL=(?is)^\\s*(select|call|with)\\s+[^;]+;?\\s*$