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:

Flow Control in ODI12c

Scenario:
While loading 10000 records if there is an error in one record then I need to load that record into E$ (error table) and rest of records into target table.

Example:
Source

As per requirement, whose MID values are null consider to be invalid records

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

Flow Control:

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

Check Control Knowledge Module (CKM) will create E$ table and SNP_CHECK_TAB table for data quality check.

It will validate data in I$ table before inserting data into target table.

If it has any errors then it will delete from I$ table and insert into E$ table and common error message and interface name into SNP_CHECK_TAB

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 Flow control as true 

[*Note: There should be key if we enable flow control. In my example EMPID is key column]

Step 5:

Execute it

Output: