Example of using Tk GUI (swank) in OMB*Plus

It is a preview of GUI configurator for SCD2 mapping generator script.

namespace eval ::owbeg {}

set currContext [OMBDCC]
set currCtxType [lindex $currContext 0]
set currContext [lindex $currContext 1]
#check current context type?
set tgtTableList [OMBLIST TABLES]
set tgtSeqList [OMBLIST SEQUENCES]

set ::owbeg::V_MAP_NAME MAP_TEST
set ::owbeg::P_SRC_TABLE [file join $currContext [lindex $tgtTableList 0]]
set ::owbeg::G_PARTYID_LIST bk1
set ::owbeg::P_TGT_TABLE [file join $currContext [lindex $tgtTableList 0]]
set ::owbeg::G_DET_PARTYID_LIST partyid
set ::owbeg::G_DET_HISTID_NAME HIST_ID
set ::owbeg::G_DET_STARTDATE_NAME start_date
set ::owbeg::G_DET_FINALDATE_NAME end_date
set ::owbeg::P_HISTID_SEQ seq_1

set tgtTableColumns [OMBRETRIEVE TABLE '$::owbeg::P_TGT_TABLE' GET COLUMNS]

wm title . "Parameters for SCD2 mapping generator"

frame .fttl
frame .fbot
frame .fsrc
frame .ftgt

label .fttl.lb_mapname -text "Mapping name"
entry .fttl.entr_mapname -textvariable ::owbeg::V_MAP_NAME -width 40
grid .fttl.lb_mapname
grid .fttl.entr_mapname

button .fbot.butok -text "Generate" -command "run_gen_proc"
grid .fbot.butok


label .fsrc.lb_srctab -text "Source table"
button .fsrc.but_srctab -text "Choose" -command "chooseSrcTable"
entry .fsrc.entr_srctab -textvariable ::owbeg::P_SRC_TABLE -width 25
label .fsrc.lb_srcbuskey -text "BusinessKey column"
jcombobox .fsrc.cb_srcbuskey -variable ::owbeg::G_PARTYID_LIST -width 10
eval ".fsrc.cb_srcbuskey item append $tgtTableColumns"
grid .fsrc.lb_srctab .fsrc.but_srctab -sticky wn
grid .fsrc.entr_srctab -columnspan 2 -sticky w
grid .fsrc.lb_srcbuskey -columnspan 2 -sticky w
grid .fsrc.cb_srcbuskey -columnspan 2 -sticky w

label .ftgt.lb_tgttab -text "Target table"
button .ftgt.but_tgttab -text "Choose" -command "chooseTgtTable"
entry .ftgt.entr_tgttab -textvariable ::owbeg::P_TGT_TABLE -width 25
label .ftgt.lb_tgtbuskey -text "BusinessKey column"
jcombobox .ftgt.cb_tgtbuskey -variable ::owbeg::G_DET_PARTYID_LIST -width 10
eval ".ftgt.cb_tgtbuskey item append $tgtTableColumns"
label .ftgt.lb_tgthistid -text "Dimension key column"
jcombobox .ftgt.cb_tgthistid -variable ::owbeg::G_DET_HISTID_NAME -width 10
eval ".ftgt.cb_tgthistid item append $tgtTableColumns"
label .ftgt.lb_tgtstrtdate -text "Start date column"
jcombobox .ftgt.cb_tgtstrtdate -variable ::owbeg::G_DET_STARTDATE_NAME -width 10
eval ".ftgt.cb_tgtstrtdate item append $tgtTableColumns"
label .ftgt.lb_tgtenddate -text "End date column"
jcombobox .ftgt.cb_tgtenddate -variable ::owbeg::G_DET_ENDDATE_NAME -width 20
eval ".ftgt.cb_tgtenddate item append $tgtTableColumns"
label .ftgt.lb_seqname -text "Dimension key sequence"
jcombobox .ftgt.cb_seqname -variable ::owbeg::P_HISTID_SEQ -width 20
eval ".ftgt.cb_seqname item append $tgtSeqList"
grid .ftgt.lb_tgttab .ftgt.but_tgttab -sticky w
grid .ftgt.entr_tgttab -columnspan 2 -sticky w
grid .ftgt.lb_tgtbuskey -columnspan 2 -sticky w
grid .ftgt.cb_tgtbuskey -columnspan 2 -sticky w
grid .ftgt.lb_tgthistid -columnspan 2 -sticky w
grid .ftgt.cb_tgthistid -columnspan 2 -sticky w
grid .ftgt.lb_tgtstrtdate -columnspan 2 -sticky w
grid .ftgt.cb_tgtstrtdate -columnspan 2 -sticky w
grid .ftgt.lb_tgtenddate -columnspan 2 -sticky w
grid .ftgt.cb_tgtenddate -columnspan 2 -sticky w
grid .ftgt.lb_seqname -columnspan 2 -sticky w
grid .ftgt.cb_seqname -columnspan 2 -sticky w

