Re: Java stored procedure in insert/update trigger on CLOB column

Kevin Rodgers <ihs_4664_at_yahoo.com> wrote in message news:<2tqdfuF2268niU1_at_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,

May be the problem is not in java code per se but in the PL/SQL code in trigger which passes the locator. Error could also be the way PL/SQL function is mapped to java function.

I have done similar things with BLOB i.e. invoke a trigger which calls a
Java function for compressing BLOB's without any problem. Received on Thu Oct 21 2004 - 19:37:45 CDT