Extract Content in CLOB datatype (XML) in Oracle

Scripts:

Query:

SELECT
   XMLTYPE(CCS.xml).EXTRACT('//price/text()').getStringVal() as PRICE
FROM
   CLOB_CONTENT_SEARCH CCS;

Output:

What is mean by Schema and Work Schema in ODI?

Work Schema - Where ODI will create $ tables like E$ , C$ ..,
Schema - Where Actual data resides

How to Extract data from database and populate it as Header and Detail style into Fixed length file?

Scenario:
Let's take one scenario where we need to populate Department Name (Header) and correspondent Employee information (Detail) under that Department.

Assumption:
You should aware of Topology Configuration for Oracle and File Technology
You should aware of Creating Oracle Model & File Model
You should aware of Creating variable

Steps:

1. Creating File Datastore (Header)-- Right click on File Model and select new datastore.
Mention as per below screenshot - Defintion , File and Attributes Tab

Definition Tab



Files Tab



Attributes:

2. Create file datastore (Detail) -- Right click on file Model and select new datastore

3. Create Mapping
Drag and drop Source Datastores (Employees and departments) from oracle Model
Drag and drop Target Datastores (Header and Details)

Create Variable - v_department_id

Drag and Drop filter component and join Out connector of department datastore with in connector of filter component and specify as below.

DEPARTMENTS.DEPARTMENT_ID = #PRACTICE_PROJECT.v_department_id

Drag and Drop filter component and join Out connector of department datastore with in connector of filter component and specify as below.

EMPLOYEES.DEPARTMENT_ID = #PRACTICE_PROJECT.v_department_id

Connect out connector of Department Filter Component with in connector of datastore (header) component and disable the "Create attributes of Target" and keep remaining as it

Connect out connector of Employee Filter Component with in connector of datastore (detail) component and disable the "Create attributes of Target" and keep remaining as it

Do mapping for name as "EMPLOYEES.FIRST_NAME||'.'||EMPLOYEES.LAST_NAME"
Dept_name as "DEPARTMENTS.DEPARTMENT_NAME"

Create a sequence for Department
seq_dept

Now map Department_Seq with "#PRACTICE_PROJECT.seq_dept_NEXTVAL"
Department_seq with

Save Mapping

4. Create Package -- Right click on package and select new package
Drag and drop Variable and Mapping into package as mention below

5. Create Scenario -- Right click on package and select generate Scenario
Click on ok

(Make a note of Scenario name)

5. Create Procedure -- Right click on Procedure Object and select new procedure
Create one task
Under target side enter following code and select technology as ODI tools
OdiStartScen "-SCEN_NAME=<SCENARIO_NAME>" "-SYNC_MODE=1" "-VERSION=001" "-PRACTICE_PROJECT.v_department_id=#DEPT_ID"

Source Side enter following code and select technology as Oracle and logical Schema related to that Schema

SELECT DEPARTMENT_ID DEPT_ID FROM HR.DEPARTMENTS


Output:

IKM Oracle Incremental Update is running forever

If you are using IKM Oracle Incremental Update make sure the volume of your target should be less than 1 lakh or 0.1 million records otherwise there will be huge impact on loading data into target.

If you having more than 1 Lakh records better to go with IKM Oracle Incremental Update (Merge) knowledge instead of IKM Oracle Incremental Update.

How to create user defined function in ODI 12c?

Scenario:
For a give float number I need to find out decimal length and precision length

TEST (SOURCE)
N_FORMAT VARCHAR2(10)

TEST_TRG (TARGET)
N_FORMAT VARCHAR2(10)
DEC_LEN NUMBER(7)
FRACT_LEN NUMBER(3)

Assumptions:
1.Aware of creating model
2.Aware of creating Interface (table to table load)

Steps:
1.Go to Designer Navigator --> Go to Project Accordion --> Expand Project --> Right click on User Functions and select New User Function
2. Definition Tab
Name : DEC_LEN
Group : LENGTH
Syntax:
      DEC_LEN($(P1))

