How to connect to Oracle DB and raise user defined exception using Jython from ODI ?

Jython Code: In this example I am just connecting to DB and raising an user exception

For this you have to create a procedure and copy below code on target on command and on source on command you have to select corresponding logical schema and database to which you want to connect.

import java.util.Date as Date

from com.ziclix.python.sql import zxJDBC

# Getting credentials using API's

URL = "<%=odiRef.getInfo("SRC_JAVA_URL")%>"

userName = "<%=odiRef.getInfo("SRC_USER_NAME")%>"

password = "<%=odiRef.getInfo("SRC_PASS")%>"

#Driver we are using to connect to Database

Driver = "oracle.jdbc.driver.OracleDriver"

#Connecting to database

db = zxJDBC.connect(URL,userName,password,Driver)

c = db.cursor()

sqlQry = "SELECT TO_TIMESTAMP(TO_CHAR(SYSTIMESTAMP,'dd/mm/yyyy hh24:mi:ss'),'dd/mm/yyyy hh24:mi:ss') ,  TO_TIMESTAMP(TO_CHAR(SYSTIMESTAMP+2,'dd/mm/yyyy hh24:mi:ss'),'dd/mm/yyyy hh24:mi:ss') FROM dual"

#Executing the query

c.execute(sqlQry)

#Fetching values

for cur in c.fetchall():

time1 = cur[0].time()

time2 = cur[1].time()

        #Raising user defined exception to the check values in operator

if (time1 <= time2):

try:

raise ValueError("Time1:"+str(time1)+" Time2:"+str(time2))

except:

raise

db.close()

else:

try:

raise ValueError("Time1:"+str(time1)+" Time2:"+str(time2))

except:

raise

db.close()