grid .fttl -columnspan 2
grid .fsrc .ftgt -sticky n
grid .fbot -columnspan 2

proc chooseSrcTable {} {
  tk_messageBox -message "Choose source table" -type ok
}
proc chooseTgtTable {} {
  tk_messageBox -message "Choose target table" -type ok
}

proc run_gen_proc {} {
  destroy .fttl
  destroy .fbot
  destroy .fsrc
  destroy .ftgt
  puts $::owbeg::G_PARTYID_LIST
}

proc tab_dlg_choose {} {
puts "[.jd.f.tree path get]"
destroy .jd
}

proc tree_dialog {} {
jdialog .jd
frame .jd.f
button .jd.f.butok -text OK -command "tab_dlg_choose"
.jd add .jd.f
jtree .jd.f.tree
.jd.f.tree node add root "OWB"
.jd.f.tree node add -1 "Tables"
.jd.f.tree node add -1 "Views"
.jd.f.tree node add 0 "T1"
.jd.f.tree node add 0 "T2"
.jd.f.tree node add 0 "T3"
.jd.f.tree node add 0 "T4"
.jd.f.tree node add 1 "T1"
.jd.f.tree node add 1 "T1"
.jd.f.tree node add 1 "T1"
.jd.f.tree node add 1 "T1"
pack .jd.f.butok -side bottom
pack .jd.f.tree -side top
.jd configure -modal 1 -visible 1
}

# tree_dialog

proc my_dialog {} {
namespace eval ::mydlgproc {}
set ::mydlgproc::newTab ""
set newt2 ""
jdialog .jd
frame .jd.f
button .jd.f.butok -text OK -command {puts $::mydlgproc::newTab ; set ::mydlgproc::newTab aa; destroy .jd;}
.jd add .jd.f
pack .jd.f.butok -side bottom
.jd configure -visible 1
vwait ::mydlgproc::newTab
set newt2 $::mydlgproc::newTab
namespace delete ::mydlgproc
return $newt2
}

OMB*Plus extensibility - Tk GUI in OMB*Plus

One of the important feature of OWB10g R2 is integration into design client Tcl-based scripting language (OMB*Plus) enhaced with GUI componets - OWB Expert. OWB Expert allow execution of OMB script dirrectly from design client (it is possible to add new command to context menu called from project tree). GUI components play secondary role - the main purpose of these components is setting of paramters passed to OMB script, for example there is component for choosing table from specified module (so usually GUI component perform single task). In most cases for execution of simple task we needed to compose a sequence of sevaral components (choosing module -> choosing table, etc.) - it was quite inconvenient.Maybe the most interesting component is Custom Dialog (allow create dialog windows with several items like text field, label, radio button, etc.), but it has one drawback - this window is non-interactive (there is no way to specify reply for user action, like choosing new value in list, enabling/disabling check box, etc.).

Tcl interpreter allows to write GUI application with Tk extension. And as I explored there is GUI extension for Jacl Tcl-interpreter (OMB*Plus is based on Jacl/tcljava). Name of this extension is SWANK.

Link to swank

Last jar-file of swank compiled with lates Java compiler, so the best way to experiment - use last OWB release (OWB11g R2 which use Java 6 for execution).
Configuration steps:

• copy swank.jar file to OWB_HOME\owb\lib\ext directory;
• add to ombinit.tcl file (OWB_HOME\owb\bin/admin directory) at the end line
  source -url resource:/com/onemoonscientific/swank/library/init.tcl.

OMB*Plus example with Tk widgets:

namespace eval ::owbland {}