[Note:  Syntax : <function_name>(<parameter_list>)

3. Implementation Tab
Click on Plus Symbol. Provide following code
(CASE WHEN $(P1) IS NULL THEN 0
     WHEN INSTR($(P1),'.') = 0 THEN LENGTH($(P1))
     ELSE INSTR($(P1),'.')-1
END)

Select Technology as "oracle"

4. Similarly create one more function as mention below
Definition Tab
Name : FRACT_LEN
Group : LENGTH
Syntax:
      FRACT_LEN($(P1))

5.Implementation Tab
(CASE WHEN $(P1) IS NULL THEN 0
       WHEN INSTR($(P1),'.') = 0 THEN 0
     ELSE LENGTH($(P1))-INSTR($(P1),'.')
END)

Select Technology as "oracle"

6.In the interface mapping should be as below:

Output:

How to create file datastore for hierarchy data?

Scenario:
Client is maintainting Customer and Item information in file as follows.

1,1,Trinesh,kumar Reddy,Koduru,250
2,1,1,Soap,10,10,100
2,2,1,Paste,2,75,150
1,2,Tejesh,Kumar Reddy,Koduru,350
2,3,2,Pen,10,15,150
2,4,2,Pencil,10,5,50
2,5,2,Pad,2,50,100
2,6,2,Wallet,1,50,50

The record starts with 1 belongs to Customer information and record starts with 2 belongs to items purchased by the custoemr
1 - Customer information
2 - Item information

I need to load customer information into customer table and Items purchased by customer will be loaded into items table.

Pre-requisites:
Assumptions
1.Already Topology configuration is available
(Physical Architecture
Technology-File
DataServer
Physical Schema(Folder Location)
Technology-Oracle
DataServer
Physical Schema(Schema)

Logical Architecture
Technology-File
Logical Schema(LG_File_SRC)
Technology-Oracle
Logical Schema(LG_ORCL_TRG)
Context
Global
Schema Tab - LG_File_SRC - Physical Schema of File
    LG_ORCL_TRG - Physical Schema of Oracle)

Steps:
1.Login to ODI
2.Go to Designer Navigator
3.Expand Model Accordion
4.Create Model for Source
FILE_SRC(Model)-->Logical Schema as mentioned above (i.e., LG_File_SRC) --> Reverse Engineer tab, select context as global)
Right click model and click on new datastore
Provide DataStore name as "Header" --> Resource Name "Select File Name"
Go to File tab --> File Format as "Delimited" --> Field Seperator (others --> ,)
Go to Attributes tab --> Create attributes as mention below screenshot

Provide DataStore name as "Detail" --> Resource Name "Select File Name"
Go to File tab --> File Format as "Delimited" --> Field Seperator (others --> ,)
Go to Attributes tab --> Create attributes as mention below screenshot

ORCL_TRG(Model) (Assumption you are aware of this step)

5.Right click on Header (datastore) click on viewdata. You can see data in the below screenshot

Right click on Detail (Datastore) click on viewdata. You can see data in the below screenshot

How to create FlexiField and usage of Flexifield?

Scenario:

xyz company, it is having 2 braches one in hyderabad and another one is Chennai. By End of Day I need to populate sales information into Main branch along with branch code but in my source datastore there is no branch_code attributes whereas in my target datastore there is branch_code attribute.

I need to populate branch_code value based on context selection I need populate value into my target datastore.

Scripts:

CREATE USER R_HYD IDENTIFIED BY R_HYD;

/

CREATE USER R_CHN IDENTIFIED BY R_CHN;

/

CREATE USER XYZ IDENTIFIED BY XYZ;

/

GRANT ALL PRIVILEGES TO R_CHN;

/

TARGET DATASTORE

CREATE TABLE XYZ.PROD_SALES (PRODUCT_ID NUMBER(4),

PNAME VARCHAR2(100),

SALES_COUNT NUMBER(3),

BRANCH_CODE VARCHAR2(3),

SALES_DATE DATE);

/

SOURCE DATASTORE (HYDERABAD)

CREATE TABLE R_HYD.PROD_SALES (PRODUCT_ID NUMBER(4),

PNAME VARCHAR2(100),

SALES_COUNT NUMBER(3),

SALES_DATE DATE);

/

SOURCE DATASTORE(CHENNAI)

CREATE TABLE R_CHN.PROD_SALES (PRODUCT_ID NUMBER(4),

PNAME VARCHAR2(100),

SALES_COUNT NUMBER(3),

SALES_DATE DATE);

/

INSERT INTO R_HYD.PROD_SALES

