28 Warehouse Builder Transformations Reference

WB_ABORT

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 terminate 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 terminate an application. You can use this function in a post-mapping process to terminate deployment if there is an error in the mapping.

WB_COMPILE_PLSQL

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.

Example

The following hypothetical example compiles the procedure called add_employee_proc:

EXECUTE WB_COMPILE_PLSQL('ADD_EMPLOYEE_PROC', 'PROCEDURE');

WB_DISABLE_ALL_CONSTRAINTS

Syntax

WB_DISABLE_ALL_CONSTRAINTS(p_name)

where p_name is the name of the table on 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';

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 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

WB_DISABLE_ALL_TRIGGERS

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:

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 all triggers on the table OC_ORDERS.

EXECUTE WB_DISABLE_ALL_TRIGGERS ('OC_ORDERS');

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     DISABLED
ORDERS_ITEMS_TRG               DISABLED

WB_DISABLE_CONSTRAINT

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 defined.

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

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 DISABLED

WB_DISABLE_TRIGGER

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.

ECECUTE WB_DISABLE_TRIGGER ('ORDERS_TRG');

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     DISABLED
ORDERS_ITEMS_TRG               ENABLED

WB_ENABLE_ALL_CONSTRAINTS

Syntax

WB_ENABLE_ALL_CONSTRAINTS(p_name)

where p_name is the name of the table for which all constraints should be enabled.

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 enabling 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

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

WB_ENABLE_ALL_TRIGGERS

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 all triggers defined on the table OE.OC_ORDERS.

EXECUTE WB_ENABLE_ALL_TRIGGERS ('OC_ORDERS');

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     ENABLED
ORDERS_ITEMS_TRG               ENABLED

WB_ENABLE_CONSTRAINT

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 defined.

Purpose

This program unit enables 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 reenable 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

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

WB_ENABLE_TRIGGER

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

WB_TRUNCATE_TABLE

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 reenables 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

WB_LOOKUP_CHAR (number)

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.

WB_LOOKUP_CHAR (varchar2)

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.

WB_IS_SPACE

Syntax

WB_IS_SPACE(attibute)

Purpose

Checks whether a string value only contains spaces. This function returns a Boolean value. 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.

Example

WB_IS_SPACE returns TRUE if attribute contains only spaces.

WB_RT_GET_ELAPSED_TIME

Syntax

WB_RT_GET_ELAPSED_TIME(audit_id)

Purpose

This function returns the elapsed time, in seconds, for the job execution given by the specified audit_id. It returns null if the specified audit ID does not exist. For example, you can use this function on a transition if you want to make a choice dependent on the time taken by the previous activity.

Example

The following example returns the time elapsed since the activity represented by audit_id was started:

declare
   audit_id NUMBER := 1812;
   l_time NUMBER;
begin
   l_time:= WB_RT_GET_ELAPSED_TIME(audit_id);
end;

WB_RT_GET_JOB_METRICS

Syntax

WB_RT_GET_JOB_METRICS(audit_id, no_selected, no_deleted, no_updated, no_inserted, no_discarded, no_merged, no_corrected)

where no_selected represents the number of rows selected, no_deleted represents the number of rows deleted, no_updated represents the number of rows updated, no_inserted represents the number of rows inserted, no_discarded represents the number of rows discarded, no_merged represents the number of rows merged, and no_corrected represents the number of rows corrected during the job execution.

Purpose

This procedure returns the metrics of the job execution represented by the specified audit_id. The metrics include the number of rows selected, deleted, updated, inserted, discarded, merged, and corrected.

Example

The following example retrieves the job metrics for the audit ID represented by audit_id.

declare
   audit_id NUMBER := 16547;
   l_nselected NUMBER;
   l_ndeleted NUMBER;
   l_nupdated NUMBER;
   l_ninserted NUMBER;
   l_ndiscarded NUMBER;
   l_nmerged NUMBER;
   l_ncorrected NUMBER;
