ODI Variables not getting assigned

651718

Hello Everyone,

I am facing problem in assigning a value to a ODI Global Variable through Scenario.

I am putting down the steps which I have taken,

i.) Created a Global Variable(MAX_RECORDS), with a
default value untouched(i.e I haven't entered anything),
datatype Alphanumeric,
Action Type - NotPersistant
Refreshing Command as SELECT #MAX_RECORDS FROM DUAL (I have entered the variable name using expression editor).

2.) Created a Project Variable(FILE_NAME), a refreshing variable with the refreshing command SELECT CONCAT(#MAX_RECORDS,'FILE.txt') FROM DUAL
3.) Then an interface which loads a flat file from a table. The file name will depend on the variable FILE_NAME.(which have been correctly set in the Model Datastore).

4.) In the package the flow is as follows,

MAX_RECORDS(a declare variable) ---> MAX_RECORDS(a set variable) ---> MAX_RECORDS(a refresh variable) ---> FILE_NAME(a refresh variable) --> Interface

5.) A scenario has been generated using the above package with the startup parameter as GLOBAL.MAX_RECORDS
6.) On launching the scenario from the Designer, it prompted a for the GLOBAL.MAX_RECORDS which I have passed as 3

But the scenario failed with the error 'java.lang.Exception: Variable has no value: GLOBAL.MAX_RECORDS'

Any help would really be helpful and appreciated. Please let me know if the problem stated is not clear.

Thanks,
Saravanan

651718

Hello Everyone,

Can anyoine please help, Am I doing something wrong here?

What could be reason for java.sql.SQLException: ORA-00936: missing expression

Thanks,
Saravanan

566902

You probably need to add quotes aound the variable where you use it:

e.g. SELECT *'*#MAX_RECORDS*'* FROM DUAL

This is because ODI will substitute the value of the variable before submitting the query.

geeo73

Saravanan,

couple of things I thought while reading this post:

- If you do not persist the value of the variable, as soon as you refresh it, it will not have a value in it; i.e. you set it to 3, and then refresh it but it is trying to reference itself (which is none persistent), I would expect it not to have a value. Try just using one step in the package.

- Why are you using a refresh type; this is geared towards retrieving a value from a datastore - if you have set it when you start the scenario, why do you need to refresh it again.

- Have you tried using the latest value type in the variable creation (it holds the last value held)

- In your query, try using #GLOBAL.MAX_RECORDS instead of #MAX_RECORDS.

Hope this guides you towards the resolution you are after.

G

651718

Hello Everyone,

Oracle has confirmed that this is a bug till version 10.1.3.4.0 and the bug has been fixed and is ready with version 10.1.3.5.0.

i.e When the scenario is launched with the scenario variable being passed from the designer, the variable is not getting assigned the passed value.

Thanks,
Saravanan

User766381-Oracle

Hi Saravana,

Is there any reference for the Issue mentioning the fix will be in 10.1.3.5.

Thanks.