OWB 11gR2 – Table Functions

Here is a follow-up on an old post on table functions to illustrate how they work in 11gR2. There were some changes in 11gR2 to support table functions as a design object rather than a best practice, below is the OMB for the examples from the older post here.

11gR2 Scalar Table Function Example

The code for the scalar example is pretty much the same apart for unbound table functions the default operator has a different output group name and no input group (really useful..not).

# ===============================================
# 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 INPUT GROUP 'INGRP1' OF OPERATOR 'GET_EMPS'

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 'RETURN' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'ENAME' OF GROUP 'RETURN' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256')
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'LVL' OF GROUP 'RETURN' 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 'RETURN' OF OPERATOR 'GET_EMPS' TO GROUP 'INOUTGRP1' OF OPERATOR 'EMPLOYEES'

This produces the following mappings and generates the code illustrated in the Generation Results panel;

image

 

11gR2 Ref Cursor Table Function Example

The ref cursor example is now a little different but when you reflect on what the actual table function definition is (look at the parameters, its a ref cursor) then it makes some sense. A ref cursor is build using the CONTRUCT_OBJECT operator, that might not have been obvious.

# ===============================================
# 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'  ADD INPUT GROUP 'INGRP1' OF OPERATOR 'GET_EMPS'

OMBALTER MAPPING 'TF_REFCURSOR_PARAM'  ADD CONSTRUCT_OBJECT OPERATOR 'SYS_REFCURSOR'

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C1' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO GROUP 'INGRP1' OF OPERATOR 'SYS_REFCURSOR'
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C2' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO GROUP 'INGRP1' OF OPERATOR 'SYS_REFCURSOR'
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C3' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO GROUP 'INGRP1' OF OPERATOR 'SYS_REFCURSOR'

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' MODIFY ATTRIBUTE 'OUTPUT1' OF GROUP 'OUTGRP1' OF OPERATOR 'SYS_REFCURSOR' SET PROPERTIES (DATATYPE) VALUES ('SYS_REFCURSOR')

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'OUTPUT1' OF GROUP 'OUTGRP1' OF OPERATOR 'SYS_REFCURSOR' TO GROUP 'INGRP1' OF OPERATOR 'GET_EMPS'

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

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

When you execute this it builds the following mapping and generates the following code;

image

11gR2 Table Function Import

The table function operator can operate on bound and unbound table functions, the table functions can be imported from the data dictionary, so the database import has been extended to include them and can be captured into the OWB design repository.

image

So can now import the examples from the earlier post rather than defining via OMB;

image

The import will import any dependent objects such as types and nested tables;

image

Note I had success with this example but if there are PLSQL types and collections then this didn’t work (such as the ones from JP’s blog post on MapReduce with table functions).

If you import the table functions then you can just drop the table function into the mapping canvas and build as normal;

image

When the table function is dropped on to the canvas you will see the input is defined with type SYS_REFCURSOR, so you must provide a ref cursor as I described above using the CONSTTRUCT_OBJECT operator.

image

Anyway that’s a very quick run through that hopefully will help illustrate how this hangs together.

Comments:

Hi David, This works like a charm. Thx for claring tnis up for me.:-)

Posted by Michael Reitsma on April 27, 2011 at 03:47 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today