Table of Contents

Search

  1. Preface
  2. Components
  3. Business services
  4. File listeners
  5. Fixed-width file formats
  6. Hierarchical schemas
  7. Intelligent structure models
  8. Mapplets
  9. Saved queries
  10. Shared sequences
  11. User-defined functions

Components

Components

Stored Procedures in mapplets

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;


Updated August 03, 2020