29 Oracle Warehouse Builder Code Template Tools and Substitution Methods Reference

This chapter contains the following topics:

Code Template Tools Supported in Oracle Warehouse Builder

Oracle Warehouse Builder includes a subset of the ODI tools used to make code templates. For the full reference for all tools, see Oracle Data Integrator Tools Reference Manual for ODI 10.1.3.5 at:

http://www.oracle.com/technetwork/middleware/data-integrator/overview/index.html

The following tables list the tools supported in Oracle Warehouse Builder and the tools that were omitted.

Table 29-1 Code Template Tools Supported by Oracle Warehouse Builder

Code Template Description

OdiAnt

Calls the building tool Ant of the Apache Software Foundation. This tool helps run several commands such as archiving, compiling, deploying, documenting, EJBs. It runs Operating System processes, manipulates files, .NET tasks, remote tasks, and so on.

OdiBeep

Plays a default beep or sound file on the computer hosting the agent.

OdiOSCommand

Invokes an operating system command shell to perform a command and redirects the output to files.

OdiFtpGet

Retrieves a local file from a remote FTP server computer using the FTP protocol GET command.

OdiFtpPut

Copies a local file to a remote FTP server computer using the FTP protocol PUT command.

OdiInvokeWebService (Replaces OdiExecuteWebService)

Invokes a web service using the HTTP protocol and writes the returned response to an XML file.This tool invokes a specific operation on the port of a web service whose description file (WSDL) URL is provided.If this operation requires a SOAP request, it is provided either in a request file or directly written out in the tool call (XMLRequest). The response of the web service request is written to an XML file that can be used in Oracle Data Integrator.Note: This Tool replaces OdiExecuteWebService.Note: This Tool supports both HTTP and HTTPS protocols.

OdiReadMail

Can be used to read emails and their attachments from a POP or IMAP internet mail service.The use of this command enables the connection to the mail hosted on the server MAILHOST using the connection parameters specified by USER and PASS. The execution agent is then in waiting mode until MAX_MSG messages are received or the maximum waiting time specified by TIMEOUT is reached. The extracted messages must match the filters such as those specified by the parameters SUBJECT and SENDER. When a message satisfies these criteria, its content and its Internet 53 attachment are extracted in a directory specified by the parameter FOLDER. If the parameter KEEP is set to No, the retrieved message is then suppressed from the mailbox.

OdiScpGet

Retrieves a file from a remote SSH server.

OdiScpPut

Copies a local file to a directory on the remote SSH server computer.

OdiSendMail

Can send an email through an SMTP server.

OdiSftpGet

Retrieves a file from a remote SSH server with an enabled SFTP subsystem.

OdiSftpPut

Copies a local file to a remote SSH server computer that has the SFTP subsystem enabled.

OdiFileAppend

Concatenates a set of files into a single file.

The HEADER parameter is used to suppress n number of first lines of the input files (for instance, headers of csv files). The KEEP_FIRST_HEADER parameter is used to keep or remove the first occurrence of the header lines in the resulting file.When the HEADER parameter is omitted, the concatenation is binary (no text reading of the file is performed).

OdiFileCopy

Copies files or a folder into files or a folder on the computer of the execution agent.

OdiFileDelete

Deletes, based on the file modification time, files or a directory on the computer running the agent which runs it.If -FROMDATE is omitted, all files with a modification date earlier than the -TODATE date is deleted.If -TODATE is omitted, all files with a modification date later than the -FROMDATE date is deleted.If both parameters are omitted, all files matching the -FILE parameter value is deleted.

OdiFileMove

Moves files or a directory into files or a directory on the computer of the execution agent.This command can also be used to rename files or directories.

OdiFileWait

Scans the directory DIR and waits for files matching the mask given in PATTERN. When the specified files are found, the action indicated by the parameter ACTION is triggered.The execution agent is in standby mode waiting to receive FILECOUNT files unless the timeout limit, given by TIMEOUT is reached. The directory is scanned every POLLINT milliseconds.

OdiMkDir

Creates a directory.If the parent directory does not exist, this command recursively creates the parent directories.

OdiOutFile

Generates a text file on the computer where the execution agent is running.The file is stored in the path defined by <file_name> and contains the text specified by <Text>.The optional parameter -APPEND is used to append the <Text> after the file if it contains data. The parameters -FILE and -APPEND must be specified on the same line.

OdiSqlUnload

Generates a data file by executing the SQL query <sql_query> on the data server whose connection parameters are provided by <driver>, <url>, <user> and <encoded_pass>. The file is written to the path defined by <file_name> (relative to the agent).

