Call oracle stored procedure with oracle object parameter

Question

I upgraded oracle and ojdbc versions from 11.1.0.2 to 12.1.0.2. I want to give an array of oracle object types as a parameter to an oracle procedure. This procedure takes 2 varchar2 parameters on oracle side. For example, I am sending 3 objects as an array of key-value pairs and oracle accepts these objects. My problem is that the parameters (of varchar2 type) are null, when calling my procedure.

Here is the code I have tried.

OracleConnection oraconn = conn.unwrap(OracleConnection.class);
Struct[] paramStructArray = new Struct[3];
paramStructArray[0] = oraconn.createStruct("KEY_VALUE_ENTRY",new Object[]{"key1","value1"});
paramStructArray[1] = oraconn.createStruct("KEY_VALUE_ENTRY",new Object[]{"key2","value2"});
paramStructArray[2] = oraconn.createStruct("KEY_VALUE_ENTRY",new Object[]{"key3","value3"});
Array array = oraconn.createOracleArray("KEY_VALUE_MAP", paramStructArray);

CallableStatement cstmt = getStatement(Statement.REGISTER_REQUEST);
cstmt.setString(1, requestId);
cstmt.setArray(2, array);
cstmt.execute();

This is my java code and here is also my oracle objects


CREATE OR REPLACE TYPE "KEY_VALUE_ENTRY"  AS
OBJECT (
  key VARCHAR2(32),
  val VARCHAR2(2048)
);


CREATE OR REPLACE TYPE "KEY_VALUE_MAP"    AS
TABLE OF key_value_entry;

and my procedure

PROCEDURE register_request_(p_request_id IN varchar2
                             ,p_params     IN key_value_map) AS
  BEGIN
    IF p_params IS NOT NULL THEN
      INSERT INTO test_table
        (request, NAME, VALUE)
        SELECT test_seq.nextval
              ,t.key
              ,t.val
          FROM TABLE(CAST(p_params AS key_value_map)) t;
      COMMIT;
  END IF;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END;

I am taking 3 KEY_VALUE_ENTRY type objects on oracle side but this objects' key and val fields are both null. I can not figure out what I am doing wrong.

Did this work as expected before the upgrade? Just check as I dont know if it did or didnt? — MNM, Jun 17 '19 at 07:49
Yes it worked before update, but the code was little different, There was classes like STRUCT , ARRAY and ArrayDescriptor which are deprecated on this version of ojdbc — Gog1nA, Jun 17 '19 at 07:53
You may have to rewrite a lot then I would suspect, What I would do is revert the code back to its working form and then step by step replace each section all the while debugging it. So make a change debug make a change debug. Its a little tedious, but I think that you can find where the issues are — MNM, Jun 17 '19 at 07:56
This is the best I could find, and I still don't think it answers your question. https://stackoverflow.com/questions/37160300/how-to-pass-list-from-java-to-oracle-procedure — MNM, Jun 17 '19 at 08:09
This is what i exactly want and doing same, but for some reason it does not work https://stackoverflow.com/questions/19888520/pass-array-from-java-to-oracle-java-sql-sqlexception-fail-to-convert-to-intern — Gog1nA, Jun 17 '19 at 08:10
For the two variables, is it sending null or just receiving null? — MNM, Jun 17 '19 at 08:13
I think there is problem where I am trying to create ```OracleConnection``` from ```Connection``` I returned everything on old version code except creating ```OracleConnection``` and it is still same result, when I returned ```OracleConnection``` to old version too, it throws error here is my old version code ``` OracleConnection oraconn = (OracleConnection) ((org.apache.commons.dbcp.DelegatingConnection) conn) .getInnermostDelegate(); ``` — Gog1nA, Jun 17 '19 at 08:36

score 2 · Accepted Answer · answered Jun 18 '19 at 13:11

2

There was Encoding problem, in which java gives and oracle takes this values, after adding orai18n.jar in my classpath everything works fine

answered Jun 18 '19 at 13:11

Gog1nA

376
1
8
30

score 0 · Answer 2 · answered Jun 24 '19 at 15:37

I tried this code and it works as expected without adding orai18n.jar to classpath. I kept db methods as they are.

public void insertTable(List<KeyValueObj> list, Connection conn)
            throws SQLException {
        String str = "{call Test_schema.Test_pkg.Register_Request(?,?)}";
        OracleCallableStatement statement = null;
        OracleConnection oraConn = null;
        String requestId = "111";
        try{
            oraConn = conn.unwrap(OracleConnection.class);
            statement = (OracleCallableStatement) oraConn.prepareCall(str);
            statement.setObject(2, getOracleRowObjectList(list, oraConn));
            statement.setString(1,requestId);
            statement.execute();
        }finally {
            try {
                statement.close();
                } catch (Exception e) {

            }
        }

    }

     private Array getOracleRowObjectList(List<KeyValueObj> list, OracleConnection conn) throws SQLException{
        String type = "Test_schema.KEY_VALUE_MAP";
        Struct[] structList = new Struct[list.size()];

        for(int i=0;i<list.size();i++){
            structList[i] = getOracleRowObject(conn, list.get(i));
        }

        return conn.createOracleArray(type, structList);
    }


    private Struct getOracleRowObject(OracleConnection conn, KeyValueObj obj) throws SQLException{
        String typeRow = "Test_schema.KEY_VALUE_ENTRY";
        return conn.createStruct(typeRow,
                new Object[]{ obj.getKey(),
                              obj.getValue(),

        });
    }

KeyValueObj class

public class KeyValueObj {
    private String key;
    private String value;

    public KeyValueObj(String key, String value) {
        super();
        this.key = key;
        this.value = value;
    }

    public String getKey() {
        return key;
    }
    public void setKey(String key) {
        this.key = key;
    }
    public String getValue() {
        return value;
    }
    public void setValue(String value) {
        this.value = value;
    }   

}

Hope, this would help someone facing the same issue.

Call oracle stored procedure with oracle object parameter

2 Answers2