Home » Developer & Programmer » JDeveloper, Java & XML » Binding variable for DBMS_SPM.NAME_LIST in OracleCallableStatement
Binding variable for DBMS_SPM.NAME_LIST in OracleCallableStatement [message #385976] Thu, 12 February 2009 02:03
casttree
Messages: 83
Registered: August 2008
Member
I need to run dbms_spm.evolve_sql_plan_baseline function in jdbc. dbms_spm.evolve_sql_plan_baseline has a input paramter plan_list type as DBMS_SPM.NAME_LIST,

What type should set for NAME_LIST in OracleCallableStatement?


            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();



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 for DBMS_SPM.NAME_LIST, the definition is below
Defined Data Type TYPE name_list IS TABLE OF VARCHAR2(30); 




Previous Topic: extractvalue for xmltype
Next Topic: I get a SQLException, when trying to connect to oracle 10g xe in java(NetaBeans IDE 6.1)
Goto Forum:
  


Current Time: Thu Mar 28 10:43:54 CDT 2024