OdiUnZip

Used to unzip an archive file to a directory.

OdiZip

Creates ZIP files from a directory or several files.

OdiXMLConcat

Extracts all instances of a given element from a set of source XML files and concatenates them into one target XML file.This tool parses and generates well-formed XML.It does not modify or generate a DTD for the generated files. A reference to an existing DTD can be specified in the HEADER parameter or preserved from the original files using the -KEEP_XML_PROLOGUE.

Note: XML Namespaces are not supported by this tool. You must provide the local part of element name (without namespace or prefix value) in the -ELEMENT_NAME parameter.

OdiXMLSplit

Extracts all instances of a given element stored in a source XML file and splits it over several target XML files.This tool parses and generates well-formed XML.It does not modify or generate a DTD for the generated files. A reference to an existing DTD can be specified in the HEADER parameter or preserved from the original files using the -KEEP_XML_PROLOGUE.

Note: XML Namespaces are not supported by this tool. Please provide the local part of element name (without namespace or prefix value) in the -ELEMENT_NAME parameter.

OdiFileWait

Scans the directory DIR and waits for files matching the mask given in PATTERN. When the specified files are found, the action indicated by the parameter ACTION is triggered.The execution agent is in standby mode waiting to receive FILECOUNT files unless the timeout limit, given by TIMEOUT is reached. The directory is scanned every POLLINT milliseconds

OdiReadMail

Read emails and their attachments from a POP or IMAP internet mail service.The use of this command enables the connection to the mail hosted on the server MAILHOST using the connection parameters specified by USER and PASS. The execution agent is then in waiting mode until MAX_MSG messages are received or the maximum waiting time specified by TIMEOUT is reached. The extracted messages must match the filters such as those specified by the parameters SUBJECT and SENDER. When a message satisfies these criteria, its content and its attachments are extracted in a directory specified by the parameter FOLDER. If the parameter KEEP is set to No, the retrieved message is then suppressed from the mailbox.

OdiSleep

Waits for <delay> milliseconds.


Code Template Tools Not Supported in Oracle Warehouse Builder

The following code template tools are not support in Oracle Warehouse Builder.

Table 29-2 Unsupported Code Template Tools

Unsupported Tools

OdiReverseGetMetaData

OdiReverseResetTable

OdiReverseSetMetaData

OdiDeleteScen

OdiExportAllScen

OdiExportEnvironmentInformation

OdiExportLog

OdiExportMaster

OdiExportObject

OdiExportScen

OdiExportWork

OdiGenerateAllScen

OdiImportObject

OdiImportScen

OdiDataQuality

OdiKillAgent

OdiPingAgent

OdiPurgeLog

OdiReinitializeSeq

OdiStartScen

OdiUpdateAgentSchedule

OdiSAPALEClient

OdiSAPALEServer

OdiWaitForChildSession

OdiWaitForData

OdiWaitForLogData

OdiWaitForTable

OdiRefreshJournalCount

OdiRetrieveJournalData

OdiWaitForData

OdiWaitForLogData

OdiWaitForTable


Code Template Substitution Methods Supported in Oracle Warehouse Builder

For the full reference for all tools, see Oracle Data Integrator Tools Reference Manual for ODI 10.1.3.5. Visit the ODI home page on Oracle Technology Network and follow the documentation link at:

http://www.oracle.com/technetwork/middleware/data-integrator/overview/index.html

Oracle Warehouse Builder supports the following substitution methods, listed with their supported parameters and values.

In the following tables, in the Supported column, Yes indicates that the parameter is supported in Oracle Warehouse Builder, and Yes indicates it is not used in Oracle Warehouse Builder.

getAK

Parameter Supported Values Supported Values Not Supported
pPropertyName Yes KEY_NAME FULL_NAME MESS ID flexfield_code

getAKColList

Parameter Supported Values Supported Values Not Supported
Parameter Supported Values Supported Values Not Supported
pStart Yes    
pPattern Yes COL_NAME POS LONGC SCALE FILE_POS BYTES FILE_END_POS CHECK_FLOW CHECK_STAT COL_FORMAT EXPRESSION CX_COL_NAME ALIAS_SEP SOURCE_DT SOURCE_CRE_DT SOURCE_WRI_DT DEST_CRE_DT DEST_WRI_DT DEST_DT I_COL COL_HEADING COL_DESC IND_WRITE COL_MANDATORY COL_DEC_SEP REC_CODE_LIST COL_NULL_IF_ERR DEF_VALUE SCD_COL_TYPE flexfield_code
pSeparator Yes    
pEnd Yes    

