Oracle Java stored function - Inconsistent data type for CLOB

Question

I have a CLOB column that stores html code. To parse it, I've written a Java stored function -

CREATE or replace FUNCTION parse_html(value in CLOB) 
  RETURN CLOB  
   AS LANGUAGE JAVA NAME 'MyClass.parse(java.lang.String) return java.lang.String';

and added a corresponding Java class.

When I call this function parse_html(clob_col_name) -

SELECT PARSE_HTML(m.clob_col_name) 
  FROM my_table m
  where length(m.clob_col_name) > 4000;

I got the following error -

ORA-00932: inconsistent datatypes: expected a value at argument position 1 that is convertible to a Java java.lang.String got an Oracle CLOB 00932. 00000 - "inconsistent datatypes: expected %s got %s"

Update 1

As I mentioned earlier, I need to parse the html data using Jsoup. jsoup.parse expects String as an argument. Therefore I need to convert Clob to String. Here is the parse method -

public static Clob parse(Clob clob) {
    String value = clobToString(clob);

    if (!isEmpty(value)) {
        return stringToClob(Jsoup.parse(value).text().trim());
    }

    return null;
}

This method is doing the conversion from Clob to String -

private static String clobToString(Clob clob) {
    StringBuilder sb = new StringBuilder();

    if (clob == null) {
        return null;
    }

    try {
        Reader reader = clob.getCharacterStream();
        BufferedReader br = new BufferedReader(reader);

        String line;

        while (null != (line = br.readLine())) {
            sb.append(line);
        }

        br.close();
    } catch (IOException e) {
        // handle this exception
    } catch (SQLException e) {
        //e.printStackTrace();
    }

    return sb.toString();
}

And again I need to convert String back to Clob before returning the parsed value.

private static Clob stringToClob(String value) {
    if (isEmpty(value)) {
        return null;
    }

    Clob myClob = null;

    try {
        myClob = new SerialClob(value.toCharArray());
    } catch (SQLException e) {
        //e.printStackTrace();
    }

    return myClob;
}

While calling the function -

create or replace FUNCTION parse(value in CLOB) 
   RETURN CLOB  
      AS LANGUAGE JAVA NAME 'HtmlParser.parse(java.sql.Clob) return java.sql.Clob';

I got the following error -

ORA-00932: inconsistent datatypes: expected a return value that is an instance of a user defined Java class convertible to an Oracle type got an object that could not be converted

Since I used javax.sql.rowset.serial.SerialClob to initialize Clob. I don't want to use Connection in this case. Is there any way around?

Erfan, as the message says, you're crreating a function that takes a CLOB as input, while in java you're expecting a string. So, Oracle is NOT converting the string when passing it (as you tell it that you're passing a CLOB), and the java code complains. So either declare (value in varchar2) or in your java class, tell it to expect a clob. — gsalem

Marmite Bomber Marmite Bomber · Accepted Answer · 2020-01-05T11:48:09

Your declaration of the Java method in Oracle Java Stored Procedure using String will work only for VARCHAR2 parameter.

here a simple example

create or replace and compile java source named  "HelloVarchar" as
public class HelloVarchar
{
  public static String world(String txt)
  {
    return "Hello " + txt;
  }
};
/

CREATE or replace FUNCTION parse_varchar(value in VARCHAR2) 
  RETURN VARCHAR2  
   AS LANGUAGE JAVA NAME 'HelloVarchar.world(java.lang.String) return java.lang.String';
/

To use CLOB the option was to declare oracle.sql.CLOB, but this class si apparently deprecated compensated with java.sql.Clob

Here a trivial example only passing the parameter back

create or replace and compile java source named  "HelloClob" as
import java.sql.Clob;
import java.sql.SQLException;

public class HelloClob
{
  public static Clob world(Clob txt) throws SQLException 
  {
    return  txt;
  }
};
/

CREATE or replace FUNCTION parse_clob(value in CLOB) 
  RETURN CLOB  
   AS LANGUAGE JAVA NAME 'HelloClob.world(java.sql.Clob) return java.sql.Clob';
/

Oracle Java stored function - Inconsistent data type for CLOB

Update 1

1 Answers