OMBCC '/'
set projList [OMBLIST PROJECTS]
set ::owbland::res "a"
jcombobox .combproj -variable ::owbland::res -command "procCSEL"
foreach proj $projList {
.combproj item append $proj
}
pack .combproj -side top


listbox .projlist -height 5
pack .projlist -side bottom


wm title . "Super;) Module Viewer"
wm geometry . 400x250


proc procCSEL {} {
  #puts "selected"
  OMBCC '/$::owbland::res'
  if {[catch {set modList [OMBLIST ORACLE_MODULES]} err]} {
    puts $err
  } else {
    #puts $modList
    set lstSz [.projlist size]
    .projlist delete 0 [expr {$lstSz-1}]
    foreach mod $modList {.projlist insert end $mod}
  }
}

OMB*Plus extensibility - commands redefinition at startup with ombinit.tcl

There is simpler solution for defining commands during OMB*Plus startup - at startup OMB*Plus executes ombinit.tcl script located in OWB_HOME\owb\bin\admin directory.
This file contains definition of several procedures and version from OWB10g R2 also contains code for correcting of multiple path in TCLLIBPATH. In previous post (part 1 of OMB*Plus extensibility) I said that it is impossible to define more than one path in TCLLIBPATH - but with this procedure in ombinit.tcl TCLLIBPATH allow multiple paths sepparated by space.

Here is another addition to previous post - how to modify auto_mkindex to work.
As I said tcljava implementation of auto_mkindex contains a bug (it exist even in last tcljava release).
auto_mkindex is a general Tcl procedure which defined in init.tcl file (in tcljava implementation this file located in jacl.jar archive).
The first we need extract init.tcl file from archive - start command line in Windows and execute commands (suppose OWB installed in c:\oracle\owb10g_r2):

C:\>cd \oracle\owb10g_R2\owb\lib\int
C:\Oracle\owb10g_R2\owb\lib\int>..\..\..\jdk\bin\jar -xf jacl.jar tcl/lang/library/init.tcl
C:\Oracle\owb10g_R2\owb\lib\int>

As result we get file C:\Oracle\owb10g_R2\owb\lib\int\tcl\lang\library\init.tcl, we need to modify it  -
find in file line

puts $f $index nonewline

and replace it with

puts -nonewline $f $index

Now from command line move modified file back to archive

C:\Oracle\owb10g_R2\owb\lib\int>..\..\..\jdk\bin\jar -uf jacl.jar tcl\lang\library\init.tcl

Now auto_mkindex works under OMB*Plus:

OMB*Plus: Release 10.2.0.3.33
Copyright (c) 2000, 2006, Oracle. All rights reserved.
OMB+> auto_mkindex c:/oracle/tcllib myprocedure.tcl
OMB+>

OMB*Plus extensibility

OMB*Plus extensibility - defining new command automatically during startup.

There are several ways to extend features of OMB*Plus for development.
OMB*Plus is an TCL interpreter based on tcljava implementation (look at OWB_HOME\owb\lib\int directory - you will find jacl.jar and tcljava.jar files which are the core of tcljava interpreter).

It is quite simple in Tcl (and in OMB*Plus) add new command - after definition of new procedure you will get new command with the name of just created procedure:

OMB*Plus: Release 10.2.0.3.33
Copyright (c) 2000, 2006, Oracle. All rights reserved.
OMB+> proc MYCOMMAND {txt} {
>   return $txt
> }
OMB+> MYCOMMAND "Text from procedure"
Text from procecure
OMB+>

If you have a lot such procedures (and use them extensively) you can include them in single file and execute this file with source command each time you start OMB*Plus.
It is possible to automate this procedure with Tcl auto_load (search for auto_load or auto_path on wiki.tcl.tk site) feature. When Tcl interpreter cannot find called command withing standard (internal) commands or defined procedures it looks directory from auto_path variable for definitions of this command via tclIndex file (this file contains for each "external" command/procedure reference to file with it definition), run file with definition this command (via source Tcl command) and finally - run requested command.

To enable auto_load feature in OMB*Plus we should make three things:

• create file with definition of new procedure and place this file in selected directory (say to c:\oracle\tcllib)
• create tclIndex file in the same directory
• guide OMB*Plus where look for definitions of new procedures

