TechWare

Sometimes we need to find out times taken by certain packages for certain reasons. For this we can make use of ODI repository table  SNP_SESSION. So for that we can use following query which you can run on your work repository schema. select  sess_name Package_Name,SESS_BEG Start_Time,SESS_END End_TIme, (SESS_DUR/60) Time_in_Minutes from SNP_SESSION where  (sess_name='PKG_A_VERSION_2' or sess_name='PKG_B_VERSION_2' or sess_name='PKG_C_VERSION_2' or sess_name='PKG_D_VERSION2' or sess_name='PKG_E_VERSION_2' or sess_name='PKG_F' or sess_name='PKG_G' or sess_name='PKG_H') and sess_beg > sysdate-1 order by SESS_BEG; Here we will get result in following format. PACKAGE_NAME START_TIME END_TIME TIME_IN_MINUTES PKG_A_VERSION_2 13-06-13 23:44 13-06-13 23:54 9.783333333 PKG_B_VERSION_2 13-06-13 23:54 14-06-13 0:11 16.8 PKG_C_VERSION2...

Oracle Data Integrator (ODI) is a very powerful product when handled correctly. Unfortunately, some mistakes may lead to dramatic results in Integration projects. This post compiles the Top 10 best practices that avoid the most common mistakes seen in integration projects made with Oracle Data Integrator. Best Practice #1 – Understand and Use Correctly Topology and Contexts The ODI topology and the contexts are one of the most powerful feature for running your design-time or run-time artifacts in various environments. In ODI, all developments as well as executions are performed on top of a  Logical Architecture  (Logical schemas, logical agent), that resolves, in a given  Context  to a  Physical Architecture  (real/physical source/targets data servers/schemas and ODI run-time agents). Contexts allow you to switch the execution of the artifacts from one environment (context) to another. Now, read the previous paragraph again. Make sure you got the ...

You can get any part of data and any sorted order you want with Analytic functions. Its syntax is OVER ((PARTITION BY … ORDER BY …)). Here is some samples with analytic functions I have used. First sample is getting number of (COUNT) employees by departments and ordered by (ROW_NUMBER ) departments. select     A. DEPTNO ,  row_number  ()   over   (partition   by   A. DEPTNO  order   by   A. EMPNO )  DEPT_EMP_ROWNO ,   A. EMPNO ,  row_number  ()   over   (order   by   A. DEPTNO ,   A. EMPNO )  EMP_ROWNO ,   count   (  *  )   over   (partition   by   A. DEPTNO )  DEPT_TOTAL_EMP_CNT ,   count   (  *  )   over   ()  TOTAL_EMP_CNT ,   count   (distinct   A. DEPTNO )   over   ()  TOTAL_DEPT_CNT from    SCOTT . EMP  A order   by ...