Update on “Forms 11G and DB function with result cache”

In my previous post I mentioned the problem with compiling a program unit that calls a database function with result cache.

Like I wrote in that post, the problem exists in the PL/SQL Client version.
There’s a patch available to update the PL/SQL client: Oracle Database Server Version Patch 33.

This is a database patch, but it can also be applied to the middleware home.

After applying the patch, the program unit compiles…Problem solved!

So, go and start using Result cache!

XMLTYPE column larger THAN 4000 bytes => ORA-19011

When you try to convert an xml file, which has a tag that contains for example one or more pdf files, into an XMLTYPE object, you will get the following error:
ORA-19011: CHARACTER string BUFFER too small

This error will be thrown because the content of a tag of an XMLTYPE is limited to 4000 bytes(this problem should be solved in 11g).
I have developed a solution/workaround for this.

Continue reading

Java ArrayList, Callable Statements and PL/SQL Procedures

If you’re developing a Java Application which integrates with an Oracle back-end, you’ve probably run into the following technicalities:
transforming an arraylist as a parameter to a callable statement to process and persist the data in your Oracle Database.

How can you accomplish this in a generic and re-usable manner:
1. Create an objecttype (PL/SQL) – Backend:

CREATE OR REPLACE type ot_emp as object
( emp_id number(10),
emp_cd varchar2(20),
name varchar2(100));

2. Create a collection-type based on the objecttype (PL/SQL) – Backend:

CREATE OR REPLACE type ct_emp as table of ot_emp;

3. Create a PL/SQL procedure to process and persist the data coming from the front-end app:

PROCEDURE p_insert_emp(
pi_employees IN ct_emp_coll,
po_message_info OUT ot_message_info);

4. Create a Java Object for each Oracle object-type implementing the SQLData-API:

public class Employee extends SuperEmployee implements SQLData
public void readSQL(SQLInput stream, String typeName) {}
public String
getSQLTypeName() { return
“HR.OT_EMP”; }
public void writeSQL(SQLOutput stream)
throws SQLException {

The readSQL() method is used to map the Oracle data to Java data, the getSQLTypeName() and writeSQL() is used to map java data to Oracle data.

After putting together the framework, you can then pass the arraylist with the callablestatement and process the information into your back-end environment.

Of course this kind of functionality is handled by ORM frameworks as well, such as Hibernate, Toplink, iBatis, …