JDBC input parameter for type IS TABLE OF VARCHAR2

[Steve Jiang]

I need to set the input parameter for plan_list (Type DBMS_SPM.NAME_LIST) in dbms_spm.evolve_sql_plan_baseline in JDBC ,

dbms_spm.evolve_sql_plan_baseline(
plan_list  IN DBMS_SPM.NAME_LIST,
time_limit IN INTEGER := DBMS_SPM.AUTO_LIMIT,
verify     IN VARCHAR2 := 'YES',
commit     IN VARCHAR2 := 'YES')
RETURN CLOB;

and

Defined Data Type TYPE name_list IS TABLE OF VARCHAR2(30); 

Normally which kind of structure should we use in OracleCallableStatement query?

[Steve Jiang]

I think I find the answers for it. We can use setPlsqlIndexTable of OracleCallableStatement to bind the input parameter as table.

Thanks,

[Steve Jiang]

I tried to

            stmt = (OracleCallableStatement) connection.prepareCall(EVOLVE_SQL);
            // I tried setPlsqlIndexTable, but get the error PLS-00306: wrong number or types of arguments in call
           // stmt.setPlsqlIndexTable(2, name_list, maxLen, maxLen, OracleTypes.VARCHAR, 30); 
            
            stmt.registerOutParameter(1, OracleTypes.CLOB);  
            stmt.execute();

Which type should we set for DBMS_SPM.NAME_LIST in OracleCallableStatement?


Thanks,