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;