This time I will present a solution for a character validation on the APEX item level.
Normally you create a simple validation based on a REGULAR EXPRESSIONS which checks for a set of characters which are allowed inside your item. If there are characters inside your item which are not in the list an error will be displayed. Unfortunately the end user doesn't know the exact character which is not allowed. If you have a text-box and you allow 500 characters then this will be a problem for the usability of your application.

Workaround
Create a new validation as:
"Function Returning Error Text"

declare
 v_text varchar2(16000) := :P1_TEXT;
 v_text_cnt number;
 v_text_err varchar2(1000);
begin
 select count(*) into v_text_cnt from dual
 where
 REGEXP_LIKE (v_text,'^[[:alpha:] .,-:;!?[:digit:]]+$');
 /* List of allowed characters */

 if v_text_cnt = 0 then
   select regexp_replace (v_text,'[[:alpha:] .,-:;!?[:digit:]]+'
      )   invalid_characters into v_text_err
   from dual;

   return 'Error. The following characters are not allowed: ' || v_text_err;
 end if;
end;

Advantage:
The end user can search for the wrong character and fix it himself.

I added a forum entry to it: https://forums.oracle.com/forums/thread.jspa?messageID=10237706

Inside APEX it’s possible to specify ‘Page Items to Submit’: Within the action of the type ‘Execute PL/SQL-code’ of a dynamic action. Within a region of the type ‘Interactive report’ or ‘Classic Report’. The listed page items will be submitted to the server and thus available to use within your ‘PL/SQL-code’ or ‘Source’. When you’re using a page item the ‘Session State Protection’ of this item must be ‘Unrestricted’. If you use another setting, par example ‘Checksum Required – Application Level’, the following Javascript-error will occur: Firefox: Error: JSON.parse: unexpected character Source: http://localhost:8585/i/javascript/apex_4_1.min.js Line: 16 Chrome: Uncaught SyntaxError: Unexpected token < apex_4_1.min.js:16

It’s been a while since my last post but this is simply because I’ve never been so busy in my life. Both Peter, myself, and the team have been working long hours 7 days a week, but… today I’m taking a moment to breathe and give you some insight what’s happening behind the scenes within the formation of our new company.

It’s all about plugins

We have built (and continue to build) a framework of plugins that is essentially a new foundation layer for APEX application development. We have been modularly building these plugins for over 12 months and constantly refactoring them to work cohesively together or independently. Using several plugins or the entire suite, developers can build rich internet applications backed by the Sencha Ext JS 4 UI library and Application Express.

But plugins are just the beginning

My personal drive for building plugins is not for the achievement/reward of writing them, but to use them, and use them in multiple different ways. The ultimate goal is so we can achieve any complex development requirement thrown at us. Now that we have the first version of the framework almost ready, we can start building applications. This is the exciting part which allows us to see a whole range of different possibilities and understand where we still need to improve to make things even better than they currently are.

Framework Experiments

In order to document and use the plugins we are working on ways to demo their features in a real world usable way. One of the most usable ways we can think of is to assist the APEX builder and look for ways to help improve developer productivity in building applications using our plugins. Both Peter and myself are devoted SQL Developer users and we want to bring some of those GUI capabilities to APEX. Here’s a screenshot from our LAB of our FBuilder project that we’re designing to do just that! (and built using just our plugins in APEX)

Inspiration has come from a lot of places, a special mention goes out to Noel Portugal for his OraTweet solution released a number of years ago… I hope Noel doesn’t mind us promoting his work and drawing inspiration from with our tweet deck inspired demo using our twitter and layout plugins.

Partnerships

To ensure we have the most secure plugins available we have formed an alliance with Recx Ltd but our partnership goes beyond that. We want people to build the most beautiful and secure applications possible, and to do that we want to evangelize how important security is and embed it within the cycle of your development process. But were not security specialists and that’s why we called on the best in the business, Recx Ltd, to be part of a new era in APEX development setting a whole new standard.

Release Dates

It won’t be long before we’re releasing our new company website and opening an invitation up for interested parties willing to be involved in our hosted framework beta testing program, not dissimilar to apex.oracle.com. With a downloadable release to follow after completion of the beta trials. For those that have immediate project requirements we will be open to discussions to help get you moving ASAP before our official release launch.

Spreading the Word

Peter and I will both be at the Austrian User Group conference day in June, and will both be attending Kscope 12 and presenting. If your interested in what we are doing we urge you to come and meet us and talk with us. We’re both passionate about APEX development and want to plant the same seeds in you, that were planted in us.

Patience

The amount of work to get where we are has been huge, but it pales in comparison to the mountain that is ahead of us which we have started to climb. We’re on a long journey and sometimes we underestimate the amount of time it takes to get to the next base camp. The point to take away is that we’re always moving forwards and our first base camp is now in sight. We have an ambitious roadmap to bring new applications to life, release version 2.0, produce a Sencha Touch framework for APEX, introduce new modular solutions similar to interactive reports, the list goes on… and on…

