CODEWITHSUNDEEP
javaoracle-databaseclobjava-stored-procedures
Erfan Ahmed
Erfan Ahmed

Reputation: 1613

Oracle Java stored function - Inconsistent data type for CLOB

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?

Upvotes: 1

Views: 1554

Answers (2)

Y K
Y K

Reputation: 1

You can use connection, you don't need any additional data to create it. Here is my solution

import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class TestClob {
    public static Clob decode(Clob clobParam) throws SQLException {
        String value = clobParam.getSubString(1, (int)(clobParam.length()));
        Connection conn = DriverManager.getConnection("jdbc:default:connection:");
        Clob cOut = conn.createClob();
        cOut.setString(1, "Decoded " + value);
        return cOut;
    }
}

create or replace function TestClob(obj Clob) return Clob
as language java name 'TestClob.decode(java.sql.Clob) return java.sql.Clob';

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21095

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

Upvotes: 2

Related Questions