CODEWITHSUNDEEP
javasqloracle-databasestored-proceduresoracle10g
John
John

Reputation: 763

Oracle Java Stored Function - ORA-00932: inconsistent datatypes

Attempting to call a java stored function and get a java object back and convert it to an oracle type. This is a 10g database and we are using Java 1.4.2. ( I know upgrade is the answer, unfortunately I am constrained to this environment.)

Code and errors listed below.

Any help would be greatly appreciated.

TestMapping.java

public class TestMapping {
    public static TestObject getResponse(){
        TestObject testObject = new TestObject();
        testObject.setMyTestValue("TEST");

        return testObject;
    }
}

TestObject.java

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class TestObject implements SQLData {
    private String myTestValue;

    private String sqlTypeName;
    public String getSQLTypeName() throws SQLException {
        return sqlTypeName;
    }

    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        sqlTypeName = typeName;
        myTestValue = stream.readString();
    }

    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeString(myTestValue);
    }

    public String getMyTestValue() {
        return myTestValue;
    }

    public void setMyTestValue(String myTestValue) {
        this.myTestValue = myTestValue;
    }
}

Command Line:

javac TestObject.java
javac TestMapping.java
loadjava -user user/pass@server:1521:instance -t -r TestObject.class
loadjava -user user/pass@server:1521:instance -t -r TestMapping.class

Oracle Type:

create or replace
type test_object  as object(
myTestValue  varchar2(50));

Publish Java Function:

create or replace
FUNCTION test_mapping RETURN test_object
AS LANGUAGE JAVA
NAME 'TestMapping.getResponse() return TestObject';

Anonymous Block to Test:

set serveroutput on;
declare 
  response test_object; 
begin
  response := test_mapping();
  DBMS_OUTPUT.PUT_LINE('myTestValue : ' || response.myTestValue);
end;

Error:

Error report:
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
ORA-06512: at "USER.TEST_MAPPING", line 1
ORA-06512: at line 4
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:

Upvotes: 3

Views: 3195

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17944

You need to return an instance of oracle.sql.STRUCT, not an instance of your custom Java type. This is true both in your Oracle publication...

create or replace
FUNCTION test_mapping RETURN test_object
AS LANGUAGE JAVA
NAME 'TestMapping.getResponse() return oracle.sql.STRUCT';

... and in the Java class ...

public static oracle.sql.STRUCT getResponse(){ ...

To build the STRUCT that getResponse() must return, you need to do something like this:

// This is the object you really want to pass back
TestObject result;

STRUCT oracleResult;
try{
  StructDescriptor resultStructDescriptor = StructDescriptor.createDescriptor("TEST_OBJECT", yourOracleConnection);

  Object[] attributes = {result.myTestValue};
  oracleResult = new STRUCT(resultStructDescriptor, yourOracleConnection, attributes);
} catch ( SQLException e ) {
    throw new RuntimeException(e);
}
return oracleResult;

Upvotes: 2

Related Questions