3 Replies Latest reply: Jan 23, 2009 11:27 AM by SPowell42 RSS

    Informatica "source qualifier override" type functionality in OWB?

    SPowell42
      Hi, we're running in to an issue where we'd like to create an OWB map that exactly mimics SQL that looks like this (and works properly):

      select column1,
      column2,
      SYS.DBMS_METADATA_UTIL.LONG2VARCHAR@dblink(4000, 'tablename', 'column3', rowid)
      from tablename@dblink

      Just to explain, this is calling a remote function on the remote database to translate a LONG into a varchar2(4000) column. The problem is, I can't just put the function into an expression after the table operator - because the function itself needs to be called on the remote database PRIOR to passing the CLOB data back. Also, even trying to drop a table into a map that has a LONG column makes OWB throw out an error.

      Is there any way to do this through the GUI and/or any way to just paste a set of SQL into an operator?

      Thanks very much for any advice! p.s. - no, we can not change the datatype on the source (sadly)
      Scott