begin
   WB_RT_GET_JOB_METRICS(audit_id, l_nselected, l_ndeleted, l_nupdated,
                         l_ninserted, l_ndiscarded, l_nmerged, l_ncorrected);
   dbms_output.put_line('sel=' || l_nselected || ', del=' l_ndeleted ||
                        ', upd=' || l_nupdated);
   dbms_output.put_line('ins='|| l_ninserted || ' , dis=' || l_ndiscarded );
   dbms_output.put_line('mer=' || l_nmerged || ', cor=' ||l_ncorrected);
 end;

WB_RT_GET_LAST_EXECUTION_TIME

Syntax

WB_RT_GET_LAST_EXECUTION_TIME(objectName, objectType, objectLocationName)

where objectName represents the name of the object, objectType represents the type of the object (for example MAPPING, DATA_AUDITOR, PROCESS_FLOW, SCHEDULABLE), and objectLocationName represents the location to which the object is deployed.

Purpose

This transformation gives you access to time-based data. Typically, you can use this in a Process Flow to model some design aspect that is relevant to "time". For example you can design a path that may execute different maps if the time since the last execution is more than 1 day.

You can also use this transformation to determine time-synchronization across process flows that are running concurrently. For example, you can choose a path in a process flow according to whether another Process Flow has completed.

Example

The following example retrieves the time when the mapping TIMES_MAP was last executed and the if condition determines whether this time was within 1 day of the current time. Based on this time, it can perform different actions.

declare
    last_exec_time DATE;
begin
    last_exec_time:=WB_RT_GET_LAST_EXECUTION_TIME('TIMES_MAP','MAPPING','WH_LOCATION');
    if last_exec_time < sysdate - 1 then
--       last-execution was more than one day ago
--       provide details of action here
          NULL;
    Else
--       provide details of action here
          NULL;
    end if;
end;

WB_RT_GET_MAP_RUN_AUDIT

Syntax

WB_RT_GET_MAP_RUN_AUDIT(audit_id)

Purpose

This function returns the map run ID for a job execution that represents a map activity. It returns null if audit_id does not represent the job execution for a map. For example, you can use the returned ID as a key to access the ALL_RT_MAP_RUN_<name> views for more information.

Example

The following example retrieves the map run ID for a job execution whose audit ID is 67265. It then uses this map run ID to obtain the name of the source from the ALL_RT_MAP_RUN_EXECUTIONS public view.

declare
  audit_id NUMBER := 67265;
  l_sources VARCHAR2(256);
  l_run_id NUMBER;begin  l_run_id := WB_RT_GET_MAP_RUN_AUDIT_ID(audit_id);  SELECT source_name INTO l_sources FROM all_rt_map_run_sources         WHERE map_run_id = l_run_id;end;

WB_RT_GET_NUMBER_OF_ERRORS

Syntax

WB_RT_GET_NUMBER_OF_ERRORS(audit_id)

Purpose

This function returns the number of errors recorded for the job execution given by the specified audit_id. It returns null if the specific audit_id is not found.

Example

The following example retrieves the number of errors generated by the job execution whose audit ID is 8769. You can then perform different actions based on the number of errors.

declare
   audit_id NUMBER := 8769;
   l_errors NUMBER;begin   l_errors := WB_RT_GET_NUMBER_OF_ERRORS(audit_id);
   if l_errors < 5 then
      .....
   else
      .....
   end if;
end;

WB_RT_GET_NUMBER_OF_WARNINGS

Syntax

WB_RT_GET_NUMBER_OF_WARNINGS(audit_id)

Purpose

This function returns the number of warnings recorded for the job executions represented by audit_id. It returns null if audit_id does not exist.

Example

The following example returns the number of warnings generated by the job execution whose audit ID is 54632. You can then perform different actions based on the number of warnings.

declare   audit_is NUMBER := 54632;
   l_warnings NUMBER;begin   l_ warnings:= WB_RT_GET_NUMBER_OF_WARNINGS (audit_id);
   if l_warnings < 5 then
      .....
   else
      .....
   end if;
end;

WB_RT_GET_PARENT_AUDIT_ID

Syntax

WB_RT_GET_PARENT_AUDIT_ID(audit_id)

Purpose

This function returns the audit id for the process that owns the job execution represented by audit_id. It returns null if audit_id does not exist. You can then use the returned audit id as a key into other public views such as ALL_RT_AUDIT_EXECUTIONS, or other Control Center transformations if further information is required.

