Create a View in Oracle

A view in Oracle is a virtual table that represents data from one or more tables. It can be used to simplify complex data queries, aggregate data, and enforce data security. To create a view in Oracle, use the following syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

CREATE VIEW customer_view AS
SELECT customer_id, first_name, last_name
FROM customers
WHERE status = 'active';

Note:

• The view name must be unique within the database.
• The SELECT statement should define the columns and data to be included in the view.
• The WHERE clause is optional and can be used to filter the data in the view.

Once the view is created, you can query the view just like a regular table using the view name. To update the view, you will need to use an INSTEAD OF trigger.

Java Transformation in Informatica

Java Transformation in Informatica is a powerful tool that enables you to perform complex transformations within the Informatica PowerCenter environment. This transformation type allows you to write custom Java code to manipulate data within the Informatica mapping.

Advantages:

• Flexibility: Java Transformation provides a high level of flexibility to perform complex transformations not possible with other transformation types.
• Reusability: Java Transformation can be reused in multiple mappings, reducing the need for duplication of code.
• Performance: Java Transformation can be optimized for performance, making it a suitable option for large-scale data processing.

Use cases:

• Data cleansing: Java Transformation can be used to cleanse data by using Java logic to validate and correct data values.
• Complex calculations: Java Transformation can be used to perform complex calculations, such as financial calculations, that cannot be done with simple expressions.
• Data enrichment: Java Transformation can be used to enrich data by integrating information from external sources.

In conclusion, Java Transformation in Informatica is a useful tool for performing complex transformations and data manipulations within the Informatica PowerCenter environment. It provides a high level of flexibility, reusability, and performance, making it a valuable addition to any Informatica solution.

Oracle Programming Advanced

Lets see a Simple procedure in this post.

Below is the code for a simple procedure which Queries the database and display the record when it is called.


Create or Replace Procedure P_GetEmpName 
As
V_Emp_Name varchar2 ;      -- Declaration Section
Begin                                     -- Execution Section

Select EmpName Into V_Emp_Name From Emp Where Emp_No = 12345;
Dbms_Output.Put_Line("The Employee Name is : " V_Emp_Name)
End;

The Above Procedure when called will give the following result.

Exec P_GetEmpName ;

Output :
The Employee Name is :John


Below is the code for a simple procedure which Queries the database and display the record when it is called.

Passing a Parameter to Procedure to Insert into a table

Create or Replace Procedure P_InsertRecord(P_Name varchar2)
As 
V_name varchar2(8) := P_Name;   -- Declaration Section
Begin                                              -- Execution Section
Insert into dept values(20,V_name,'f');
End;

The Above Procedure when called will give the following result.

Exec P_InsertRecord('John');

Select * From Dept ;

Output :
Dept No    Dept Name   Job Type
20                 John             f


Let us see about Dynamic Sql and execution of it in this post.

Create or Replace Procedure P_DynamicSql(P_Table_Name Varchar2) 
As

V_Table_Name Varchar2(10) := P_Table_Name ;          -- Declaration Section

V_Dynaic_Query Varchar2(50) ;
Begin  -- Execution Section

V_Dynaic_Query := 'Create Table ' || V_Table_Name || ' (Name Varchar2(50))'  ;

Execute Immediate  V_Dynaic_Query ;

Exception   -- Exception Section

When Others Then

Dbms_Output.Put_Line('Error Creating Table') ;

End;


When the above Procedure is called , Table with the Provided name will be create with a column called Name.


Exec P_DynamicSql('Emp') ;


The Table Gets Created

Oracle SQL Programming Basics

Lets learn the basic of Oracle Programming in this blog

Select statement to query a table
Select * from emp;
Select * From Dept ;
Here * denotes that every column of the table has been selected.
emp -> table_name

Select statement to query a table with a filter condition.
Select * From EMP Where Employee_name = 'Joe' ;
Here
1) We are selecting all the columns of table EMP
2) The Query has been filtered for Employee name 'Joe'

Describe columns name present in the table "Dept"
Desc Dept ;

Insert statement
Now , Let try to insert a new row with values like dept no = 10 , dept name = eee , job = e

Insert into Dept values(10,'eee','e');

The above Query will insert a row into the table Dept and it will be displayed by using the Select Statement.

Now , Let try to insert a new row with values like Emp no = 100 , Emp Name = "John", Designation = "Manager",
Manager id = 200 , Hired Date = 26-Aug-2010 , Salary = 25000 , Tax = 2000 , Dept no = 10


Insert into Emp values(100,'John','Manager',200,'26-Aug-2010',25000,2000,10) ;

Different table Join

Inner join

Select  A.Emp_name , B.Dept_name From Emp AS A 
Inner Join Dept AS B

On A.Dept_id = B.Dept_id;

Check out : Oracle Programming Advanced

Vlookup in Excel

Here is how we do Vlookup in Excel

Lets Say this is the Lookup table from where we want to get the Information

Name	Salary	Age
Robert	150000	25
Kevin	200000	30
John	100000	35
Smith	50000	26
Rose	75000	65
Shiela	250000	55

We want to get the Salary for following People

Name

Robert

Kevin

Leslie

The Vlookup formula will look like this

=VLOOKUP(G2,A1:C7,2,0)

Here Vlookup is the Function name

G2 - the column (Name) we are looking info about

A1:C7 - this is the range in excel where our lookup data is present

2 - to retrieve the second column information from Lookup table

0 - returns false if name doesn't exist

Result is

Name	Salary
Robert	150000
Kevin	200000
Leslie	#N/A

Unix Script to Copy a file in Informatica

Lets now see the steps involved in Copying a file and placing in a destination required using the unix script from informatica.

1) Create a command task in your workflow or Open the Pre session command/post session command Of your Session property.

2) Now Provide the Script name in the space provided where we are going to place our unix script to do the above job. Script name should be given in the following way :

sh pathname/filename.sh

3) Now , Write the following script into the Script file to obtain the required result

. OSPENV
cp pathname/filename pathname/filename

4) Execute the script by running the workflow in informatica to see the required result.

Unix Script to Create a empty file in Target

Lets see the steps involved in creating a empty file in the target folder using the unix script.

1) Create a Command Task in the Workflow
2) Provide the Command name and Script file name in the Space provided. This will call the script during run time .
3) Now Write the following Unix script in the Script file .

. ospenv
Touch Filepath/filename

4) The above command will create a 0kb empty file in the target folder specified by us.

Pre Session Command in Informatica

Lets see how Pre session command can be given in a Informatica Workflow.


The Below Screen shows a session where the pre session command is going to be added.

Now right click on that session -> click on edit

Now click on Components tab

We can see many Properties under this tab. Now select "Non Reusable" option from the drop down next to Pre session command property.

Now Select the Pencil mark present in Value column to specify the command script which we are going to use.

Click on "Add new command" button and specify the path and name of the script in the space provided

Now Click on "Apply" and "Ok".

Based on the Order we have given , the Command Scripts get executed before the Start of the session.
We can have as many scripts as we want (commands) to be run be run before session.

Pls chk our : Post Session command

scenario6

I have a scenario where i need to load a table called "name_address" first for storing all the claimant name with a generation of a number (using given formula).

This number will be used in other main tables like claim table , transaction .. etc

Can u please help me whether constraint based loading will be useful or i need to specify target load plan.

Scenario5

Source              
Col_1    Col_2       
D1       A1          
D1       A2          
D2       A3         
D2       A4           

D2       A5           

D3       A6           
D3       A7 

Target 
Col_1    Col_2   
D1     A1-A2 
D2    A3-A4-A5 
D3     A6-A7


Answer :

The Following can be obtained by using a Sorter transformation , Expression transformation and a aggregator transformation.

Let me show the mapping now.,

This shows the mapping for the above scenario.
Now lets see them in detail
1) Usual Sorter on Col1
2) Expression t/f like below

3) Aggregator t/f as below

Create different flatfiles as output

We have a scenario in our project where i have to create different flatfiles as output based on the company number

The issue is 
1) we run this workflow once in every month
2) New company numbers and corresponding records may be added anytime during the month ., So each time wen a company is added we have to create a flatfile for that also
3) The name of the flatfile should be "Loss history report for" + company number
4) One more thing is every month wen the workflow runs , the flatfiles have to be places in that month folder . eg : In january the files shud be placed in Jan2012 folder.
The folders will be already present.,
5) Is it possible to create the folders also inside target every month ?

Does anyone have answer for this?

Java Transformation scenario2

Here i will show the mapping for scenario where we need the input records from emp table to be inserted into target thrice.

  

One more requirement is to change ename to "java" || job

They both are implemented the below way :

check out this link for other java scenario

Java Transformation

Let me show the Simple mapping involving Java transformation

Here the source is emp table and the target is rangasp table in oracle.

The requirement here is to change all the employee name to "java".
The java code has been written in such a way that the ename of all records will be changed to "java"

The left side contains all the input and output ports.
Generaterow() is used to generate a row for each records.

The output is something like this :

eno ename
721 java
678 java
899 java

Please do comment and ask if you have any doubts!! thankyou

Scenario 4

Input :

empno    ename    job
7219     Sri      CLERK
7782     Uma    MANAGER
7839     Ragu   PRESIDENT
7934     Sendil   CLERK

Output :

Repeation of the rows three times and also change the ename as "Srivatsan"
empno    ename    job
7219     Srivatsan CLERK
7219     Srivatsan CLERK
7219     Srivatsan CLERK
7782     Srivatsan MANAGER
7782     Srivatsan MANAGER
7782     Srivatsan MANAGER
7839     Srivatsan PRESIDENT
7839     Srivatsan PRESIDENT
7839     Srivatsan PRESIDENT
7934     Srivatsan CLERK
7934     Srivatsan CLERK
7934     Srivatsan CLERK


Post your mapping , one lucky winner will be selected!!

Transaction control Transformation

Lets discuss about transaction control transformation in this video :

I will show you a scenario using transaction control statement and explain you wen it has to be used.

Here we can see three components

1) expression trans

2) transaction ctrl t/f

3) target

The source for us is the emp table of scott schema

the requirement is to split the source rows into different flatfiles based on the dept number.


that is:

dept no : 10 , 20 ,30 need to splited and saved in seperate files

We can ask a question like "why cant this be done easily by a router"

yep a "good ques : the answer is "wen the dept no changes dynamically how we will be able to get the router groups to change??

So this is how this scenario is handled..

here we have filed where we are checking this condn : IIF(v_DEPTNO_2 != DEPTNO, 0, 1)

if a dept number comes for the first time then this will be zero and our transaction control t/f will create a flatfile with corresp rows.

filename is a field used to provide name of flatfile which need to be generated dynamically.

Based on the column where we gave condn the tc t/f does the following commiting activity
IIF(DEPTNO_SPLITTING = 0, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

here wenever a new dept number comes a commit is been done and a flatfile is created and one imp note is "the input has to be sorted initially"

In this way the files are created on each dept numbers

Thankyou , please comment!!