VALUES ( 1,'SOAP',20,SYSDATE);

/

INSERT INTO R_HYD.PROD_SALES

VALUES ( 2,'BRUSH',10,SYSDATE);

/

INSERT INTO R_HYD.PROD_SALES

VALUES ( 3,'PASTE',30,SYSDATE);

/

INSERT INTO R_HYD.PROD_SALES

VALUES ( 4,'MUG',4,SYSDATE);

/

SELECT * FROM R_HYD.PROD_SALES;

/

INSERT INTO R_CHN.PROD_SALES

VALUES ( 1,'SOAP',23,SYSDATE);

/

INSERT INTO R_CHN.PROD_SALES

VALUES ( 2,'BRUSH',11,SYSDATE);

/

INSERT INTO R_CHN.PROD_SALES

VALUES ( 3,'PASTE',21,SYSDATE);

/

INSERT INTO R_CHN.PROD_SALES

VALUES ( 4,'MUG',2,SYSDATE);

/

SELECT * FROM R_CHN.PROD_SALES;

ODI Steps:

1.Login to ODI

2.Go to Security Navigator

3.Expand Profiles Accordion

4.Expand Topology Admin

5.Double click on Context

6.Go to FlexiFields tab --> Add one flexifield by pressing + symbol

7.Save it

8.Go to Topology Navigator

9.Expand Context Accordion

10.Double click on the Chennai Context

11.Go to FlexiFields tab --> Provide values as 'CHN'

12. Repeat 8-11 steps for Hyderabad context as well

Provide value as 'HYD'

13. Create mapping and drag and drop appropriate datastores from model and select automap. For branch_code the mapping is as shown below

Here BRANCH_CODE is nothing but flexiField Code value if you observe the flexifield in screenshot one you can see this value.

14. Based on context value the branch_code will populated.

For Example if you selected Hyderabad then branch_code value for those records is 'HYD'

if you selected Chennai then branch_Code values for those records is 'CHN'

Completed Successfully!!!!!!!!!!!!!!

How to rename files using ODI?

Approach:

1. Create Package

2. Drag and Drop ODIFileMove from tool bar to package

