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