Advanced Properties of Location: New for 11gR2

In OWB 11gR2 the location object has a number of advanced properties for supporting a wide variety of data systems. These properties allow users to specify a range of information from how systems are accessed to the naming convention for temporary objects on the system. We will introduce these properties below.


In OWB Design Client, when you open the editor of a location, you will see several tabs such as Details, Advanced, Prefix, Mask and Connection Pool listed on the left panel. Click on the Details tab, the right panel displays the general credential information for a location. Let's take an Oracle location for example, as shown bellow.


For Oracle location, we have Name, Description, Connection Type, User Name, Password, Host, Port, Service Name, Schema, Version and Use Global Name in details page. They vary from location to location. To get the general properties for a specific location, please see doc Sources and Targets Guide 11g Release 2.

Note the property "User Global Name", it specifies the unique name of the database, which is composed of the database name and the domain, in the form of database_name.database_domain. If you see the error ORA-02085: database link *** connects to *** during the import of metadata of database objects into OWB. It may mean the database has the system parameter global_names set to true, but you did not select the option "Use Global Name" of the Oracle location, as a result, the generated database link name for the import doesn't include the global database name and hence cannot access the database.

2. Advanced

Click on the Advanced tab of the location editor, we see several properties displayed in the right panel. Again, let's take a look at an example for an Oracle location.


These properties are mostly suitable for the data sources that are accessible through JDBC and are utilized within the Code Templates framework.

Jndi Name:

is used to identify the location in Code Template framework. This property is generated which is not editable.

Driver Class:

JDBC driver class name. To connect to a data source through JDBC, you must install the appropriate JDBC driver for the data source. You can change this property according to the driver you choose. A default one is given based on the platform. For example, here oracle.jdbc.driver.OracleDriver is the default JDBC driver class for Oracle.

Url Template:

The default URL for the driver, this is used as a template. For example, jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Host)(PORT=Port)))(CONNECT_DATA=(SERVICE_NAME=Service Name))) is the default url template for Oracle.


The connection string used to set up JDBC connection. We can easily get url by substituting required properties into url template. Here substituting the Host, Port and Service Name with corresponding properties that are specified on the Details tab, we get the url string as bellow:


Work Schema:

This property is utilized by Code Templates framework. If you want to put the temporary tables or temporary objects created by framework into different schema, then you can specify a work schema. Click the button "Browse", the schemas in database which can be used as work schema are listed, select the one you'd like to. Note for Flat file location, the "schema" means directory.

Catalog and work Catalog:

These two properties are not utilized for Oracle location, but are utilized by SQL Server location. Catalog is used to specify a database, and the work catalog just works similarly as the work schema. If you want to put the temporary objects into different database, then specify a work catalog.

Data Server:

is used by various Code Templates. The Code Templates use data server for many purposes. For example LCT Oracle to Oracle (DBLINK) uses the data server property to capture the database link name, and the ICT Teradata to File uses the data server as the Teradata Server name.


Click on the Prefix tab of the location editor, the properties related to prefix are displayed.


These properties are used to prefix the temporary objects that the Code Templates create. Default values are provided and they can be changed to specify the naming standards of the data source system.

Error table prefix:

is used to prefix the error tables. Error tables are widely used in Control Code Templates(CCTs). CCTs provide data quality checks to ensure data integrity. These include checking for key constraints as well as other user-defined data loading rules. The usual practice in CCT is inserting those invalid records against key constraints or data loading rules into an error table. For example, in our predefined CCT CCT_ORACLE, there is a task called "5_CREATE_ERROR_TABLE", whose scripting looks like as follows:

                        create table <%=odiRef.getTable("L","ERR_NAME", "W")%>

While executing this task via runtime APIs, it will create a table with error table prefix (here is E$_). So with the above example, if the target table name is T1, the error table name will be E$_T1.

Loading table prefix:

is used to prefix the load tables. Load tables are commonly used in Load Code Templates (LCTs). Take the predefined LCT LCT_SQL_TO_SQL for example, it has a task called "2_CREATE_WORK_TABLE", the scripting looks like as follows:

                     create table <%=snpRef.getTable("L", "COLL_NAME", "A")%>

While executing this task via runtime APIs, it will create a table with loading table prefix (here is C$_). So if the target table name is T1, the loading table name will be C$_T1.

Integration table prefix:

is used to prefix the integration tables. We can find these tables in Integration Code Templates(ICTs). For example, in predefined ICT ICT_SQL_CONTROL_APPEND, there is a task called"3_CREATE_FLOW_TABLE_I_", it's scripting looks like as follows:

                     create table <%=snpRef.getTable("L", " INT_NAME", "A")%>

While executing this task via runtime APIs, it will create a flow table with integration table prefix (here is I$_). So if the target table name is T1, the integration table name will be I$_T1.

Data store prefix: is used to prefix the tables such as Journal tables.

View prefix: is used to prefix the views.

Trigger prefix:

is used to prefix the triggers. An important usage of trigger is capturing changes for a data object in Change Data Capture Code Template(CDC CTs). Take the predefined CDC CT JCT_ORACLE_SIMPLE for example, it has a task called "13_CREATE_TIGGER". When this task executes via runtime APIs, it will create a trigger prefixed with "T$_".


Click on the Mask tab of the location editor, the properties related to mask are displayed in the right panel.


These mask properties are used to tell the Code Templates how to structure the specific object basically at job runtime. For example, when we specify a local database object, the mask is %SCHEMA.%OBJECT in this case. At the job runtime, the placeholder %SCHEMA will be replaced by the value of the schema or work schema which we provide and the placeholder %OBJECT will be replaced by object actual name.

Here it provides default masks from corresponding platform for different types. These properties are editable, you can change them according to the rules of the data source system that the location is about.

5.Connection Pool

Click on the Connection Pool tab of the location editor, you can specify name/value pair properties for Connection Pool into spread table in the right panel.


Here we clear most of the advanced properties of the location. They really improve usability and flexibility to access to the various data sources.


Hello, I am trying to reverse XSD file using ODI Tool, there are few duplicate element which has to be reversed. Where in local mask I can avoid duplicate tables getting created.

Posted by Sangeeta Naidu on May 31, 2010 at 03:49 PM PDT #

Aw, this was a really quality post. In theory I'd like to write like this too - taking time and real effort to make a good article... but what can I say... I procrastinate alot and never seem to get something done

Posted by Kamagra on October 29, 2010 at 01:00 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.


« April 2014