Components

Back Next

Stored Procedures in mapplets

When a PowerCenter mapplet that you want to use contains a Stored Procedure transformation, the stored procedure must include exception handling.

Exception handling can be as complex as necessary. Or, you can use the following simple example:

Exception
when NO_DATA_FOUND
then NULL;
END;

For example, you have the following stored procedure in a PowerCenter workflow:

CREATE OR REPLACE PROCEDURE SP_GETSAL_WITH_EXCEPTION (EMP_ID NUMBER, EMP_NAME OUT VARCHAR, SAL OUT NUMBER)
  AS
  BEGIN
  SELECT EMPNAME INTO EMP_NAME FROM EMPLOYEE WHERE EMPID=EMP_ID;
SELECT SALARY INTO SAL FROM EMPLOYEE WHERE EMPID=EMP_ID;

Before you export the workflow, add exception handling as follows:

CREATE OR REPLACE PROCEDURE SP_GETSAL_WITH_EXCEPTION (EMP_ID NUMBER, EMP_NAME OUT VARCHAR, SAL OUT NUMBER)
  AS
  BEGIN
  SELECT EMPNAME INTO EMP_NAME FROM EMPLOYEE WHERE EMPID=EMP_ID;
SELECT SALARY INTO SAL FROM EMPLOYEE WHERE EMPID=EMP_ID; 
Exception
when NO_DATA_FOUND
then NULL;
END;

PowerCenter mapplets

Updated January 22, 2021

Download Guide

Send Feedback

Explore Informatica Network

Communities

Knowledge Base

Success Portal

Table of Contents

Components

Components

Stored Procedures in mapplets

Stored Procedures in mapplets