Lookup's in Informatica

Reusable transformations

   A reusable transformation is a reusable meta data object created with business logic using single transformation

There are 2 different ways to create reusable transformations

  1. Converting a non reusable transformation into a reusable transformation
  2. Using transformation developer tool


Source qualifier transformation can’t be used as reusable T/R


Converting non reusable transformation into a reusable T/R select the mapping, drop to the mapping designer work space

Double click on the transformation, from transformation tab

Select make reusable , click on yes

Click apply, click ok

Using transformation developer

From tools menu select transformation developer tool

From transformation menu select create

Select the transformation type sequence generator

Enter the name click on create and done

From repository menu click on save

User defined function

A user defined function is a power center object which is created using set of built in function to    meet specific requirements.

A user defined function created using power center designer client.

Built – in – function

LTrim                Trim                  LTrim (RTrim (arg1))



 From repository navigation window select user defined function sub folder.

From tool menu select user defined functions click on new

Enter the name Trim, select the type public

From arguments click on add new argument

NameData typePrecision
Arg1String 10


Click on count editor, develop the following express


Click on validate, click ok , click ok

From repository menu click on save

Lookup's in Informatica


A user defined function is defined with following identifier count _ UDF

Desired to gain proficiency on Informatica? Explore the blog post on Informatica training to become a pro in Informatica.

Unconnected stored procedure

An unconnected stored procedure is not part of mapping data flow

It is neither connect to the source nor connect to the target

An unconnected stored procedure can receive the multiple but return single output port by default.

An unconnected stored procedure act as a function that can be set in an transformation that supports to write expression

An unconnected stored procedure can be set with the following expression

:SP. Stored procedure transformation name(:SP.SPTR NAME)

Lookup's in Informatica


Create the following stored procedure in the target data base

SQL> create procedure annual _ proc (SAL IN NUMBER , A SAL OUT NUMBER)



Asal := Sal*12;


Create the source and target definitions

Empno, ename, job,sal, annual,deptno

Create a mapping with the name M_ unconnected _proc

Drop the source and target definition

Create the transformation type stored procedure and expression

From source qualifier copy the required ports  to the expression transformation

Double click on expression transformation select the ports tab

Port NameData typePrecisionScaleI   O    VValue
ANUALDecimal2:SP .USP(Sal , proc - Result)

Click apply, click ok

Double click on unconnected stored procedure transformation

Select the properties tab

Transformation AttributeValue
Connection InformationBatch _ Trg - Writer

Click apply, click ok

For indepth understanding of Informatica, click on