getCK

Parameter Supported Values Supported Values Not Supported
Parameter Supported Values Supported Values Not Supported
pPropertyName Yes COND_ALIAS COND_NAME COND_TYPE COND_SQL FULL_NAME COND_SQL_DDL MESS ID

getColList

Parameter Supported Values Supported Values Not Supported
Parameter Supported Values Supported Values Not Supported
pStart Yes    
pPattern Yes COL_NAME POS LONGC SCALE FILE_POS BYTES FILE_END_POS CHECK_FLOW CHECK_STAT COL_FORMAT EXPRESSION CX_COL_NAME ALIAS_SEP SOURCE_DT SOURCE_CRE_DT SOURCE_WRI_DT DEST_DT DEST_CRE_DT DEST_WRI_DT JDBC_TYPE I_COL COL_HEADING COL_DESC IND_WRITE COL_MANDATORY COL_DEC_SEP REC_CODE_LIST COL_NULL_IF_ERR DEF_VALUE SCD_COL_TYPE MANDATORY_CLAUSE DEFAULT_CLAUSE flexfield_code
pSeparator Yes    
pEnd Yes    
pSelector Yes INS UPD TRG NULL PK UK REW UD1 UD2 UD3 UD4 UD5 MAP SCD_SK SDK_NK SCD_UPD SCD_INS SCD_FLAG SCD_START SCD_END NEW OLD WS_INS WS_UPD WS_SEL

getDataType

Parameter Supported Values Supported Values Not Supported
pDataTypeName Yes SRC_VARCHAR SRC_NUMERIC SRC_DATE DEST_VARCHAR DEST_NUMERIC DEST_DATE  
pDataTypeLength Yes    
pDataTypePrecision Yes    

getFK

Parameter Supported Values Supported Values Not Supported
pPropertyName Yes FK_NAME FK_TYPE FK_ALIAS PK_ALIAS PK_CATALOG PK_SCHEMA PK_TABLE_NAME FULL_NAME MESS ID ID_TABLE_PK COMPLEX_SQL PK_I_MOD flexfield_code

getFKColList

Parameter Supported Values Supported Values Not Supported
pStart Yes    
pPattern Yes COL_NAME POS LONGC SCALE FILE_POS BYTES FILE_END_POS CHECK_FLOW CHECK_STAT COL_FORMAT EXPRESSION CX_COL_NAME ALIAS_SEP SOURCE_DT SOURCE_CRE_DT SOURCE_WRI_DT DEST_CRE_DT DEST_WRI_DT DEST_DT PK_COL_NAME PK_COL_HEADING PK_COL_DESC PK_LONGC PK_SCALE PK_FILE_POS PK_BYTES PK_FILE_END_POS PK_CHECK_FLOW PK_CHECK_STAT PK_COL_FORMAT I_COL COL_HEADING COL_DESC IND_WRITE COL_MANDATORY COL_DEC_SEP REC_CODE_LIST COL_NULL_IF_ERR DEF_VALUE PK_I_COL PK_POS PK_IND_WRITE PK_COL_MANDATORY PK_COL_DEC_SEP PK_REC_CODE_LIST PK_COL_NULL_IF_ERR PK_DEF_VALUE SCD_COL_TYPE flexfield_code
pSeparator Yes    
pEnd Yes    

getFilter

Parameter Supported Values Supported Values Not Supported
no_params Yes    

getFilterList

Parameter Supported Values Supported Values Not Supported
pStart Yes    
pPattern Yes EXPRESSION ID
pSeparator Yes    
pEnd Yes    

getFrom

Parameter Supported Values Supported Values Not Supported
no_params Yes    

getGrpBy

Parameter Supported Values Supported Values Not Supported
no_params Yes    

getGrpByList

Parameter Supported Values Supported Values Not Supported
pStart Yes    
pPattern Yes EXPRESSION ID
pSeparator Yes    
pEnd Yes    

getHaving

Parameter Supported Values Supported Values Not Supported
no_params Yes    

getHavingList

Parameter Supported Values Supported Values Not Supported
pStart Yes    
pPattern Yes EXPRESSION ID
pSeparator Yes    
pEnd Yes    

getInfo

