1) We are using the Oracle Schema level types ARRAYS & STRUCT which I see is not supported by TimesTen. What are the recommendations to change these types to so that we can minimize the changes and abstract it out in a package/library.
2) Associative array binding issue.
Example: TYPE NT_KEYS_V1 IS TABLE OF VARCHAR(16) INDEX BY BINARY_INTEGER;
TYPE NT_SYMBOLS_V1 IS TABLE OF VARCHAR(25) INDEX BY BINARY_INTEGER;
PROCEDURE get_detail_nt(
p_keys_i IN NT_KEYS_V1,
p_symbols_i IN NT_SYMBOLS_V1
p_detail_cur_o OUT sys_refcursor);
Java code:
String SQL = "{call GET_DETAIL_NT(?, ?, ?)}";
TimesTenCallableStatement pstmt = (TimesTenCallableStatement) connection.prepareCall(SQL);
pstmt.setPlsqlIndexTable(1, new String[]{"A","B"}, 2, 2, Types.VARCHAR, 16);
pstmt.setPlsqlIndexTable(2, new String[]{"C","D"}, 2, 2, Types.VARCHAR, 25);
pstmt.registerOutParameter(3, TimesTenTypes.CURSOR);
pstmt.execute();
The above works fine but in a scenario where I want to pass null to the second param:
pstmt.setNull(1, Types.VARCHAR); in TimesTen this assignment does not work. Validated with Types.OTHER/VARCHAR/JAVA_OBJECT etc.
pstmt.setObject(<>,<>,<>) implementations also do not work. This way the default can be applied at Stored procedure level.
Is there any other way of binding associative array apart from setPlsqlIndexTable ?
3) What java.sql.Type should the below be mapped to?
create or replace PACKAGE types_pkg AS
TYPE VARRAY_ACCTS IS VARRAY(25) OF VARCHAR(16);
end types_pkg;
In Stored Procedure if I map input param to
p_accounts_i IN types_pkg.VARRAY_ACCTS DEFAULT NULL
In Java mapping:
new SqlParameter(parameterName, Types.<>);
Validated using Types.OTHER , Types.JAVA_OBJECT but end with [TimesTen][TimesTen 18.1.3.3.2 ODBC Driver]Parameter ? not bound exception.
Since driver does not support Types.ARRAY is there any way we can map the above correctly?
I understand VARRAY is not supported but running the above SP in PL/SQL we are able to get result.
4) Multiple REF cursors
Issue: Is there any means by which TimesTen will support multiple ref cursors?
Exception: [TimesTen][TimesTen 18.1.3.3.2 ODBC Driver]a maximum of one ref cursor per statement is allowed