Forum Stats

  • 3,837,894 Users
  • 2,262,306 Discussions


ODI Variables not getting assigned

651718 Member Posts: 32
edited Oct 7, 2008 9:43AM in Data Integrator
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.



  • 651718
    651718 Member Posts: 32
    Hello Everyone,

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

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

  • 566902
    566902 Member Posts: 809
    You probably need to add quotes aound the variable where you use it:
    This is because ODI will substitute the value of the variable before submitting the query.
  • geeo73
    geeo73 Member Posts: 54

    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.

  • 651718
    651718 Member Posts: 32
    Hello Everyone,

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

    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.

  • User766381-Oracle
    User766381-Oracle Member Posts: 22 Employee
    Hi Saravana,

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

This discussion has been closed.