Stored Procedure Trans

Stored Procedure Trans

1. Passive

2. Connected

3. to populate and maintain databases

4. 3 types of data that is passed between IS and SP

   • Input/Output parameters
     • Provide Values / Receive Values
   • Return Values
     • it can act similar to a single output parameter, or it may only return an integer value.
   • status codes
     • issues a status code that notifies whether or not it completed successfull

5. Unconnected

6. When is it run

   • Normal
   • Pre-load of the Source.
   • Post-load of the Source
   • Pre-load of the Target.
   • Post-load of the Target.
   • When passing a datetime value through a pre- or post-session stored procedure, the value must be in the Informatica default date format and enclosed in double quotes as follows: SP(“12/31/2000 11:45:59”)

7. Return values

   • local variable.
   • system variable PROC_RESULT.
     • assign the value of the return parameter directly to an output port,
     • Use only within an expression.
   • If neither, the port captures a NULL

8. The parameters in the expression must include all of the parameters in the Stored Procedure transformation. You cannot leave out an input parameter. If necessary, pass a dummy variable to the stored procedure

9. TIPS

   • Reduce the row count. Use an active transformation prior to the Stored Procedure transformation to reduce the number of rows that must be passed the stored procedurE
   • Most of the logic used in stored procedures can be easily replicated using expressions in the Designer.

10. Parameter Types

    • IN -something passed to
    • OUT - something returned
    • INOUT - both input and output only Oracle