A. Described below is a scenario where the requirement is to have a stored procedure that returns a cursor as a source.By and large PowerCenter does not support a stored procedure that returns a cursor as a source. The workaround for this is1. The procedure that will load the data to a new table:

CREATE OR REPLACE procedure load (p_initial_date in date, p_final_Date in date) as

str_load varchar2 (500);
str_clean varchar2 (500);
begin
str_clean:= ‘DELETE FROM EMP’;
str_load:= ‘INSERT INTO EMP select * from EMPLOYEE where DOJ between trunc

(p_initial_date) and trunc (p_final_Date) ‘;
execute immediate str_clean;
execute immediate str_load;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
end load;

2. Create the table that will receive the data from the procedure:

SQL> create table EMP as SELECT * from EMPLOYEE where 1 > 2;

3. Add a Store Procedure transformation to the PowerCenter mapping. This transformation will execute this new procedure called as LOAD on this example.

4. Set the run method to be Source Pre Load, to be executed before read the source table.

5. Import the EMP table as a Source Definition. This table will be populated by the new Store Procedure.

If the original store procedure is used by the customer application and you can’t change the source code, you can create a new store procedure that call the original one (without inserting into a table), and execute the insert on the new table executing a loop on the returned cursor.

B. Given below is a situation where you wanted to pass a mapping variable to a stored procedure transformation (it can either be connected or unconnected).

Connected Stored Procedure

The parameters that are passed to a connected Stored Procedure have to be linked from another transformation.
Given below are the steps to pass mapping variable to a connected Stored Procedure transformation:

  1. Create an Expression transformation.
  2. Create an output port in the Expression transformation with the following expression:

$$mapping_variable

This sets the value of this output port to the mapping variable.

  1. Link this output port to the Stored Procedure transformation.

Unconnected Stored Procedure

For unconnected Stored Procedure transformations you can use the mapping variable in the expression calling the stored procedure.
Follow the steps below to pass mapping variable to a unconnected Stored Procedure transformation:

  1. Create an Expression transformation.
  2. Create an output port in the Expression transformation with the following expression:

: SP.GET_NAME_FROM_ID ($$mapping_variable, PROC_RESULT)

In case if you are attempting to use a mapping variable to store the output value of the stored procedure, the session will fail with the below error.

“TE_7002 Transformation Parse Fatal Error; transformation stopped: invalid function reference. Failed to Initialize Server Transformation.”

To resolve the issue replace the mapping variable with the PROC_RESULT system variable.

Example:

Incorrect, using a mapping variable:

:SP.PROCEDURE(FIELD1, $$mapping_variable)

Correct, using the PROC_RESULT system variable:

:SP.PROCEDURE(FIELD1,PROC_RESULT)

Or

:SP.PROCEDURE($$mapping_variable,PROC_RESULT)

The PROC_RESULT system variable assigns the stored procedure output to the port with this expression.

Posted by Rajamani Vasudevan
Comments (8)
August 8th, 2008

Comments (8)

Ashok - April 29th, 2011

Thank u Devidas

Jamie Beu - April 13th, 2011

@Senthil, In order to use the Pre-session script, I would need to embed the call to the stored procedure within a Unix shell script that calls sqlplus. This would require hard-coding the password in the script, which goes against our security standards. Do you have any other suggestions? The Pre-SQL error was "unexpected end-of-line" error.

Anju - April 13th, 2011

Hi Badri, I used your tip to create multiple output ports. But I am facing a unique issue. If i have 5 records in my source, the second output value is seen for last 4 records. For the first record, it stays null. The output I am expecting is to the same for all 5 records. Any ideas??

Senthil Kumar Aiyappan - March 25th, 2011

@Jamie Beu The workaround is to use Pre session script to call the procedure. I would like to understand the error while using the EXEC statement.

Jamie Beu - March 8th, 2011

How do I use a stored procedure in the Pre SQL attribute of a target? For example, I want to run a "truncate table" stored procedure (I don't trust Informatica's "truncate" check box) to truncate my target table. Unfortunately, Informatica doesn't use the SQL*Plus "EXECUTE" command, nor does it allow me to wrap the procedure in a BEGIN...END block. Any ideas?

Devidas Kanchetti - August 10th, 2010

Thanks boss

Badri Narayanan - January 6th, 2009

You can get more than one output parameter from Unconnected Stored procedure. Check the below example. To capture all the return values from a stored procedure that returns more than one value create a variable port for each additional output value with a datatype that matches the datatype of the Stored Procedure's output fields. Example: You have a stored procedure defined in Oracle as: PROCEDURE SP_MYTEST ( INPUT_VALUE IN VARCHAR2, OUTPUT_VALUE_ONE OUT VARCHAR2, OUTPUT_VALUE_TWO OUT VARCHAR2) For this example create the following ports in an Expression Transformation: PORT NAME TYPE EXPRESSION V_SP_OUTPUT_TWO V V_SP_OUTPUT_TWO SP_RETURN O :SP.SP_MY_TEST(INPUT_STRING,PROC_RESULT,V_SP_OUTPUT_TWO) SP_OUTPUT_TWO O V_SP_OUTPUT_TWO More Information PowerCenter has a special field called PROC_RESULT which is used in an unconnected Stored procedure call as a place holder for the output value from the Stored Procedure. However, if there is more than one output value returned when calling the Stored Procedure additional parameters are needed.

Kollu - November 18th, 2008

Hi, Is there any limitation of return values from Unconnected Stored Procedure? I have like 23 Input parameters and 5 output parameters to a stored procedure. I want to use that as Unconnected. Could you please provide me some hints. Thanks, Kollu

Comments are closed.