First task is clear - place our MYCOMMAND procedure in file with name MyProcedure.tcl under c:\oracle\tcllib directory.
Tcl contains standard command auto_mkindex for creation tclIndex file, but tcljava implementation contains bug which prevent using this command. So we will create tclIndex file manualy (the first line is VERY IMPORTANT!):

# Tcl autoload index file, version 2.0
set auto_index(MYCOMMAND) [list source [file join $dir MyProcedure.tcl]]

Now if we add to auto_path variable our directory (MyProcedure) we will get possibility to run commands indexed in tclIndex file:

OMB+> puts $auto_path
resource:/tcl/lang/library
OMB+> info commands MY*
OMB+> MYCOMMAND
invalid command name "MYCOMMAND"
OMB+> lappend auto_path c:/oracle/tcllib
resource:/tcl/lang/library c:/oracle/tcllib
OMB+> puts $auto_path
resource:/tcl/lang/library c:/oracle/tcllib
OMB+> info commands MY*
OMB+> MYCOMMAND "Text to display"
Text to display
OMB+> info commands MY*
MYCOMMAND
OMB+>

And last thing - to set auto_path automatically it is enogh to define new environment variable TCLLIBPATH (the only problem - it seems impossible to define more than one directory via TCLLIBPATH, general method for sepparating paths on Windows OS with semicolon doesn't work for TCLLIBPATH).

Generate mapping creation tcl script

I started development of OMB*Plus program for generating mapping creation script (sometime it is better to use OMB for moving OWB objects to other environment).

Current status - implemented processing for 50% of operators (see table below) and processed a limited list of operator properties (for example not processed EXTRACTION_HINTS, SCHEMA, etc. properties). You can download it from map_reconstruct.tcl.

Implemented operator processing

Operator type	Planed to implement	Implemented	Processed operator properties
ADVANCED_QUEUE
AGGREGATOR	x	x	GROUP_BY_CLAUSE, HAVING_CLAUSE
ANYDATA_CAST
CONSTANT	x	x	EXPRESSION
CONSTRUCT_OBJECT
CUBE	?
DATA_GENERATOR
DEDUPLICATOR	x	x	-
DIMENSION	?
EXPAND_OBJECT
EXPRESSION	x	x	EXPRESSION
EXTERNAL_TABLE	x	x
FILTER	x	x	FILTER_CONDITION
FLAT_FILE	?
INPUT_PARAMETER	x	x
INPUT_SIGNATURE
ITERATOROPERATOR
JOINER	x	x	JOIN_CONDITION
KEY_LOOKUP	x	x
LCRCAST
LCRSPLITTER
MATCHMERGE
MATERIALIZED_VIEW
NAME_AND_ADDRESS
OUTPUT_PARAMETER	x	x
OUTPUT_SIGNATURE
PIVOT	x
PLUGGABLE_MAPPING	?
POSTMAPPING_PROCESS	x
PREMAPPING_PROCESS	x
SEQUENCE	x	x
SET_OPERATION	x
SORTER	?
SPLITTER	x	x
TABLE	x	x	LOAD_COLUMN_WHEN_INSERTING_ROW, LOAD_COLUMN_WHEN_UPDATING_ROW, MATCH_COLUMN_WHEN_UPDATING_ROW, MATCH_COLUMN_WHEN_DELETING_ROW, UPDATE_OPERATION
TABLE_FUNCTION
TRANSFORMATION	x	x
UNPIVOT	x
VIEW	x	x	LOAD_COLUMN_WHEN_INSERTING_ROW, LOAD_COLUMN_WHEN_UPDATING_ROW, MATCH_COLUMN_WHEN_UPDATING_ROW, MATCH_COLUMN_WHEN_DELETING_ROW, UPDATE_OPERATION

OWB11g R2 impressions

Standalone version of OWB11g R2 for Windows has been released in april 2010. This event pushed me to deep learning of new OWB release.

What is good:

1) now OWB is multiplatform tool which allow to use any RDBMS as target

What is bad:

1) bug which prevent deployment of processflow packages from Design Center/Control Center Manager

2) by default OWB generate execution unit in Template Mapping with the same name (worlaround - always rename execution unit and that name is unique across target/stage database)

3) substitution API differs from ODI (API calls work in different way than in ODI)
4) new OWB GUI is less usable (slower and provide smaler working area in mapping/processflow editors) compared to previous release