How to use multiple values in a parameter with Informatica, with IN function/clause

Several times we have to use a list of values for filter. It becomes a tedious job, when the list keep changing frequently. Now using parameter file we can actually reduce the overhead. Just parameterize the filter and change the parameter file whenever the change is required. It is little tricky, when it becomes to use multiple values in a parameter(for IN condition). Especially when you have all numbers or decimals in your list. Lets take an example of a filter condition as following

Product_id IN (3940, 3856, 4050, 2109, 5789)

Your port is a decimal/number data type, where as your filter will become a string as you use comma and parenthesis for it, obviously to create a delimited list.

So, here is a way you can still implement it. Create a parameter on mapping level with datatype string. Make sure you have enough length to accommodate the whole list(may be 500 or so)

$$MP_PRODUCT_ID_LIST

Next use it in your filter condition. If it is in SQL override, replace the list with parameter

Product_id IN ($$MP_PRODUCT_ID_LIST) 

Now comes the tricky part, you have to declare it in your parameter file. This is how we are going to do it. each product_id should be quoted with single quotes.

$$MP_PRODUCT_ID_LIST= '3940', '3856', '4050', '2109', '5789'

You should be able to use it now. I had to go through a lot of experiments, before I could really use it, but I am happy that I figured it out.

How to create and use a Parameter file in Informatica

              I know how tedious and painful it is to change and migrate the code every time you have to do a little change. On the other hand it is not a best practice to use hard coded values in the mappings. It is always useful to create a generic code, so that it can be used as per requirements with a little change. So, today I will be talking about parameterization in Informatica. There are actually two types of parameters in Informatica based on the scope.

Workflow parameters-defined on the workflow level
Mapping parameters-defined on the mapping level

I am going to take an example here to describe both of these types of parameters. Let's say we have a requirement to use a filter on some port (field/column). It should be a mapping level parameter if the filter is only going to be used in this mapping and not in the other mappings(with same value) under the same workflow. So we will define the parameter by following the steps shown in pictures here. Say we have the filter like Dept='SALES'. This tells me that it is a string data type and length can be 10.
$$MP_DEPT

     
                                  

                         

Once you have defined the parameter, lets use it in the transformation, say SQL qualifier, we can use it as

Dept=$$MP_DEPT

Now the next step is to create a parameter file. write similar text in a text file, save it as test.prm or the name you wish.

[GLOBAL]

$DBConnection=ORA1
$$MP_DEPT='SALES'

$DB_Connection here is a workflow parameter and used for providing connection for in workflow.
Next is to give the path of parameter file in the your workflow. Right click on your workflow. Click on edit workflow and select Properties tab.

Now edit the Parameter Filename attribute. Remember to provide the full path of your parameter file along with the name.

Good to go. Have fun with your creativity and parameterization.

For steps creating a workflow parameter, I will write a separate post.

How to Delete duplicate records in Oracle table

Many times we get duplicate records in a table. Mostly we can truncate the whole table and reload it again from source, but if the table is big and takes long time to reload, we should probably not truncate the table and reload it again. We can manually run a script to delete all the duplicates from the table and make it clean for users.

There are various approaches through which we can delete duplicate records, but my favorite is by row_id

Here is a script to perform that

DELETE FROM 
   table_name A
WHERE 
  a.rowid > 
   ANY (
     SELECT 
        B.rowid
     FROM 
        table_name B
     WHERE 
        A.col1 = B.col1
     AND 
        A.col2 = B.col2
        );

Please note that you must specify all of the columns that make the row a duplicate in the SQL where clause.
Hope this is helpful. :)

How to truncate a partition of a table in Oracle with Informatica Pre SQL

So, I only wanted to delete a set of data from a huge table and not wanted to truncate the whole table, as it would take long time to reload all the data.So I decided to partition the table by list on a column values. I created informatica mappings for each partition separately and put all the sessions in one workflow. Now here is the trick I applied. For each session's properties in under mapping tab, click on the target and scroll down, you will see a PRE SQL section where you write a truncate statement.

ALTER TABLE table_name TRUNCATE PARTITION partition_name;

Click OK and save the workflow. I did this for all the sessions, basically for each partition I had created.
This allows me to run the sessions manually if I want and run one at a time or all of them. Pre and Post sql options are awesome and gives you lots of flexibility to work with.

List all the table information with specified column_name

Say, we have a column_name and we want to find out which table it belongs to, or to know all the tables and views have this column. Here is my way

SELECT * FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME = 'ID'

OR

SELECT * FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE '%ID%'

Always use upper case for column_name value. It will list all the tables info along with the owner schema.

How to Create Index on a table in Oracle

Here is the syntax to create an index on a table in oracle:

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME);

If you want to specify tablespace you can do it as follows:

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME)
TABLESPACE TABLE_SPACE;

Isn't that easy?

How to add more columns to an existing table with a simple SQL in Oracle

Most of you may know that you can add a column easily with Toad by going through add column wizard or Alter table wizard. Let's say if you have to add lots of columns to a table, I would rather write a simple sql code than to add columns through wizard. Here is the simple sql you can write for the same

Alter table_name add 

(column_a   datatype,

column_b   datatype,

column_c   datatype,

column_d   datatype,

....

)

You can add as many columns as you want in just one shot. Is' nt that useful??