Tasks

Back Next

Exception handling in stored procedures

When a mapping that you want to use in a PowerCenter task 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 workflows

Download Guide

Watch

Comments

Cloud Data Integration Homepage