Parameter Supported Values Supported Values Not Supported
pPropertyName Yes COLL_NAME INT_NAME ERR_NAME TARG_NAME SRC_CATALOG SRC_SCHEMA SRC_WORK_CATALOG SRC_WORK_SCHEMA DEST_CATALOG DEST_SCHEMA DEST_WORK_CATALOG DEST_WORK_SCHEMA SRC_DSERV_NAME SRC_JAVA_DRIVER SRC_JAVA_URL SRC_USER_NAME SRC_ENCODED_PASS SRC_TAB_ALIAS_WORD SRC_DATE_FCT SRC_DDL_NULL SRC_MAX_COL_NAME_LEN SRC_MAX_TAB_NAME_LEN DEST_DSERV_NAME DEST_JAVA_DRIVER DEST_JAVA_URL DEST_USER_NAME DEST_ENCODED_PASS DEST_COL_ALIAS_WORD DEST_TAB_ALIAS_WORD DEST_DATE_FCT DEST_DDL_NULL DEST_MAX_COL_NAME_LEN DEST_MAX_TAB_NAME_LEN CT_ERR_TYPE CT_ORIGIN JRN_NAME JRN_VIEW JRN_DATA_VIEW JRN_TRIGGER JRN_ITRIGGER JRN_UTRIGGER JRN_DTRIGGER SUBSCRIBER_TABLE CDC_SET_TABLE CDC_TABLE_TABLE CDC_SUBS_TABLE CDC_OBJECTS_TABLE  

getJDBCConnection

Parameter Supported Values Supported Values Not Supported
pPropertyName Yes SRC DEST  

getJoin

Parameter Supported Values Supported Values Not Supported
no_params Yes    

getJoinList

Parameter Supported Values Supported Values Not Supported
pStart Yes    
pPattern Yes EXPRESSION ID
pSeparator Yes    
pEnd Yes    

getJrnInfo

Parameter Supported Values Supported Values Not Supported
pPropertyName Yes FULL_TABLE_NAME JRN_FULL_NAME JRN_FULL_VIEW JRN_FULL_DATA_VIEW JRN_FULL_TRIGGER JRN_FULL_ITRIGGER JRN_FULL_UTRIGGER JRN_FULL_DTRIGGER JRN_SUBSCRIBER JRN_NAME JRN_VIEW JRN_DATA_VIEW JRN_TRIGGER JRN_ITRIGGER JRN_UTRIGGER JRN_DTRIGGER JRN_SUBSCRIBER JRN_COD_MODE JRN_METHOD CDC_SET_TABLE CDC_TABLE_TABLE CDC_SUBS_TABLE CDC_OBJECTS_TABLE  

getJrnFilter

No parameters.

getNotNullCol

Parameter Supported Values Supported Values Not Supported
pPropertyName Yes COL_NAME MESS ID flexfield_code

getObjectName

Parameter Supported Values Supported Values Not Supported
pMode Yes L R  
pObjectName Yes    
pLogicalSchemaName No    
pContextName No    
pLocation Yes W D  

getObjectNameDefaultPSchema

Parameter Supported Values Supported Values Not Supported
pPropertyName      

getOption

Parameter Supported Values Supported Values Not Supported
pPropertyName Yes    

getPK

Parameter Supported Values Supported Values Not Supported
pPropertyName Yes KEY_NAME FULL_NAME MESS ID flexfield_code

getPKColList

Parameter Supported Values Supported Values Not Supported
pStart Yes    
pPattern Yes COL_NAME POS LONGC SCALE FILE_POS BYTES FILE_END_POS CHECK_FLOW CHECK_STAT COL_FORMAT EXPRESSION CX_COL_NAME ALIAS_SEP SOURCE_DT SOURCE_CRE_DT SOURCE_WRI_DT DEST_CRE_DT DEST_WRI_DT DEST_DT PK_COL_NAME PK_COL_HEADING PK_COL_DESC PK_LONGC PK_SCALE PK_FILE_POS PK_BYTES PK_FILE_END_POS PK_CHECK_FLOW PK_CHECK_STAT PK_COL_FORMAT I_COL COL_HEADING COL_DESC IND_WRITE COL_MANDATORY COL_DEC_SEP REC_CODE_LIST COL_NULL_IF_ERR DEF_VALUE SCD_COL_TYPE flexfield_code
pSeparator Yes    
pEnd Yes    

getPop

Parameter Supported Values Supported Values Not Supported
pPropertyName Yes POP_NAME TABLE_NAME KEY_NAME DISTINCT_ROWS MAX_ERR MAX_ERR_PRCT IKM LKM CKM HAS_JRN I_POP FOLDER IND_WORK_TARG LSCHEMA_NAME DESCRIPTION WSTAGE OPT_CTX TARG_CTX flexfield_code

GetSchemaName

Parameter Supported Values Supported Values Not Supported
pLogicalSchemaName      
pContextCode      
pLocation      

getSchemaNameDefaultPSchema

