This discussion is archived
9 Replies Latest reply: Dec 28, 2012 4:11 PM by user709772 RSS

Variable value not replaced in interface filter when executed

user709772 Newbie
Currently Being Moderated
I'm using a date variable as a interface filter. when I execute the interface, the variable is not being replaced with it's value. Receiving a Java sql error: Caused By: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
     at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1899)


Is there a setting I've missed for the variable to render when the interface is executed? The first time I ran the interface with the variable it worked, but consecutive executions of the interface is no longer replacing the data variables value.

The session code clearly shows the variable not being replaced with the date variable's value: And (TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= #OAL_CENTAL_ODI_HUB.D_OALHCM_EFFDT)
  • 1. Re: Variable value not replaced in interface filter when executed
    SutirthaRoy Guru
    Currently Being Moderated
    user709772 wrote:
    I'm using a date variable as a interface filter. when I execute the interface, the variable is not being replaced with it's value. Receiving a Java sql error: Caused By: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
         at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1899)


    Is there a setting I've missed for the variable to render when the interface is executed? The first time I ran the interface with the variable it worked, but consecutive executions of the interface is no longer replacing the data variables value.

    The session code clearly shows the variable not being replaced with the date variable's value: And (TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= #OAL_CENTAL_ODI_HUB.D_OALHCM_EFFDT)
    If the variable is a date/charcter type variable then it should be enclosed within single quoats
    i.e.
    And (TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= '#OAL_CENTAL_ODI_HUB.D_OALHCM_EFFDT')

    Also the variable needs to be refreshed/set before using it in a filter
  • 2. Re: Variable value not replaced in interface filter when executed
    user709772 Newbie
    Currently Being Moderated
    Adding quotes causes a literal error when executing the interface. I've refreshed the variable and reran the interface. Still receiving same error.
  • 3. Re: Variable value not replaced in interface filter when executed
    JeromeFr Expert
    Currently Being Moderated
    Hi,

    Could you try to create a package with a refresh of the variable as first step and the interface as second test ?

    I already had that kind of issue and putting it inside a package sorted it.


    Regards,
    JeromeFr
  • 4. Re: Variable value not replaced in interface filter when executed
    tina.wang Journeyer
    Currently Being Moderated
    have you tried
    :OAL_CENTAL_ODI_HUB.D_OALHCM_EFFDT

    eg: in my interface filter, I write
    EMPLOYEES.HIRE_DATE>:mydate
  • 5. Re: Variable value not replaced in interface filter when executed
    Bhabani Ranjan Guru
    Currently Being Moderated
    My 1st question to you.
    Are you using a package to run the interface or not ? If not then you have use it anyhow.

    Thanks
    Bhabani
    http://bhabaniranjan.com/
  • 6. Re: Variable value not replaced in interface filter when executed
    user709772 Newbie
    Currently Being Moderated
    I've setup a package to execute the variable refresh and interface and set the filter variable to use the colon. I noticed something odd in the execution's session code. When I prefix the variable in the filter with a colon, I don't receive the IN/Out error, but the session code is not substituting the project name correctly nor do I see the variable value used the session code.

    Example: Project name: OAL_CENTRAL_ODI_HUB
    Variable name: D_OALHCM_EFFDT
    Interface Filter: TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= :D_OALHCM_EFFDT

    When pkg is executed, the session step's code shows this:
    And (TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= :OAL_CENTAL_ODI_HUB.D_OALHCM_EFFDT)

    It should show this:
    And (TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= :OAL_CENTRAL_ODI_HUB.D_OALHCM_EFFDT)

    So, the session code is missing the letter 'R' in 'CENTRAL'

    Also, from the sesssion code, I would have expected to have seen the variable replaced by its value not the variable name. So in this case, I should have seen this:(right?)
    And (TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= '01-nov-2012')


    I don't understand why the execution is mis-naming the project name. I switched the variable back to use # and it's doing the same thing.

    So, I'm still not seeing the variable work.
  • 7. Re: Variable value not replaced in interface filter when executed
    958953 Newbie
    Currently Being Moderated
    Please try to do something like this.
    1. Create a alphanumeric variable. In the refresh tab provide something like this : select to_char(sysdate,'DD-MM-YYYY') from dual

    2. In the interface use the fully qualified name of variable TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= TO_DATE('#OAL_CENTRAL_ODI_HUB.D_OALHCM_EFFDT' , 'DD-MM-YYYY')

    3. In the package refresh the variable before the interface.

    This has worked for me..
  • 8. Re: Variable value not replaced in interface filter when executed
    SutirthaRoy Guru
    Currently Being Moderated
    user709772 wrote:
    I've setup a package to execute the variable refresh and interface and set the filter variable to use the colon. I noticed something odd in the execution's session code. When I prefix the variable in the filter with a colon, I don't receive the IN/Out error, but the session code is not substituting the project name correctly nor do I see the variable value used the session code.

    Example: Project name: OAL_CENTRAL_ODI_HUB
    Variable name: D_OALHCM_EFFDT
    Interface Filter: TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= :D_OALHCM_EFFDT

    When pkg is executed, the session step's code shows this:
    And (TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= :OAL_CENTAL_ODI_HUB.D_OALHCM_EFFDT)

    It should show this:
    And (TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= :OAL_CENTRAL_ODI_HUB.D_OALHCM_EFFDT)

    So, the session code is missing the letter 'R' in 'CENTRAL'
    This may be because your project name is OAL_CENTRAL_ODI_HUB
    But your project code is OAL_CENTAL_ODI_HUB

    You can double click onto your project name and see it's name and code .
    >
    Also, from the sesssion code, I would have expected to have seen the variable replaced by its value not the variable name. So in this case, I should have seen this:(right?)
    And (TRUNC(PER_ALL_ASSIGNMENTS_M.LAST_UPDATE_DATE) >= '01-nov-2012')


    I don't understand why the execution is mis-naming the project name. I switched the variable back to use # and it's doing the same thing.

    So, I'm still not seeing the variable work.
  • 9. Re: Variable value not replaced in interface filter when executed
    user709772 Newbie
    Currently Being Moderated
    I found the issue. The variable was not working due to the LKM Knowledge Module. I had to switch the Oracle LKM Oracle to Oracle DBlink to LKM Sql to Oracle.

    It didn't matter if I set the variable as a date or a character. It was due to the LKM used.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points