| Oracle® Warehouse Builder Transformation Guide 10g Release 1 (10.1) Part Number B12151-02 |
|
|
View PDF |
| Oracle® Warehouse Builder Transformation Guide 10g Release 1 (10.1) Part Number B12151-02 |
|
|
View PDF |
This chapter contains the following topics:
For related information, see:
Oracle SQL Reference
Oracle Warehouse Builder User's Guide
The following sections describe the transformation libraries and introduce how to use custom transformations in Warehouse Builder.
Warehouse Builder supports the following transformation types:
User Transformation Package: This category contains package functions and procedures that you define.
Predefined Transformations: These categories exist in the Oracle Library and consist of built-in and seeded functions and procedures.
Functions: The functions category is automatically created in every warehouse module. This category contains any standalone functions used as transformations. These functions can be defined by the user or imported from a database. A function transformation takes 0-n input parameters and produces a result value.
Procedures: The procedures category is automatically created in every warehouse module. This category contains any standalone procedures used as transformations. These procedures can be defined by the user or imported from a database. A procedure transformation takes 0-n input parameters and produces 0-n output parameters.
Imported Package: This category is created by importing a PL/SQL package. Although you can modify the package body, you cannot modify the package header, which is the signature for the function or procedure. You can view the package in the transformation library property sheet.
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:
Global Shared Library: a collection of reusable transformations categorized as functions and procedures defined within your repository.
Oracle Library: a collection of predefined functions from which you can define procedures for your Global Shared Library.
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:
Administration
Character
Conversion
Date
Numeric
Other
XML
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:
You can edit, save, and deploy the imported PL/SQL functions and procedures.
You cannot edit imported PL/SQL packages.
Wrapped PL/SQL objects are not readable.
Imported packages can be viewed and modified in the category property sheet.
You can edit the imported package body but not the imported package specification.
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.
Syntax
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.
Purpose
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.
Example
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.
Syntax
WB_ANALYZE_SCHEMA
No parameters are required for this function.
Purpose
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.
Example
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.
Syntax
WB_ANALYZE_TABLE(p_name)
where p_name is the table on which the analyze command is executed.
Purpose
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.
Example
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.
Syntax
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'
Purpose
This program unit compiles a stored object in the database.
Syntax
WB_DISABLE_ALL_CONSTRAINTS(p_name)
where p_name is the table name that determines which constraints are disabled.
Purpose
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.
Example
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 DISABLED
5 rows selected
Note:
This statement uses a cascade option to allow dependencies to be broken by disabling the keys.Syntax
WB_DISABLE_ALL_TRIGGERS(p_name)
where p_name is the table name on which the triggers are disabled.
Purpose
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.
Example
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
Syntax
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.
Purpose
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.
Example
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'; 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
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
Note:
This statement uses a cascade option to allow dependencies to be broken by disabling the keys.Syntax
WB_DISABLE_TRIGGER(p_name)
where p_name is the trigger name to be disabled.
Purpose
This program unit disables the specified trigger. The owner of the trigger must be the current user (in variable USER).
Example
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
Syntax
WB_ENABLE_ALL_CONSTRAINTS(p_name)
where p_name is the table name which determines which constraints are disabled.
Purpose
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.
Example
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
Syntax
WB_ENABLE_ALL_TRIGGERS(p_name)
where p_name is the table name on which the triggers are enabled
Purpose
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).
Example
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
Syntax
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.
Purpose
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 shows you how to enable the constraints one at a time.
Example
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 DISABLED
5 rows selected
Syntax
WB_ENABLE_TRIGGER(p_name)
where p_name is the trigger name to be enabled.
Purpose
This program unit enables the specified trigger. The owner of the trigger must be the current user (in variable USER).
Example
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
Syntax
WB_TRUNCATE_TABLE(p_name)
where p_name is the table name to be truncated.
Purpose
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.
Example
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.
Syntax
ascii::=ASCII(attribute)
Purpose
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.
Example
The following example returns the ASCII decimal equivalent of the letter Q:
SELECT ASCII('Q') FROM DUAL;
ASCII('Q')
----------
81
Syntax
asciistr::=ASCIISTR(attribute)
Purpose
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 (/).
Example
The following example returns the ASCII string equivalent of the text string "flauwekul":
SELECT ASCIISTR('flauwekul') FROM DUAL;
ASCIISTR('FLAUW
---------------
\6<65\756<\6700
Syntax
chartorowid::=CHARTOROWID(attribute)
Purpose
CHARTOROWID converts a value from CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to ROWID data type.
Example
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
Syntax
chr::=CHR(attribute)
Purpose
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.
Examples
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 preceeding example as follows:
SELECT CHR(195)||CHR(193)||CHR(227) "Dog" FROM DUAL; Dog --- CAT
The following example uses the UTF8 character set:
SELECT CHR (50052 USING NCHAR_CS) FROM DUAL; CH -- Ä
Syntax
concat::=CONCAT(attribute1, attribute2)
Purpose
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 (||).
Example
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
Syntax
convert::=CONVERT(attribute, dest_char_set, source_char_set)
Purpose
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.
The attribute1 argument is the value to be converted. It can of the data types CHAR and VARCHAR2.
The dest_char_set argument is the name of the character set to which attribute1 is converted.
The 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.
Example
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:
US7ASCII: US 7-bit ASCII character set
WE8DEC: West European 8-bit character set
WE8HP: HP West European Laserjet 8-bit character set
F7DEC: DEC French 7-bit character set
WE8EBCDIC500: IBM West European EBCDIC Code Page 500
WE8PC850: IBM PC Code Page 850
WE8ISO8859P1: ISO 8859-1 West European 8-bit character set
Syntax
initcap::=INITCAP(attribute)
Purpose
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.
Example
The following example capitalizes each word in the string:
SELECT INITCAP('the soap') "Capitals" FROM DUAL;
Capitals
---------
The Soap
Syntax
instr::=INSTR(attribute1, attribute2, n, m) instrb::=INSTRB(attribute1, attribute2, n, m)
Purpose
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.
Examples
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
Syntax
length::=LENGTH(attribute) lengthb::=LENGTHB(attribute)
Purpose
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.
Example
The following examples use the LENGTH function using single- and multibyte 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
Syntax
lower::=LOWER(attribute)
Purpose
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.
Example
The following example returns a string in lowercase:
SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"
FROM DUAL;
Lowercase
--------------------
mr. scott mcmillan
Syntax
lpad::=LPAD(attribute1, n, attribute2)
Purpose
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 multibyte character sets, the display length of a character string can differ from the number of characters in the string.
Example
The following example left-pads a string with the characters "*.":
SELECT LPAD('Page 1',15,'*.') "LPAD example"
FROM DUAL;
LPAD example
---------------
*.*.*.*.*Page 1
Syntax
ltrim::=LTRIM(attribute, set)
Purpose
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.
Example
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
Syntax
nlssort::=NLSSORT(attribute, nlsparam)
Purpose
NLSSORT returns the string of bytes used to sort attribute. The parameter attribute is of type VARCHAR2. Use this function to compare based on a linguistic sort of sequence rather than on the binary value of a string.
The value of nlsparam can have the form 'NLS_SORT = sort' where sort is a linguistic sort sequence or BINARY. If you omit nlsparam, this function uses the default sort sequence for your session.
Example
The following example creates a table containing two values and shows how the values returned can be ordered by the NLSSORT function:
CREATE TABLE test (name VARCHAR2(15));
INSERT INTO TEST VALUES ('Gaardiner');
INSERT INTO TEST VALUES ('Gaberd');
SELECT * FROM test ORDER BY name;
NAME
------
Gaardiner
Gaberd
SELECT *
FROM test
ORDER BY NLSSORT(name, 'NLSSORT = XDanish');
Name
------
Gaberd
Gaardiner
Syntax
nls_initcap::=NLS_INITCAP(attribute, nlsparam)
Purpose
NLS_INITCAP returns attribute, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. The value of nlsparam can have the form 'NLS_SORT = sort', where sort is either a linguistic sort sequence or BINARY. The linguistic sort sequence handles special linguistic requirements for case conversions. These requirements can result in a return value of a different length than the attribute. If you omit 'nlsparam', this function uses the default sort sequence for your session.
Example
The following examples show how the linguistic sort sequence results in a different return value from the function:
SELECT NLS_INITCAP('ijsland') "InitCap"
FROM dual;
InitCap
---------
Ijsland
SELECT NLS_INITCAP('ijsland','NLS_SORT=XDutch) "InitCap"
FROM dual;
InitCap
---------
IJsland
Syntax
nls_lower::=NLS_LOWER(attribute, nlsparam)
Purpose
NLS_LOWER returns attribute, with all letters lowercase. Both attribute and nlsparam can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of data type VARCHAR2 and is in the same character set as attribute. The value of nlsparam can have the form 'NLS_SORT = sort', where sort is either a linguistic sort sequence or BINARY.
Example
The following example returns the character string 'citta'' using the XGerman linguistic sort sequence:
SELECT NLS_LOWER('CITTA''','NLS_SORT=XGerman) "Lowercase"
FROM DUAL;
Lowercase
------------
citta'
Syntax
nls_upper::=NLS_UPPER(attribute, nlsparam)
Purpose
NLS_UPPER returns attribute, with all letters uppercase. Both attribute and nlsparam can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 data type and is in the same character set as attribute. The value of nlsparam can have the form 'NLS_SORT = sort', where sort is either a linguistic sort sequence or BINARY.
Example
The following example returns a string with all letters converted to uppercase:
SELECT NLS_UPPER('große') "Uppercase"
FROM DUAL;
Uppercase
------------
GROßE
SELECT NLS_UPPER('große', 'NLS_SORT=XGerman) "Uppercase"
FROM DUAL;
Uppercase
------------
GROSSE
Syntax
replace::=REPLACE(attribute, 'search_string', 'replace_string')
Purpose
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.
Example
The following example replaces occurrences of "J" with "BL":
SELECT REPLACE('JACK and JUE','J','BL') "Changes"
FROM DUAL;
Changes
--------------
BLACK and BLUE
Syntax
rpad::=RPAD(attribute1, n, attribute2)
Purpose
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 multibyte character sets, the display length of a character string can differ from the number of characters in the string.
Example
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
Syntax
rtrim::=RTRIM(attribute, set)
Purpose
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.
Example
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
Syntax
soundex::=SOUNDEX(attribute)
Purpose
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:
Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
Assign numbers to the remaining letters (after the first) as follows:
b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6
If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, omit all but the first.
Return the first four bytes padded with 0.
Data types for attribute can be CHAR and VARCHAR2. The return value is the same data type as attribute.
Example
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
Syntax
substr::=SUBSTR(attribute, position, substring_length) substrb::=SUBSTRB(attribute, position, substring_length)
Purpose
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.
If position is 0, it is treated as 1.
If position is positive, Warehouse Builder counts from the beginning of attribute to find the first character.
If position is negative, Warehouse Builder counts backward from the end of attribute.
If substring_length is omitted, Warehouse Builder returns all characters to the end of 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.
Examples
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
Syntax
to_date::=TO_DATE(attribute, fmt, nlsparam)
Purpose
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.
Example
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
Syntax
to_multi_byte::=TO_MULTI_BYTE(attribute)
Purpose
TO_MULTI_BYTE returns attribute with all of its single-byte characters converted to their corresponding multibyte 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 multibyte 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 multibyte characters.
Example
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
Syntax
to_number::=TO_NUMBER(attribute, fmt, nlsparam)
Purpose
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.
Examples
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
Syntax
to_single_byte::=TO_SINGLE_BYTE(attribute)
Purpose
TO_SINGLE_BYTE returns attribute with all of its multibyte 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 multibyte characters in attribute that have no single-byte equivalents appear in the output as multibyte characters.
This function is useful only if your database character set contains both single-byte and multibyte characters.
Example
The following example illustrates going from a multibyte 'A' in UTF8 to a single byte ASCII 'A':
SELECT TO_SINGLE_BYTE( CHR(15711393)) FROM DUAL; T - A
Syntax
translate::=TRANSLATE(attribute, from_string, to_string)
Purpose
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.
Examples
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
Syntax
trim::=TRIM(attribute)
Purpose
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.
Example
This example trims leading and trailing spaces from a string:
SELECT TRIM (' Warehouse ') "TRIM Example"
FROM DUAL;
TRIM example
------------
Warehouse
Syntax
upper::=UPPER(attribute)
Purpose
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.
Example
The following example returns a string in uppercase:
SELECT UPPER('Large') "Uppercase"
FROM DUAL;
Upper
-----
LARGE
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 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.
Purpose
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.
Example
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.
Purpose
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.
Example
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.
Note:
This function is a row-based key lookup. Set-based lookups are supported when you use the lookup operator.Syntax
WB_IS_SPACE(attibute)
Purpose
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.
Example
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 following sections.
Syntax
add_months::=ADD_MONTHS(attribute, n)
Purpose
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.
Example
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
Syntax
last_day::=LAST_DAY(attribute)
Purpose
LAST_DAY returns the date of the last day of the month that contains the date in attribute.
Examples
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
Syntax
months_between::=MONTHS_BETWEEN(attribute1, attribute2)
Purpose
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.
Example
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
Syntax
new_time::=NEW_TIME(attribute, zone1, zone2)
Purpose
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:
AST, ADT: Atlantic Standard or Daylight Time
BST, BDT: Bering Standard or Daylight Time
CST, CDT: Central Standard or Daylight Time
CST, EDT: Eastern Standard or Daylight Time
GMT: Greenwich Mean Time
HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.
MST, MDT: Mountain Standard or Daylight Time
NST: Newfoundland Standard Time
PST, PDT: Pacific Standard or Daylight Time
YST, YDT: Yukon Standard or Daylight Time
Example
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
Syntax
next_day::=NEXT_DAY(attribute, attribute2)
Purpose
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.
Example
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
Syntax
round_date::=ROUND(attribute, fmt)
Purpose
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.
Example
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
Syntax
sysdate::=SYSDATE
Purpose
SYSDATE returns the current date and time. The data type of the returned value is DATE. The function requires no arguments. In distributed SQL statements, this function returns the date and time on your local database. You cannot use this function in the condition of a CHECK constraint.
Example
The following example returns the current date and time:
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')"NOW" FROM DUAL;NOW-------------------04-13-2001 09:45:51
Syntax
to_char_date::=TO_CHAR(attribute, fmt, nlsparam)
Purpose
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.
Example
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
Syntax
trunc_date::=TRUNC(attribute, fmt)
Purpose
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.
Example
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
Syntax
WB_CAL_MONTH_NAME(attribute)
Purpose
The function call returns the full-length name of the month for the date specified in attribute.
Example
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
Syntax
WB_CAL_MONTH_OF_YEAR(attribute)
Purpose
WB_CAL_MONTH_OF_YEAR returns the month (1-12) of the year for date in attribute.
Example
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
Syntax
WB_CAL_MONTH_SHORT_NAME(attribute)
Purpose
WB_CAL_MONTH_SHORT_NAME returns the short name of the month (for example 'Jan') for date in attribute.
Example
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
Syntax
WB_CAL_QTR(attribute)
Purpose
WB_CAL_QTR returns the quarter of the Gregorian calendar year (for example Jan - March = 1) for date in attribute.
Example
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
Syntax
WB_CAL_WEEK_OF_YEAR(attribute)
Purpose
WB_CAL_WEEK_OF_YEAR returns the week of the year (1-53) for date attribute.
Example
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
Syntax
WB_CAL_YEAR(attribute)
Purpose
WB_CAL_YEAR returns the numerical year component for a date in attribute.
Example
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
Syntax
WH_CAL_YEAR_NAME(attribute)
Purpose
WB_CAL_YEAR_NAME returns the spelled out name of the year for the date in attribute.
Example
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
Syntax
WB_DATE_FROM_JULIAN(attribute)
Purpose
WB_DATE_FROM_JULIAN converts Julian date attribute to a regular date.
Example
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
Syntax
WB_DAY_NAME(attribute)
Purpose
WB_DAY_NAME returns the full name of the day for date attribute.
Example
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
Syntax
WB_DAY_OF_MONTH(attribute)
Purpose
WB_DAY_OF_MONTH returns the day number within the month for date attribute.
Example
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
Syntax
WB_DAY_OF_WEEK(attribute)
Purpose
WB_DAY_OF_WEEK returns the day number within the week for date attribute based on the database calendar.
Example
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
Syntax
WB_DAY_OF_YEAR(attribute)
Purpose
WB_DAY_OF_YEAR returns the day number within the year for the date attribute.
Example
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
Syntax
WB_DAY_SHORT_NAME(attribute)
Purpose
WB_DAY_SHORT_NAME returns the three letter abbreviation or name for the date attribute.
Example
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
Syntax
WB_DECADE(attribute)
Purpose
WB_DECADE returns the decade number within the century for the date attribute.
Example
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
Syntax
WB_HOUR12(attribute)
Purpose
WB_HOUR12 returns the hour (in a 12-hour setting) component of the date corresponding to attribute.
Example
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
Note:
For a date not including the timestamp (in the second example), Oracle uses the 12:00 (midnight) timestamp and therefore returns 12 in this case.Syntax
WB_HOUR12MI_SS(attribute)
Purpose
WB_HOUR12MI_SS returns the timestamp in attribute formatted to HH12:MI:SS.
Example
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
Note:
For a date not including the timestamp (in the second example), Oracle uses the 12:00 (midnight) timestamp and therefore returns 12 in this case.Syntax
WB_HOUR24(attribute)
Purpose
WB_HOUR24 returns the hour (in a 24-hour setting) component of date corresponding to attribute.
Example
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
Note:
For a date not including the timestamp (in the second example), Oracle uses the 00:00:00 timestamp and therefore returns the timestamp in this case.Syntax
WB_HOUR24MI_SS(attribute)
Purpose
WB_HOUR24MI_SS returns the timestamp in attribute formatted to HH24:MI:SS.
Example
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
Note:
For a date not including the timestamp (in the second example), Oracle uses the 00:00:00 timestamp and therefore returns the timestamp in this case.Syntax
WB_IS_DATE(attribute, fmt)
Purpose
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.
Example
WB_IS_DATE returns true in PL/SQL if attribute contains a valid date.
Syntax
WB_JULIAN_FROM_DATE(attribute)
Purpose
WB_JULIAN_FROM_DATE returns the Julian date of date corresponding to attribute.
Example
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
Syntax
WB_MI_SS(attribute)
Purpose
WB_MI_SS returns the minutes and seconds of the time component in the date corresponding to attribute.
Example
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
Note:
For a date not including the timestamp (in the second example), Oracle uses the 00:00:00 timestamp and therefore returns the timestamp in this case.Syntax
WB_WEEK_OF_MONTH(attribute)
Purpose
WB_WEEK_OF_MONTH returns the week number within the calendar month for the date corresponding to attribute.
Example
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 following sections.
Syntax
abs::=ABS(attribute)
Purpose
ABS returns the absolute value of attribute.
Example
The following example returns the absolute value of -15:
SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15
Syntax
acos::= ACOS(attribute)
Purpose
ACOS returns the arc cosine of attribute. The argument attribute must be in the range of -1 to 1, and the function returns values in the range of 0 to pi, expressed in radians.
Example
The following example returns the arc cosine of .3:
SELECT ACOS(.3) "Arc_Cosine" FROM DUAL; Arc_Cosine ---------- 1.26610367
Syntax
asin::=ASIN(attribute)
Purpose
ASIN returns the arc sine of attribute. The argument attribute must be in the range of -1 to 1, and the function returns values in the range of -pi/2 to pi/2, expressed in radians.
Example
The following example returns the arc cosine of .3:
SELECT ACOS(.3) "Arc_Sine" FROM DUAL; Arc_Sine ---------- .304692654
Syntax
atan::=ATAN(attribute)
Purpose
ATAN returns the arc tangent of attribute. The argument attribute can be in an unbounded range, and the function returns values in the range of -pi/2 to pi/2, expressed in radians.
Example
The following example returns the arc tangent of .3:
SELECT ATAN(.3) "Arc_Tangent" FROM DUAL; Arc_Tangent ---------- .291456794
Syntax
atan2::=ATAN2(attribute1, attribute2)
Purpose
ATAN2 returns the arc tangent of attribute1 and attribute2. The argument attribute1 can be in an unbounded range, and the function returns values in the range of -pi to pi, depending on the signs of attribute1 and attribute2, and are expressed in radians. ATAN2(attribute1,attribute2) is the same as ATAN2(attribute1/attribute2).
Example
The following example returns the arc tangent of .3 and .2:
SELECT ATAN2(.3,.2) "Arc_Tangent2" FROM DUAL; Arc_Tangent2 ------------ .982793723
Syntax
cos::=COS(attribute)
Purpose
COS returns the cosine of attribute (an angle expressed in degrees).
Example
The following example returns the cosine of 180 degrees:
SELECT COS(180 * 3.14159265359/180) "Cosine" FROM DUAL;
Cosine
------
-1
Syntax
cosh::=COSH(attribute)
Purpose
COSH returns the hyperbolic cosine of attribute.
Example
The following example returns the hyperbolic cosine of 0:
SELECT COSH(0) "Hyperbolic Cosine" FROM DUAL;
Hyperbolic Cosine
-----------------
1
Syntax
ceil::=CEIL(attribute)
Purpose
CEIL returns smallest integer greater than or equal to attribute.
Example
The following example returns the smallest integer greater than or equal to 15.7:
SELECT CEIL(15.7) "Ceiling" FROM DUAL; Ceiling ---------- 16
Syntax
exp::=EXP(attribute)
Purpose
EXP returns e raised to the nth power represented in attribute, where e = 2.71828183...
Example
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
Syntax
floor::=FLOOR(attribute)
Purpose
FLOOR returns the largest integer equal to or less than the numerical value in attribute.
Example
The following example returns the largest integer equal to or less than 15.7:
SELECT FLOOR(15.7) "Floor" FROM DUAL; Floor ---------- 15
Syntax
ln::=LN(attribute)
Purpose
LN returns the natural logarithm of attribute, where attribute is greater than 0.
Example
The following example returns the natural logarithm of 95:
SELECT LN(95) "Natural Logarithm" FROM DUAL; Natural Logarithm ----------------- 4.55387689
Syntax
log::=LOG(attribute1, attribute2)
Purpose
LOG returns the logarithm, base attribute1 of attribute2. The base attribute1 can be any positive number other than 0 or 1 and attribute2 can be any positive number.
Example
The following example returns the logarithm of 100:
SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL;
Log base 10 of 100
------------------
2
Syntax
mod::=MOD(attribute1, attribute2)
Purpose
MOD returns the remainder of attribute1 divided by attribute2. It returns attribute1 if attribute2 is 0.
Example
The following example returns the remainder of 11 divided by 4:
SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3
Syntax
power::=POWER(attribute1, attribute2)
Purpose
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.
Example
The following example returns three squared:
SELECT POWER(3,2) "Raised" FROM DUAL; Raised ---------- 9
Syntax
round_number::=ROUND(attribute1, attribute2)
Purpose
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.
Examples
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
Syntax
sign::=SIGN(attribute)
Purpose
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.
Example
The following example indicates that the function's argument (-15) is <0:
SELECT SIGN(-15) "Sign" FROM DUAL; Sign ---------- -1
Syntax
sin::=SIN(attribute)
Purpose
SIN returns the sine of attribute (expressed as an angle)
Example
The following example returns the sine of 30 degrees:
SELECT SIN(30 * 3.14159265359/180) "Sine of 30 degrees" FROM DUAL;
Sine of 30 degrees
------------------
.5
Syntax
sinh::=SINH(attribute)
Purpose
SINH returns the hyperbolic sine of attribute.
Example
The following example returns the hyperbolic sine of 1:
SELECT SINH(1) "Hyperbolic Sine of 1" FROM DUAL;
Hyperbolic Sine of 1
--------------------
1.17520119
Syntax
sqrt::=SQRT(attribute)
Purpose
SQRT returns square root of attribute. The value in attribute cannot be negative. SQRT returns a "real" result.
Example
The following example returns the square root of 26:
SELECT SQRT(26) "Square root" FROM DUAL; Square root ----------- 5.09901951
Syntax
tan::=TAN(attrin=bute)
Purpose
TAN returns the tangent of attribute (an angle expressed in radians).
Example
The following example returns the tangent of 135 degrees:
SELECT TAN(135 * 3.14159265359/180) "Tangent of 135 degrees" FROM DUAL;
Tangent of 135 degrees
----------------------
-1
Syntax
tanh::=TANH(attribute)
Purpose
TANH returns the hyperbolic tangent of attribute.
Example
The following example returns the hyperbolic tangent of 5:
SELECT TANH(5) "Hyperbolic tangent of 5" FROM DUAL;
Hyperbolic tangent of 5
-----------------------
.462117157
Syntax
to_char_number::=to_char(attribute, fmt, nlsparam)
Purpose
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:
Decimal character
Group separator
Local currency symbol
International currency symbol
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.
Examples
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-
Syntax
trunc_number::=TRUNC(attribute, m)
Purpose
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.
Example
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
Syntax
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.
Purpose
To perform a key look up that returns a NUMBER value from a database table using a NUMBER column as the matching key.
Example
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.
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 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.
Purpose:
To perform a key lookup which returns a NUMBER value from a database table using a VARCHAR2 column as the matching key.
Example
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.Syntax
WB_IS_NUMBER(attibute, fmt)
Purpose
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.
Example
WB_IS_NUMBER returns true in PL/SQL if attribute contains a valid number.
OLAP transformations enable Warehouse Builder users to load data stored in relational dimensions and cubes into an analytic workspace.
The OLAP transformations provided by Warehouse Builder are:
The WB_OLAP_LOAD_CUBE, WB_OLAP_LOAD_DIMENSION, and WB_OLAP_LOAD_DIMENSION_GENUK transformations are used for cube cloning in Warehouse Builder. Use these OLAP transformations only if your database version is Oracle Database 9i or Oracle Database 10g Release 1.
The examples used to explain these OLAP transformations are based on the scenario depicted in Figure 3-1.
Figure 3-1 Example of OLAP Transformations
The relational dimension TIME_DIM and the relational cube SALES_CUBE are stored in the schema WH_TGT. The analytic workspace AW_WH, into which the dimension and cube are loaded, is also created in the WH_TGT schema.
Syntax
wb_olap_load_cube::=WB_OLAP_LOAD_CUBE(olap_aw_owner, olap_aw_name, olap_cube_owner, olap_cube_name, olap_tgt_cube_name)
where olap_aw_owner is the name of the database schema that owns the analytic workspace; olap_aw_name is the name of the analytic workspace that stores the cube data; olap_cube_owner is the name of the database schema that owns the related relational cube; olap_cube_name is the name of the relational cube; olap_tgt_cube_name is the name of the cube in the analytic workspace.
Purpose
WB_OLAP_LOAD_CUBE loads data from the relational cube into the analytic workspace. This allows further analysis of the cube data. This is for loading data in an AW cube from a relational cube which it was cloned from. This is a wrapper around some of the procedures in the DBMS_AWM package for loading a cube.
Example
The following example loads data from the relational cube SALES_CUBE into a cube called AW_SALES in the AW_WH analytic workspace:
WB_OLAP_LOAD_CUBE('WH_TGT', 'AW_WH', 'WH_TGT', 'SALES_CUBE', 'AW_SALES')
Syntax
wb_olap_load_dimension::=WB_OLAP_LOAD_DIMENSION(olap_aw_owner, olap_aw_name, olap_dimension_owner, olap_dimension_name, olap_tgt_dimension_name)
where olap_aw_owner is the name of the database schema that owns the analytic workspace; olap_aw_name is the name of the analytic workspace that stores the dimension data; olap_dimension_owner is the name of the database schema in which the related relational dimension is stored; olap_dimension_name is the name of the relational dimension; olap_tgt_dimension_name is the name of the dimension in the analytic workspace.
Purpose
WB_OLAP_LOAD_DIMENSION loads data from the relational dimension into the analytic workspace. This allows further analysis of the dimension data. This is for loading data in an AW dimension from a relational dimension which it was cloned from. This is a wrapper around some of the procedures in the DBMS_AWM package for loading a dimension.
Example
The following example loads the data from the relational dimension TIME_DIM into a dimension called AW_TIME in the analytic workspace AW_WH:
WB_OLAP_LOAD_DIMENSION('WH_TGT', 'AW_WH', 'WH_TGT', 'TIME_DIM', 'AW_TIME')
Syntax
wb_olap_load_dimension_genuk::=WB_OLAP_LOAD_DIMENSION_GENUK(olap_aw_owner, olap_aw_name, olap_dimension_owner, olap_dimension_name, olap_tgt_dimension_name)
where olap_aw_owner is the name of the database schema that owns the analytic workspace; olap_aw_name is the name of the analytic workspace that stores the dimension data; olap_dimension_owner is the name of the database schema in which the related relational dimension is stored; olap_dimension_name is the name of the relational dimension; olap_tgt_dimension_name is the name of the dimension in the analytic workspace.
Purpose
WB_OLAP_LOAD_DIMENSION_GENUK loads data from the relational dimension into the analytic workspace. Unique dimension identifiers will be generated across all levels. This is for loading data in an AW dimension from a relational dimension which it was cloned from. This is a wrapper around some of the procedures in the DBMS_AWM package for loading a dimension.
If a cube has been cloned and if you select YES for the Generate Surrogate Keys for Dimensions option, then when you want to reload the dimensions, you should use the WB_OLAP_LOAD_DIMENSION_GENUK procedure. This procedure generates surrogate identifiers for all levels in the AW, because the AW requires all level identifiers to be unique across all levels of a dimension.
Example
Consider an example in which the dimension TIME_DIM has been deployed to the OLAP server by cloning the cube. The parameter generate surrogate keys for Dimension was set to true. To now reload data from the relational dimension TIME_DIM into the dimension AW_TIME in the analytic workspace AW_WH, use the following syntax.
WB_OLAP_LOAD_CUBE('WH_TGT', 'AW_WH', 'WH_TGT', 'TIME_DIM', 'AW_TIME')
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.
Syntax:
WB_XML_LOAD(control_file)
Purpose
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.
Example
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 Oracle Warehouse Builder User's Guide.
Syntax
WB_XML_LOAD_F(control_file)
Purpose
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.
Example
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 Oracle Warehouse Builder User's 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.
Syntax
case_expression::=CASE attrubute1 WHEN inputvalue THEN outputvalue [WHEN inputvalue THEN outputvalue]... ELSE elsevalue END
Purpose
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.
Simple CASE Example
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
Searched CASE Example
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
Warehouse Builder Example
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-2, "CASE Mapping Example".
Syntax
nvl::=NVL(attribute1, attrbitute2)
Purpose
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.
Example
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.
Syntax
nls_charset_decl_len::=NLS_CHARSET_DECL_LEN(byte_count,charset_id)
Purpose
NLS_CHARSET_DECL_LEN returns the declaration width (in number of characters) of an NCHAR column. The byte_count argument is the width of the column. The charset_id argument is the character set ID of the column.
Example
The following example returns the number of characters that are in a 200-byte column when you are using a multibyte character set:
SELECT NLS_CHARSET_DECL_LEN(200, nls_charset_id('ja16eucfixed')) FROM DUAL;
NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED'))
----------------------------------------------------------
100
Syntax
nls_charset_id::= NLS_CHARSET_ID(text)
Purpose
NLS_CHARSET_ID returns the character set ID number corresponding to character set name text. The text argument is a run-time VARCHAR2 value. The text value 'CHAR_CS' returns the database character set ID number of the server. The text value 'NCHAR_CS' returns the national character set ID number of the server.
Invalid character set names return null.
Example
The following example returns the character set ID number of a character set:
SELECT NLS_CHARSET_ID('ja16euc') FROM DUAL;
NLS_CHARSET_ID('JA16EUC')
--------------------------
830
Syntax
nls_charset_name::= NLS_CHARSET_NAME(number)
Purpose
NLS_CHARSET_NAME returns the name of the character set corresponding to ID number. The character set name is returned as a VARCHAR2 value in the database character set.If number is not recognized as a valid character set ID, then this function returns null.
Example
The following example returns the character set corresponding to character set ID number 2:
SELECT NLS_CHARSET_NAME(2) FROM DUAL; NLS_CH -------- WE8DEC
Syntax
uid::=UID()
Purpose
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.
Example
The following returns the local database user id logged into this session:
select uid from dual;
UID
----------
55
Syntax
user::=USER()
Purpose
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.
Example
The following example returns the local database user logged into this session:
select user from dual; USER ------------------------------ OWB9I_RUN
