Forum Stats

  • 3,838,256 Users
  • 2,262,346 Discussions
  • 7,900,561 Comments

Discussions

ODI 12c - Using PL/SQL Packages in mappings

kovar
kovar Member Posts: 46 Red Ribbon
edited Jan 8, 2018 5:23AM in Data Integrator

Hi everyone!

What is some common best practice on using PL/SQL Packages in ODI 12c mappings?

package.png

I have a FILTER in my mapping that uses a function value inside PL/SQL Package:

... WHERE xyz NOT IN (package_name.function_name)

I was thinking about defining a ODI variable that will SELECT that value from package_name.function_name and then execute it before the mapping runs in the Begin Mapping Command.

I'm not sure if this is a best 'practice thing' to do, so I'm willing to hear from you your opinions about this situation.

Thanks,

- kovar

Tagged:

Answers

  • Adrian_Popescu-Oracle
    Adrian_Popescu-Oracle RomaniaPosts: 1,984 Employee
    edited Jan 7, 2018 10:15AM

    Hi Kovar,

    There are no such official best practices regarding packages.

    It works with a variable, but you need to create an ODI package and add the variable and the mapping.

    But it should work using the package in the filter.

    Still, use Schema.package.function.

    And be sure about user permissions on that package.

    Adrian

  • Pavan8u
    Pavan8u Member Posts: 278 Silver Badge
    edited Jan 8, 2018 5:23AM

    If that package.function_name return only single value then I suggest to go with variable, because it will be easy for debugging, you know what is the value that function is returning at run time.

    If you have to pass source.column to the function then use that package directly in the mapping, because function return value will change for each record.

    Regards,

    Pavan

This discussion has been closed.