Oracle JDeveloper can be used to publish PL/SQL procedures and functions as web services and that is a very useful feature. Originally this functionality was available in Oracle JPublisher that was later incorporated into JDeveloper.
Everything seems to be nice when one uses this functionality to wrap stored procedures that return simple types (NUMBER, VARCHAR2, etc.) but as soon as more complex stored procedures are being tried to be published certain limitations occur.
One of such limitations that I find very annoying - inability to publish stored procedures that output REF CURSOR. That, according to Oracle, is a limitation of JDBC (binding problems???). A workaround to this problem could be rewriting that stored procedure as a function. Therefore, if you want a rowset as a result, you must use PL/SQL function that return a REF CURSOR.
Well, OK, if I really need a rowset, I could agree to this workaround IF everything else worked smoothly. For instance, you have a database with AL32UTF8 charset. Probably you’ll want UTF-8 encoding in your application as well. That wouldn’t be a problem if your REF CURSOR contains characters that are available in ISO 8859-1 AKA Latin-1 BUT you’ll have a problem if not.
You won’t notice that someting is not right until you deploy your web service and try to invoke it. Then you’ll probably see an error like this: “Internal Server Error (Caught exception while handling request: java.rmi.RemoteException: java.sql.SQLException: Invalid UTF8 encoding.)“.
If you have this problem it doesn’t matter whether you’re using a standalone or application server version of OC4J (at the moment of writing current version is 10.1.3.2). The problem lies in the default encoding that is ISO 8859-1 (historical legacy???). Notice this when deploying the generated Java classes from JDeveloper to your Oracle Application Server - file encoding always defaults to ISO 8859-1. Don’t forget to change that according to your situation.
Anyway, there is only one solution (known to me) to this problem. If you’re using a standalone OC4J instance for debugging in JDeveloper then go to your project properties, J2EE Application settings, and in the configuration for the Embedded OC4J Command Line options string, after ${oracle.jms.lock.flag} add this: -Dfile.encoding=utf-8 . This will solve your UTF-8 problem and web service will start working properly. Just don’t forget to terminate the currently running OC4J instance for the new setting to take effect.
As for Oracle Application server, the same Java flag should be applied to the Server section of the OC4J instance you intend to use for your web services. In your Application Server control, select the OC4J instance, click on Administration, select Server properties, add a new option line to Start-parameters: Java Options and insert -Dfile.encoding=utf-8 , click Apply and restart this OC4J instance. I hope you’re smart enough not to use the default home OC4J instance for your applications and have created a new OC4J instance for your custom development purposes - this will save lots of time for you, trust me.
