Forum Stats

  • 3,853,440 Users
  • 2,264,222 Discussions
  • 7,905,361 Comments

Discussions

Using variables at a procedure

Daniel Hein
Daniel Hein Member Posts: 36
edited Nov 12, 2008 10:52AM in Data Integrator
Hello experts,

i'm new in odi but have been doing just fine but i'm not achieving to use variables ate a odi package.
let me explain the issue:

1) i create three variables non-persistents at odi that retrieves value from a sql select statement, like this, for example:

select '<=%odiRef.getPrevStepLog("SESSION_NO")%>'FROM DUAL; (Variable Name-SESS_NO)
select '<=%odiRef.getPOP("POP_NAME")%>' FROM DUAL;(Variable Name-INTER_NAME)
select '<=%odiRef.getPrevStepLog("STATUS")%>' FROM DUAL;(Variable Name-FLAG_STA)
...

2) then i create a odi procedure that insert values in a log table: insert into <table_name> values ('#SESS_NO','INTER_NAME','#FLAG_STA')

3) then i create a package that has the follow steps:

Interface (Success) -> Procedure (with the insert statement)

The issue is, where should i put the variables? Wherever i put them, the operator shows me a error message that, at the execution of the insert statement, indicate that the variable does not have value. I try inserting then before the procedure, ( Interface (Success) -> var1 (Success) -> Var2 (Success) -> Var3 (Success) ->Procedure (with the insert statement)) but them, obviously, a error is shown because the previous step of the var2 is var1, where should be the interface.

I try to create a scenario (new package) with the variables and the principal package becomes like this:

( Interface (Success) -> Scenario (new package) (Success)-> Procedure (with the insert statement)

But the error persists and no value is assigned to the variables.

Any help will be gratefully appreciated.

Thanks in advance,

Daniel Hein

Edited by: user10470648 on Nov 10, 2008 4:11 AM
Tagged:

Best Answer

  • Cezar Santos
    Cezar Santos Member Posts: 1,901
    edited Nov 12, 2008 8:34AM Answer ✓
    Hi Daniel,

    Sorry by my delay to reply you...

    About the problems....

    1) OK

    2) You need to repeat the "status refresh variable" after execute each interface or procedure. It is possible to make paralallel execution but that is another architecture.

    3) About the variables, just drag and drop it to the package and let it as declare type. That wll allow you, after create a scenario, use those variable as parmeters.

    I will send you some ODI document about how to use it (in replay to the email you sent me)


    Does it help you?

    Edited by: Cezar Santos on 12/11/2008 11:34

Answers

  • 566902
    566902 Member Posts: 809
    Daniel
    In your package you should put three Variable steps, drag each of the variables on to the package and make it a "Refresh" step. This will force a value into the variable, which should then make it available for substitution later in the package.
    C
    566902
  • Cezar Santos
    Cezar Santos Member Posts: 1,901
    Hi Daniel,

    How are you?

    You have some problems at you logic, let me try to help...

    Problem 1)
    Isn't possible to get "POP_NAME" outside from an interface, I mean, as this keyword returns a interface name only is possible to use in a interface.

    One way to "put" this information out of an interface is altering the IKM.

    Add a step (as first once you want to use this for log) with the following code:

    at Target tab:
    Technology: JavaScript
    <@String vIntName="<=%odiRef.getPOP("POP_NAME")%>";@&gt;

    now you can use a Java variable at any point of the code, just use the syntax: <@[email protected]&gt;

    Problem 2)
    The "PrevStepLog" API only works well if it is the imediate step after the step that you want to get the "Last Log".
    I mean, if you need to get the final status from interface and want to get "OK" or "KO" create the variable as you already did and make the flow "OK" and "KO" to this variable letting it as refresh.



    Problem 3)
    You told that "create a package", that is OK, but "not persistent" variable exists just in its original session and each scenario call create a new session.
    It means that to send those values to a new scenario you need to declare those variables inside the new scenario.


    Beside these small issues your process logic are very well.

    You don't need to create a variable to get the session at the first package, at any time at the package the Session API is available, use it directly when call the second scenario.


    Does it make any sense to you?


    Cezar Santos
    Cezar Santos
  • Daniel Hein
    Daniel Hein Member Posts: 36
    edited Nov 10, 2008 11:30AM
    CTs,

    Thanks for help me but i already made this and the error persist.

    Do i have to link the interface to the variables or just put them into the diagram?

    Edited by: user10470648 on Nov 10, 2008 8:30 AM
  • Hi Cezar, i'm fine, thanks, and you?

    First of all, thank you again for the fast response.

    1) i really wrote my example wrongly. The correct ones referent to the "interface's name" that i'm using is:

    select '<%=odiRef.getPrevStepLog("STEP_NAME")%>' from dual

    anyway, your hint is now saved and will be used :-)

    2) In this case, how can i make the flow "OK" and "KO", after interface, to all variables? because if i just make the flow to one variable, the "previous step" about the others variables won't be the interface.


    3) Can you explain these please. i don't know the steps to workaround this.

    I already have created the variables at my project. Do i have to create a scenario with these variables and then create other declare variables that receives the values of "my odi variables" at the principal package? If this is the right way, how can a set the declare variables to receive the value of "my odi variables" listed above?


    Thanks for your hopefully help.

    best regards.

    Daniel Hein
  • Daniel Hein
    Daniel Hein Member Posts: 36
    Good morning Cezar,

    Can you help me with the workaround that i wrote in the previous post?

    i really apprecciate your help.

    Thanks in advance,

    Daniel
  • Daniel Hein
    Daniel Hein Member Posts: 36
    The error "no previous step" was happening because the "transaction isolation" was set to the default option, "no definition", instead of "serializable".


    Anyway, thanks for all the help.

    Regards,

    Daniel Hein
  • Cezar Santos
    Cezar Santos Member Posts: 1,901
    edited Nov 12, 2008 8:34AM Answer ✓
    Hi Daniel,

    Sorry by my delay to reply you...

    About the problems....

    1) OK

    2) You need to repeat the "status refresh variable" after execute each interface or procedure. It is possible to make paralallel execution but that is another architecture.

    3) About the variables, just drag and drop it to the package and let it as declare type. That wll allow you, after create a scenario, use those variable as parmeters.

    I will send you some ODI document about how to use it (in replay to the email you sent me)


    Does it help you?

    Edited by: Cezar Santos on 12/11/2008 11:34
  • Help a lot Cezar.

    I really apprecciate your concern about my issue.

    Best regards,

    Daniel hein
This discussion has been closed.