Importing a stored procedure from oracle to Informatica

Question

I have to import a stored procedure from Oracle database into Informatica. But when I try to use the Stored Procedure Transformation I cannot view the respective stored procedure. I am using the correct oracle driver since I don't have any trouble in importing target database tables. I tried viewing the stored procedures in the oracle database using select * from all_objects where object_type='FUNCTION' but could not find the function I am looking for. Please advise me on these:

• Can we view the list and code of Stored procedure(s)/function(s) in Oracle

• What am I missing in Informatica stored procedure transformation?

Answer 1

If you don't get any results from this..

select * from all_objects where object_type='FUNCTION'

It either means you don't have any functions or the current user does not have the privileges to execute any functions. Find out which user owns the function and grant the following.

example :

grant execute on <owner>.<function_name> to ETL_USER;

To avoid confusion, make sure the username in the informatica connection and the one you are using when connecting to the database are the same.