I need to run an Oracle SP in informatica, Which is in different server.
I am loading data from one oracle server to another oracle server. (Its like one to one mapping) From one table to another,
After that i need to execute an SP from that same Oracle server. (That i can given in the POST SQL in the target table in Informatica)
I know how to run the SQL SP, is by giving EXEC spname,
but in Oracle 10 g how to execute the SP?
kindly let me know..
In Informatica we can execute stored procedures in two ways:
1) Directly connecting input ports to the stored procedures and getting the output ports
2) By Unconnected using the syntax :SP.<stored procedure name>(I/p values)
In unconnected we can 3 ways
a) Within the mapping
b) Pre session
c) Post session
Note for the b & c no need to give inputs the basic function of these is to do data manipulations on the loaded tables /DMLS
In either of the ways informatica will execute the SP by itself only
Thanks Ravi 4 ur reply,
To execute the oracle SP, can we give just CALL PROCEDURE_NAME() in the Pre session command or Post session command in the session of workflow managar?
From workflow manager this is not possible
we can do this in mapping only
in the properties tab of Stored procedure transformation we have option to execute it
in 4 ways
1) Source Pre-Load
2) Source Post-Load
3) Target Pre-Load
4) Target Post-Load
to do this the stored procedure that is imported into the mapping need to be unconnected
Not true, you CAN call an Oracle stored procedure in the pre/post SQL on either the source or target of a mapping, or as override in a session (within the workflow manager) using the syntax "call sp_name();" as shankar stated above. Calling the proc outside of the mapping itself means of course that you can't send any row-based data as parameters to the proc. I.E. your options for using parameters are limited to the scope from where the proc is called.