I often use Oracle Data Integrator (ODI) for automation and ETL processes in the EPM projects I help deliver. ODI is a wonderfully flexible and powerful tool that works incredibly well out of the box. But there are some little tweaks to ODI that I find necessary to resolve some occasional hiccups I encounter. I use ODI variables to make ODI packages as dynamic as possible by minimizing hard-coding of values used in the packages and other objects. Perhaps the most powerful type of ODI variable are variables that have values set through the use of SQL queries. These SQL-based variables can be refreshed at run-time, updating their values with data pulled from relational sources. However, one problem I find myself encountering with ODI variables is that out-of-the-box ODI only allows alphanumeric values of up to 250 characters maximum in length. If you try to assign a value with a length greater than 250 characters, you will receive an ODI exception (ODI-1228) with a reference to an Oracle DBMS exception of ORA-12899. A screenshot of this exception is show below. Essentially it states that you are trying to assign too long of a value to a variable (and the back-end column that stores the value).
This error was generated when attempting to refresh the value of an ODI variable from a SQL query that returned a value with 407 characters. The error also indicates the maximum length allowed to be 250 characters. Well, 250 characters are not that large of a value and can potentially limit what you can do with ODI variables in your automation and ETL processes. Never being one to accept things as they are, I set out to find out where the values for (refreshing) variables are stored and whether the default value can be altered.
Lifting the hood on the ODI Work repository back-end seemed like the logical place to start looking. Thankfully, the naming conventions for the tables in the back-end are fairly explicit as can be seen from the screen shot showing the section of tables related to ODI variables.
In particular, we’re interested in the SNP_VAR_DATA table (highlighted above). In this example, Oracle schemas were used for the back-end repositories for ODI (Master and Work repositories). If we examine the column details for this table, we’ll find a column named “VAR_V”. This column is used to store the variable values that are then referenced in ODI packages and other objects. The screen shot below shows the default column settings for the SNP_VAR_DATA table.
As you can see, ODI ships with a 250 character limit for variable values. In my opinion, this length is too small and often can become an issue. The default DATA_TYPE is VARCHAR2 (again this is for ODI installations with Oracle databases). So what are our options you may ask? Depending on the particular database used for the ODI installation, we can simply increase the allowable limit of characters. Typically with Oracle databases (pre-12c), the maximum length for VARCHAR2 data types is 4000 characters. In my example, the install database is Oracle 11g, so to fix the ‘ORA-12899: value too large for column’ errors I increased the column size for the VAR_C column in the SNP_VAR_DATA table to the maximum 4000 characters.
There was no need to restart any agents or re-open the ODI studio after this change. I re-ran the variable refresh that had previously failed and successfully refreshed the value of the variable. Now I can assign values up to 4000 characters long to variables in ODI.
Some additional notes about the changes discussed here. The examples presented are for installations using Oracle database(s) for the ODI repositories. I have not had an ODI installation with SQL Server or any other RDBMS other than Oracle (at least not in a long time). My assumption is that you will encounter similar limits (250 characters) for the VAR_V column used to contain the variable value with other database as well. If you encounter this scenario, you should be able to increase the initial value just the same. Please note that the maximum lengths for the data type of the VAR_V field for other databases may be greater or lesser than the 4000 character limit in Oracle 11g. For example, Oracle 12c now supports extended data types (with proper configuration) in which the VARCHAR2 data type can be increased from a maximum of 4000 characters to 32767 characters! Hopefully this post helps others that take ODI to the limit and beyond!
Uh-oh. Look, ODI makes baby Ragnar so sleepy. Nighty-night baby Ragnar.
