Using Variables in ODI: The Timestamp Example

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Model, Project, Interface and Package are used here assuming that you understand these concepts in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information on these concepts.

This is the first post of a series that will explore how variables can be used in ODI for more flexibility in code generation.
In this discussion, we will use a Timestamp to extract changes from a source table. This will help us illustrate the usage of variables in Interfaces and Packages.
Subsequent posts will explain the usage of variables in more advanced cases:
- using variables to pass parameters to a process
- bind variables
- using variables in Topology.

But first, let's get started with the general usage of variables with our Timestamp extract example.

INTRODUCTION

Detailed instructions will be provided throughout this post to describe how to reproduce this case. We will create 2 variables in ODI for this example. Then we will implement the following logic:
- Store current date and time in variable1 as we start our process
- Filter out the data from the table on using variable2 that contains the data and time of the last operation
- Update variable2 with the value of variable1
- Run again!

The examples given below assume that we are using an Oracle database, but can easily be adapted to work on any other database.

1. VARIABLES SETUP IN ODI

1.1 Variables Definition

1.1.1 General Information
The first step is to create two variables in ODI. The first variable will be used to store the date and time at which we started the process – only for the duration of the process. The second variable will be used to save that date for the next iteration of the package.

To create a variable in ODI, expand your project and right-click on the ‘Variables’ folder. Then select ‘Insert Variable’

Create New Variable


You will then see the Variable definition window:

New Variable Definition

1.1.2. First Variable: StartTime
We will name our first variable StartTime.
Set the data type to ‘Alphanumeric’, simply because it is generally easier to handle than dates.
Define a default value, for instance
’29-MAR-2009 10:08:12’.
Note the single quotes around the text for the default value. It is usually better to define a default value, if only to make sure that if you forget to set a value for your variable, you will not be left with a NULL...
Fill in the ‘Description’ field to explain what the variable will be used for. This is important as this field will be retrieved by ODI when the documentation is generated from the “Print” menu in your project:
This variable will be used to store the time at which the extraction process starts so that we can remember this value for the next run of this same process

Now click on the ‘Refreshing’ tab to see the following window:

Variable Refresh SQL Query

Select any logical schema on Oracle and type the following SQL query:
Select SYSDATE from DUAL

Note:: You can run the query to make sure it is valid. Click the ‘refresh’ button in the above window. Then close the variable definition window, and check in the ODI Logs that the query runs successfully. You can then re-open the variable definition window, and check the resulting value in the ‘History’ tab of the variable.

Click Ok to save your first variable.