3. Select ODIFileMove -- Go to Property inspector -- Go to General -- File name option (Specify your original file name -- Target File name option (Specify your new file name)

Completed Successfully!!!!

ODI-1217: Session () fails with return code 0.

Error Message:
ODI-1217: Session <session name> (<Session number>) fails with return code 0.
at oracle.odi.runtime.agent.execution.SessionExecutor.closeSession(SessionExecutor.java:2693)

Whenever you start a job it fails immediately without executing it's steps.

Root Cause:
Tablespace is full.

How to confirm?

1. Connect to Database Admin (SYSDBA) where your repositories are created and execute Following Query:

SELECT a.file_name,

       substr(A.tablespace_name,1,20) tablespace_name,

       trunc(decode(A.autoextensible,'YES',A.MAXSIZE-A.bytes+b.free,'NO',b.free)/1024/1024) free_mb,

       trunc(a.bytes/1024/1024) allocated_mb,

       trunc(A.MAXSIZE/1024/1024) capacity,

       a.autoextensible ae

FROM (

     SELECT file_id, file_name,

            tablespace_name,

            autoextensible,

            bytes,

            decode(autoextensible,'YES',maxbytes,bytes) maxsize

     FROM   dba_data_files

     GROUP BY file_id, file_name,

              tablespace_name,

              autoextensible,

              bytes,

              decode(autoextensible,'YES',maxbytes,bytes)

     ) a,

     (SELECT file_id,

             tablespace_name,

             sum(bytes) free

      FROM   dba_free_space

      GROUP BY file_id,

               tablespace_name

      ) b

WHERE a.file_id=b.file_id(+)

AND A.tablespace_name=b.tablespace_name(+)

and a.tablespace_name in ('<work repo tablespace name>','<work repo tablespace name>')

ORDER BY A.tablespace_name ASC;

Note: If you don't know your tablespace name then execute following query:

SELECT

  ts.tablespace_name,

  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE

FROM

  dba_free_space fs,

  dba_tablespaces ts,

  dba_users us

WHERE

  fs.tablespace_name(+)   = ts.tablespace_name

AND ts.tablespace_name(+) = us.default_tablespace

and us.username in ('<work repo name>','<master repo name>')

GROUP BY

  ts.tablespace_name;

Solution:

Increase tablespace will resolve the issue.

How to promote code from one environment to another environment?

Scenario:
I need to promote my project from Development environment to SIT environment.

Approach:
Step 1:
Login to ODI Dev Work Repository.
Go to Designer Navigator -- Click on designer menu  -- Click Export -- Select Smart Export and then click ok.

Provide location where you want to export your porject.

Step 2:
Now drag and drop your project from designer navigator into the pop-up and click ok

Step 3:
Login to ODI SIT Work Repository.
Go to Designer Navigator -- Click on designer menu  -- Click Import -- Select Smart Import and then click ok.

Provide location where exported earlier and click on next.


Step 4:
Then it will opens following pop-up

Expand Topology -- 

If you want to use the objects which exported latest means you need to select overwrite. If you want to use already existing object means you need to select re-use. If it is completely new object it will create copy of it. If  want to ignore an object then you need to select ignore.

For example:

Then click ok.

Hence completed successfully!!!

How to Schedule jobs in ODI?

Note: For this demo, I used one package which is already created whose name is PKG_MAIN.

1. Connect to ODI

2. Go to designer tab

3. Expand Project Expand First Folder -- Expand Packages -- Right click on PKG_MAIN -- Click on Generate Scenario

4. Now Expand PKG_MAIN Version 001 scenario

5. There you will find schedule -- Right click on it à click on New scheduling

6. Update the attributes as mention below

i.e., Context: TEST, Logical Agent: Agent and Log Level: 5

Status:

Active need to be enable

Execution:

Daily need to be enable (if you want to schedule daily means you need to enable daily)

Time: You need to specify at what time it need to start.

[Note: Agent is installed in MDM server so it will consider server timezone]

7. We need to update schedule. For that go to topology navigator

[Note: if it not visible then go to menu bar and select window then click on Topology]

8.Expand Physical Architecture Accordion à Expand Agent à Double click on MDMDIAgent -- Click on update Schedule button then it will pop-up to select work repository, you have to select <Work Repo Name> and say ‘OK’

9. Click on View Schedule to confirm whether our schedule is updated or not.Then select tomorrow date under ‘TO’ and click refresh symbol as mention below

Then you can view your schedule there.

[Note: For Example purpose schedule that. Now I am removing it from schedule]

How to convert rows into cols using ODI 11g? (which is pivot in ODI 12c)

Scripts we are going to use in this demo:

--Source Table1 CREATE TABLE COMPANY_BRANCHES (BRANCH_CODE VARCHAR2(3), BRANCH_NAME VARCHAR2(20), ADDRESS VARCHAR2(100)) --Source Table2 CREATE TABLE COMPANY_STATS (BRANCH_CODE VARCHAR2(3), YEAR VARCHAR2(4), QUARTER VARCHAR2(2), PROFIT_MILLIONS NUMBER(8,2)) --Target Table CREATE TABLE COMPANY_ANALYSIS (BRANCH_NAME VARCHAR2(20), ADDRESS VARCHAR2(100), YEAR VARCHAR2(4), QUARTER1_MILLIONS NUMBER(8,2), QUARTER2_MILLIONS NUMBER(8,2), QUARTER3_MILLIONS NUMBER(8,2), QUARTER4_MILLIONS NUMBER(8,2) ) --Source Table1 Data INSERT INTO COMPANY_BRANCHES VALUES('NLR','NELLORE','ARAVINDANAGAR'); INSERT INTO COMPANY_BRANCHES VALUES('GDR','GUDUR','EAST STREET'); --Source Table2 Data INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q1',0.8); INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q2',1.2); INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q3',1.5); INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q4',0.75); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q1',0.9); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q2',1.35); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q3',1.4); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q4',0.82); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q1',0.6); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q2',1.0); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q3',1.3); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q4',0.8); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q1',1.0); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q2',1.3); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q3',1.32); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q4',0.78);

Approach:

Let us consider source data store and target data store are available in ODI Model.

Step 1:

Create new interface and drag & drop 2 source datastore into source editor and drag and drop 1 target datastore into target editor.

Step 2:

Create join between two sources as follows

COMPANY_BRANCHES.BRANCH_CODE=COMPANY_STATS.BRANCH_CODE

