OWB SQL*Loader Mapping

In response to a quesion raised on OTN Forum, http://forums.oracle.com/forums/thread.jspa?threadID=514132&tstart=0

The OWB Mapping for SQL*Loader is -

• FILE MODULE CREATION

Create a File Module, if you have already created goto 2.
To create the File Module navigate to - Project -> Files.
Right Click on the FilesNode, and create a new Module.
During this step, you need specify the "local directory" from the source files need to imported and sampled. Usually this will be on your desktop.

• IMPORT THE SOURCE FILE

Now Import the file into the File Module just created.
To do this, right click on FileModuleName and Select "Import". While importing you can sample the source file to define the field names, data types and record structure.

here, you can specify if you want to skip any records from the beginning of the file





















Here, you have to specify whether the file is a delimited / fixed record format. If delimited, choose the appropriate delimitor. The field "Fixed Delimitor" is editable ie if you have a delimitor which is not listed in the drop down box, you can just type the delimitor.







Here, you can specify the data types and any SQL*Loader conversion functions. Also, you have a check box to say if the first record in the file specifies the field names.

Click Next and Finish.

This completes the "Import and Sampling " of source files.

• BUILD OWB MAPPING

Expand the Oracle Module, where the mapping need to be created. Right click onf "Mappings" node and select "Create Mapping". Build the mapping as per your requirements.

• CONFIGURING THE OWB MAP

The things that are important for SQL*Loader mapping are

• specifying the source data file.

To specify a logical name for the soure file, Select "Source Data File" tab and click on the [...] button, which opens up a pop-up window where you can give a name , and click on "Add" and then on "Ok" button.













Now, expand the "Source Data File" tab, and you should see an entry with the name as mentioned in previous step.
Expand the Source File and you should see entries to specify the

• Data File Location - This is the Location Name pointing to the directory path where the source file is residing on server
• Data File Name - Name of the source file on server, the name should exactly match as on the server.
• Bad File Location & Name - specify the location pointing to the directory where you want the bad file to be created on server.
• Discard File Location & Name - specify the location pointing to the directory where you want the discard file to be created on server.
• Log file location and file name - specify the location pointing to the directory where you want the Log file to be created.
  
• Errors Allowed - Specify the number of errors that can be allowed before the OWB mapping to fail.
  
• Control File Location and Name - specify the location pointing to the directory where you want the Sql*Loader control file to be created.
• Expand the tab "Source and Targets", expand your target table, expand "Sql*Loader Parameters" and change "Trailing Nullcols" to "True".
  
• with all the above configuration settings, the window should be as
  

• Close the above window, commit the changes , Register the locations ( by specifying the access parameters, the directory path on the server where the source file is residing )Deploy the mapping and Run the mapping.
  

If everything is right, you should load data from you source file into target table.

Deployment Script

OMB+ Script to Deploy Mappings

This script is tested for 10gR1 Release.
=====================================================================

puts " ******************* WARNING ********************"
puts ""
puts ""
puts " The proc owb_deploy_map is Created"
puts " To execute the proc call the above proc name at OMB+ command prompt "
puts ""
puts " ================================================="
puts "Requires the following parameters"
puts " Design Repos User"
puts " Design Repos Password"
puts " Host Name"
puts" Service Name"
puts " Port"
puts " Runtime Connection Name "
puts " Runtime User Password"
puts " ================================================"

puts "**************************************************************"
#
proc owb_deploy_map { desuser despwd host port srvc rtcon rtpwd } {
set fname [ open "c:/temp/deploy_maps.log" w]
puts $fname "----------------------------------------------------------"
puts $fname "Connecting to the Design Repository "
puts $fname "----------------------------------------------------------"
OMBCONNECT $desuser/$despwd@$host:$port:$srvc
set projList [ OMBLIST PROJECTS ]
#
foreach projName $projList {
OMBCC '$projName'
set projcon [OMBDCC]
puts $fname "current project context is $projcon"
puts $fname "CONNECTING TO RUNTIME REPOSITORY"
puts $fname "----------------------------------------------------------"
OMBCONNECT RUNTIME '$rtcon' USE PASSWORD '$rtpwd'
set ModList [OMBLIST ORACLE_MODULES ]
set i 1
OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP'
#
foreach ModName $ModList {
puts $fname " Working on : $ModName"
OMBCC '$ModName'
set curcon [OMBDCC]
set mapList [OMBLIST MAPPINGS]
set j 1
#
foreach mapName $mapList {
puts $fname " Creating Delpoyment Action Plans for :$mapName"

OMBALTER DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP' ADD ACTION '$mapName.DROP' SET PROPERTIES(OPERATION) VALUES ('DROP') SET REFERENCE MAPPING '$mapName'OMBALTER DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP' ADD ACTION '$mapName.CREATE' SET PROPERTIES(OPERATION) VALUES ('CREATE') SET REFERENCE MAPPING '$mapName'

incr j
}

puts $fname " Executing the Deployment Action for MAPPINGS"


OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP'

OMBCOMMIT

OMBCC '..'
set curcon [OMBDCC]
puts $fname "context at the end is --- $curcon"
incr i

}
OMBCOMMIT
OMBCC '/'

}
#
# DROP THE DEPLOYMENT ACTIONS PLANS
#
OMBCC '$projval'
set depList [OMBLIST DEPLOYMENT_ACTION_PLANS]
set n 1

puts $fname "Dropping the Deployment Action Plans"

foreach specName $depList {

puts $fname "Dropped $specName"
OMBDROP DEPLOYMENT_ACTION_PLAN '$specName'
incr n

}
OMBCOMMIT
OMBDISC
#
#**********************************************************************************************
# END OF PROC owb_deploy_map
#**********************************************************************************************
}

Family OWB Mapping

This post is in response to a question raised by a OWB Developer on OTN Forum, follow the link for full history - http://forums.oracle.com/forums/thread.jspa?threadID=509336&tstart=0.

The issue is -

Hi Mahesh,

Hope you have a nice weekend :)

That is what i don't know how to achive this through OWB mapping.

I will repeat again. I have data in this format.

ID | FamilyNum | RelationType
-------------------------------------------
100 1000 Head
850 1000 Wife
1100 1000 Son

and i need records in connection table as below:

ID | FamilyNum | RelationType
-------------------------------------------
100 850 Husband
850 100 Wife

100 110 Father
1100 100 Son

If you observe this is kind of opposite entry for each record.

This is very simple in PL/SQL coding. I thought use Cursor Loop in OWB, but don't find in help. If this is not possible then i implement this in SP and call it from OWB. No other option.

Thanks,
Danish


The Solution is -

OWB Map
-----------------



1. sources

HEAD FAMILY Table for Head of the Family
REST FAMILY Tale for rest of the family

2. The Join Condition is

HEAD.RELATION_TYPE IS NULL
AND HEAD.FAMILY_KEY = REST.FAMILY_KEY
AND REST.RELATION_TYPE IS NOT NULL

3. From the Output of Join To SET Operator (UNION ALL)

4. SET Operator has 2 Ingroups REST & REST_HEAD
The Ingroup REST takes input straight from OUTGRP of JOIN operator
The Ingroup REST_HEAD takes input from the OUTGRP of EXPRESSION.

5. The Expression ( this is to generate relation record for each family member ie Husband / Father ) has 3 input attributes

REST.RELATION_TYPE
REST.MEMBER_ID
HEAD.MEMBER_ID

From OUTGRP of JOIN.

6. The Output attributes and the business rules of Expression
FAMILY_NUM
(For this I am assuming that you are taking first 3 digits of the family member_id to get the Family Num of Father / Husband )

TransformationRule - TO_NUMBER(SUBSTR(TO_CHAR(INGRP1.R_MEMBER_ID,1,3 )))

MEMBER_ID

TransformationRule - INGRP1.H_MEMBER_ID

RELATION_TYPE

Transformation Rule -
CASE INGRP1.R_RELATION_TYPE
WHEN 'Wife' THEN 'Husband'
WHEN 'Son' THEN 'Father'
WHEN 'Daughter' THEN 'Father'
WHEN 'Husband' THEN 'Wife'
ELSE 'Unknown'
END

7. Map straight from the SET operator to Target Table.



Test Case

-----------------------
-- Source Table
-----------------------

desc family


MEMBER_ID NUMBER
FAMILY_KEY NUMBER
RELATION_TYPE VARCHAR2(30)

SELECT * FROM FAMILY ORDER BY FAMILY_KEY

100 1000 (null)
850 1000 Wife
1100 1000 Son

1500 2000 Son
500 2000 (null)
9000 2000 Husband
200 2000 Daughter

Two Set of records ( Family Key 1000, 2000) . One Family has 3 records and the other has 4 records including Head of Family.

----------------------------------------
--Before Running the OWB Map
-----------------------------------------

--Target Table structure

DESC TGT_FAM
FAMILY_NUM NUMBER
MEMBER_ID NUMBER
RELATION_TYPE VARCHAR2(30)
FAMILY_KEY NUMBER

4 rows selected

SELECT * FROM TGT_FAM


FAMILY_NUM MEMBER_ID RELATION_TYPE FAMILY_KEY
---------------------- ---------------------- ------------------------------ ----------------------

0 rows selected


--------------------------
--Run the OWB Map
-- Check the results
-----------------------------

SELECT * FROM TGT_FAM ORDER BY FAMILY_KEY

FAMILY_NUM MEMBER_ID RELATION_TYPE FAMILY_KEY
---------------------- ---------------------- ------------------------------ ----------------------
100 1100 Son 1000
100 850 Wife 1000
850 100 Husband 1000
110 100 Father 1000

150 500 Father 2000
200 500 Father 2000
900 500 Wife 2000
500 1500 Son 2000
500 200 Daughter 2000
500 9000 Husband 2000

10 rows selected

Unix

Unix (officially trademarked as UNIX®) is a computer operating system originally developed in the 1960s and 1970s by a group of AT&T employees at Bell Labs including Ken Thompson, Dennis Ritchie and Douglas McIlroy. Today's Unix systems are split into various branches, developed over time by AT&T as well as various commercial vendors and non-profit organizations.

The present owner of the trademark UNIX® is The Open Group, an industry standards consortium. Only systems fully compliant with and certified to the Single UNIX Specification qualify as "UNIX®" (others are called "Unix system-like" or "Unix-like").

During the late 1970s and early 1980s, Unix's influence in academic circles led to large-scale adoption of Unix (particularly of the BSD variant, originating from the University of California, Berkeley) by commercial startups, the most notable of which is Sun Microsystems. Today, in addition to certified Unix systems, Unix-like operating systems such as Linux, Mac OS X and BSD derivatives are commonly encountered.

Overview

---

Unix operating systems are widely used in both servers and workstations. The Unix environment and the client-server program model were essential elements in the development of the Internet and the reshaping of computing as centered in networks rather than in individual computers.

Both Unix and the C programming language were developed by AT&T and distributed to government and academic institutions, causing both to be ported to a wider variety of machine families than any other operating system. As a result, Unix became synonymous with "open systems".

Unix was designed to be portable, multi-tasking and multi-user in a time-sharing configuration. Unix systems are characterized by various concepts: the use of plain text for storing data; a hierarchical file system; treating devices and certain types of inter-process communication (IPC) as files; and the use of a large number of small programs that can be strung together through a command line interpreter using pipes, as opposed to using a single monolithic program that includes all of the same functionality. These concepts are known as the Unix philosophy.

Under Unix, the "operating system" consists of many of these utilities along with the master control program, the kernel. The kernel provides services to start and stop programs, handle the file system and other common "low level" tasks that most programs share, and, perhaps most importantly, schedules access to hardware to avoid conflicts if two programs try to access the same resource or device simultaneously. To mediate such access, the kernel was given special rights on the system and led to the division between user-space and kernel-space.

The microkernel tried to reverse the growing size of kernels and return to a system in which most tasks were completed by smaller utilities. In an era when a "normal" computer consisted of a hard disk for storage and a data terminal for input and output (I/O), the Unix file model worked quite well as most I/O was "linear". However, modern systems include networking and other new devices. Describing a graphical user interface driven by mouse control in an "event driven" fashion didn't work well under the old model. Work on systems supporting these new devices in the 1980s led to facilities for non-blocking I/O, forms of inter-process communications other than just pipes, as well as moving functionality such as network protocols out of the kernel.

Components

---

The Unix system is composed of several components that are normally packaged together. By including — in addition to the kernel of an operating system — the development environment, libraries, documents, and the portable, modifiable source-code for all of these components, Unix was a self-contained software system.

The following are the main components , which forms a Unix System.

• Kernel — source code in /usr/sys, composed of several sub-components:
  • conf — configuration and machine-dependent parts, including boot code
  • dev — device drivers for control of hardware (and some pseudo-hardware)
  • sys — operating system "kernel", handling memory management, process scheduling, system calls, etc.
  • h — header files, defining key structures within the system and important system-specific invariables
• Development Environment — Early versions of Unix contained a development environment sufficient to recreate the entire system from source code:
  • cc — C language compiler
    
  • as — machine-language assembler for the machine
  • ld — linker, for combining object files
  • lib — object-code libraries (installed in /lib or /usr/lib) libc, the system library with C run-time support, was the primary library, but there have always been additional libraries for such things as mathematical functions (libm) or database access.
    
  • make - build manager for effectively automating the build process
  • include — header files for software development, defining standard interfaces and system invariants.
• Commands — Unix makes little distinction between commands (user-level programs) for system operation and maintenance (e.g. cron), commands of general utility (e.g. grep), and more general-purpose applications such as the text formatting and typesetting package. Nonetheless, some major categories are:
  • sh — The "shell" programmable command-line interpreter, the primary user interface on Unix before window systems appeared, and even afterward (within a "command window").
  • Utilities — the core tool kit of the Unix command set, including cp, ls, grep, find and many others. Subcategories include:
    • System utilities — administrative tools such as mkfs, fsck, and many others
    • User utilities — environment management tools such as passwd, kill, and others.
  • Document formatting — Unix systems were used from the outset for document preparation and typesetting systems, and included many related programs such as nroff, troff, tbl, eqn, refer, and pic. Some modern Unix systems also include packages such as TeX and GhostScript.
  • Graphics — The plot subsystem provided facilities for producing simple vector plots in a device-independent format, with device-specific interpreters to display such files. Modern Unix systems also generally include X11 as a standard windowing system and GUI, and many support OpenGL.
  • Communications — Early Unix systems contained no inter-system communication, but did include the inter-user communication programs mail and write. V7 introduced the early inter-system communication system UUCP, and systems beginning with BSD release 4.1c included TCP/IP utilities.
• Documentation — Unix was the first operating system to include all of its documentation online in machine-readable form. The documentation included:
  • man — manual pages for each command, library component, system call, header file, etc.
  • doc — longer documents detailing major subsystems, such as the C language and troff