1.1.3 Second Variable: LastUpdate
We will name our second variable LastUpdate.
Set the data type to ‘Alphanumeric’. The default value will be the original start date (basically the date for the oldest recors in our source file. (be careful here with the date format).
’01-JAN-1997 00:00:01’
No need to put a refresh value here. (Though an alternative may have been to put here a query to retrieve the oldest date in the source system!)

Click Ok to save your second variable

Both variables can be seen in the Project Tree if you expand the Variables entry.


2. USING THE VARIABLES IN INTERFACES AND PACKAGES

2.1. Use the ‘LastUpdate’ variable to filter incoming records

When you are designing your interface, you now want to filter data based on the timestamp. This will be done with the LastUpdate variable
1. In the interface, create a filter: drag and drop a date column in on the dark gray area to create the filter. ODI will display a little funnel and open a property box for you to specify the filter expression. That box is pre-filled with the column name
2. Edit the filter expression and add:
>=#LastUpdate
(We only want the records where the timestamp is greater than the value of the variable)

Variable In Interface

2.2 Create a package to handle the timestamp values

Now you can create a package (in your project, right click on Packages and select Insert Package), in which you will do the following:
1. Drag and drop the LastUpdate variable from the Project Tree into the package. Then click on the icon representing the variable in the package and set the type to declare variable. Set the step name to Declare LastUpdate
2. Drag and drop the StartTime variable from the Project Tree into the package. Then click on the icon and set the type to declare variable. Set the step name to Declare StartTime.
3. Drag and drop the StartTime variable from the Project Tree into the package again. Then click on this icon and check that the type is set to refresh variable. This will run the associated query and store the returned value in our variable.
4. Drag and drop your interface in the package
5.Drag and drop the variable LastUpdate again, but this time set the type to Set Variable, click the assign bullet and put the entire name of the StartTime variable (including the project code)

Note: in the screen shot below, replace SALES with the CODE of the project where the variables have been defined. To retrieve the project code, double-click on the project name in the objects tree

6. Then click on the PackageOK.PNG button (available in the Package upper toolbar), and connect the icons sequentially with green arrows to define the execution order for these steps. Once this is done, save your package. This package will now process incremental loads of your sales.

The screen shot below shows the sequential ordering of the steps, as well as the value set for our LastUpdate variable after processing the interface.

Variables In Package

3. UNDERSTANDING POTENTIAL ERRORS

3.1 Project CODE vs. project NAME

Be careful when you use a variable to prefix it with a # sign, and with the project CODE, not the project NAME.

3.2 Mistyped variable names

Mistyped variable names (in the variable affectation step, or in the interface) might have unexpected results. Pay attention in particular to the case used for the variable names and when you reference them: variables are case sensitive!

3.3 Understanding Variable Values

Variables values are not visible in the generated code that you can review in the Operator interface. For instance, the code we have described above for the interface would appear in the Operator as
Select (…) from (…) where HIRE_DATE>=#SALES.LastUpdate

Even though the variable value is not visible here, we know that ODI has recognized the variable as it has inserted the project code. This is good news and guaranties that the value will properly be substituted at runtime.

If you want to see the value taken by the variable at runtime, look at the History tab in the variable definition, or look at the “Variables” folder in the operator (under the session ID) and then check out the history tab of all variables used in this execution.

 

MORE ON VARIABLES IN ODI

For more information on ODI variables, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "Creating and Using Variables"

All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

Comments:

I have created the package as it is mentioned in this article. However, this is the error message I receive when I try to run the package. 1830 : 22008 : java.sql.SQLException: ORA-01830: date format picture ends before converting entire input string java.sql.SQLException: ORA-01830: date format picture ends before converting entire input string at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185) at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633) at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:984) at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:535) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1051) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3026) at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execCollOrders(SnpSessTaskSql.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java) at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java) at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java) at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java) at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java) at com.sunopsis.dwg.cmd.e.i(e.java) at com.sunopsis.dwg.cmd.g.y(g.java) at com.sunopsis.dwg.cmd.e.run(e.java) at java.lang.Thread.run(Unknown Source) Please help me out.

Posted by Kranthi Kumar on September 01, 2009 at 07:49 AM PDT #

This is an error message returned by the database indicating that you are using an invalid date format for Oracle. If your data format is not "DD-MMM-YYYY" (i.e. "03-SEP-2009" for today's date) you probably have to use the to_date function for Oracle to understand your date format.

Posted by Christophe Dupupet on September 03, 2009 at 06:28 AM PDT #

Hi,
Please Help me ASAP..

Its Really Urgent.

I have Created a package as per your blog post.
When i am running First time it is showing sucessfully completed and when i am running at second time,it is displaying the following error..

ODI-1228: Task Incremental part 1 (Integration) fails on the target ORACLE connection Oracle Landing.
Caused By: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1890)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3747)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3937)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1535)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:163)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2906)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:537)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)

Posted by guest on October 01, 2011 at 11:13 PM PDT #

Where exactly in the execution of the package do you have this error? This could be because one variable is not set properly. Can you check the history of values taken by the variables?

Posted by Christophe Dupupet on October 03, 2011 at 09:30 AM PDT #

How can I perform conditional testing on variables? I would like #Current_LYM to equal either #Ovrd_LYM_Date or #QDB_LYM_Current based on the Ovrd_LYM_Flag. Yes = use override date otherwise use the QDB date.

