OWB 11gR2 – Mappings and Inline SQL
By David Allan-Oracle on Aug 13, 2010
'Let me define the SQL' was the cry! Quick post on one of those many little features that was asked for over the years - inline view, inline SQL, SQL override...whatever you want to call it.
Now in 11gR2 you can use the View operator in a mapping to place your own SQL inline within the mapping. The view operator has a couple of additional properties in 11gR2;
- Inlined - If set to true, the view query will be used as an inline sub-query in the generated code. (OMB view operator property is INLINED)
- View Query - The SQL query (OMB view operator property is VIEW_QUERY)
These properties enable you to define the SQL you'd like to embed plus indicate that it is to be used. If you don't click the inlined property the view name will be used in the generated code rather than the SQL defined for it.
In the example below there is a view operator with the inlined property enabled and the SQL query defined (a simple 'select empno,ename from emp'). The columns in the view operator must match those projected in the SQL query.
When the code is generated the SQL defined in the 'View Query' property is simply placed inline in the overall generated code for the mapping.
The example above illustrates a view operator in a mapping that is what we term 'unbound', there is no view data object defined in OWB that is linked to, the query is defined in the mapping and that's about it. You can also define a view data object with the SQL query and share across mappings if it happens to be common, then you can at least have some reuse rather than having the SQL duplicated all over.
Judging by some of the comments recently on LinkedIn this capability is welcomed by many, so hopefully more will find this useful.