Java stored procedure in insert/update trigger on CLOB column

From: Kevin Rodgers <ihs_4664_at_yahoo.com>
Date: Thu, 21 Oct 2004 12:29:12 -0600
Message-ID: <2tqdfuF2268niU1@uni-berlin.de>

Disclaimer: I'm a very inexperienced Java/JDBC/Oracle programmer.

We have a new database that is populated via replication from our old database. Some columns in the databases use different formats, so we use update/insert triggers in the new database to convert the values. A simple example is converting ad hoc language codes like "ENGL" to ISO 639 codes ("en"). That is easily handled by a PL/SQL stored procedure, called from the trigger.

There are more complicated examples that we want to implement as Java stored procedures. (Nothing as complicated as invoking the Xalan XSLT processor, but it could happen.) This works fine for VARCHAR2 columns, where the Java method takes a String and returns a String:

CREATE OR REPLACE FUNCTION convertFoo(old_foo VARCHAR2) RETURN VARCHAR2   AS LANGUAGE JAVA
NAME 'com.ihs.metabase.convertFoo(java.lang.String) RETURN java.lang.String';

But we haven't been able to get a version working for a CLOB column:

CREATE OR REPLACE FUNCTION convertBar(old_bar CLOB) RETURN CLOB   AS LANGUAGE JAVA
NAME 'com.ihs.metabase.convertBar(java.sql.Clob) RETURN java.sql.Clob';

I've been Googling like crazy, and am really stumped. Here's the error message that we get in sqlplus:

ORA-29532: Java call terminated by uncaught Java exception: java.io.IOException: ORA-22275: invalid LOB locator specified ORA-06512: at "METABASE.BAR_TRIGGER", line 3 ORA-04088: error during execution of trigger 'METABASE.BAR_TRIGGER'

And here's the offending code:

package com.ihs.metabase;

import java.io.*;               // Reader, Writer, IOException
import java.sql.*;              // Clob, SQLException

import oracle.sql.CLOB;

     public static Clob convertBar (Clob bar)
         throws SQLException, IOException
     {
         Reader input = bar.getCharacterStream();
         Writer output = ((CLOB) bar).getCharacterOutputStream();
         char[] cbuf = new char[32*1024]; // 32 KB
         int chars;

         while ((chars = input.read(cbuf)) != -1) {
             output.write(convertFoo(new String(cbuf, 0, chars)));
         }
         input.close();
         output.flush();
         output.close();
         return bar;
     }

(The only reason I import oracle.sql.CLOB is to cast bar and call the getCharacterInputStream method, because we're running Oracle 9.2 which only supports Java 1.3, and the setCharacterStream method was added to the java.sql.Clob interface in Java 1.4.)

Thanks,

-- 
Kevin Rodgers

Received on Thu Oct 21 2004 - 13:29:12 CDT