Subject: Behavior Of ODI odiRef.getObjectName() Method

<%=odiRef.getInfo("I_SRC_SET")%>
<%=snpRef.getInfo("SRC_DSERV_NAME")%>.
 "-DRIVER=<%=odiRef.getInfo("SRC_DRIVER")%>"
 "-URL=<%=odiRef.getInfo("SRC_JAVA_URL")%>"
 "-USER=<%=odiRef.getInfo("SRC_USER_NAME")%>"
 "-PASS=<%=snpRef.getInfo("SRC_ENCODED_PASS")%>"
snpRef.getJDBCConnection("SRC")

Subject:	Behavior Of ODI odiRef.getObjectName() Method
	Doc ID:	602388.1	Type:	TROUBLESHOOTING
	Modified Date :	20-FEB-2009	Status:	PUBLISHED

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details
     Getting Started
     odiRef.getObjectName() Substitution Method
     Behaviour of odiRef.getObjectName() with the pMode set to "L":
     Behavior of odiRef.getObjectName() with the pMode parameter set to "R":
     The purpose of setting the pMode to "R" is to return a remote name
  References

---

Applies to:

Oracle Data Integrator - Version: 3.2.03.01
Information in this document applies to any platform.

Purpose

This document is provided to assist in understanding the behavior and usage of Oracle Data Integrator (ODI) odiRef.getObjectName() substitution method.

 

odiRef.getObjectName() and snpRef.getObjectName() substitution methods are equivalent; snpRef.getObjectName() being used in former Sunopsis.

Last Review Date

August 26, 2008

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

Getting Started

To understand how odiRef.getObjectName() works, users need to be familiar with these terms in Oracle Data Integrator (ODI):

Instance/dblink (Data Server),
Schema,
Work Schema,
Default Physical Schema,
Local Object Mask and
Remote Object Mask.

Let's look at an example Data Server set up for Oracle Technology:

Data Server: ORACLE_CRM_DEV.

This Data Server establishes connection to Oracle Instance CRM_DEV_HOST (sid).

1. This Data Server has two Physical Schema:
   • MAIN
   • SALES
     
2. Physcal Schema MAIN is defined with:
   • Schema: MAIN (Database schema that contains the data.)
   • Work Schema: STAG (All the temporary tables from ODI Integration Interface flows will be created here.)
   • It's Default check box is checked which means it is the Default Physical Schema of this Data Server.
   • The Context/Logical Schema pair it is associated with is Global/ORACLE_CRM_MAIN.
     (Its not shown in the image but the Context/Logical Schema pair Physical Schema SALES is associated with is Global/ORACLE_CRM_SALES).
     
     
3. The Local Object Mask is: %SCHEMA.%OBJECT
4. The Remote Object mask is: %SCHEMA.%OBJECT@DSERVER
   The DSERVER refers to the Data Server's Instance/dblink (Data Server) name, in our case CRM_DEV_HOST.
   
   
   Note that the Object Masks vary accroding to the Technology.
   
   For example, Microsoft SQL Server's:
   - Local Object Mask is %CATALOG.%SCHEMA.%OBJECT
   - Remote Object Mask is %DSERVER.%CATALOG.%SCHEMA.%OBJECT

odiRef.getObjectName() Substitution Method

It returns the complete name of a physical object, including its Catalog and Schema.

<%=odiRef.getObjectName("pMode", "pObjectName", "pLogicalSchemaName", "pContextName", "pLocation")%>

Most of its parameters are self explanatory or please see ODI documentation > Substitution Methods Reference > Global Methods> getObjectName.

The pMode parameter indicates the Object Mask to use. Its value can be either "L" or "R":

• "L" use the Local Object Mask to build the complete path of the object.
• "R" use the Remote Object Mask to build the complete path of the object.
  Note: When using the Remote Object Mask, getObjectName always resolved the object name using the default Physical Schema of the remote server.

The pLocation parameter indicates the Schema will be used to prefix the object name returned. Its value can be either "W" or "D":

• "W" prefix the object name with the Work Schema of the Physical Schema the pLogicalSchemaName/pContextName pair is associated with.
• "D" prefix the object name with the Schema name of the Physical Schema the pLogicalSchemaName/pContextName pair is associated with.

