Stored Procedure Transfirmation

Lab 8 Stored Procedure

Lab at a Glance................................................................2 Objectives..............................................................2 Summary................................................................2 Duration.................................................................2 Exercises..........................................................................3 Exercise 1: Create the Mapping..................... Mapping..................... ........3

PowerCenter 7 Level I Developer – Lab Guide

8-1

Lab 8. Stored Procedure

Lab at a Glance The exercises in this lab are designed to walk the student through the process of using a Normalizer transformation.

Objectives After completing the lab, the student will be able to use a Stored Procedure transformation.

Summary The purpose of this lab is to invoke the Store Procedure or  Function from Informatica. SOURCE:

DEPT

TARGET:

DEPT_MAX_SAL

The completed mapping should look as follows:

Duration This lab should take approximately 30 minutes.

8- 2

PowerCenter 9 Level I Developer – Lab Guide

Lab 8. Stored Procedure

Exercises Exercise 1: Create the Mapping Step 1. Import the source Flat file. 

Clear the Source Analyzer workspace (right-click  anywhere in the workspace and select Clear All).



Continue to work in the assigned student folder and import the DEPT table. The source should look as follows:

Step 2. Import the target. 

Clear the Warehouse Designer workspace (right-click  anywhere in the workspace and select Clear All).



Create the DEPT_MAX_SAL file The target should look as follows:



Save the repository.

Step 3. Creating a mapping. 

Create a mapping called m_stored_procedure

PowerCenter 9 Level I Developer – Lab Guide

 .

8-3

Lab 8. Stored Procedure

Step 4. Add source and target. 

Add the DEPT source definition with its respective Source Qualifier to the mapping:



Add the target definition DEPT_MAX_SAL.



Save the repository.

Step 5. Create a Stored Procedure transformation. 

Create a Stored Procedure transformation named SP_MAX_SAL. Import the Stored Procedure

 The only invoices that will meet this filter condition contain a date in the DATE_CLOSED column and were not canceled.

8- 4

PowerCenter 9 Level I Developer – Lab Guide

Lab 8. Stored Procedure

PowerCenter 9 Level I Developer – Lab Guide

8-5  

Lab 8. Stored Procedure

8- 6

PowerCenter 9 Level I Developer – Lab Guide

Lab 8. Stored Procedure

Unconnected SP with multiple Outputs:

Note:- Should get the result into variable ports as shown above VAR_AVG_SAL := :SP.SP_MAX_AVG_SAL (DEPTNO,VAR_MAX_SAL,PROC_RESULT)

PowerCenter 9 Level I Developer – Lab Guide

8-7