This is an Oracle related issue. You will need some kind of "cross-DB synonym" from the DB hosting the MV to the DB hosting the table, otherwise you can't do that. Using that "synonym" creating the MV should be a breeze using a SQL transformation.
What I mean to say is: this has nothing to do with Informatica software, this is purely a DB management issue.
Sorry that I can't be of more help here, this is not my area of expertise.
Hi Nico, Thank you for taking a stab at my post.
I am trying to find an alternate approach that is possible in Informatica. I need syntax that informatica understands something like dbname.schemaname.table_name
That can work if you have such a DB link from the DB where you need the MV to the DB hosting the table. Again, this is a DB management issue, nothing related to PowerCenter. PowerCenter just takes SQL statements as you write them and forwards them to the underlying DB. It does NOT change them on its own (with one exception which doesn't apply here).
Of course it would also be possible to first "copy" the original table to the DB hosting the MV; this "copy" can be done using PowerCenter, no question. Then you can issue the "CREATE MATERIALIZED VIEW" statement via PowerCenter. But again, this is nothing but a circumvention of the fact that for a MV you need a DB link in your case.