"The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
Bookmark and Share      

Data Transformer for Oracle

Sonswish - Oracle database browser
More about Sonswish
Download Sonswish Trial
Order Sonswish

The software will be issued in 4th quarter 2005

  • What is Data Transformer for Oracle?
  • DTO Scenarios
  • DTO Queries
  • Purposes
  • Creating a transformation with DTO tool
  • Special features
  • Storing image data
  • Data Updating Interface
  • Create an expression using the Data Updating Interface

  • What is Data Transformer for Oracle?

    The Data Transformer for Oracle (DTO) is a data converting software allows the user to interactively create a link mapping that can be used to transform heterogeneous data using OO4O. The DTO can be used to transform data between an Oracle database and other data sources, including:

    Microsoft Excel spreadsheets Microsoft Access databases

    DTO will open a Microsoft Access? database or MS Excel? Book (what you will want) as a Data Source and an Oracle database as a Data Target. Then you will be able to generate new or update existing Oracle tables from the information contained in Source tables or in spreadsheets.

    DTO checks to see if the destination table already exists and gives you the option to drop and re-create the destination table if desired. If the DTO software does not properly create the destination table, verify that the column mappings are correct, select a different data type mapping, or create the table manually. Then, copy the data.

    Each database defines its own data types, and column and object naming conventions. DTO attempts to define the best possible data type matches between a source and destination.

    DTO uses the source object?s name as a default. However, you can also change destination table names that contain characters, which are supported by Oracle DBMS 8i version.

    For converting MS Excel? Books to Oracle database you must have the MS Excel? is installed on your PC.

    For creating transformations using DTO you can choose one of two ways. You can generate DTO Scenario or DTO Query. All DTO tasks are stored in DTO Scenarios or in DTO Queries, which can be run using the Data Transformer for Oracle (DTO).


    DTO Scenarios

    Before create DTO Scenario you have to name this Scenario and then create and configure the transformation. The scenario will be saved with all related connections and transformation rules. You can run the scenario immediately or execute it later.


    DTO Queries

    You can copy data that results from an SQL query. SQL queries can include joins of multiple source tables from the same database or distributed queries. As part of the process, the DTO Query creates the destination table for you automatically if none exists.


    Purposes

    This software has the purpose of transforming data between most popular database formats, to convert information from Excel or Access files to Oracle database.

    Many research tasks need to convert raw information to centralize experimental data. However, their data is stored in a variety of formats and in different locations. The Data Transformer for Oracle is a graphical tool that lets you extract, transform, and consolidate data from disparate sources into single destination. If your data are only available on special software like Microsoft Access or MS Excel and if you do not have an easy way to access the data through a network - you might need to convert these data into the Oracle format.

    This program was written to meet my own project?s need to convert the old Access 95 project mdb database into the new project Oracle database. Among problems with the conversion of the data, one of the most significant is that the data held in the Excel books has particular data type for each cell on a spreadsheet. As is known, Oracle table can have particular data type for each field only. Also Oracle column name cannot include all of the ASCII characters set and can be in upper case only.


    Creating a transformation with DTO tool

    The Data Transformer for Oracle has a graphical interface which lets you extract, transform, and consolidate data from disparate sources into single destination ? Oracle database. By using DTO tool to graphically build a link mappings you can create custom data movement solutions tailored to the specialized current needs of your project.

    The DTO tool offers the simplest method of executing data converting, interactively guiding you through the process of copying and transforming data. Following basic steps will show you how to build, configure and execute a DTO transformation. This information can be used to help make your data conversion a success.

    1. Choose a source.

    In the toolbar on main form, click Data Source button. Choose an object from the ?Select Data Source? dialog box and press Open. The DTO operating software allows for the user to choose the raw data storage format from either the Access database or Excel Book structure.

    2. Choose a destination.

    In the toolbar on main form, click Data Target button. Where would you like to transform data? Choose whether to copy a source tables. In order to connect you must specify the user name, password and database name on the ?Choose a Destination? dialog box. You can copy data to Oracle database only.

    3. Configure the transformation.

    Click the Transformation icon and you will see the Transformation dialog box. Specify what data to move and how you want it to be transformed. In the Transformation dialog box, on the ?Source table? column, you can choose a source table by clicking on its check box. You can select all source tables for copy by clicking ?Select all? button. On the ?Destination table? column, you can choose an existing table or create a new table. Press ?Fill all by order? button and rows of ?Destination table? column will are filled by names of existing Oracle database tables.

    4. Execute the transformation.

    On the Transformation form, click Execute. DTO will automatically convert the source data to tables in the destination. This program will allow you to:

    ?Create new tables in your Oracle database. ?Recreate an existent table with new data. ?Add new records to existing table in DB. ?Update a table using special interface.

    When you create a new table in your Oracle database you can update column names, data type and data size of new destination table. For this case DTO will offer you special window with convenient interface. Also this tool allows you to edit column expression. In left field for each new table column you can insert name of source table column, combination of these columns or put expression using special SQL functions. About updating a table using special interface see additional information below.

    Important: Do not open a Microsoft Excel file that is being used as a source during the DTO tool execution, because a "file in use" error will occur.


    Special features

    Also the DTO is capable of managing databases. This software has database navigation features that provide the ability to view pictorial representations of your entire database. DTO allows users to see, in graphic detail, Source and Target databases that they are opening for converting. You can see also the complex relationships between tables, indexes, constraints, etc.

    After opening Source or Target database DTO graphically displays database objects and allows you to analyze the objects. The DTO trees are populated with database objects by selecting source or destination object from an appropriate Data Transformer dialog box. The tree displays a direct view of the database's objects and the relationships among objects.

    Each object type in the tree is identified by an icon and name. If there is a '+' or '-' to the left of an object's icon and name, the object is a container that can be expanded to display other objects. You can expand tree nodes to view the objects and relationships in the database. For example, you can expand a node to view the tables and properties on the database. If you expand a table, you can access nodes such as fields, indexes, constraints, and properties of this table. By clicking right mouse button on the database tree DTO allows you to:

    ?Open current table in the tree ?Rename the table selected in tree ?Delete the selected table

    Important: Before to update database (to rename or drop tables) you must be sure that your actions will not to result in loss of the information. Also you must have special security permission to this database. Otherwise, you can lose current data.


    Storing image data

    If your destination database table has columns with special data type BLOB the DTO can be used to store image data, display image information, copy digitized images from table field into files. The DTO allows users to quickly and efficiently store, organize, and import or export their images.

    On an expanded current table click the mouse button on the BLOB field record and an image-maintenance menu box should appear. This menu will allow you to:

    ?Display image information stored in your table ?Save image data from table as a binary file ?Copy image information from file into Oracle DB

    For displaying image data containing in your database click ?Display? bar on this menu. You can change the window display size by clicking mouse button on the edge of window frame and pulling it.

    Save your image as a file select ?Save as file? option. You will see dialog box where you can choose directory and name your new image file.

    Also you can fill some image into a database for later show and retrieval. The Data Transformer for Oracle provides for the easy storage of images into Oracle databases. Click ?Write from file? bar on the menu box and you will see the special ?Select file for writing in current record? dialog box. Specify what file to copy and click Open button. Image data from chosen file will be saved in your database.


    Data Updating Interface

    This Interface will appear when you try to execute the transformation on the ending of a configuration with DTO tool. The Data Updating Interface is used for building two character expressions. Then these expressions will be used to execute data converting between Source and Target. Using the Data Updating Interface you can interactively specify transformation rules that govern how data will be copied from the source table to the destination Oracle table.

    The Data Updating Interface has three sections:

    1. In the upper section of the Interface is an expression box where you build the Constraint expression. Use the lower section of the Interface to create elements of the Constraint expression, and then paste these elements into the expression box to form an expression. You can also type parts of the expression directly into the expression box.

    2. In the middle section of the Interface is an expression box where you build the Set expression for each column of destination table. Use the lower section of the Interface to create elements of the Set expression, and then paste these elements into the expression box to form an expression. You can also type parts of the expression directly into the expression box.

    3. In the lower section of the Interface are the following parts: In the upper-left box are buttons for commonly used operators. If you click one of the operator buttons, the Data Updating Interface inserts the operator at the insertion point in the one of expression boxes. For a complete list of operators and constants you can use in expressions, click the lower-left boxes, which content all operators and constants. The two right boxes contain lists of the Source table columns and Target table columns, which you can insert into your expressions.


    Create an expression using the Data Updating Interface

    In the lower section of the Data Updating Interface, double-click the element you want.

    When you paste an identifier in your expression, the Data Updating Interface pastes only the parts of the identifier that are required in the current context.

    Paste any operators you want in the expression by placing the insertion point in the expression box where you want the operator, and clicking one of the operator buttons that are in the upper-left box of the lower section of the Interface.

    When both of your (Constraint and Set) expressions are complete, click OK.

    The article describes how to solve the conversion problem. It is not production software?





    Copyright © 2001-2017 Rustemsoft LLC All Rights Reserved.