Oracle Warehouse Builder (OWB) Weblog: June 3, 2008 Archives

Using table functions is a useful way to both boost performance and extend the source/target capabilities in OWB. Here we will see how to setup table functions as a source and a target in OWB. Table functions let you define a set of PL/SQL statements that when queried, will behave like a standard SQL query to a table. We will do this by simple examples to illustrate how it hangs together.

Firstly let's define 2 maps, both use the table function as a source, one uses a table function which takes a scalar parameter, the other takes a REF CURSOR, so an arbitrary SQL query can be passed as a parameter. The functions are skeletal just to illustrate how it all works. You can also have a table function as a target so can write to systems using APIs for example, we will look at this later.

Scalar Table Function Example

The scalar table function example uses a constant (a scalar) as a parameter to the table function, and the results are returned from the table function and then the map writes to rows returned to the target table.

Table Function Scalar Source:

Here is the OMB script to create the table function as source example (script 1).

# ===============================================
# Source: Scalar TF Map:
# ===============================================
OMBCREATE MAPPING 'TF_SCALAR_PARAM' ADD TABLE_FUNCTION OPERATOR 'GET_EMPS' SET PROPERTIES (TABLE_FUNCTION_NAME) VALUES ('SCOTT.SCALARF')

OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD INPUT_PARAMETER OPERATOR 'INPUTS'
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'FILTERREC' OF GROUP 'OUTGRP1' OF OPERATOR 'INPUTS' SET PROPERTIES (DATATYPE,DEFAULT_VALUE) VALUES ('NUMBER', '0')

OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'FILTER_EMP' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'EMPNO' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'ENAME' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256')
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'LVL' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')

OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'FILTERREC' OF GROUP 'OUTGRP1' OF OPERATOR 'INPUTS' TO ATTRIBUTE 'FILTER_EMP' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS'

OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD TABLE OPERATOR 'EMPLOYEES'
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD CONNECTION FROM GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' TO GROUP 'INOUTGRP1' OF OPERATOR 'EMPLOYEES'

Ref Cursor Table Function Example

An arbitrary SQL query can also be passed in as a ref cursor into the table function, you define the group with this information in order to get the correct SQL generated;

Table Function Ref Cursor Source:

The table function which is referenced is defined via the property below, if it is in a different schema you prefix the name with the schema name;

Table Function Setup:

Here is the OMB script to create the table function as source example using a ref cursor (script 2).

# ===============================================
# Source: Ref Cursor TF Map:
# ===============================================
OMBCREATE MAPPING 'TF_REFCURSOR_PARAM' ADD TABLE_FUNCTION OPERATOR 'GET_EMPS' SET PROPERTIES (TABLE_FUNCTION_NAME) VALUES ('SCOTT.REFCURSORF')

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD TABLE OPERATOR 'SRC'
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'C1' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'C2' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'C3' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' MODIFY GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (GROUP_TYPE) VALUES ('REF_CURSOR')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'EMPNO' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'ENAME' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'LVL' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'EMPNO' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'ENAME' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'LVL' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C1' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO ATTRIBUTE 'EMPNO' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS'
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C2' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO ATTRIBUTE 'ENAME' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS'
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C3' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO ATTRIBUTE 'LVL' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS'

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD TABLE OPERATOR 'EMPLOYEES_SAL'
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' TO GROUP 'INOUTGRP1' OF OPERATOR 'EMPLOYEES_SAL'

Types and Table Functions for Source Examples

Here are the types and table functions created in SCOTT, you will have to grant execute on your functions to the target schema;

CREATE OR REPLACE TYPE emp_obj AS OBJECT(
empno NUMBER,
ename VARCHAR2(255),
lvl number
);
/

CREATE OR REPLACE TYPE tb_emp_obj AS TABLE OF emp_obj;
/

CREATE OR REPLACE FUNCTION REFCURSORF(input_values sys_refcursor) RETURN tb_emp_obj pipelined AS
out_pipe emp_obj := emp_obj(null,null,null);
ename VARCHAR2(255):=null;
empno number; lvl NUMBER;
BEGIN
LOOP
    FETCH input_values INTO empno, ename, lvl;
    EXIT WHEN input_values%NOTFOUND;
    out_pipe.empno:=empno;
    out_pipe.ename:=ename;
    out_pipe.lvl:=lvl;
    PIPE ROW(out_pipe);
END LOOP;
CLOSE input_values;
RETURN;
END;
/

CREATE OR REPLACE FUNCTION SCALARF(input_val NUMBER) RETURN tb_emp_obj pipelined AS
out_pipe emp_obj := emp_obj(null,null,null);
ename VARCHAR2(255):=null;
empno number; lvl NUMBER;
cursor INPUT_VALUES is select empno, ename, sal from emp;
BEGIN
open INPUT_VALUES;
LOOP
    FETCH INPUT_VALUES INTO empno, ename, lvl;
    EXIT WHEN input_values%NOTFOUND;
    out_pipe.empno:=empno;
    out_pipe.ename:=ename;
    out_pipe.lvl:=lvl;
    PIPE ROW(out_pipe);
END LOOP;
CLOSE input_values;
RETURN;
END;
/