Step 3:

Map columns as follows:

Target Datastore Columns	Source Datastore Columns
BRANCH_NAME	COMPANY_BRANCHES. BRANCH_NAME
ADDRESS	COMPANY_BRANCHES. ADDRESS
YEAR	COMPANY_STATS.YEAR
QUARTER1_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q1',COMPANY_STATS.PROFIT_MILLIONS,NULL))
QUARTER2_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q2',COMPANY_STATS.PROFIT_MILLIONS,NULL))
QUARTER3_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q3',COMPANY_STATS.PROFIT_MILLIONS,NULL))
QUARTER4_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q4',COMPANY_STATS.PROFIT_MILLIONS,NULL))

Explanation:

We need to group all 

QUARTER1_MILLIONS,

QUARTER2_MILLIONS,

QUARTER3_MILLIONS,

QUARTER4_MILLIONS

based on BRANCH_NAME, ADDRESS, YEAR so that we need MAX

If we maps the columns like this in ODI then it internally group by following columns

GROUP BY 

COMPANY_BRANCHES.BRANCH_NAME,

COMPANY_BRANCHES.ADDRESS,

COMPANY_STATS.YEAR

Select appropriate KM's.

Output:

Change Data Capture - Simple

CDC:

Change Data Capture name itself clearly mentioning that we are capture all the new data which arriving to datastore.

Scenario:

We have a requirement - First we need load all the data which was existing in datastore. 

After that if there is new data arrive to that datastore means we need to capture that data (new data in the sense inserted or updated records) and perform 

operation only on newly capture data.

Simply we can say it as initial/full load and incremental load.

Approach:

Step 1:

Go to model --> go to data store --> right click on data store --> Change Data Capture --> Subscriber -->Subscriber --> Add SUNOPSIS and say ok

To add subscriber we need to have JKM knowledege module to be import into our project.

KM name:  JKM Oracle Simple

[Note: From where we need to import knowledge modules - <installed ODI path>\Oracle\Middleware\Oracle_Home\odi\sdk\xml-reference]

Open model and then go to Journalizing tab and select JKM knowledge module and save it.

