Table of Contents

Search

  1. Preface
  2. Components
  3. API collections
  4. Business services
  5. File listeners
  6. Fixed-width file formats
  7. Hierarchical schemas
  8. Intelligent structure models
  9. Refining intelligent structure models
  10. Mapplets
  11. Saved queries
  12. Shared sequences
  13. 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;

0 COMMENTS

We’d like to hear from you!