Behaviour of odiRef.getObjectName() with the pMode set to "L":

A call to	Returns
<%=odiRef.getObjectName( "L","SRC_CUSTOMERS", "ORACLE_CRM_MAIN", "GLOBAL","D" )%>	MAIN.SRC_CUSTOMERS
<%=odiRef.getObjectName( "L", "SRC_CUSTOMERS" , "ORACLE_CRM_MAIN","GLOBAL","W" )%>	STAG.SRC_CUSTOMERS

In the above 2 calls:

• The pMode is set to L, so the Local Object Mask %SCHEMA.%OBJECT is used.
• The pLogicalSchemaName/pContextName (or Execution Context if pContextName is not set) pair ORACLE_CRM_MAIN/GLOBAL points at Physical Schema MAIN, so the Object name returned is MAIN.SRC_CUSTOMERS when the pLocation is "D", STAG.SRC_CUSTOMERS when the pLocation is "W".

Behavior of odiRef.getObjectName() with the pMode parameter set to "R":

A call to	 Returns
<%=odiRef.getObjectName( "R" , "SRC_CUSTOMERS", "ORACLE_CRM_SALES", "GLOBAL","D" )%>	MAIN.SRC_CUSTOMERS@CRM_DEV_HOST
<%=odiRef.getObjectName("R","SRC_CUSTOMERS", "ORACLE_CRM_SALES" , "GLOBAL", "W" )%>	STAG.SRC_CUSTOMERS@CRM_DEV_HOST

In these 2 calls:

• The pMode is set to R, so the Remote Object Mask %SCHEMA.%OBJECT@%DSERVER is
  used. @DSERVER resolved into @CRM_DEV_HOST because CRM_DEV_HOST is the name of our Instance/dblink (Data Server).
• The pLogicalSchemaName/pContextName (or Execution Context) pair ORACLE_CRM_SALES/Global points at Physical Schema SALES.
• Since the pMode is set to R, the Default Physical Schema of the Data Server of Physical Schema SALES, MAIN (when the pLocation is "D") is returned and Default Physical Schema MAIN's Work Schema STAG is returned (when the pLocation is "W").

The purpose of setting the pMode to "R" is to return a remote name

A pMode set to "R" can return very different results depending on the parameters used.

In any case, it will always return the Default Physical Schema of the identified Dataserver.

Case 1

• The getObjectName substitution method always returns the Default Physical Schema set on the Data Server of the Physical Schema that the pLogicalSchemaName/pContextName pair corresponds to.
• If the pLogicalSchemaName is not set, ODI (Sunopsis) will use the one set on the Procedure command (set in the drop down list parameter of the Procedure).
• If the pContextName is not set, ODI (Sunopsis) will use the one of the execution.
  

Case 2

• There are some cases where the Logical Schema is neither set in the command's drop down list, or in the substitution method itself:
• If the getObjectName is used on the Command On Source tab, it will retrieve information based on the Logical Schema of the Command On Target tab. Therefore it returns the Default Physical Schema set on the Dataserver that corresponds to the Command On Target tab Logical Schema associated with the execution Context.
• If the getObjectName is used on the Command On Target tab, it will retrieve information based on the Logical Schema of the Command On Source tab. Therefore it returns the Default Physical Schema set on the Dataserver that corresponds to the Command On Source tab Logical Schema associated with the execution Context.

@Archived Note 424176.1 as it is turned into a troubleshooting guide in Note 602388.1

References

Note 424059.1 - Error Messages Signaled On the "Create work table " Step Of An ODI Integration Interface.
Note 424208.1 - 'Incorrect syntax near 'getObjectNameDefaultPSchema..' Signaled When Registering A New Subscriber For Journalized ODI Datastores
Note 754267.1 - Unable to Start Change Data Capture Journal "The requested operation cannot succeed. The object no longer exists"
Note 424051.1 - '...Exception getObjectNameDefaultPSchema...' Message When Using LKM Oracle DBLINK With ODI (Sunopsis)

Keywords

DBLINK ; ODI ; SUNOPSIS ; SUBSTITUTION~METHODS ; REMOTE~SERVER ; ORACLE~DATA~INTEGRATOR ; SQL~SERVER ; SUBSTITUTION~METHODS ; 

---

Help us improve our service. Please email us your comments for this document. .