How to bypass procedure the step in ODI 11g?

Scenario:
Based on the option value I need to execute that step or skip that step.

Example:
IKM whenever we select create target table true then only target table will be created. Similar functionality even we can provide to procedure as well.

Process:

1. Create an option for an procedure
2. Disable always execute option as mentioned below:

and select SKIPSTEP option

      3. Option value is True. So that I will skip that step

How to trigger scenario from jython code in ODI?

Triggering Scenario from Jython code:

In this example I am consider all the scenario's whose name starts with SCN_INT.
Path provided here is where standalone agent is installed in my machine. You have to specify according to your machine.

Note: If it is windows make sure that your using '\\' instead of '\' in the path
Oracle won't recommends using directly metadata tables in your script. It is just for your information.


import java.util.Date as Date
from com.ziclix.python.sql import zxJDBC
import java.sql as sql
import os
import thread

def ins(path):
os.chdir("D:\\SoftwareInstalled\\Oracle\\Middleware\\Oracle_ODI\\oracledi\\agent\\bin")
list=os.system(path)

URL = "<%=odiRef.getInfo("SRC_JAVA_URL")%>"
userName = "<%=odiRef.getInfo("SRC_USER_NAME")%>"
password = "<%=odiRef.getInfo("SRC_PASS")%>"
Driver = "oracle.jdbc.driver.OracleDriver"

myCon = sql.DriverManager.getConnection(URL,userName,password)
myStmt = myCon.createStatement()

sqlQry = "SELECT SCEN_NAME,SCEN_VERSION FROM DEV_ODI_REPO.SNP_SCEN WHERE SCEN_NAME LIKE 'SCN_INT%'"

jobRs = myStmt.executeQuery(sqlQry)

while (jobRs.next()):
path = 'startscen.bat '+jobRs.getString("SCEN_NAME")+' '+jobRs.getString("SCEN_VERSION")+' GLOBAL'
thread.start_new_thread(ins,(path,))
myCon.close()

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