Ex: CASEWHEN (#Ovrd_LYM_Flag = 'Yes', #Ovrd_LYM_Date, #QDB_LYM_Current)

I get the following error: Error while executing query. Statement does not generate a result set.

Is there a schema that only uses logic? Any help would be appreciated.

Mike

Posted by Mike Mueller on October 10, 2011 at 02:31 PM PDT #

Mike,

The refresh statement of a varaible must be a fully qualified sql query that returns a singleton. You logic will work if you wrap it in a complete SQL query.
On Oracle. something like the follwing will work:
select
case when #Ovrd_LYM_Flag = 'Yes' then #Ovrd_LYM_Date
else #QDB_LYM_Current
end
from dual

Posted by guest on October 11, 2011 at 02:43 AM PDT #

Thanks Christophe for the solution and quick response!

I implemented the following code for variable #Current_LYM which works for SQL Server:

SELECT
CASE
WHEN '#Ovrd_LYM_Flag' = 'Yes'
THEN '#Ovrd_LYM_Date'
ELSE '#QDB_LYM_Current'
END AS "Current_LYM"

Thanks, Mike

Posted by Mike Mueller on October 11, 2011 at 05:09 AM PDT #

Hi, i have created 4 variables in ODI 11g as mentioned below:

1.Variable_name = v_date
datatype = alphanumeric
default value = '30-NOV-2011 11:00:00'

2.Variable_name = v_time_offset
datatype = alphanumeric
default value = 125

3.Variable_name = v_starttime
datatype = alphanumeric
default value = '30-NOV-2011 11:00:00'

4.Variable_name = v_endtime
datatype = alphanumeric
default value = '30-NOV-2011 11:30:00'

I have created a filter on the source table to pull the records only for the time between v_starttime and v_endtime.

Now, in the ODI package, I have declared all the above 4 variables and here I want to calculate the variable values v_starttime and v_endtime to pass on to the interface. So, I have set the variables as below
v_starttime = #v_date+(#v_time_offset/1440)
v_endtime = #v_date+((#v_time_offset+5)/1440)

Now when I execute the package, the package(the interface) is getting failed with the error - invalid charecter. When I looked into the variables to see whether they are refreshed properly, they wont. In the Operators, Under 'History' of variables section, the value of both the variables are showing as #v_date+(#v_time_offset/1440) and #v_date+((#v_time_offset+5)/1440). Thats means the varaibles are not evaluated properly. KIndly help me how can I fix this ? Many thanks in advance ...

regards,
sai

Posted by guest on November 30, 2011 at 03:27 AM PST #

Sai,

You could use the "refresh" tab of the variables for these calculations. Because of the different data types (alphanumerics combined with math operators) I think that will be your best option.

Something like the following should work for v_starttime(assuming you are using Oracle - but you can easily adapt for other databases):
select to_date(#v_date)+(#v_time_ofset/1440) from dual

Then you do a "refresh variable" to execute this code in your package. Addintional bonus: if anything fails, you'll have an error message from the database telling you exactly what failed and why.

-Christophe

Posted by Christophe Dupupet on November 30, 2011 at 12:42 PM PST #

Thanks for your response Christophe !!!

I have followed your suggestion but still could not resolve the problem.
I have mentioned both the variable as alphanumeric only and haven't mentioned any default value for both the variables. NOw in the Refreshing tab, I have mentioned 'select to_date(#v_date)+(#v_time_ofset/1440) from dual'. When I try to Test the query, it has given the error - 'ODI-20123: Error while executing the query'. Even though i am getting this error, I proceed to the package and then changed both the variables as 'Refresh Variable'. And the package is getting failed with the error : ORA-00911:invalid character. The values of the variables in the History section of Variables in the operator are still not evaluated with the actual values. Don't know the problem really !!! :-(

Posted by guest on November 30, 2011 at 10:58 PM PST #

Sai,

There are two issues at work here.
ODI-20123 is most likely due to the fact that you are using variables in another variable... with no value defined for these variables. This type of refresh will only work in a package, where you can set the value of the variables (either with a "set variable" or a "declare variable" if you pass the values as parameters.
For the ORA-00911 the issue is most likely that the variables are not recognized - as instead of being substituted with values, they are sent as is to the database... who doesn't know what to do with that (with reasons - these are ODI variables).
A few things to check:
- in the generated code, you shold the variable name should appear prefixed with the code of your project. Something like #MYPROJ.myvariable. You can actually insert that project code in your refresh code (double click on the project name to retrieve the matching code)
- variable names are case sensitive. Make sure that the definition and usage of the variable respect the same case
- if you are using global variables, use the syntax #GLOBAL.myvariable
- you can only use variables that are defined in the project where the package is defined. If you need to use variables that are outside of the current project, then these variables MUST be global variables

That should do it!

-Christophe

Posted by Christophe Dupupet on December 01, 2011 at 02:52 PM PST #

I'm having an issue with "File" model using the variable to set the file name. I fully qualified the variable, i.e. APB_PROJECT.VS_Actls_File_Nam and gets refreshed from a SQL query. I see the correct value in the variable, but the package that runs the interface is using the variable name and not the value.

Any suggestions? Was working when I hard-coded the variable, but I'm generating 13 text files with the same format only different names.

Thanks, Mike

Posted by Mike Mueller on December 13, 2011 at 02:40 AM PST #

I'm having an issue with package running other scenarios and picking up the correct variable values. When the separate package/scenario is run by itself it runs correctly. When they are all placed into the main package using the scenarios, the first one runs correctly, the next one which reuses the variables seems to be using the first ones values.

Seems to be only in the ODI Procedure with the SQL code using the variables for various column values.

Thanks,
Mike

Posted by Mike Mueller on December 15, 2011 at 06:49 AM PST #

Mike,

A few elements to validate if the substitution does not work:
- make sure that you prefix the variable name with the # sign: #APB_PROJECT.VS_Actls_File_Nam
- remember that variable names are case sensitive
- the prefix is the project code, not the project name - that can be confusing at times.
One way to make sure that the variable is properly recognized it to omit the project code altogether, and only use the variable name. If the variable is recognized, the project code will appear in the logs. So you would use #VS_Actls_File_Nam and in the logs you would read #APB_PROJECT.VS_Actls_File_Nam

The second issue can be more tricky. Packages share variable values with their parents, but not usually with their siblings... so unless the variable value is refreshed by the parent in between children calls, the value that will be passed along will always be the one that was set prior to calling the first child.

I hope this helps

-Christophe

Posted by Christophe Dupupet on December 23, 2011 at 03:59 AM PST #

hey, i did not understand why you had same variable of different type. you had 3 variable then an interface and a variable. can you please explain what you are trying to achieve with the first three variables (each step by step)? i have read ur blog completely but could not figure out why a declare and a refresh of a same variable.

thanks,
Karthik

Posted by Karthik on February 20, 2012 at 04:29 AM PST #

Karthik,

It is true that declaring a variable just before you refresh it is overkill. I got into the habit of declaring all variables in my packages before using them, the same way you would in some programming langage, to make sure that it is initialized before you actually use it. This way, if my code evolves over time (say I move the refresh part to a later portion of the package) I am certain that the variable will be instanciated before it is used in the interface.

-Christophe

Posted by Christophe Dupupet on February 29, 2012 at 06:52 AM PST #

Can Oracle PL/SQL anonymous blocks be used in ODI Project Variable Refreshing sections?
Thanks,
Dain

Posted by Dain Andrus on February 11, 2014 at 03:14 PM PST #

Hi Dain

No. With 12c database you can using inline PLSQL however, so you could wrapper the PLSQL code in an inline function and invoke the function from a SQL statement.

Here is an illustration, the increment PLSQL function is defined inline and used from the SQL statement;
https://blogs.oracle.com/dataintegration/resource/odi_12c/odi_variable_refresh_inline_plsql.jpg

You can find more information on inline PLSQL from Oracle documents or blogs such as the one below from AMIS;
http://technology.amis.nl/2013/06/25/oracle-database-12c-in-line-plsql-functions-in-sql-queries/

Cheers
David

Posted by David on February 13, 2014 at 10:08 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today