ODI 12c - Using PL/SQL Packages in mappings

kovar

Hi everyone!

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

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

Adrian_Popescu-Oracle

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

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