Oracle Java存储函数-CLOB数据类型不一致

Question

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

并添加了相应的Java类。

当我调用这个函数parse_html(clob_col_name)-

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

我得到了以下错误-

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

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

    return null;
}

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();
}

在返回解析后的值之前，我还需要将string转换回clob。

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;
}

在调用函数时-

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

我得到了以下错误-

Answer 1

您在Oracle Java存储过程中使用string声明的Java方法仅适用于varchar2参数。

这里有一个简单的例子

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';
/

要使用CLOB，选项是声明oracle.sql.CLOB，但是这个类si显然不推荐使用java.sql.CLOB进行补偿

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';
/