Forum Stats

  • 3,827,551 Users
  • 2,260,791 Discussions


Oracle function call ?

user5912565 Member Posts: 87
edited Jun 7, 2013 9:54PM in Data Integrator
I have to create a loop using an integer variable (1 to 10) in a package in such way for each number I have to call an Oracle function which is testing the number (as input parameter). The loop stops when the function returns TRUE.

How it can be done in ODI?


  • mRainey
    mRainey Member Posts: 386 Bronze Badge

    A quick search on the internet will lead you to this blog post:

    It should get you started.

    Michael Rainey
  • 1. Create a variable Iteration. Set it to 1.
    2. Create another variable func_output and put a query like: SELECT 1 FROM DUAL WHERE your_oracle_function(#Iteration) = TRUE
    3. Create a package. Set Iteration to 1. Then refresh #func_output. If the refresh fails, increment #Iteration and go to refresh step (i.e. step 2) after that. This loop will terminate when the variable is refreshed successfully (that is when the function returned true)
  • user5912565
    user5912565 Member Posts: 87
    Thank you!

    How about if I use a Date as loop? Let's say initial value is current date and it is checking all previous dates until condition is met.

    I have tried SELECT 1 FROM DUAL WHERE your_oracle_function(#Iteration) = TRUE but it didn't work.
  • Why did it not work? Was there error? Did the function work and returned?

    Yes, using date is possible if you can handle it. Handling the date and passing it to RDBMS is tricky.
This discussion has been closed.