Questions on Java applications migrating from Oracle DB to TimesTen.

4228428

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

ChrisJenkins-Oracle

I'm afraid these are all limitations that you will need to work around, but I don't have any good recommendations for easy workarounds, sorry.

Typically you will have to modify your code to eliminate the use of these problem types.

Not the answer you wanted to hear, sorry.

4228428

Hi Chris,

Thanks for your reply. The way our java modules are abstracted makes this migration to TimesTen more challenging.

Few questions related to this:

1) If we log a enhancement request via Oracle Support can the above features be implemented/customized for our use case?

2) What would an estimated implementation timeline look like so that we can be prepared with alternatives?

Appreciate your inputs.

ChrisJenkins-Oracle

Hi,

You can certainly log an ER via support, but a more productive route would be to engage directly with the TimesTen Product Management team (i.e. myself). If you can give me a contact e-mail address I will contact you directly to discuss.

Chris