Parameter Supported Values Supported Values Not Supported
pLogicalSchemaName      
pContextCode      
pLocation      

getSrcTablesList

Parameter Supported Values Supported Values Not Supported
pStart Yes    
pPattern Yes TABLE_NAME RES_NAME CATALOG WORK_CATALOG SCHEMA WORK_SCHEMA TABLE_ALIAS FILE_FORMAT FILE_SEP_FIELD SFILE_SEP_FIELD FILE_ENC_FIELD FILE_SEP_ROW SFILE_SEP_ROW FILE_FIRST_ROW IND_JRN JRN_ORDER I_TABLE MODEL_NAME SUB_MODEL_NAME TECHNO_NAME LSCHEMA_NAME POP_TAB_ALIAS TABLE_TYPE DESCRIPTION R_COUNT XFILE_SEP_FIELD XFILE_SEP_ROW FILE_DEC_SEP METADATA OLAP_TYPE flexfield_code
pSeparator Yes    
pEnd Yes    

getSrcColList

Parameter Supported Values Supported Values Not Supported
pStart      
pUnMappedPattern      
pMappedPattern      
pSeparator      
pEnd      

getSubscriberList

Parameter Supported Values Supported Values Not Supported
pStart Yes    
pPattern Yes SUBSCRIBER  
pSeparator Yes    
pEnd Yes    

getTable

Parameter Supported Values Supported Values Not Supported
pMode Yes L R A  
pProperty Yes TARG_NAME COLL_NAME INT_NAME ERR_NAME CHECK_NAME CT_NAME FK_PK_TABLE_NAME JRN_NAME JRN_VIEW JRN_DATA_VIEW JRN_TRIGGER JRN_ITRIGGER JRN_UTRIGGER JRN_DTRIGGER SUBSCRIBER_TABLE CDC_SET_TABLE CDC_TABLE_TABLE CDC_SUBS_TABLE CDC_OBJECTS_TABLE ID flexfield_code
pLocation Yes W D A  

getTargetColList

Parameter Supported Values Supported Values Not Supported
pStart Yes    
pPattern Yes COL_NAME POS LONGC SCALE FILE_POS BYTES FILE_END_POS CHECK_FLOW CHECK_STAT COL_FORMAT ALIAS_SEP SOURCE_DT SOURCE_CRE_DT SOURCE_WRI_DT DEST_DT DEST_CRE_DT DEST_WRI_DT JDBC_TYPE I_COL COL_HEADING COL_DESC IND_WRITE COL_MANDATORY COL_DEC_SEP REC_CODE_LIST COL_NULL_IF_ERR DEF_VALUE SCD_COL_TYPE MANDATORY_CLAUSE DEFAULT_CLAUSE COL_DESC flexfield_code
pSeparator Yes    
pEnd Yes    
pSelector Yes    

getTargetTable

Parameter Supported Values Supported Values Not Supported
pPropertyName Yes TECHNO_NAME TABLE_NAME RES_NAME CATALOG WORK_CATALOG SCHEMA WORK_SCHEMA TABLE_ALIAS FILE_FORMAT FILE_SEP_FIELD SFILE_SEP_FIELD FILE_ENC_FIELD FILE_SEP_ROW SFILE_SEP_ROW FILE_FIRST_ROW IND_JRN JRN_ORDER MODEL_NAME SUB_MODEL_NAME LSCHEMA_NAME TABLE_TYPE DESCRIPTION TABLE_DESC R_COUNT FILE_DEC_SEP XFILE_SEP_FIELD XFILE_SEP_ROW METADATA_DESC OLAP_TYPE TABLE_DESC WS_NAME WS_NAMESPACE WS_JAVA_PACKAGE WS_ENTITY_NAME WS_DATA_SOURCE flexfield_code

getUserExit

Parameter Supported Values Supported Values Not Supported
pOptionName      

Code Template Substitution Methods Not Supported in Oracle Warehouse Builder

This section lists code template substitution methods not supported in Oracle Warehouse Builder.

  • getCatalogName

  • getCatalogNameDefaultPSchema

  • getColDefaultValue

  • getColumn

  • getContext

  • getFlexFieldValue

  • getIndex

  • getIndexColList

  • getJRNFilter

  • getModel

  • getNewColComment

  • getNewTableComment

  • getPrevStepLog

  • getSession

  • getSessionVarList

  • getStep

  • getSysDate

  • hasPK

  • isColAttrChanged

  • nextAK

  • nextCond

  • nextFK

  • setNbDelete

  • setNbErrors

  • setNbInsert

  • setNbRows

  • setNbUpdate