Supported Transformations and Other Mapping Objects
You can include the following transformations and other mapping objects in the workflow for a PowerCenter task:
- •Flat file, database, SAP (SAPALEIDoc), Amazon Redshift, Salesforce, and web service sources and targets
- •Aggregator transformation
- •Data Masking transformation
- •Expression transformation
- •Filter transformation
- •HTTP transformation
- •Java transformation
- •Joiner transformation
- •Lookup transformation
- •Normalizer transformation
- •Router transformation
- •Salesforce Lookup transformation
- •Salesforce Picklist transformation
- •Salesforce Merge transformation
- •SAP IDOC Interpreter transformation
- •SAP IDOC Prepare transformation
- •Sequence Generator transformation
- •Sorter transformation
- •Stored Procedure transformation
- •Transaction Control transformation
- •Union transformation
- •Update Strategy transformation
- •Web Services consumer transformation
- •XML Parser transformation with file or database sources.
- •XML Generator transformation with file or database sources.
If the workflow contains other mapping objects, the workflow import may fail. Other PowerCenter functionality, such as shortcuts or session parameters, are not supported unless included in the list above.
Exception Handling in Stored Procedures
When you use a Stored Procedure transformation in a workflow for a PowerCenter task, the stored procedure must include exception handling. Exception handling can be as complex as necessary. Or, you can use the following simple example of exception handling code:
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;