top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Importing a stored procedure from oracle to Informatica

+1 vote
543 views

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?

posted Mar 5, 2014 by Rohini Agarwal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

1.While importing object into Informatica, listed objects are always owned by the "connecting" user. If your object is owned by some other user, you wont see it. To see objects not owned by connecting user, you'd have to choose "All" in the schema text box, while connecting.

2.You should be looking for object_type='PROCEDURE' and not "FUNCTION". Therefore, your query should read as select * from all_objects where object_type='PROCEDURE'

3.If all this is done and you still dont see the object, check privileges on that object to your connecting user. For this, the best course is a. ask your dba to grant execute privileges on that object to your connecting user. OR b. connect to database as the owner of that object and run the following command grant execute on to

answer Mar 5, 2014 by Shweta Singh
Similar Questions
0 votes

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?

+1 vote

In Informatica's PowerCenter, is it possible to set a parameter's value based on the output of a stored procedure? The parameter I want to set is a parameter I defined in a flat-file data object.

+1 vote

I have a Time column in my SQL Server table which has data like (14:00:00.0000000).

I am trying to load this type of data in Oracle table which has corresponding column datatype as timestamp. I am trying to convert the time column in SQL server to timestamp column in oracle using Informatica.

Whats the best way to do it?

+2 votes

I'm trying to load within an Oracle XE 10g database. When I run an Informatica PowerCenter Workflow, I get a following error:

CMN_1022    Database driver error...
CMN_1022 [Database driver event...Error occurred loading library [pmora8.dll]. System error encountered is 193. Error text is %1 is not a valid Win32 application.
.]

Any ideas what might be causing this error? My environment is as follows:
•Windows Server 2008 R2
•Informatica PowerCenter 9.5
•Oracle XE 10 G

Powercenter repository is on SQL Server 2012.

0 votes

We are bulkloading few of our target Oracle tables. Many of our tables had one column with datatype timestamp(6) with local time zone. when we insert just the date value(mm-dd-yyyy) then target database is defaulting the hh:mi:ss to 11:00:00 PM. But when we make the load type as normal then the it is inserting 12:00:00 AM in hh:mi:ss part. Could someone explain why this is happening?

Thanks in advance.

...