[Note: we can add any name but if you don't want change means use SUNOPSIS by default in code you will get subscriber as SUNOPSIS]

Step 2:

Now we need to add datastore to CDC

Right click datastore -- Change Data Capture -- Add to CDC and click on Yes

Step 3:

Now we need to start the Journal

Right click datastore -- Change Data Capture -- Start Journal and select Subscriber and click on OK

Check the operator it started successfully or not.

Step 4:

Create mapping -- Drag and Drop source and target datastores from model

Connect output connector of source datastore to input connector of target datastore

Mention Auto map,

[Note: In this example I am using two KM's those are IKM Oracle incremental Update and CKM Oracle]

This physical is initial load or full load

Now we will one more physical for Incremental load. (It is the new feature available in ODI 12c onwards) 

[Note:If you want to implement in ODI 11g means we need to create two different interfaces]

Go to Physical Tab and click on new physical as mention below

Name them as :

Physical : Initial

Physical1: Incremental

Go to incremental and select source datastore -- go to properties inspector -- go general tab and enable journalized data only check box

Hence Developement Completed !!!

How to unlock DB account?

ALTER USER <user> IDENTIFIED BY <password> ACCOUNT UNLOCK;

For Example:
Most of the cases Oracle HR user - by default it will be locked. To unlock that account use following query:

ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;

How to find a value is numeric or not in Oracle?

Scenario:

Value               Result
12345              Number
12Z3A             Alpha Numeric

Simple Query:

select '12345' input,
DECODE( TRANSLATE('12345','0123456789',' '), NULL, 'Number','Alpha Numeric') Result
 from dual

select '12Z3A' input,
DECODE( TRANSLATE('12Z3A','0123456789',' '), NULL, 'Number','Alpha Numeric') Result
 from dual;

Infront of variables which symbol is best to use ':' or '#' in ODI

Based on the requirement, consider following steps and select the appropriate symbol

Serial Number	Using ‘:’ infront of variables	Using ‘#’ infront of variables
1	When we are using variable as SQL bind variable it is better to use colon rather than a hash
2	Syntax is subject to restrictions as it only applies to SQL DML statements, not for OS commands or ODI API calls	No restrictions as it applies on all
3	Performance loss	Optimal Performance at run-time
4	The name of the Variable is NOT substituted when the RDBMS engine determines the execution plan. The variable is substituted when the RDBMS executes the request. (this mechanism is called Binding Mechanism)	The name of the Variable is substituted when the RDBMS engine determines the execution plan
5	The variable reference need not to be enclosed in single quote characters. For example : NAME = :GLOBAL.V_NAME	The variable reference needs to be enclosed in single quote characters. For example NAME = '#GLOBAL.V_NAME'.

How to create relationship between options and task in ODI procedure?

Scenario:
There are three steps in procedure based on the option value I need to skip or execute task in ODI procedure.

For Example:
Step 1: Create Table
Step 2: Truncate Table
Step 3: Insert record into table

Options:
Create
Truncate

If I select create option value as true then it needs to create table.
If I select Truncate option value as true then it needs to truncate table.

Approach:
Step 1:
Create a procedure
With three tasks as follows

Step 2:
Create two options (go to option tab and click on '+' symbol)

Step 3:
Select Create Option and go to property inspector and then expand options -- Disable Always execute check box and enable Create check box as follows

Step 4:

Repeat step 3 for truncate and change accordingly

Step 5:

Create a package.

Drag and drop procedure into package --  go to options tab -- select create option and set value to "true"

Step 6:

Execute it

Output:

In above execution, it skipped truncate step

Step 7:

Now we will check by changing the options as create - "false" and Truncate as "true"

Step 8: 

Execute Package

Output:

In above execution, it skipped create step

[Note: As mentioned in above steps our knowledge modules will work]

How to pass parameters to ODI procedure?

Scenario:
We need to pass directory location so that it will pick file names and delimited file names with ';' as mentioned in below example

For Example:
Dir: <FOLDER_LOCATION>
      Sample_File.txt(File)
      Sample_File.csv(File)
      Sample_File.xls(File)

Output should be 'Sample_File.txt;Sample_File.csv;Sample_file.xls'

Steps:

Step1:
Create a procedure and select target technology as Oracle

Step 2:
Go to Option and create new option whose name is "Dir" , Data type as "Text" and Direct Value "<DIRECTORY_LOCATION>" (eg : C:/Sample_files)

Step 3:
Copy below code and paste in target side and select technology as  Oracle and Logical Schema accordingly

<?
import java.io.File;
public class FileLists{
   public static String FileNames() {
     
      File f = null;
      String[] FileList;
   String fileNames="";
           
      try{    
       
         f = new File("<%=odiRef.getOption( "Dir" )%>");
         FileList = f.list();
         for(String FileName:FileList)
         {
           if(FileName.contains("."))
     {
   if (!fileNames.isEmpty())
    fileNames  = fileNames+','+ FileName ;
   else
    fileNames = FileName;
     }
         }
 
      }catch(Exception e){
         e.printStackTrace();
      }
    return fileNames;
   }
}
?>
select '<?=FileLists.FileNames()?>' from dual


Step 4:
Drag and drop the procedure into package and go to options and provide value to Dir option as below

Static Control in ODI 12c

Scenario:
While loading 10000 records if there is an error in one/more records then I need to make a note that record into E$ (error table) and all records into target table.

Example:
Source

As per requirement, whose MID values are null consider to be invalid records. Though those are not valid records we are accepting it and providing to option to end-user to correct by themselves by providing error message

(i.e., SRC <= TRG+E$)

Static Control:

Data quality validation is done after loading the data into target tables.

CKM will validate data on target table and if any error is detected it will be inserted to E$ table and SNP_CHECK_TAB.

Remember that the incorrect entry will not be deleted as in Flow control. 

Approach:

Step 1:

Create Model -- Reverse Engineer source datastore and target datastore 

Step 2:

Expand Target data store -- Right click on constraint --Select  "New Condition"

Message is nothing error message

Actually this is condition is not there at data server level. Type indicates it is ODI condition.

Step 3:

Go to Control tab and enable check box for both flow control and static control

Step 4:

Create mapping -- Drag and drop Source and target data store then connect source output connector to target input connector -- go to physical tab and then select target datastore -- go to Property Inspector -- Integration Knowledge Module --  Enable Static control as true 

Step 5:

Execute it

Output: