Oracle_TCL_CooooolTooooolCmdLanguage

May 22, 2015

Oracle_TCL_CooooolTooooolCmdLanguage

TCL - The Tool Command Language

TCL - The Tool Command Language allows simple notepad/text file scripts to be written
to do a variety of useful things, including run OMBPLUS scripts for Oracle OWB automation!

One of my too cool tool scripts

OMBCONNECT MYUSER/MYPWD@MYSVR:1577:MYDW
OMBCC 'MYPROJ/DWSTAGE'
OMBALTER MAPPING 'MAP_PSCS_SPMS_PROJECT_STG' ADD TABLE OPERATOR 'STG_PSCS_SPMS_PROJECT' BOUND TO TABLE 'STG_PSCS_SPMS_PROJECT'
OMBALTER MAPPING 'MAP_PSCS_SPMS_PROJECT_STG' ADD TABLE OPERATOR 'PSCS_SPMS_PROJECT_MV' BOUND TO TABLE '../PSCS_DEVN/PSCS_SPMS_PROJECT_MV'
OMBALTER MAPPING 'MAP_PSCS_SPMS_PROJECT_STG' MODIFY OPERATOR 'STG_PSCS_SPMS_PROJECT' SET PROPERTIES (LOADING_TYPE) VALUES ('TRUNCATE/INSERT')
OMBSYNCHRONIZE MAPPING 'MAP_PSCS_SPMS_PROJECT_STG' OPERATOR 'STG_PSCS_SPMS_PROJECT' TO TABLE 'STG_PSCS_SPMS_PROJECT' USE (RECONCILE_STRATEGY 'REPLACE', MATCHING_STRATEGY 'MATCH_BY_OBJECT_NAME')
OMBCOMMIT

Validate a MetaTable
OMBVALIDATE MAPPING 'MAP_PSCS_SPMS_PROJECT_STG' OUTPUT VALIDATION_RESULT TO 'c:/TEMP' WRITE (SUCCESS,ERROR)

Deploy a Mapping
OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN 'DEPLOY_MY_MAP_PLAN'
ADD ACTION 'DEPLOY_MY_MAP_ACTION'
SET PROPERTIES (OPERATION) VALUES ('REPLACE')
SET REFERENCE MAPPING 'MAP_PSCS_SPMS_PROJECT_STG'
Action plan DEPLOY_MY_MAP_PLAN created.
OMB+> OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'DEPLOY_MY_MAP_PLAN'
Deploying MAP_PSCS_SPMS_PROJECT_STG
Deployment completed.
OMB+> OMBCOMMIT

Execute a Mapping

OMB+> OMBEXECUTE MAPPING 'MAP_PSCS_SPMS_PROJECT_STG'

Starting Execution MAP_PSCS_SPMS_PROJECT_STG

Starting Task MAP_PSCS_SPMS_PROJECT_STG

Completing Task MAP_PSCS_SPMS_PROJECT_STG

Completing Execution MAP_PSCS_SPMS_PROJECT_STG

40949 rows inserted

0 rows deleted

0 rows updated

0 rows merged

Run Name is MAP_PSCS_SPMS_PROJECT_STG

Completion Status is OK

Alter a MetaTable

OMBALTER TABLE 'PSCS_LOIS_SUBFIRM' \

ADD COLUMN 'LOIS_SUBFIRM_ID' SET PROPERTIES(DATATYPE, NOT_NULL, PRECISION, SCALE) VALUES ('NUMBER', 'true', 22, 9) \

ADD COLUMN 'LOIS_SUBMITTAL_ID' SET PROPERTIES(DATATYPE, NOT_NULL, PRECISION, SCALE) VALUES ('NUMBER', 'true', 22, 9) \

ADD COLUMN 'FIRM_ID' SET PROPERTIES(DATATYPE, NOT_NULL, PRECISION, SCALE) VALUES ('NUMBER', 'true', 22, 9) ;

ADD COLUMN 'FIRM_WORK_PCNT' SET PROPERTIES(DATATYPE, NOT_NULL, PRECISION, SCALE) VALUES ('NUMBER', 'true', 22, 4) ;

ADD COLUMN 'DBE_INDC' SET PROPERTIES(DATATYPE, NOT_NULL, LENGTH) VALUES ('VARCHAR2', 'true', 1) \

ADD COLUMN 'MBE_INDC' SET PROPERTIES(DATATYPE, NOT_NULL, LENGTH) VALUES ('VARCHAR2', 'true', 1) \

ADD COLUMN 'WBE_INDC' SET PROPERTIES(DATATYPE, NOT_NULL, LENGTH) VALUES ('VARCHAR2', 'true', 1) \

ADD COLUMN 'FIRM_NAME' SET PROPERTIES(DATATYPE, NOT_NULL, LENGTH) VALUES ('VARCHAR2', 'true', 100) \

ADD COLUMN 'OTHER_WORK_DESC' SET PROPERTIES(DATATYPE, NOT_NULL, LENGTH) VALUES ('VARCHAR2', 'true', 100) \

ADD COLUMN 'CREATED_USERID' SET PROPERTIES(DATATYPE, NOT_NULL, PRECISION, SCALE) VALUES ('NUMBER', 'true', 22, 9) \

ADD COLUMN 'CREATED_DATE' SET PROPERTIES(DATATYPE, NOT_NULL) VALUES ('DATE', 'true') \

ADD COLUMN 'UPDATED_USERID' SET PROPERTIES(DATATYPE, NOT_NULL, PRECISION, SCALE) VALUES ('NUMBER', 'true', 22, 9) \

ADD COLUMN 'UPDATED_DATE' SET PROPERTIES(DATATYPE, NOT_NULL) VALUES ('DATE', 'true') \

ADD COLUMN 'VBE_INDC' SET PROPERTIES(DATATYPE, NOT_NULL, LENGTH) VALUES ('VARCHAR2', 'true', 1) \

ADD PRIMARY_KEY 'PK_LOIS_SUBFIRM' SET REFERENCE COLUMNS ('LOIS_SUBFIRM_ID');

Table PSCS_LOIS_SUBFIRM altered.

Alter a Mapping

OMBALTER MAPPING '$mapname' \

ADD EXPRESSION OPERATOR '$exprOpName' \

set ingrpName2 [OMBRETRIEVE MAPPING '$mapname' OPERATOR '$exprOpName' GET INPUT GROUPS] \

set outgrpName2 [OMBRETRIEVE MAPPING '$mapname' OPERATOR '$exprOpName' GET OUTPUT GROUPS]

ADD ATTRIBUTE 'INPUT_VAL' OF GROUP '$ingrpName2' OF OPERATOR '$exprOpName'

SET PROPERTIES (DATATYPE,PRECISION,SCALE) VALUES ('NUMBER','20','2')

ADD CONNECTION

FROM ATTRIBUTE 'TAB_COLUMN' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC_TABLE_OP'

TO ATTRIBUTE 'INPUT_VAL' OF GROUP '$ingrpName2' OF OPERATOR '$exprOpName'

ADD ATTRIBUTE '$outAttrName' OF GROUP '$outgrpName2' OF OPERATOR '$exprOpName'

SET PROPERTIES (DATATYPE,PRECISION,SCALE,EXPRESSION) VALUES ('NUMBER','20','2','$ingrpName2.$attrName')

Error handling

if [catch { OMBCONNECT CONTROL_CENTER USE 'mypassword' } errmsg] {

puts "Error connecting to control center: $errmsg"

return 1

} else {

#..continue on

}

List/Search Mappings/Tables/View names

puts "connected"

puts "Projects: [OMBLIST PROJECTS]";

set pSearch PSCS_

############################################

# Listing all views,tables,mappings,opers #

############################################

puts "Please enter the OWB_module name (dflt: DWTARGET): "

gets stdin modName

if { [string length $modName ]==0 } {

set modName "DWTARGET"

}

puts "**************************************************************";

puts "Listing views, tables and mappings in DWTARGET like '$pSearch'";

puts "**************************************************************";

OMBCC 'MYPROJ/$modName'

if [catch {

puts "Views: [OMBLIST VIEWS '.*$pSearch.*']";

puts "Tables: [OMBLIST TABLES '.*$pSearch.*']";

} errmsg] {

puts "Error_occurred: $errmsg"

return 1

}

puts "Listing opers for relevant mappings in DWTARGET";

if [catch {

set mapList [OMBLIST MAPPINGS '.*$pSearch.*']

foreach mapName $mapList {

puts " Map...: $mapName [OMBRETRIEVE MAPPING '$mapName' GET PROPERTIES(DESCRIPTION)]"

set opers [OMBRETRIEVE MAPPING '$mapName' GET OPERATORS];

foreach oper $opers {

puts " Oper...: $oper Desc...:[OMBRETRIEVE MAPPING '$mapName' OPERATOR '$oper' GET PROPERTIES(DESCRIPTION)]"

}

} errmsg] {

puts "Error_occurred: $errmsg"

OMBDISCONNECT

puts "DisConnected"

return 1

}

OMBDISCONNECT

puts "DisConnected"

puts "completed"

Gen CREATE TABLE statement from OWB MetaTable

#########################################

# TCL script to... gen Create table stmt from OWB metadata

# written by...... Michael Dockery

# on.............. 09July2014

#########################################

set myparms $argv

set parmcount 0

foreach myparmval $myparms {

incr parmcount

puts "parm $parmcount is $myparmval"

if { $parmcount == 1 } {set modName $myparmval }

if { $parmcount == 2 } {set tableName $myparmval }

}

if { $parmcount != 2 } {

puts "Next time, please pass OWB_module and tablename as parms. "

puts " Example: C:/product/11.2.0/dbhome_1/owb/bin/win32/OMBPlus.bat c:/app/test.tcl DWTARGET DW_PROJECT_DIM"

puts "Please enter the OWB_module name (dflt: DWTARGET): "

gets stdin modName

if { [string length $modName ]==0 } {

set modName "DWTARGET"

}

puts "Please enter the table name: (dflt: DW_PSCS_CONTRACT_DIM)"

gets stdin tableName

if { [string length $tableName ]==0 } {

set tableName "DW_PSCS_CONTRACT_DIM"

}

puts "======================================="

puts "modName is $modName"

puts "tableName is $tableName"

puts "connecting"

OMBCONNECT MYUSER/MYPWD@MYSVR:1577:MYDW

puts "opening OWB module $modName"

OMBCC 'MYPROJ/$modName'

puts "Logical name = [lindex [OMBRETRIEVE TABLE '$tableName' GET \ PROPERTIES(BUSINESS_NAME)] 0]"

puts "Description = [lindex [OMBRETRIEVE TABLE '$tableName' GET \ PROPERTIES(DESCRIPTION)] 0]"

puts "---------------------------------------"

puts "---------- Create Stmt Below ----------"

puts "---------------------------------------"

puts "create table $modName.$tableName ("

set columnList [OMBRETRIEVE TABLE '$tableName' GET COLUMNS]

set colCount 1

foreach colName $columnList {

if { $colCount > 1 } { puts ", " }

set dt [OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' GET PROPERTIES(DATATYPE)]

puts -nonewline " $colName $dt "

if { $dt == "VARCHAR2" || $dt == "VARCHAR" || $dt == "CHAR" } {

puts -nonewline "([OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' GET PROPERTIES(LENGTH)])"

} elseif { $dt == "NUMBER" } {

set numPrecision [OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' GET PROPERTIES(PRECISION)]

if { $numPrecision != "0" } {

puts -nonewline "([OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' GET PROPERTIES(PRECISION)],"

puts -nonewline " [OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' GET PROPERTIES(SCALE)])"

}

} elseif { $dt == "DATE" } {

#do_nothing_special

}

set notnullflag [OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' GET PROPERTIES(NOT_NULL)]

if { $notnullflag == "1" } {puts -nonewline " not null"}

incr colCount

}

puts " "

puts ");"

if { $modName == "DWSTAGE" } { puts "Grant SELECT on $modName.$tableName to DWUSER " }

if { $modName == "DWTARGET" } { puts "Grant SELECT on $modName.$tableName to DWUSER2, DWUSER3 " }

puts "DISconnecting/closing session"

OMBDISCONNECT

puts "Press enter to continue..."

gets stdin byebye

Run OWB Mappings and/or ProcessFlows from SQL/Plus:

--set serveroutput on;
WHENEVER SQLERROR EXIT FAILURE;
BEGIN --run as MYUSER on MYDW
--DBMS_OUTPUT.PUT_LINE(TO_CHAR(owbsys.wb_rt_api_exec.run_task('DWTARGET_LOC','PLSQLMAP', 'MAP_DW_ORDER_DIM', null,null, 1) ) );
--DBMS_OUTPUT.PUT_LINE(TO_CHAR(owbsys.wb_rt_api_exec.run_task('PF_MOD_LOC','PROCESSFLOW','DW_PROCESS_STG_PSCS','','',1,0) ) );
DBMS_OUTPUT.PUT_LINE(TO_CHAR(owbsys.wb_rt_api_exec.run_task('PF_MOD_LOC','PROCESSFLOW','DW_PROCESS_PSCS_TARGET','','',1,0) )

--If your mapping pulls from text/csv file (and therefore uses sqlldr …sql loader)

DBMS_OUTPUT.PUT_LINE(TO_CHAR(owbsys.wb_rt_api_exec.run_task('PlatformSchema' ,'SQL_LOADER', 'MAP_PSCS_MBE_FIRMS_STG' , null,null, 1) ) );

);
END;
/
Sample Output:
anonymous block completed
Stage 1: Decoding Parameters
| location_name=PF_MOD_LOC
| task_type=PROCESSFLOW
| task_name=DW_PROCESS_PSCS_TARGET
Stage 2: Opening Task
| l_audit_execution_id=7720269
Stage 3: Overriding Parameters
Stage 4: Executing Task
| l_audit_result=1 (SUCCESS)
Stage 5: Closing Task
Stage 6: Processing Result
| exit=0
0

#########################################
# TCL script to... List Mappings details (mappings/opers/groups/attrs)
# written by...... Michael Dockery
# on.............. 20Jan2016
# Parms........... Pass the OWB module name (ex: DWTARGET)
#########################################
OMBCONNECT someUSER/someUSER@MYORAP05VW:1577:MYDW

if [catch {
set dstModName [lindex $argv 0]
OMBCC 'INDOTDW/$dstModName'
set mapList [OMBLIST MAPPINGS]
foreach mapName $mapList {
set opers [OMBRETRIEVE MAPPING '$mapName' GET OPERATORS];
foreach oper $opers {

set grpList [OMBRETRIEVE MAPPING '$mapName' OPERATOR '$oper' GET GROUPS]
foreach group $grpList {

if [catch {
set attrList [OMBRETRIEVE MAPPING '$mapName' OPERATOR '$oper' GROUP '$group' GET ATTRIBUTES]
foreach attr $attrList {
puts "$dstModName~$mapName~$oper~$attr"
}
} errmsg] {}
}
}
}
} errmsg] {
puts "Error_occurred: $errmsg"
OMBDISCONNECT
return 1
}
OMBDISCONNECT

Search This Blog

Trek4Tech