Example

The following example retrieves the parent audit ID for a job execution whose audit ID is 76859. It then uses this audit ID to determine the elapsed time for the parent activity. You can perform different actions based on the elapsed time of the parent activity.

declare
   audit_id NUMBER := 76859;
   l_elapsed_time NUMBER;   l_parent_id NUMBER;begin   l_parent_id := WB_RT_GET_PARENT_AUDIT_ID(audit_id);   l_elapsed_time := WB_RT_GET_ELAPSED_TIME(l_parent_id);
   if l_elpased_time < 100 then
      .....
   else
      .....
   end if;
end;

WB_RT_GET_RETURN_CODE

Syntax

WB_RT_GET_RETURN_CODE(audit_id)

Purpose

This function returns the return code recorded for the job execution represented by audit_id. It returns null if audit_id does not exist. For a successful job execution, the return code is greater than or equal to 0. A return code of less than 0 signifies that the job execution has failed.

Example

The following example retrieves the return code for the job execution whose audit ID is represented by audit_id.

declare   audit_id NUMBER:=69;
   l_code NUMBER;begin   l_code:= WB_RT_GET_RETURN_CODE(audit_id);end;

WB_RT_GET_START_TIME

Syntax

WB_RT_GET_START_TIME(audit_id)

Purpose

This function returns the start time for the job execution represented by audit_id. It returns null if audit_id does not exist. For example, you can use this in a transition if you wanted to make a choice dependent on when the previous activity started.

Example

The following example determines the start time of the job execution whose audit ID is 354.

declare   audit_id NUMBER:=354;
   l_date TIMESTAMP WITH TIME ZONE;begin   l_date := WB_RT_GET_START_TIME(audit_id);end;

WB_CAL_MONTH_NAME

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

WB_CAL_MONTH_OF_YEAR

Syntax

WB_CAL_MONTH_OF_YEAR(attribute)

Purpose

