Connecting JasperReport server to Oracle RAC using JDBC

Due to a bug in JasperReport server v4.7, it is not possible to enter JDBC Oracle TNS style descriptor for RAC as a Data Source.  This bug is documented on Jaspersoft Community Forum.

Workaround is as follows:

1) change in JasperServer 4.7 configuration file- validation.properties
example path on Windows server:
C:\Program Files\jasperreports-server-cp-4.7.0\apache-tomcat\webapps\jasperserver\WEB-INF\classes\esapi\validation.properties

# similar to AlphaNumPunctuation, and add space explicitly
# Validator.PathWithSpace=^[\\p{L}\\p{M}\\p{N}\\p{Pd}\\p{Pc}\\p{Po}\\p{Pi}\\p{Pf}\\s\\=\\:\\/\\%\\.\\~\\|]*$

# intead of line above, insert line below
Validator.PathWithSpace=^[\\p{Ps}\\p{Pe}\\p{L}\\p{M}\\p{N}\\p{Pd}\\p{Pc}\\p{Po}\\p{Pi}\\p{Pf}\\s\\=\\:\\/\\%\\.\\~\\|]*$

2) change in JasperServer 4.7 configuration file- security-config.properties
example path on Windows server:
C:\Program Files\jasperreports-server-cp-4.7.0\apache-tomcat\webapps\jasperserver\WEB-INF\classes\esapi\security-config.properties

# change variables to false!
# Turns request parameter validation on or off.
security.validation.input.on=false
# Turns CSRF attack guard on or off.
security.validation.csrf.on=false
# Turns sql validation on or off.
security.validation.sql.on=false

3) login as Tomcat administrator (Tomcat Web Application Manager) and do Reload on application jasperserver (JasperServer UI application)

4) login as JasperReport server administrator and enter new Data Source, now it is possible to enter Oracle TNS style descriptor for RAC in URL field
example:

jdbc:oracle:thin:@(DESCRIPTION=(enable=broken)(load_balance=yes)(address=(protocol=TCP)(host=192.168.0.1)(port=1521))(address=(protocol=TCP)(host=192.168.0.2)(port=1521))(connect_data=(service_name=orcl)(failover_mode=(type=select)(method=basic))))


Links:
http://community.jaspersoft.com/jasperreports-server/issues/2727
http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configuration
http://community.jaspersoft.com/wiki/jaspersoft-security-security-configuration-files
http://community.jaspersoft.com/questions/543822/problems-connect-server-using-jdbc-rac

Searching through source code in Oracle Apex repository

In Apex SQL Workshop, there is an option to search through PL/SQL code, but this is limited to code in your database shema, and does not extend to code that you put in your pages.

By snooping around APEX_040000 shema, one can find that apex page code is saved in CLOB format.  We can easily display code that is not longer that 4000 bytes, and if it is, it probably should be put in database package/procedure.

Searching through PL/SQL page processes, in my example for 'mime_type':

 SELECT  application_id,  
      application_name,  
      page_id,  
      page_name,  
      process_name,  
      process_point,  
      DBMS_LOB.SUBSTR (PROCESS_SOURCE, 4000, 1) source  
  FROM  APEX_APPLICATION_PAGE_PROC  
  WHERE  DBMS_LOB.SUBSTR (PROCESS_SOURCE, 4000, 1) LIKE '%mime_type%'  

Searching through PL/SQL shared application processes, in my example for 'mime_type':

 SELECT  application_id,  
      application_name,  
      process_name,  
      DBMS_LOB.SUBSTR (PROCESS, 4000, 1)  
  FROM  APEX_APPLICATION_PROCESSES  
  WHERE  DBMS_LOB.SUBSTR (PROCESS, 4000, 1) LIKE '%mime_type%'  

Searching through Javascript,  in my example for 'getElementById':

 SELECT  application_id,  
      application_name,  
      page_id,  
      page_name,  
      NVL (DBMS_LOB.SUBSTR (JAVASCRIPT_CODE, 4000, 1),  
        DBMS_LOB.SUBSTR (PAGE_HTML_HEADER, 4000, 1))  
       js  
  FROM  APEX_APPLICATION_PAGES  
  WHERE  NVL (DBMS_LOB.SUBSTR (JAVASCRIPT_CODE, 4000, 1),  
        DBMS_LOB.SUBSTR (PAGE_HTML_HEADER, 4000, 1)) LIKE  
       '%getElementById%';  

It would be nice to have this in a easy to call function, or even inside Oracle Apex.

When Apex/jQuery UI Datepicker is not enough

Recently, I had a need for list of values that will be able to pick a week in a year.  After a few futile tries with custom SQL function, I needed to have a look at options that Jquery UI Datepicker has. That led me to a even better datepicker than the one that comes with Apex 4 -the Datepick. It turns out that our Apex/Jquery UI Datepicker is stripped down version of this fantastic picker. There are just 2 files to be incuded since  jQuery is already in:

<style type="text/css">@import "css/jquery.datepick.css";</style>
<script type="text/javascript" src="js/jquery.datepick.js"></script>


If you need all the stuff that is availabile with this picker, don't forget to also include jquery.datepick.ext.js file!