Release 9.2
Part Number B12001-01
 Contents |
 Index |
| Oracle9i Warehouse Builder Transformation Guide Release 9.2 Part Number B12001-01 |
|
This chapter contains the following topics:
For related information, see:
The following sections describe the transformation libraries and introduce how to use custom transformations in Warehouse Builder.
Warehouse Builder supports the following transformation types:
Each time you create a warehouse module, Warehouse Builder creates a Transformation Library for that module containing transformation operations. This library contains the standard Oracle Library and an additional library for each warehouse module defined within the repository.
Transformation Libraries consist of the following types:
When you create a custom transformation, add it to the Global Shared Library to share across warehouse modules. If the transformation is specific to one module, add it to the transformation library within that module.
The Global Shared Library stores transformations that are shared across a repository. The default categories are:
The Oracle Library includes a set of standard transformations organized into categories including:
You can access the Transformation Libraries from the Expression Builder, the Add Transformation dialog, or the New Transformation Wizard. You can also access Transformation Libraries from the navigation tree in the Warehouse Builder console. Additionally, you can create your own transformation libraries to organize transformations according to your needs.
Use the Import Wizard to import PL/SQL functions, procedures, and packages into a Warehouse Builder project.
When you use the imported PL/SQL:
Use the transformation properties sheet to edit a transformation. Be sure to edit properties consistently. For example, if you change the name of a parameter, then you must change its name in the implementation code. You can view transformation properties from the Mapping Editor using the Operator Property sheet. These settings are read-only.
Administration transformations, or functions, are actions that are regularly performed in ETL processes. The main focus of these transformations is in the DBA related areas or to improve performance. For example, it is common to disable constraints when loading tables and then to re-enable them after loading has completed. Warehouse Builder provides pre-built functionality for this purpose in the administration transformations.
The administration functions in Warehouse Builder are all custom functions and are listed in alphabetical order.
WB_ABORT(p_code,p_message)
where p_code is the abort code, and must be between -20000 and -29999; and p_message is an abort message you specify.
WB_ABORT enables you to abort the application from a Warehouse Builder component. You can run it from a post mapping process or as a transformation within a mapping.
Use this administration function to abort an application. You can use this function in a post mapping process to abort deployment if there is an error in the mapping.
WB_ANALYZE_SCHEMA
No parameters are required for this function.
After loading data into the warehouse, the statistics need to be refreshed to ensure optimal performance recommendations from the cost-based optimizer in the warehouse. WB_ANALYZE_SCHEMA calls DBMS_DDL.ANALYZE_OBJECT to analyze a schema to supply these statistics. It analyzes the entire schema, which may take some time depending on the number of tables and the number of rows in these tables.
You can use this administration package to automatically run the analyze command on the schema you loaded. This can be done using the post mapping process of the last mapping in a dependency diagram. You can also deploy the procedure to the database schema and invoke the procedure from OEM using a SQL statement created in the client tool.
WB_ANALYZE_TABLE(p_name)
where p_name is the table on which the analyze command is executed.
After loading data into the warehouse the statistics need to be refreshed to ensure optimal performance recommendations from the cost-based optimizer in the warehouse. WB_ANALYZE_TABLE calls DBMS_DDL.ANALYZE_OBJECT to analyze a specific table in the user schema and supply these statistics. It analyzes the table, which may take some time depending on the number of rows in this table.
You can use this administration package to automatically run the analyze command on the table you have loaded. This can be done using a post mapping process of the mapping that loads data into the table.
WB_COMPILE_PLSQL(p_name,p_type)
where p_name is the name of the object that is to be compiled; p_type is the type of object to be compiled. The legal types are:
'PACKAGE' 'PACKAGE BODY' 'PROCEDURE' 'FUNCTION' 'TRIGGER'
This program unit compiles a stored object in the database.
WB_DISABLE_ALL_CONSTRAINTS(p_name)
where p_name is the table name that determines which constraints are disabled.
This program unit disables all constraints that are owned by the table as stated in the call to the program.
For faster loading of data sets, you can disable constraints on a table. The data is now loaded without validation. This is mainly done on relatively clean data sets.
The following example shows the disabling of the constraints on the table OE.CUSTOMERS:
select constraint_name ,decode(constraint_type , 'C', 'Check' , 'P', 'Primary' ) Type , status from user_constraints where table_name = 'CUSTOMERS'; 5 rows selected
CONSTRAINT_NAME TYPE STATUS ------------------------------ ------- -------- CUST_FNAME_NN Check ENABLED CUST_LNAME_NN Check ENABLED CUSTOMER_CREDIT_LIMIT_MAX Check ENABLED CUSTOMER_ID_MIN Check ENABLED CUSTOMERS_PK Primary ENABLED
Perform the following in Scalpels or Warehouse Builder to disable all constraints:
Execute WB_DISABLE_ALL_CONSTRAINTS('CUSTOMERS'); CONSTRAINT_NAME TYPE STATUS ------------------------------ ------- -------- CUST_FNAME_NN Check DISABLED CUST_LNAME_NN Check DISABLED CUSTOMER_CREDIT_LIMIT_MAX Check DISABLED CUSTOMER_ID_MIN Check DISABLED CUSTOMERS_PK Primary DISABLED5 rows selected
WB_DISABLE_ALL_TRIGGERS(p_name)
where p_name is the table name on which the triggers are disabled.
This program unit disables all triggers owned by the table as stated in the call to the program. The owner of the table must be the current user (in variable USER). This action stops triggers and improves performance.
The following example shows the disabling of all triggers on the table OE.OC_ORDERS:
Available triggers:
select trigger_name , status from user_triggers where table_name = 'OC_ORDERS'; TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG ENABLED ORDERS_ITEMS_TRG ENABLED
Perform the following in Scalpels or Warehouse Builder to disable the specified constraint.
Execute WB_DISABLE_ALL_TRIGGERS ('OC_ORDERS');TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG DISABLED ORDERS_ITEMS_TRG DISABLED
WB_DISABLE_CONSTRAINT(p_constraintname,p_tablename)
where p_constraintname is the constraint name to be disabled; p_tablename is the table name on which the specified constraint is disabled.
This program unit disables the specified constraint that is owned by the table as stated in the call to the program. The user is the current user (in variable USER).
For faster loading of data sets, you can disable constraints on a table. The data is then loaded without validation. This reduces overhead and is mainly done on relatively clean data sets.
The following example shows the disabling of the specified constraint on the table OE.CUSTOMERS:
select constraint_name , decode(constraint_type , 'C', 'Check' , 'P', 'Primary' ) Type , status from user_constraints where table_name = 'CUSTOMERS'; 5 rows selected CONSTRAINT_NAME TYPE STATUS ------------------------------ ------- -------- CUST_FNAME_NN Check ENABLED CUST_LNAME_NN Check ENABLED CUSTOMER_CREDIT_LIMIT_MAX Check ENABLED CUSTOMER_ID_MIN Check ENABLED CUSTOMERS_PK Primary ENABLED
Perform the following in SQL*Plus or Warehouse Builder to disable the specified constraint.
Execute WB_DISABLE_CONSTRAINT('CUSTOMERS_PK','CUSTOMERS'); CONSTRAINT_NAME TYPE STATUS ------------------------------ ------- -------- CUST_FNAME_NN Check ENABLED CUST_LNAME_NN Check ENABLED CUSTOMER_CREDIT_LIMIT_MAX Check ENABLED CUSTOMER_ID_MIN Check ENABLED CUSTOMERS_PK Primary ENABLED 5 rows selected
WB_DISABLE_TRIGGER(p_name)
where p_name is the trigger name to be disabled.
This program unit disables the specified trigger. The owner of the trigger must be the current user (in variable USER).
The following example shows the disabling of a trigger on the table OE.OC_ORDERS:
select trigger_name, status from user_triggers where table_name = 'OC_ORDERS'; TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG ENABLED ORDERS_ITEMS_TRG ENABLED
Perform the following in SQL*Plus or Warehouse Builder to disable the specified constraint.
Execute WB_DISABLE_TRIGGER ('ORDERS_TRG'); TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG DISABLED ORDERS_ITEMS_TRG DISABLED
WB_ENABLE_ALL_CONSTRAINTS(p_name)
where p_name is the table name which determines which constraints are disabled.
This program unit enables all constraints that are owned by the table as stated in the call to the program.
For faster loading of data sets, you can disable constraints on a table. After the data is loaded, you must enable these constraints again using this program unit.
The following example shows the disabling of the constraints on the table OE.CUSTOMERS:
select constraint_name , decode(constraint_type
, 'C', 'Check' , 'P', 'Primary) Type , status
from user_constraints where table_name = 'CUSTOMERS'; CONSTRAINT_NAME TYPE STATUS ------------------------------ ------- -------- CUST_FNAME_NN Check DISABLED CUST_LNAME_NN Check DISABLED CUSTOMER_CREDIT_LIMIT_MAX Check DISABLED CUSTOMER_ID_MIN Check DISABLED CUSTOMERS_PK Primary DISABLED 5 rows selected
Perform the following in SQL*Plus or Warehouse Builder to enable all constraints.
Execute WB_ENABLE_ALL_CONSTRAINTS('CUSTOMERS'); CONSTRAINT_NAME TYPE STATUS ------------------------------ ------- -------- CUST_FNAME_NN Check ENABLED CUST_LNAME_NN Check ENABLED CUSTOMER_CREDIT_LIMIT_MAX Check ENABLED CUSTOMER_ID_MIN Check ENABLED CUSTOMERS_PK Primary ENABLED 5 rows selected
WB_ENABLE_ALL_TRIGGERS(p_name)
where p_name is the table name on which the triggers are enabled
This program unit enables all triggers owned by the table as stated in the call to the program. The owner of the table must be the current user (in variable USER).
The following example shows the enabling of all triggers on the table OE.OC_ORDERS:
select trigger_name , status from user_triggers where table_name = 'OC_ORDERS'; TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG DISABLED ORDERS_ITEMS_TRG DISABLED
Perform the following in SQL*Plus or Warehouse Builder to enable the specified constraint.
Execute WB_ENABLE_ALL_TRIGGERS ('OC_ORDERS'); TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG ENABLED ORDERS_ITEMS_TRG ENABLED
WB_ENABLE_CONSTRAINT(p_constraintname,p_tablename)
where p_constraintname is the constraint name to be disabled and p_tablename is the table name on which the specified constraint is disabled
This program unit disables the specified constraint that is owned by the table as stated in the call to the program. The user is the current user (in variable USER). For faster loading of data sets, you can disable constraints on a table. After the loading is complete, you must re-enable these constraints. This program unit allows you to enable the constraints one at a time.
The following example shows the enabling of the specified constraint on the table OE.CUSTOMERS:
select constraint_name , decode(constraint_type , 'C', 'Check' , 'P', 'Primary' ) Type , status from user_constraints where table_name = 'CUSTOMERS'; CONSTRAINT_NAME TYPE STATUS ------------------------------ ------- -------- CUST_FNAME_NN Check DISABLED CUST_LNAME_NN Check DISABLED CUSTOMER_CREDIT_LIMIT_MAX Check DISABLED CUSTOMER_ID_MIN Check DISABLED CUSTOMERS_PK Primary DISABLED 5 rows selected
Perform the following in SQL*Plus or Warehouse Builder to enable the specified constraint.
Execute WB_ENABLE_CONSTRAINT('CUSTOMERS_PK','CUSTOMERS'); CONSTRAINT_NAME TYPE STATUS ------------------------------ ------- -------- CUST_FNAME_NN Check DISABLED CUST_LNAME_NN Check DISABLED CUSTOMER_CREDIT_LIMIT_MAX Check DISABLED CUSTOMER_ID_MIN Check DISABLED CUSTOMERS_PK Primary ENABLED
5 rows selected
WB_ENABLE_TRIGGER(p_name)
where p_name is the trigger name to be enabled.
This program unit enables the specified trigger. The owner of the trigger must be the current user (in variable USER).
The following example shows the enabling of a trigger on the table OE.OC_ORDERS:
select trigger_name , status from user_triggers where table_name = 'OC_ORDERS'; TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG DISABLED ORDERS_ITEMS_TRG ENABLED
Perform the following in SQL*Plus or Warehouse Builder to enable the specified constraint.
Execute WB_ENABLE_TRIGGER ('ORDERS_TRG'); TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG ENABLED ORDERS_ITEMS_TRG ENABLED
WB_TRUNCATE_TABLE(p_name)
where p_name is the table name to be truncated.
This program unit truncates the table specified in the command call. The owner of the trigger must be the current user (in variable USER). The command disables and re-enables all referencing constraints to enable the truncate table command. Use this command in a pre-mapping process to explicitly truncate a staging table and ensure that all data in this staging table is newly loaded data.
The following example shows the truncation of the table OE.OC_ORDERS:
select count(*) from oc_orders; COUNT(*) ---------- 105
Perform the following in SQL*Plus or Warehouse Builder to enable the specified constraint.
Execute WB_TRUNCATE_TABLE ('OC_ORDERS'); COUNT(*) ---------- 0
Character transformations enable Warehouse Builder users to perform transformations on Character objects. These transformations are ordered alphabetically. The custom functions provided with Warehouse Builder are prefixed with WB_.
The following character transformations are available in Warehouse Builder.
ascii::=ASCII(attribute)
ASCII returns the decimal representation in the database character set of the first character of attribute. An attribute can be of data type CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The value returned is of data type NUMBER. If your database character set is 7-bit ASCII, this function returns an ASCII value. If your database character set is EBCDIC Code, this function returns an EBCDIC value. There is no corresponding EBCDIC character function.
The following example returns the ASCII decimal equivalent of the letter Q:
SELECT ASCII('Q') FROM DUAL; ASCII('Q') ---------- 81
asciistr::=ASCIISTR(attribute)
ASCIISTR uses a string in any character set as its argument and returns an ASCII string in the database character set. The value returned contains only characters that appear in SQL and a forward slash (/).
The following example returns the ASCII string equivalent of the text string "flauwekul":
SELECT ASCIISTR('flauwekul') FROM DUAL; ASCIISTR('FLAUW --------------- \6<65\756<\6700
chartorowid::=CHARTOROWID(attribute)
CHARTOROWID converts a value from CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to ROWID data type.
The following example converts a character rowid representation to a rowid. The function returns a different rowid on different databases.
SELECT last_name FROM employees WHERE ROWID = CHARTOROWID('AAAFYmAAFAAAAFEAAP'); LAST_NAME ------------------------- Greene
chr::=CHR(attribute)
CHR returns the character with the binary equivalent to the number specified in the attribute in either the database character set or the national character set.
If USING NCHAR_CS is not specified, this function returns the character with the binary equivalent to attribute as a VARCHAR2 value in the database character set. If USING NCHAR_CS is specified in the expression builder, this function returns the character with the binary equivalent to attribute as a NVARCHAR2 value in the national character set.
The following example is run on an ASCII-based machine with the database character set defined as WE8ISO8859P1:
SELECT CHR(67)||CHR(65)||CHR(84) "Dog" FROM DUAL; Dog --- CAT
To produce the same results on an EBCDIC-based machine with the WE8EBCDIC1047 character set, modify the above example as follows:
SELECT CHR(195)||CHR(193)||CHR(227) "Dog" FROM DUAL; Dog --- CAT
The example below uses the UTF8 character set:
SELECT CHR (50052 USING NCHAR_CS) FROM DUAL; CH -- Ä
concat::=CONCAT(attribute1, attribute2)
CONCAT returns attribute1 concatenated with attribute2. Both attribute1 and attribute2 can be CHAR or VARCHAR2 data types. The returned string is of VARCHAR2 data type contained in the same character set as attribute1. This function is equivalent to the concatenation operator (||).
This example uses nesting to concatenate three character strings:
SELECT CONCAT(CONCAT(last_name, '''s job category is '), job_id) "Job" FROM employees WHERE employee_id = 152; Job ------------------------------------------------------ Hall's job category is SA_REP
convert::=CONVERT(attribute,dest_char_set,source_char_set)
CONVERT converts a character string specified in an operator attribute from one character set to another. The data type of the returned value is VARCHAR2.
attribute1 argument is the value to be converted. It can of the data types CHAR and VARCHAR2.
dest_char_set argument is the name of the character set to which attribute1 is converted.
source_char_set argument is the name of the character set in which attribute1 is stored in the database. The default value is the database character set.
Both the destination and source character set arguments can be either literals or columns containing the name of the character set. For complete correspondence in character conversion, the destination character set must contain a representation of all the characters defined in the source character set. When a character does not exist in the destination character set, it is substituted with a replacement character. Replacement characters can be defined as part of a character set definition.
The following example illustrates character set conversion by converting a Latin-1 string to ASCII. The result is the same as importing the same string from a WE8ISO8859P1 database to a US7ASCII database.
SELECT CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL; CONVERT('ÄÊÍÕØABCDE' --------------------- A E I ? ? A B C D E ?
Common character sets include:
initcap::=INITCAP(attribute)
INITCAP returns the content of the attribute with the first letter of each word in uppercase and all other letters in lowercase. Words are delimited by white space or by characters that are not alphanumeric. Attribute can be of the data types CHAR or VARCHAR2. The return value is the same data type as attribute.
The following example capitalizes each word in the string:
SELECT INITCAP('the soap') "Capitals" FROM DUAL; Capitals --------- The Soap
instr::=INSTR(attribute1, attribute2, n, m) instrb::=INSTRB(attribute1, attribute2, n, m)
INSTR searches attribute1 beginning with its nth character for the mth occurrence of attribute2. It returns the position of the character in attribute1 that is the first character of this occurrence. INSTRB uses bytes instead of characters.
If n is negative, Oracle counts and searches backward from the end of attribute1. The value of m must be positive. The default values of both n and m are 1, which means that Oracle begins searching the first character of attribute1 for the first occurrence of attribute2. The return value is relative to the beginning of attribute1, regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if attribute2 does not appear m times after the nth character of attribute1), then the return value is 0.
The following example searches the string "CORPORATE FLOOR", beginning with the third character, for the string "OR". It returns the position in CORPORATE FLOOR at which the second occurrence of "OR" begins:
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL; Instring ---------- 14
The next example begins searching at the third character from the end:
SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring" FROM DUAL; Reversed Instring ----------------- 2
This example assumes a double-byte database character set.
SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL; Instring in bytes ----------------- 27
length::=LENGTH(attribute) lengthb::=LENGTHB(attribute)
The length functions return the length of char. LENGTH calculates the length using characters as defined by the input character set. LENGTHB uses bytes instead of characters. The attribute can be of the data types CHAR or VARCHAR2. The return value is of data type NUMBER. If attribute has data type CHAR, the length includes all trailing blanks. If attribute contains a null value, this function returns null.
The following examples use the LENGTH function using single- and multi-byte database character set.
SELECT LENGTH('CANDIDE') "Length in characters" FROM DUAL; Length in characters -------------------- 7
This example assumes a double-byte database character set.
SELECT LENGTHB ('CANDIDE') "Length in bytes" FROM DUAL; Length in bytes --------------- 14
lower::=LOWER(attribute)
LOWER returns attribute, with all letters in lowercase. The attribute can be of the data types CHAR and VARCHAR2. The return value is the same data type as that of attribute.
The following example returns a string in lowercase:
SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase" FROM DUAL; Lowercase -------------------- mr. scott mcmillan
lpad::=LPAD(attribute1, n, attribute2)
LPAD returns attribute1, left-padded to length n with the sequence of characters in attribute2. Attribute2 defaults to a single blank. If attribute1 is longer than n, this function returns the portion of attribute1 that fits in n.
Both attribute1 and attribute2 can be of the data types CHAR and VARCHAR2. The string returned is of VARCHAR2 data type and is in the same character set as attribute1. The argument n is the total length of the return value as it is displayed on your screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string.
The following example left-pads a string with the characters "*.":
SELECT LPAD('Page 1',15,'*.') "LPAD example" FROM DUAL; LPAD example --------------- *.*.*.*.*Page 1
ltrim::=LTRIM(attribute, set)
LTRIM removes characters from the left of attribute, with all the left most characters that appear in set removed. Set defaults to a single blank. If attribute is a character literal, you must enclose it in single quotes. Warehouse Builder begins scanning attribute from its first character and removes all characters that appear in set until it reaches a character absent in set. Then it returns the result.
Both attribute and set can be any of the data types CHAR and VARCHAR2. The string returned is of VARCHAR2 data type and is in the same character set as attribute.
The following example trims all of the left-most x's and y's from a string:
SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL; LTRIM example ------------ XxyLAST WORD
replace::=REPLACE(attribute, 'search_string', 'replace_string')
REPLACE returns an attribute with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, attribute is returned.
Both search_string and replacement_string, as well as attribute, can be of the data types CHAR or VARCHAR2. The string returned is of VARCHAR2 data type and is in the same character set as attribute.
This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE enables you to substitute one string for another, as well as to remove character strings.
The following example replaces occurrences of "J" with "BL":
SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL; Changes -------------- BLACK and BLUE
rpad::=RPAD(attribute1, n, attribute2)
RPAD returns attribute1, right-padded to length n with attribute2, replicated as many times as necessary. Attribute2 defaults to a single blank. If attribute1 is longer than n, this function returns the portion of attribute1 that fits in n.
Both attribute1 and attribute2 can be of the data types CHAR or VARCHAR2. The string returned is of VARCHAR2 data type and is in the same character set as attribute1.
The argument n is the total length of the return value as it is displayed on your screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string.
The following example rights-pads a name with the letters "ab" until it is 12 characters long:
SELECT RPAD('MORRISON',12,'ab') "RPAD example" FROM DUAL; RPAD example ----------------- MORRISONabab
rtrim::=RTRIM(attribute, set)
RTRIM returns attribute, with all the right most characters that appear in set removed; set defaults to a single blank. If attribute is a character literal, you must enclose it in single quotes. RTRIM works similarly to LTRIM. Both attribute and set can be any of the data types CHAR or VARCHAR2. The string returned is of VARCHAR2 data type and is in the same character set as attribute.
The following example trims the letters "xy" from the right side of a string:
SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g." FROM DUAL; RTRIM e.g ------------- BROWNINGyxX
soundex::=SOUNDEX(attribute)
SOUNDEX returns a character string containing the phonetic representation of attribute. This function enables you to compare words that are spelled differently, but sound similar in English.
The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:
Data types for attribute can be CHAR and VARCHAR2. The return value is the same data type as attribute.
The following example returns the employees whose last names are a phonetic representation of "Smyth":
SELECT last_name, first_name FROM hr.employees WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE'); LAST_NAME FIRST_NAME ---------- ---------- Smith Lindsey
substr::=SUBSTR(attribute, position, substring_length) substrb::=SUBSTRB(attribute, position, substring_length)
The substring functions return a portion of attribute, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters.
attribute to find the first character.
attribute.
attribute. If substring_length is less than 1, a null is returned.
Data types for attribute can be CHAR and VARCHAR2. The return value is the same data type as attribute. Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.
The following example returns several specified substrings of "ABCDEFG":
SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL; Substring --------- CDEF SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL; Substring --------- CDEF
Assume a double-byte database character set:
SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes" FROM DUAL; Substring with bytes -------------------- CD
to_date::=TO_DATE(attribute, fmt, nlsparam)
TO_DATE converts attribute of CHAR or VARCHAR2 data type to a value of data type DATE. The fmt is a date format specifying the format of attribute. If you omit fmt, attribute must be in the default date format. If fmt is 'J', for Julian, then attribute must be an integer. The nlsparam has the same purpose in this function as in the TO_CHAR function for date conversion.
Do not use the TO_DATE function with a DATE value for the attribute argument. The first two digits of the returned DATE value can differ from the original attribute, depending on fmt or the default date format.
The following example converts character strings into dates:
SELECT TO_DATE( 'January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American') FROM DUAL; TO_DATE --------- 15-JAN-89
to_multi_byte::=TO_MULTI_BYTE(attribute)
TO_MULTI_BYTE returns attribute with all of its single-byte characters converted to their corresponding multi-byte characters; attribute can be of data type CHAR or VARCHAR2. The value returned is in the same data type as attribute. Any single-byte characters in attribute that have no multi-byte equivalents appear in the output string as single-byte characters.
This function is useful only if your database character set contains both single-byte and multi-byte characters.
The following example illustrates converting from a single byte 'A' to a multi byte.
'A' in UTF8: SELECT dump(TO_MULTI_BYTE( 'A')) FROM DUAL; DUMP(TO_MULTI_BYTE('A')) ------------------------ Typ=1 Len=3: 239,188,161
to_number::=TO_NUMBER(attribute, fmt, nlsparam)
TO_NUMBER converts attribute to a value of CHAR or VARCHAR2 data type containing a number in the format specified by the optional format model_fmt, to a value of NUMBER data type.
The following example converts character string data into a number:
UPDATE employees SET salary = salary + TO_NUMBER('100.00', '9G999D99') WHERE last_name = 'Perkins';
The nlsparam string in this function has the same purpose as it does in the TO_CHAR function for number conversions.
SELECT TO_NUMBER('-AusDollars100','L9G999D99', ' NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL; Amount ---------- -100
to_single_byte::=TO_SINGLE_BYTE(attribute)
TO_SINGLE_BYTE returns attribute with all of its multi-byte characters converted to their corresponding single-byte characters; attribute can be of data type CHAR or VARCHAR2. The value returned is in the same data type as attribute. Any multi-byte characters in attribute that have no single-byte equivalents appear in the output as multi-byte characters.
This function is useful only if your database character set contains both single-byte and multi-byte characters.
The following example illustrates going from a multi-byte 'A' in UTF8 to a single byte ASCII 'A':
SELECT TO_SINGLE_BYTE( CHR(15711393)) FROM DUAL; T - A
translate::=TRANSLATE(attribute, from_string, to_string)
TRANSLATE returns attribute with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in attribute that are not in from_string are not replaced. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in attribute, they are removed from the return value.
You cannot use an empty string for to_string to remove all characters in from_string from the return value. Warehouse Builder interprets the empty string as null, and if this function has a null argument, it returns null.
The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':
SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License" FROM DUAL; License -------- 9XXX999
The following statement returns a license number with the characters removed and the digits remaining:
SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') "Translate example" FROM DUAL; Translate example ----------------- 2229
trim::=TRIM(attribute)
TRIM enables you to trim leading or trailing spaces (or both) from a character string. The function returns a value with data type VARCHAR2. The maximum length of the value is the length of attribute.
This example trims leading and trailing spaces from a string:
SELECT TRIM (' Warehouse ') "TRIM Example" FROM DUAL; TRIM example ------------ Warehouse
upper::=UPPER(attribute)
UPPER returns attribute, with all letters in uppercase; attribute can be of the data types CHAR and VARCHAR2. The return value is the same data type as attribute.
The following example returns a string in uppercase:
SELECT UPPER('Large') "Uppercase" FROM DUAL; Upper ----- LARGE
WB.LOOKUP_CHAR (table_name , column_name , key_column_name , key_value )
where table_name is the name of the table to perform the lookup on and column_name is the name of the VARCHAR2 column that will be returned. For example, the result of the lookup key_column_name is the name of the NUMBER column used as the key to match on in the lookup table, key_value is the value of the key column mapped into the key_column_name with which the match will be done.
To perform a key lookup on a number that returns a VARCHAR2 value from a database table using a NUMBER column as the matching key.
Consider the following table as a lookup table LKP1:
KEY_COLUMN TYPE COLOR 10 Car Red 20 Bike Green
Using this package with the following call:
WB.LOOKUP_CHAR ('LKP1' , 'TYPE' , 'KEYCOLUMN' , 20 )
returns the value of 'Bike' as output of this transform. This output would then be processed in the mapping as the result of an inline function call.
|
Note: This function is a row-based key lookup. Set-based lookups are supported when you use the lookup operator. |
Syntax WB.LOOKUP_CHAR (table_name , column_name , key_column_name , key_value )
where table_name is the name of the table to perform the lookup on; column_name is the name of the VARCHAR2 column that will be returned, for instance, the result of the lookup; key_column_name is the name of the VARCHAR2 column used as the key to match on in the lookup table; key_value is the value of the key column, for instance, the value mapped into the key_column_name with which the match will be done.
To perform a key lookup on a VARCHAR2 character that returns a VARCHAR2 value from a database table using a VARCHAR2 column as the matching key.
Consider the following table as a lookup table LKP1:
KEYCOLUMN TYPE COLOR ACV Car Red ACP Bike Green
Using this package with the following call:
WB.LOOKUP_CHAR ('LKP1' , 'TYPE' , 'KEYCOLUMN' , 'ACP' )
returns the value of 'Bike' as output of this transformation. This output is then processed in the mapping as the result of an inline function call.
WB_IS_SPACE(attibute)
Checks whether a string value only contains spaces. In mainframe sources, some fields contain many spaces to make a file adhere to the fixed length format. This function provides a way to check for these spaces. The function always returns a Boolean value.
WB_IS_SPACE returns true if attribute contains only spaces.
Date transformations provide Warehouse Builder users with functionality to perform transformations on date attributes. These transformations are ordered and the custom functions provided with Warehouse Builder are all in the format WB_<function name>.
All date transformations provided with Warehouse Builder are listed in alphabetical order in the sections below.
add_months::=ADD_MONTHS(attribute, n)
ADD_MONTHS returns the date in the attribute plus n months. The argument n can be any integer. This will typically be added from an attribute or from a constant.
If the date in attribute is the last day of the month or if the resulting month has fewer days than the day component of attribute, then the result is the last day of the resulting month. Otherwise, the result has the same day component as attribute.
The following example returns the month after the hire_date in the sample table employees:
SELECT TO_CHAR(ADD_MONTHS(hire_date,1), 'DD-MON-YYYY') "Next month" FROM employees WHERE last_name = 'Baer'; Next Month ----------- 07-JUL-1994
last_day::=LAST_DAY(attribute)
LAST_DAY returns the date of the last day of the month that contains the date in attribute.
The following statement determines how many days are left in the current month.
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL; SYSDATE Last Days Left --------- --------- ---------- 23-OCT-97 31-OCT-97 8
months_between::=MONTHS_BETWEEN(attribute1,attribute2)
MONTHS_BETWEEN returns the number of months between dates in attribute1 and attribute2. If attribute1 is later than attribute2, the result is positive; if earlier, then the result is negative.
If attribute1 and attribute2 are either the same day of the month or both last days of months, the result is always an integer. Otherwise, Oracle calculates the fractional portion of the result-based on a 31-day month and considers the difference in time components attribute1 and attribute2.
The following example calculates the months between two dates:
SELECT MONTHS_BETWEEN (TO_DATE('02-02-1995','MM-DD-YYYY'), TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months" FROM DUAL; Months ---------- 1.03225806
new_time::=NEW_TIME(attribute, zone1, zone2)
NEW_TIME returns the date and time in time zone zone2 when date and time in time zone zone1 are the value in attribute. Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time.
The arguments zone1 and zone2 can be any of these text strings:
The following example returns an Atlantic Standard time, given the Pacific Standard time equivalent:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT NEW_TIME (TO_DATE('11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time" FROM DUAL; New Date and Time -------------------- 09-NOV-1999 21:23:45
next_day::=NEXT_DAY(attribute1, attribute2)
NEXT_DAY returns the date of the first weekday named by the string in attribute2 that is later than the date in attribute1. The argument attribute2 must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument attribute1.
This example returns the date of the next Tuesday after February 2, 2001:
SELECT NEXT_DAY('02-FEB-2001','TUESDAY') "NEXT DAY" FROM DUAL; NEXT DAY ----------- 06-FEB-2001
round_date::=ROUND(attribute, fmt)
ROUND returns the date in attribute rounded to the unit specified by the format model fmt. If you omit fmt, date is rounded to the nearest day.
The following example rounds a date to the first day of the following year:
SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR') "New Year" FROM DUAL; New Year --------- 01-JAN-01
to_char_date::=TO_CHAR(attribute, fmt, nlsparam)
TO_CHAR converts attribute of DATE data type to a value of VARCHAR2 data type in the format specified by the date format fmt. If you omit fmt, date is converted to a VARCHAR2 value in the default date format.
The nlsparams specifies the language in which month and day names and abbreviations are returned. This argument can have this form: 'NLS_DATE_LANGUAGE = language' If you omit nlsparams, this function uses the default date language for your session.
The following example applies various conversions on the systemdate in the database:
select to_char(sysdate) no_fmt from dual; NO_FMT --------- 26-MAR-02 select to_char(sysdate, 'dd-mm-yyyy') fmted from dual; FMTED ---------- 26-03-2002
trunc_date::=TRUNC(attribute, fmt)
TRUNC returns attribute with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, date is truncated to the nearest day.
The following example truncates a date:
SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL; New Year --------- 01-JAN-92
WB_CAL_MONTH_NAME(attribute)
The function call returns the full-length name of the month for the date specified in attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_CAL_MONTH_NAME(sysdate) from dual; WB_CAL_MONTH_NAME(SYSDATE) ---------------------------- March select WB_CAL_MONTH_NAME('26-MAR-2002') from dual; WB_CAL_MONTH_NAME('26-MAR-2002') ---------------------------------- March
WB_CAL_MONTH_OF_YEAR(attribute)
WB_CAL_MONTH_OF_YEAR returns the month (1-12) of the year for date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_CAL_MONTH_OF_YEAR(sysdate) month from dual; MONTH ---------- 3 select WB_CAL_MONTH_OF_YEAR('26-MAR-2002') month from dual; MONTH ---------- 3
WB_CAL_MONTH_SHORT_NAME(attribute)
WB_CAL_MONTH_SHORT_NAME returns the short name of the month (for example 'Jan') for date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_CAL_MONTH_SHORT_NAME (sysdate) month from dual; MONTH --------- Mar select WB_CAL_MONTH_SHORT_NAME ('26-MAR-2002') month from dual; MONTH --------- Mar
WB_CAL_QTR(attribute)
WB_CAL_QTR returns the quarter of the Gregorian calendar year (for example Jan - March = 1) for date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_CAL_QTR (sysdate) quarter from dual; QUARTER ---------- 1 select WB_CAL_QTR ('26-MAR-2002') quarter from dual; QUARTER ---------- 1
WB_CAL_WEEK_OF_YEAR(attribute)
WB_CAL_WEEK_OF_YEAR returns the week of the year (1-53) for date attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_CAL_WEEK_OF_YEAR (sysdate) w_of_y from dual; W_OF_Y ---------- 13 select WB_CAL_WEEK_OF_YEAR ('26-MAR-2002') w_of_y from dual; W_OF_Y ---------- 13
WB_CAL_YEAR(attribute)
WB_CAL_YEAR returns the numerical year component for a date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_CAL_YEAR (sysdate) year from dual; YEAR ---------- 2002 select WB_CAL_YEAR ('26-MAR-2002') w_of_y from dual; YEAR ---------- 2002
WH_CAL_YEAR_NAME(attribute)
WB_CAL_YEAR_NAME returns the spelled out name of the year for the date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_CAL_YEAR_NAME (sysdate) name from dual; NAME ---------------------------------------------- Two Thousand Two select WB_CAL_YEAR_NAME ('26-MAR-2001') name from dual; NAME ---------------------------------------------- Two Thousand One
WB_DATE_FROM_JULIAN(attribute)
WB_DATE_FROM_JULIAN converts Julian date attribute to a regular date.
The following example shows the return value on a specified Julian date:
select to_char(WB_DATE_FROM_JULIAN(3217345),'dd-mon-yyyy') JDate from dual; JDATE ----------- 08-sep-4096
WB_DAY_NAME(attribute)
WB_DAY_NAME returns the full name of the day for date attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DAY_NAME (sysdate) name from dual; NAME -------------------------------------------- Thursday select WB_DAY_NAME ('26-MAR-2002') name from dual; NAME -------------------------------------------- Tuesday
WB_DAY_OF_MONTH(attribute)
WB_DAY_OF_MONTH returns the day number within the month for date attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DAY_OF_MONTH (sysdate) num from dual; NUM ---------- 28 select WB_DAY_OF_MONTH ('26-MAR-2002') num from dual NUM ---------- 26
WB_DAY_OF_WEEK(attribute)
WB_DAY_OF_WEEK returns the day number within the week for date attribute based on the database calendar.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DAY_OF_WEEK (sysdate) num from dual; NUM ---------- 5 select WB_DAY_OF_WEEK ('26-MAR-2002') num from dual; NUM ---------- 3
WB_DAY_OF_YEAR(attribute)
WB_DAY_OF_YEAR returns the day number within the year for the date attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DAY_OF_YEAR (sysdate) num from dual; NUM ---------- 87 select WB_DAY_OF_YEAR ('26-MAR-2002') num from dual; NUM ---------- 85
WB_DAY_SHORT_NAME(attribute)
WB_DAY_SHORT_NAME returns the three letter abbreviation or name for the date attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DAY_SHORT_NAME (sysdate) abbr from dual; ABBR ------------------------------------- Thu select WB_DAY_SHORT_NAME ('26-MAR-2002') abbr from dual; NUM ------------------------------------- Tue
WB_DECADE(attribute)
WB_DECADE returns the decade number within the century for the date attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DECADE (sysdate) dcd from dual; DCD ---------- 2 select WB_DECADE ('26-MAR-2002') DCD from dual; DCD ---------- 2
WB_HOUR12(attribute)
WB_HOUR12 returns the hour (in a 12-hour setting) component of the date corresponding to attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_HOUR12 (sysdate) h12 from dual; H12 ---------- 9 select WB_HOUR12 ('26-MAR-2002') h12 from dual; H12 ---------- 12
WB_HOUR12MI_SS(attribute)
WB_HOUR12MI_SS returns the timestamp in attribute formatted to HH12:MI:SS.
The following example shows the return value on the sysdate and on a specified date string:
select WB_HOUR12MI_SS (sysdate) h12miss from dual; H12MISS ------------------------------------- 09:08:52 select WB_HOUR12MI_SS ('26-MAR-2002') h12miss from dual; H12MISS ------------------------------------- 12:00:00
WB_HOUR24(attribute)
WB_HOUR24 returns the hour (in a 24-hour setting) component of date corresponding to attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_HOUR24 (sysdate) h24 from dual; H24 ---------- 9 select WB_HOUR24 ('26-MAR-2002') h24 from dual; H24 ---------- 0
WB_HOUR24MI_SS(attribute)
WB_HOUR24MI_SS returns the timestamp in attribute formatted to HH24:MI:SS.
The following example shows the return value on the sysdate and on a specified date string:
select WB_HOUR24MI_SS (sysdate) h24miss from dual; H24MISS ------------------------------------ 09:11:42 select WB_HOUR24MI_SS ('26-MAR-2002') h24miss from dual; H24MISS ------------------------------------ 00:00:00
WB_IS_DATE(attribute, fmt)
To check whether attribute contains a valid date. The function returns a Boolean value which is set to true if attribute contains a valid date. Fmt is an optional date format. If fmt is omitted, the date format of your database session is used.
You can use this function when you validate your data before loading it into a table. This way the value can be transformed before it reaches the table and causes an error.
WB_IS_DATE returns true in PL/SQL if attribute contains a valid date.
WB_JULIAN_FROM_DATE(attribute)
WB_JULIAN_FROM_DATE returns the Julian date of date corresponding to attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_JULIAN_FROM_DATE (sysdate) jdate from dual; JDATE ---------- 2452362 select WB_JULIAN_FROM_DATE ('26-MAR-2002') jdate from dual; JDATE ---------- 2452360
WB_MI_SS(attribute)
WB_MI_SS returns the minutes and seconds of the time component in the date corresponding to attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_MI_SS (sysdate) mi_ss from dual; MI_SS ------------------------------------------- 33:23 select WB_MI_SS ('26-MAR-2002') mi_ss from dual; MI_SS ------------------------------------------- 00:00
WB_WEEK_OF_MONTH(attribute)
WB_WEEK_OF_MONTH returns the week number within the calendar month for the date corresponding to attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_WEEK_OF_MONTH (sysdate) w_of_m from dual; W_OF_M ---------- 4 select WB_WEEK_OF_MONTH ('26-MAR-2002') w_of_m from dual; W_OF_M ---------- 4
These transforms are ordered alphabetically and the custom functions provided with Warehouse Builder are prefixed with WB_.
All numerical transformations provided with Warehouse Builder are listed in alphabetical order in the sections below.
abs::=ABS(attribute)
ABS returns the absolute value of attribute.
The following example returns the absolute value of -15:
SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15
ceil::=CEIL(attribute)
CEIL returns smallest integer greater than or equal to attribute.
The following example returns the smallest integer greater than or equal to 15.7:
SELECT CEIL(15.7) "Ceiling" FROM DUAL; Ceiling ---------- 16
exp::=EXP(attribute)
EXP returns e raised to the nth power represented in attribute, where e = 2.71828183...
The following example returns e to the 4th power:
SELECT EXP(4) "e to the 4th power" FROM DUAL; e to the 4th power ------------------ 54.59815
floor::=FLOOR(attribute)
FLOOR returns the largest integer equal to or less than the numerical value in attribute.
The following example returns the largest integer equal to or less than 15.7:
SELECT FLOOR(15.7) "Floor" FROM DUAL; Floor ---------- 15
mod::=MOD(attribute1, attribute2)
MOD returns the remainder of attribute1 divided by attribute2. It returns attribute1 if attribute2 is 0.
The following example returns the remainder of 11 divided by 4:
SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3
power::=POWER(attribute1, attribute2)
POWER returns attribute1 raised to the nth power represented in attribute2. The base attribute1 and the exponent in attribute2 can be any numbers, but if attribute1 is negative, then attribute2 must be an integer.
The following example returns three squared:
SELECT POWER(3,2) "Raised" FROM DUAL; Raised ---------- 9
round_number::=ROUND(attribute1, attribute2)
ROUND returns attribute1 rounded to attribute2 places right of the decimal point. If attribute2 is omitted, attribute1 is rounded to 0 places. Additionally, attribute2 can be negative to round off digits left of the decimal point and attribute2 must be an integer.
The following example rounds a number to one decimal point:
SELECT ROUND(15.193,1) "Round" FROM DUAL; Round ---------- 15.2 The following example rounds a number one digit to the left of the decimal point: SELECT ROUND(15.193,-1) "Round" FROM DUAL; Round ---------- 20
sign::=SIGN(attribute)
If attribute < 0, SIGN returns -1. If attribute = 0, the function returns 0. If attribute > 0, SIGN returns 1. This can be used in validation of measures where only positive numbers are expected.
The following example indicates that the function's argument (-15) is <0:
SELECT SIGN(-15) "Sign" FROM DUAL; Sign ---------- -1
sqrt::=SQRT(attribute)
SQRT returns square root of attribute. The value in attribute cannot be negative. SQRT returns a "real" result.
The following example returns the square root of 26:
SELECT SQRT(26) "Square root" FROM DUAL; Square root ----------- 5.09901951
to_char_number::=to_char(attribute, fmt, nlsparam)
TO_CHAR converts attribute of NUMBER data type to a value of VARCHAR2 data type, using the optional number format fmt. If you omit fmt, attribute is converted to a VARCHAR2 value exactly long enough to hold its significant digits. The nlsparam specifies these characters that are returned by number format elements:
This argument can have the following form:
'NLS_NUMERIC_CHARACTERS = ''dg'' NLS_CURRENCY = ''text'' NLS_ISO_CURRENCY = territory '
The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Within the quoted string, you must use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.
If you omit nlsparam or any one of the parameters, this function uses the default parameter values for your session.
In this example, the output is blank padded to the left of the currency symbol.
SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount" FROM DUAL; Amount -------------- $10,000.00- SELECT TO_CHAR(-10000,'L99G999D99MI' 'NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL; Amount ------------------- AusDollars10.000,00-
trunc_number::=TRUNC(attribute, m)
TRUNC returns attribute truncated to m decimal places. If m is omitted, attribute is truncated to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.
The following example truncates numbers:
SELECT TRUNC(15.79,1) "Truncate" FROM DUAL; Truncate ---------- 15.7 SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL; Truncate ---------- 10
WB.LOOKUP_NUM (table_name , column_name , key_column_name , key_value )
where TABLE_NAME is the name of the table to perform the lookup on; COLUMN_NAME is the name of the NUMBER column that will be returned, for instance, the result of the lookup; KEY_COLUMN_NAME is the name of the NUMBER column used as the key to match on in the lookup table; KEY_VALUE is the value of the key column, for example, the value mapped into the key_column_name with which the match will be done.
To perform a key look up that returns a NUMBER value from a database table using a NUMBER column as the matching key.
Consider the following table as a lookup table LKP1:
KEYCOLUMN TYPE_NO TYPE 10 100123 Car 20 100124 Bike
Using this package with the following call:
WB.LOOKUP_CHAR('LKP1' , 'TYPE_NO' , 'KEYCOLUMN' , 20 )
returns the value of 100124 as output of this transformation. This output is then processed in the mapping as the result of an inline function call.
WB.LOOKUP_CHAR(table_name , column_name , key_column_name , key_value )
where TABLE_NAME is the name of the table to perform the lookup on; COLUMN_NAME is the name of the NUMBER column that will be returned (such as the result of the lookup); KEY_COLUMN_NAME is the name of the NUMBER column used as the key to match on in the lookup table; KEY_VALUE is the value of the key column, such as the value mapped into the key_column_name with which the match will be done.
To perform a key lookup which returns a NUMBER value from a database table using a VARCHAR2 column as the matching key.
Consider the following table as a lookup table LKP1:
KEYCOLUMN TYPE_NO TYPE ACV 100123 Car ACP 100124 Bike
Using this package with the following call:
WB.LOOKUP_CHAR ('LKP1' , 'TYPE' , 'KEYCOLUMN' , 'ACP' )
returns the value of 100124 as output of this transformation. This output is then processed in the mapping as the result of an inline function call.
|
Note: This function is a row-based key lookup. Set-based lookups are supported when you use the lookup operator described in "Key Lookup". |
WB_IS_NUMBER(attibute, fmt)
To check whether attribute contains a valid number. The function returns a Boolean value, which is set to true if attribute contains a valid number. Fmt is an optional number format. If fmt is omitted, the number format of your session is used.
You can use this function when you validate the data before loading it into a table. This way the value can be transformed before it reaches the table and causes an error.
WB_IS_NUMBER returns true in PL/SQL if attribute contains a valid number.
XML transformations provide Warehouse Builder users with functionality to perform transformations on XML objects. These transformations enable Warehouse Builder users to load and transform XML documents and Oracle AQs.
To enable loading of XML sources, Warehouse Builder provides access to the database XML functionality through custom functions, as detailed in this chapter.
WB_XML_LOAD(control_file)
WB_XML_LOAD extracts and loads data from XML documents into database targets. The control_file, an XML document, specifies the source of the XML documents, the targets, and any runtime controls. After the transformation has been defined, a mapping in Warehouse Builder calls the transformation as a pre-map or post-map trigger.
The following example illustrates a script that can be used to implement a Warehouse Builder transformation that extracts data from an XML document stored in the file products.xml and loads it into the target table called books.
begin
wb_xml_load(`<OWBXMLRuntime>' || '<XMLSource>' || ' <file>\ora817\GCCAPPS\products.xml</file>' || '</XMLSource>' || '<targets>' || ' <target XSLFile="\ora817\XMLstyle\GCC.xsl">books</target>' || '</targets>' || '</OWBXMLRuntime>' );
end;
For more information on control files, see the Oracle9i Warehouse Builder Transformation Guide.
WB_XML_LOAD_F(control_file)
WB_XML_LOAD_F extracts and loads data from XML documents into database targets. The function returns the number of XML documents read during the load. The control_file, itself an XML document, specifies the source of the XML documents, the targets, and any runtime controls. After the transformation has been defined, a mapping in Warehouse Builder calls the transformation as a pre-map or post-map trigger.
The following example illustrates a script that can be used to implement a Warehouse Builder transformation that extracts data from an XML document stored in the file products.xml and loads it into the target table books.
begin
wb_xml_load_f('<OWBXMLRuntime>' || '<XMLSource>' || ' <file>\ora817\GCCAPPS\products.xml</file>' || '</XMLSource>' || '<targets>' || ' <target XSLFile="\ora817\XMLstyle\GCC.xsl">books</target>' || '</targets>' || '</OWBXMLRuntime>' );
end;
For more information on the types handled and detailed information on control_files, see the Oracle9i Warehouse Builder Transformation Guide.
The conversion transformations enable Warehouse Builder users to perform functions that allow conditional conversion of values. These functions achieve "if - then" constructions within SQL. For example, NVL provides functionality that substitutes NULL values with any value specified, or if input = NULL then output = value.
CASE expressions enable you to use "IF...THEN...ELSE" logic in SQL statements without invoking procedures. Use this statement instead of decode.
case_expression::=CASE attribute1 WHEN inputvalue THEN outputvalue [WHEN inputvalue THEN outputvalue]... ELSE elsevalue END
In a simple CASE expression, Oracle searches for the first WHEN ... THEN pair for which attribute1 is equal to inputvalue and returns outputvalue. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns elsevalue. Otherwise, Warehouse Builder returns null.
All of the expressions (attribute1, inputvalue, and outputvalue) must be of the same data type, which can be CHAR or VARCHAR2.
For each customer in the sample oe.customers table, the following statement lists the credit limit as "Low" if it equals $100, "High" if it equals $5000, and "Medium" if it equals anything else.
SELECT cust_last_name, CASE credit_limit WHEN 100 THEN 'Low' WHEN 5000 THEN 'High' ELSE 'Medium' END FROM customers; CUST_LAST_NAME CASECR -------------------- ------ ... Bogart Medium Nolte Medium Loren Medium Gueney Medium
The following statement finds the average salary of the employees in the sample table oe.employees, using $2000 as the lowest salary possible:
SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary ELSE 2000 END) "Average Salary" from employees e; Average Salary -------------- 6461.68224
In Warehouse Builder, you can use an expression to hold the CASE statement. The Expression Builder enables you to create the statement that is incorporated in the generated code. This example is shown in Figure 3-1, "CASE Mapping Example".
nvl::=NVL(attribute1, attrbitute2)
If attribute1 is null, NVL returns attribute2. If attribute1 is not null, then NVL returns attribute1. The arguments attribute1 and attribute2 can be any data type. If their data types are different, expr2 is converted to the data type of expr1 before they are compared. Warehouse Builder provides three variants of NVL to support all input values.
The data type of the return value is always the same as the data type of attribute1, unless attribute1 is character data, in which case the return value data type is VARCHAR2, in the character set of attribute1.
The following example returns a list of employee names and commissions, substituting "Not Applicable" if the employee receives no commission:
SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable') "COMMISSION" FROM employees WHERE last_name LIKE 'B%'; LAST_NAME COMMISSION ------------------------- ---------------------------------------- Baer Not Applicable Baida Not Applicable Banda .11 Bates .16 Bell Not Applicable Bernstein .26 Bissot Not Applicable Bloom .21 Bull Not Applicable
Other transformations included with Warehouse Builder enable you to perform various functions which are not restricted to certain data types. This section describes those types.
uid::=UID()
UID returns an integer that uniquely identifies the session user, such as the user who is logged on when running the session containing the transformation. In a distributed SQL statement, the UID function identifies the user on your local database.
Use this function when logging audit information into a target table to identify the user running the mappings.
The following returns the local database user id logged into this session:
select uid from dual; UID ---------- 55
user::=USER()
USER returns the name of the session user (the user who logged on) with the data type VARCHAR2.
Oracle compares values of this function with blank-padded comparison semantics. In a distributed SQL statement, the UID and USER functions identify the user on your local database.
Use this function when logging audit information into a target table to identify the user running the mappings.
The following example returns the local database user logged into this session:
select user from dual; USER ------------------------------ OWB9I_RUN
|
 Copyright © 2003 Oracle Corporation. All Rights Reserved. |
|