Target Table Function Example

With the target table function example there is a flag on the operator to define that the table function is being used as a target in the mapping. The table function as target in OWB must return one row, in the example below I use OWB to aggregate EMP then the ref cursor is passed to the table function, the table function inserts the aggregated values into another table. You will not get the audit details written to the OWB runtime and needs some error handling, but these kind of things are possible. Also the table function scenario as target would be possible if you use the autonomous pragma transaction within the table function as below.

Table Function Targets:

Here is the OMB script to create the table function as target example (script 3).

set map_name LOAD_WITH_TABFUN
OMBCREATE MAPPING '$map_name' ADD TABLE_FUNCTION OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (TABLE_FUNCTION_NAME,TABLE_FUNCTION_IS_TARGET) VALUES ('TG_TGT', 'true')

OMBALTER MAPPING '$map_name' ADD TABLE OPERATOR 'EMP' BOUND TO TABLE '../SCOTT/EMP'
OMBALTER MAPPING '$map_name' ADD ATTRIBUTE 'DEPTNO' OF GROUP 'INGRP1' OF OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')

OMBALTER MAPPING '$map_name' ADD ATTRIBUTE 'SALARY' OF GROUP 'INGRP1' OF OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')

OMBALTER MAPPING '$map_name' ADD ATTRIBUTE 'STATUS' OF GROUP 'OUTGRP1' OF OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING '$map_name' MODIFY GROUP 'INGRP1' OF OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (GROUP_TYPE) VALUES ('REF_CURSOR')

OMBALTER MAPPING '$map_name' ADD AGGREGATOR OPERATOR 'AGG'

OMBALTER MAPPING '$map_name' ADD CONNECTION FROM ATTRIBUTE 'DEPTNO' OF GROUP 'INOUTGRP1' OF OPERATOR 'EMP' TO GROUP 'INGRP1' OF OPERATOR 'AGG'
OMBALTER MAPPING '$map_name' ADD CONNECTION FROM ATTRIBUTE 'SAL' OF GROUP 'INOUTGRP1' OF OPERATOR 'EMP' TO GROUP 'INGRP1' OF OPERATOR 'AGG'

OMBALTER MAPPING '$map_name' MODIFY OPERATOR 'AGG' SET PROPERTIES (GROUP_BY_CLAUSE) VALUES ('INGRP1.DEPTNO')

OMBALTER MAPPING '$map_name' ADD ATTRIBUTE 'SAL' OF GROUP 'OUTGRP1' OF OPERATOR 'AGG' SET PROPERTIES (DATATYPE, EXPRESSION) VALUES ('NUMBER', 'SUM(INGRP1.SAL)')

OMBALTER MAPPING '$map_name' ADD CONNECTION FROM ATTRIBUTE 'DEPTNO' OF GROUP 'OUTGRP1' OF OPERATOR 'AGG' TO ATTRIBUTE 'DEPTNO' OF GROUP 'INGRP1' OF OPERATOR 'WRITE_TO_ANYTHING'

OMBALTER MAPPING '$map_name' ADD CONNECTION FROM ATTRIBUTE 'SAL' OF GROUP 'OUTGRP1' OF OPERATOR 'AGG' TO ATTRIBUTE 'SALARY' OF GROUP 'INGRP1' OF OPERATOR 'WRITE_TO_ANYTHING'

OMBALTER MAPPING '$map_name' MODIFY GROUP 'OUTGRP1' OF OPERATOR 'WRITE_TO_ANYTHING'
SET PROPERTIES (RETURN_TABLE_OF_SCALAR) VALUES ('true')

OMBALTER MAPPING '$map_name' MODIFY OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (TABLE_FUNCTION_IS_TARGET) VALUES ('true')

#Work around bug with target table function and generation.
catch {OMBCOMPILE MAPPING '$map_name'}
OMBALTER MAPPING '$map_name' SET PROPERTIES (GENERATION_MODE, DEFAULT_OPERATING_MODE) VALUES ('SET_BASED', 'SET_BASED')
catch {OMBCOMPILE MAPPING '$map_name'}

Here is the demo table function used;

create or replace function TF_TGT(RC IN SYS_REFCURSOR
                          ) return TBL_OF_STRINGS PIPELINED
as
PRAGMA AUTONOMOUS_TRANSACTION;
DEPTNO number;
SALARY number;
begin
loop
   FETCH RC into DEPTNO,SALARY;
   INSERT INTO TFTGTTAB VALUES (DEPTNO,SALARY);
   EXIT WHEN RC%NOTFOUND;
end loop;
COMMIT;
PIPE ROW('SUCCESS');
end;
/

Here we have seen how the table function is setup in OWB and how we can extend the source and targets using an API based approach. If you would like to see a sample that leverages table functions integrating to web services see the post here - the jpublisher component generates functions and table functions around the web services defined in a WSDL.

Oracle Warehouse Builder (OWB) Weblog

ETL for the Oracle Database, with New 11gR2 Features

June 3, 2008 Archives

June 3, 2008

Table Functions as Source and Target

Tags:

Search All Blogs

Google Search

About June 2008

RSS