WB_CAL_MONTH_OF_YEAR returns the month (1 to 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

WB_CAL_MONTH_SHORT_NAME

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

WB_CAL_QTR

Syntax

WB_CAL_QTR(attribute)

Purpose

WB_CAL_QTR returns the quarter of the Gregorian calendar year (for example Jan - March = 1) 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_QTR (sysdate) quarter
FROM DUAL;

   QUARTER
----------
         1

SELECT WB_CAL_QTR ('26-MAR-2002') quarter
FROM DUAL;

   QUARTER
----------
         1

WB_CAL_WEEK_OF_YEAR

Syntax

WB_CAL_WEEK_OF_YEAR(attribute)

Purpose

WB_CAL_WEEK_OF_YEAR returns the week of the year (1 to 53) 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_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

Syntax

WB_CAL_YEAR(attribute)

Purpose

WB_CAL_YEAR returns the numerical year component 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 (sysdate) year
FROM DUAL;

      YEAR
----------
      2002

SELECT WB_CAL_YEAR ('26-MAR-2002') w_of_y
FROM DUAL;

      YEAR
----------
      2002

WB_CAL_YEAR_NAME

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

WB_DATE_FROM_JULIAN

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

WB_DAY_NAME

Syntax

WB_DAY_NAME(attribute)

Purpose

WB_DAY_NAME returns the full name of the day for the date in 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

WB_DAY_OF_MONTH

Syntax

WB_DAY_OF_MONTH(attribute)

Purpose

WB_DAY_OF_MONTH returns the day number within the month for the date in 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

WB_DAY_OF_WEEK

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

WB_DAY_OF_YEAR

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

WB_DAY_SHORT_NAME

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

WB_DECADE

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

WB_HOUR12

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

WB_HOUR12MI_SS

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

WB_HOUR24

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

WB_HOUR24MI_SS

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

WB_IS_DATE

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.

WB_JULIAN_FROM_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

WB_MI_SS

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

WB_WEEK_OF_MONTH

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

WB_LOOKUP_NUM (on a number)

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.

WB_LOOKUP_NUM (on a varchar2)

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.

WB_IS_NUMBER

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.

WB_OLAP_AW_PRECOMPUTE

Syntax

WB_OLAP_AW_PRECOMPUTE(p_aw_name, p_cube_name, p_measure_name, p_allow_parallel_ solve, p_max_job_queues_allocated)

where p_aw_name is the name of the AW where cube is deployed, p_cube_name is the name of the cube to solve, p_measure_name is the optional name of a specific measure to solve (if no measure is specified, then all measures will be solved), p_allow_parallel_solve is the boolean to indicate parallelization of solve based on partitioning (performance related parameter), p_max_job_queues_allocated is the number of DBMS jobs to execute in parallel (default value is 0). If 5 is defined and there are 20 partitions then a pool of 5 DBMS jobs will be used to perform the data load.There is a subtle different between parallel and non-parallel solving. With non-parallel solve, the solve happens synchronously, so when the API call is completed the solve is complete. Parallel solve executes asynchronously, the API call will return with a job id of the job started. The job will control parallel solving using the max job queues parameter to control its processing. The user may then use the job id to query the all_scheduler_* views to check on the status of the activity.

Purpose

WB_OLAP_AW_PRECOMPUTE is used for solving a non-compressed cube (compressed cubes are auto-solved). The load and solve steps can be done independently. By default, the cube map loads data, then solves (precomputes) the cube. You can load data using the map, then perform the solve at a different point of time (since the solve/build time is the costliest operation).

Example

The following example loads data from the relational cubes MART and SALES_CUBE into a cube called SALES and performs a simple solve execution working serially. This example has parameters for parallel solve and max number of job queues. If parallel solve is performed then an ASYNCHRONOUS solve job is started and the master job ID is returned via the return function.

declare
  rslt varchar2(4000);
begin
…
  rslt :=wb_olap_aw_precompute('MART','SALES_CUBE','SALES');
…
end;
/

WB_OLAP_LOAD_CUBE

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')

WB_OLAP_LOAD_DIMENSION

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')

WB_OLAP_LOAD_DIMENSION_GENUK

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')

REPLICATE

Syntax

REPLICATE(lcr, conflict_resolution)

where lcr stands for Logical Change Record and encapsulates the DML change. Its data type is SYS.LCR$_ROW_RECORD. conflict_resolution is a Boolean variable. If its value is TRUE, any conflict resolution defined for the table will be used to resolve conflicts resulting from the execution of the LCR. For more information about conflict resolution, see Oracle Streams Replication Administrator's Guide.

Purpose

REPLICATE is used to replicate a DML change (INSERT, UPDATE, or DELETE) that has occurred on a table in the source system on an identical table in the target system. The table in the target system should be identical to the table in the source system in the following respects:.

• The name of the schema that contains the target table should be the same as the name of the schema that contains the source table.

• The name of the target table should the same as the name of the source table.

• The structure of the target table should be the same as that of the source table. The structure includes the number, name, and data type of the columns in the table.

Example

Consider a table T1(c1 varchar2(10), c2 number primary key) in schema S on the source system and an identical table in the target system. Consider the following insert operation on the table T1 on the source system

insert into T1 values ('abcde', 10)

An LCR representing the change following the above insert of a row on the table T1 in the source system will have the following details

LCR.GET_OBJECT_OWNER will be 'S'
LCR.GET_OBJECT_NAME will be 'T1'
LCR.GET_COMMAND_TYPE will be 'INSERT'
LCR.GET_VALUE('c1', 'new') will have the value for the column 'c1' - i.e. 'abcde'
LCR.GET_VALUE('c2', 'new') will have the value for the column 'c2' - i.e. 10

Such an LCR will be created and enqueued by a Streams Capture Process on the source system that captures changes on table S.T1

REPLICATE(lcr, true) - will result in a row ('abcde', 10) being inserted into the table T1 on the target system.

WB_XML_LOAD

Syntax:

WB_XML_LOAD(control_file)

Purpose

This program unit 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 about control files, see the Oracle Warehouse Builder User's Guide.

WB_XML_LOAD_F

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 about the types handled and detailed information about control_files, see the Oracle Warehouse Builder Installation and Administration Guide for Windows and UNIX.