OWB 11gR2 – Mappings and Inline SQL

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

owb_inline_view_11gR2_unbound

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.

owb_inline_view_11gR2_code

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.

Comments:

awasome

Posted by Amir Riaz on August 13, 2010 at 07:07 PM PDT #

really very usefull

Posted by nawneet on August 14, 2010 at 07:33 PM PDT #

Is there a method of referencing a schema and database link in the query that would be portable when deploying to different databases? For example:

Select * from dev_schema.table@dev_source_link

This will break, of course, if the schema is owned by "prod_schema" instead of "dev_schema". In a mapping, the problem doesn't exist because OWB will fill in the correct schema and link name.

Posted by guest on August 31, 2011 at 11:05 AM PDT #

I think best you could do is use a synonym or a dev/prod agnostic database link name that is realized differently in each environment.

Cheers
David

Posted by David on August 31, 2011 at 12:08 PM 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