[ODI] Calling Stored Procedures From Jython Scripts In ODI

Applies to: Oracle Data Integrator - Version: 3.2.03 Information in this document applies to any platform.Goal Is it possible to call Database Stored Procedures from Jython? If so, what is the syntax? 

 Solution Yes, it is possible to call Stored Procedures from Jython. Example with IN and OUT parameters The following is a sample in which the first parameter is sent to the procedure and the second parameter contains a returned value

import java.sql as sql 

import java.lang as lang 

import java.sql.Types as types 

driver, url, user, passwd = (, , , ) 

##### Register Driver lang.Class.forName(driver) 

##### Create a Connection Object 

myCon = sql.DriverManager.getConnection(url, user, passwd) 

try: 

   inVar1 = 1

   inVar2 = 'myVar2' 

   outVar = 0

   myStmt = myCon.prepareCall();

 ## Bind 1st and 2nd parameters (IN parameters)

   myStmt.setInt(1, inVar1);

   myStmt.setString(2, inVar2);

 ## 3rd parameter is an OUT parameter

   myStmt.registerOutParameter(3, types.INTEGER);

 ## Execute the callable statement

   myStmt.execute(); 

 ## Retrieve value for OUT parameter 

   outVar= myStmt.getInt(3); 

finally:

 ## Close the Connection 

   myCon.close()