Skip to Main Content
Forums

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using variables at a procedure

Daniel HeinNov 10 2008 — edited Nov 12 2008
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
This post has been answered by Cezar Santos on Nov 12 2008
Jump to Answer

Comments

566902
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
1 person found this helpful
Cezar Santos
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")%>";@>

now you can use a Java variable at any point of the code, just use the syntax: <@=vIntName@>

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
1 person found this helpful
Daniel Hein
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
Daniel Hein
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
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
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
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
Marked as Answer by Daniel Hein · Sep 27 2020
Daniel Hein
Help a lot Cezar.

I really apprecciate your concern about my issue.

Best regards,

Daniel hein
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 10 2008
Added on Nov 10 2008
#business-intelligence-applications, #data-integrator, #odi, #system-management-and-integration, #variable
8 comments
970 views