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