So on a final note, as the saying goes “Rome wasn’t built in a day” and it wasn’t…. but soon your applications might be

Related Posts

• February 7, 2012 -- A preview of what’s under development (3)
• October 31, 2011 -- Preview: Ext JS Layouts in APEX (6)
• August 1, 2011 -- APEX Plugin – Interactive Report Dynamic Action Package (6)

Over the next month or so, my posts may slow down a touch.

One exciting reason involves this little device pictured. I won't let too much out of the bag just yet, but an opportunity presented itself and I just had to take it.

This means I need to start prioritising a few things.

I've been trying to do a technical post every Wednesday, and this has been assisted by a flurry of posts I wrote earlier in the year that I scheduled over the weeks, with the occasional scheduling mistake - a couple of posts per day. The scheduling interface could be better, but I won't complain.

Unfortunately, these pre-written posts are drying up, I have plenty of e-mails tagged ready to write about, but the task involving the pictured device will take priority. When I get ahead on that schedule, I'll make sure my presentations for the conferences this year are on track, then I'll write some more posts - because I'm enjoying it quite a lot, and I'm also finding my own blog quite useful as a self-reference. It seems the quickest way to find information is to search where you now exactly how/where to look!

Further down on the list, I also want to re-design my blog re-design, especially after following what that Jeff Smith has been up to recently. I would like to ease the clutter and modernise a little more.

Oh, and I have my regular day job, not like some people ;-)

I'll also probably be somewhat quiet in the social media arena for a while. Partially to limit the consumption of information and keep focus, and (fortunately for me) I'll be on staggered holidays over the next month.

I hope all of you aren't working too hard, and I'll see you on the other side.

Scott.

Oracle Database has had the possibility to run Java code inside the database for a long time. It's a very rare occasion when you need to use it but still. Here is one example I used to download content from HTTPS website that required user certificates for authentication. Please take the code below more as an example how to put simple Java code inside the database, not as a solution for user certificates authentication, because UTL_HTTP can do the same thing (although I wasn't successful in implementing it under 11.2.0.2).

First, load the Java source into database. The code below shows:

• How to return simple datatype (int) from Java function - makeConnection
• How to return Oracle CLOB datatype from Java - makeConnectionClob
• How to execute SQL from Java, in the same calling session

Note that method main is just added for testing from command line.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "HttpsHandler" as
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;

import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLContext;
import javax.net.ssl.SSLSocketFactory;
import javax.net.ssl.SSLSession;

import java.sql.Connection;
import java.sql.PreparedStatement;
import oracle.jdbc.driver.*;
import oracle.sql.CLOB;

public class HttpsHandler {
  
  
  public static CLOB makeConnectionClob(String keyStorePath, String keyStorePass, String trustStorePath, String httpsUrl, String proxyHost, String proxyPort) throws Exception {
    int i = makeConnection(keyStorePath, keyStorePass, trustStorePath, httpsUrl, proxyHost, proxyPort);
    String s = Integer.toString(s);
    OracleDriver driver = new OracleDriver();
    Connection dbconn = driver.defaultConnection();
    CLOB clob = CLOB.createTemporary(dbconn, false, CLOB.DURATION_CALL);
    clob.setString(1, s);
    return clob;
  }
  
  public static int makeConnection(String keyStorePath, String keyStorePass, String trustStorePath, String httpsUrl, String proxyHost, String proxyPort) throws Exception {
      //
      System.setProperty("javax.net.ssl.keyStore", keyStorePath);
      System.setProperty("javax.net.ssl.trustStore", trustStorePath);
      //System.setProperty("javax.net.debug", "ssl");
      System.setProperty("javax.net.ssl.keyStorePassword", keyStorePass);
      
      if (proxyHost != null && proxyPort != null) {
        System.setProperty("https.proxyHost", proxyHost);
        System.setProperty("https.proxyPort", proxyPort);
      }
      
      //
      SSLSocketFactory sslsocketfactory = (SSLSocketFactory) SSLSocketFactory.getDefault();
      URL url = new URL(httpsUrl);
      HttpsURLConnection conn = (HttpsURLConnection)url.openConnection();
      conn.setConnectTimeout(8000);
      conn.setSSLSocketFactory(sslsocketfactory);
      // Do not verify that hostname matches the certificate
/*      conn.setHostnameVerifier(new HostnameVerifier() {        
          public boolean verify(String hostname, SSLSession session)  {  
        return true;
          }
      });*/
      // Set request header
      conn.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
      InputStream inputstream = conn.getInputStream();
      InputStreamReader inputstreamreader = new InputStreamReader(inputstream);
      BufferedReader bufferedreader = new BufferedReader(inputstreamreader);
      
      OracleDriver driver = new OracleDriver();
      Connection dbconn = driver.defaultConnection();
      PreparedStatement dml_stmt = dbconn.prepareStatement("INSERT INTO https_output (num, line) VALUES (?,?)"); 
      
      String s = "";
      int linecount=0;
      while ((s = bufferedreader.readLine()) != null) {
        linecount++;
        dml_stmt.setInt(1, linecount);
        dml_stmt.setString(2, s);
        dml_stmt.executeUpdate();
      }
      dml_stmt.close();
      
      return linecount;
  }
  
  public static void main(String[] args) {
    try {
      int i = makeConnection("/path/to/keystore.jks", "keystore_pass", "/path/to/truststore.jks", "https://site.that.requires.user.cert/authentication/", null, null);
      System.out.println(Integer.toString(i));
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }

};

Then you need to create a wrapper package in database. This declares the PL/SQL wrapper function names and input/output parameters.

CREATE OR REPLACE package https_user_cert_wrapper as

  FUNCTION make_request(keyStorePath IN varchar2, keyStorePass IN varchar2, trustStorePath IN varchar2, httpsUrl IN varchar2, proxyHost IN varchar2, proxyPort IN varchar2)
  RETURN number AS LANGUAGE JAVA 
  NAME 'HttpsHandler.makeConnection(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return java.lang.int';

  FUNCTION make_request_clob(keyStorePath IN varchar2, keyStorePass IN varchar2, trustStorePath IN varchar2, httpsUrl IN varchar2, proxyHost IN varchar2, proxyPort IN varchar2)
  RETURN clob AS LANGUAGE JAVA 
  NAME 'HttpsHandler.makeConnectionClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return oracle.sql.CLOB';

end;
/

Download the source: java_source.java and PL/SQL wrapper.sql.

When you first execute the code, you will most likely get some privilege errors, but the error message will tell you how to grant the needed privileges. For example, for this code the following grants were needed:

exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStore', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.trustStore', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStorePassword', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', 'site.that.requires.user.cert', 'resolve' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', '1.2.3.4:443', 'connect,resolve' );

Oracle Wallet Manager and orapki do not let you extract the private key associated with user certificate located in Oracle Wallet. If you need it for some reason, for example testing with external tools like wget, then its possible to extract the private key using openssl, since Orale Wallet (ewallet.p12 file) is just PKCS#12 file.

[oracle@jfadboc1n01 wallet]$ openssl pkcs12 -in /home/oracle/wallet/ewallet.p12 -nocerts -out private_key.pem
Enter Import Password:
MAC verified OK
Warning unsupported bag type: secretBag
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
[oracle@jfadboc1n01 wallet]$ ls -l
total 16
-rw-r--r-- 1 oracle oinstall 11629 May 15 11:38 ewallet.p12
-rw-r--r-- 1 oracle oinstall  1879 May 17 08:53 private_key.pem

Here private_key.pem contains the private key extracted from Oracle Wallet.

The first password that openssl asks (Enter Import Password) is the wallet password, the other password (Enter PEM pass phrase) is used to protect the exported key.

I'm sure most people know by now that we have a VM we build and run the OTN Developer Day events based on those VMs. The key to running the events is the Oracle By Example labs. They are a great way to learn the tools and software in the VM. What everyone may not know is all the OBEs are online as well. The are runnable anywhere not just in the VM we build. The Oracle Learning Library has

For sometime Kris Rice and I have talked about wanting to do a "Soup-to-Nuts" series of labs. With many thanks to Marcie Young from Curriculum Development, such a series now exists on the Oracle Learning Library (OLL) : http://apex.oracle.com/pls/apex/f?p=44785:24:0::NO::P24_CONTENT_ID,P24_PREV_PAGE:6265,1

Marcie is also the one responsible for building the whole OLL site in APEX and even developing the Mobile version of the site.

The workshop series includes the following labs:

• Data Modeler
• SQL Developer
• APEX (x2)

Environment: Oracle database 11.2.0.3, Oracle Linux 6.2

I was debugging a possible network problem on a Oracle Linux server with a fresh installation of Oracle 11gR2, when I noticed the following:

$ netstat -s

Tcp:
1178980 active connections openings
273496 passive connection openings
911657 failed connection attempts

The number of “failed connection attempts” increased by one for every 2 seconds. So, something was clearly trying to connect to something else and this failed for some reason. I didn’t find how I could further debug this problem with netstat. However, I had free access to the server, so I tried to shut down the Oracle services one by one to see if this would stop the growing number of failed connection attempts. When I stopped the Oracle listener service, it did…

Listener logging was on and there were a lot of warnings in the listener.log file:

WARNING: Subscription for node down event still pending

I did a search on My Oracle Support and I found more information in the MOS document with ID 372959.1. Apparently, the listener service tries to contact a RAC service, but, since this is a non-RAC installation, the connection fails. The solution is to add SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener name>=OFF to your listener.ora file, and then restart the listener. This should remove the warnings and stop the failed connection attempts.

This is how the listener.ora file now looks like for my listener with name “LISTENER”:

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-tst.mydomain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

# see metalink note 372959.1
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

Matthias