How-To Tutorials - Data

Oracle SQL Developer Tool is a stand alone graphic database developer tool that connects to Oracle as well as third-party databases which can be used to perform a variety of tasks from running simple queries to migration of databases from third party vendor products to Oracle. This article by Dr. Jayaram Krishnaswamy, shows how the reader may use Oracle's most recent tool, the Oracle SQL Developer 1.5 to work with the MySQL database. An example of migrating a table in MySQL to Oracle 10G XE is also described. The Oracle SQL Developer Tool has steadily improved from its beginnings in version 1.1. The earlier versions are briefly explained here. The latest version, SQL Developer 1.5.4 released in March 2009 was described in this article. The SQL Developer tool[(1.5.4.59.40)] bundle can be downloaded from Oracle's web site, Oracle Technology Products. When you unzip the bundle you are ready to start using this tool. You may get an even more recent version of this tool as it is continuously updated. It is assumed that you have a MySQL Server that you can connect to and that you have the required credentials. The MySQL server used in developing this article was installed when the XAMPP bundle was installed. Reader will benefit by reading earlier MySQL articles 1, 2, 3 on the Packt site. Connecting to MySQL Out of the box Oracle SQL Developer 1.5.4 only supports Oracle and MS Access. The product documents clearly says that it can connect to other database products. This article will show how this is achieved. In order to install products from Oracle you must have username and password for the Oracle web Account. Bring up the Oracle SQL Developer application by clicking the executable. The program starts up and after a while the user interface gets displayed as shown. Right click on Connection, the New Connection page opens as shown displaying the default connection to the resident Oracle 10G XE server. Click the menu item Help and choose "Check for Updates". This brings up the wizard displaying the Welcome screen as shown in the next figure. Click Next. The "Source" page of the wizard shows up as shown. The updates for Oracle SQL Developer is already chosen. Place a check mark for "Third Party SQL Developer Extensions". You can choose to install looking for updates on the internet or from the downloaded bundle, if it exists. First try the internet and click Next. This brings up the "Updates" page of the wizard as shown in the next figure. Read the warning on this window. The extensions are not evaluated by Oracle but available. The details of available extensions are as follows: OrindaBuild Java Code Generator version 6.1.20090331 shown in the next figure. The JTDS DBC Driver version 11.1.58.17 shown in the next figure. The MYSQL JDBC driver shown in the next figure: The last one is a patch for the Oracle SQL Developer to fix some of the import, LDAP and performance issues as shown. For this article only the JTDS JDBC driver for MS SQL Server and the MySQL JDBC options were checked. The License agreements are for the JTDS drivers. Click Next. The License agreements must be accepted. Click I Agree. Click Next. This is the download step of the wizard. To proceed further you must have the Oracle Web Account username and password. Here you have the option to signup as well. After a while the new extensions are downloaded as shown in the next figure. Click Finish to close the wizard. You need to restart SQL Developer to complete the installation of the extensions. Click Yes on the "Confirm Exit" window that shows up. Now, when you click New Connection to create a new connection you display the "New / Select Database Connection" as shown. You can now see that other 3rd party databases are added to the window. Choose the tab for MySQL. Fill in the required details as shown in the next figure appropriate for your MySQL installation. You must provide a name for the connection. Herein the connection is named, My_MySQL. The credentials must be provided as shown or that which is appropriate for your installation. The port is the default designated for this server when you install the product. You may accept the other defaults on this page and click Test. The word "success" gets displayed in the status label at bottom left. The connection name and connection details gets added to the page shown above.

A quick overview Let's start with a run-through of the product. By spending a little time dipping into a number of areas of the product, you can start laying down a map of how the pieces connect and provide a base that you can drill down into later. Sample schemas To follow the examples in the article, you need access to SYSTEM and the shipped sample schemas, HR, OE, SH, PM, and IX available in Oracle Database 9i, 10g, or 11g. Specifcally, this article uses the sample schemas shipped with Oracle Database 11g. There are two ways to install the sample schema. The first way is when you install the database. You can elect to have the sample schema installed at that point. Second, if you have not installed these, then you can locate the sample schema in the $ORACLE_HOME/demo/schema folder and follow the instructions on installing them using the Oracle online documentation. Not all of these schemas are available for Oracle Express Edition. In this article, we use SYSTEM to verify the HR schema is unlocked, and then we use the HR sample schema, which is available in Oracle Express Edition. Creating your first connection To complete this quick walk through, you need to know the username and password of the SYSTEM user. You also need to know the location of the database, whether this is the machine name or the IP address, and the database SID. To begin, start SQL Developer. The very first time you start SQL Developer, you'll be asked if you want to migrate from a previous version. Select No and allow the tool to start up. The first thing you need to do after you have started SQL Developer for the first time is to create your initial connections. Create a connection for SYSTEM: Select Connections, right-click and select New Connection. This invokes the New Database Connection dialog. You can edit and control all of the connection details using this dialog. Complete the details, as seen in the following screenshot, relevant to your environment. Click on Test to ensure you have the connection details correct and click on Connect. You are now connected as SYSTEM. Use this connection to verify your other users, by continuing as follows: Select the new connection you have created, expand the node, and scroll down to Other Users. Expand Other Users and find the user HR. Right-click and select Edit User. Verify that the account for HR is unlocked and the Password has not expired, that is, the properties Account is Locked and Password Expired are deselected. If either of these is selected, deselect them. You can change the password for HR at this point too. It's good practice to modify the passwords of the shipped sample schemas once you have unlocked them. Now you are really ready to begin! Once again, select Connections, right-click and select New Connection. Give the connection a name (for example, HR_11g. Provide the Username (HR) and a Password. If you are working on Oracle Database 11g, be aware that passwords are now case sensitive. Select the Save Password checkbox. This makes life easy while you are working with SQL Developer. Passwords are stored in an encrypted file. However, you should always be aware of saving passwords and possible security implications this may have. Use the Basic connection. This requires no more detail than the location of the database and the SID, details you have. Click on Test to test the connection. Click on Connect. Using basic commands in the SQL Worksheet As soon as you connect to a user, SQL Developer opens an SQL Worksheet. You may  have started working with Oracle using the SQL*Plus command line, or even the GUI window. Either way, you'd start with a selection of SQL*Plus and SQL commands. Enter the following into the SQL Worksheet: DESC DEPARTMENTSSELECT * FROM DEPARTMENTS; Press the F5 key (or use the Run Script button). The output of both commands appears in the Script Output tab, which appears below the SQL Worksheet (as seen in the previous screenshot). Both commands are handled by a few simple clicks of the mouse in SQL Developer. Select and expand the HR_11g connection in the Connections navigator. Expand the Tables node and select DEPARTMENTS. The DEPARTMENTS tab now opens, displaying a list of the column names and details. These are the same details as given by the DESC (describe) SQL*Plus command that you entered in the SQL Worksheet. It also provides additional detail, such as the Primary Key and column comments. Select the Data tab and notice that you now see the output from your second command. These two tabs are included with a number of other tabs, each with additional details about the DEPARTMENTS table. You would need to write a number of SQL queries in order to get the additional detail from the data dictionary if you were working in SQL*Plus. Select the EMPLOYEES table. Notice that the new table, EMPLOYEES, immediately replaces the previous DEPARTMENTS table with its details. Select the Triggers tab, and select one of the triggers. The trigger and related trigger detail is displayed in a master-detail window: Browsing and updating data Return to the EMPLOYEES data by again selecting the Data tab. The data grid that is displayed provides a variety of options. To get started with the data grid, double-click on an item or field, such as the name of one of the employees, and change it. Tab out of the field and notice that the change is applied to the data grid and an asterisk (*) flags the record. Commit and Rollback buttons are available to send the change to the database, or to undo your action. Roll back the changes. Once again you get feedback, this time in the Data Editor log, as shown in the following screenshot: Running reports Select the Reports navigator and expand the Data Dictionary Reports node. Expand the Table node and review the available reports. Expand Constraints and select the Unique Constraints report. As you select the report, a dialog displays requesting the Connection name. Select the connection you created, HR_11g, and click on OK. An Enter Bind Values dialog now appears, requesting the table name as an input parameter. Click on Apply to accept the default, which in this case, means all tables: Run the same report for any user by selecting the Connections drop-down list on the right-hand side. Navigating around SQL Developer SQL Developer has a selection of windows, navigators, and tabs. On start-up, you are presented with the main navigator toolbars and menus: The two main navigators: Connections and Reports, are presented in a tabbed window. These and other navigators, such as the Versioning Navigator, are available through the main View menu. You can also open windows such as Snippets, Recent Objects, and Find DB Objects using the View menu. Any navigators that you open during a session, and that are  still open when you close the product, are automatically opened when you restart the product.

Oracle SQL Developer Data Modeler is available as an independent product, providing a focused data modeling tool for data architects and designers. There is also a Data Modeler Viewer extension to SQL Developer, which allows users to open previously created data models and to create read-only models of their database schemas. SQL Developer Data Modeler is a vast tool, supporting the design of logical Entity Relationship Diagrams, and relational models, with forward and reverse engineering capabilities between the two. It supports multi-dimensional, data flow, data type, and physical models, and allows files to be imported from a variety sources and exported to a variety of destinations. It allows users to set naming conventions and verify designs using a set of predefined design rules. Each of these topics is extensive, so in this two-part article by Sue Harper (author of Oracle SQL Developer 2.1)  we'll review a few of the areas, illustrating how you can use them and highlight a few key features, using the independent, stand alone release of SQL Developer Data Modeler. We'll include a rief review of the integration points of the Data Modeler Viewer extension to SQL Developer. The product offers support for Oracle and non-Oracle Databases. In the interest of time and space, we have elected to only work with the Oracle database. Oracle SQL Developer Data Modeler SQL Developer Data Modeler provides users with a lightweight tool which provides application and database developers a quick and easy way of diagrammatically displaying their data structures, making changes, and submitting the new changes to update a schema. In this article, we will not attempt to teach data modeling (except to provide some generally accepted definitions). Instead, we will discuss how the product supports data modeling and a few of the features provided. There are a variety of books available on the subject, which describe and define modeling best practice. Feature overview The Data Modeler supports a number of graphical models and a selection of text-based models. The graphical models are: Logical—this is the entity relationship model or Entity Relationship Diagram (ERD), and comprises entities, attributes, and relationships. Relational—this is the schema or database model and is comprised of tables, columns, views, and constraints. In SQL Developer Data Modeler, these models are database independent, and need to be associated with the physical model to support database specific DDL. Data Types—this is the model that supports modeling SQL99 structured types and for viewing inheritance hierarchies. The data types modeled here are used in both the logical and relational models. Multidimensional models—these models support fact, dimension, and summary classifications for multi-dimensional models. Data Flow—these models support the definition of primitive, composite, and transformational tasks. The following support these graphical models: Domains—these allow you to define and reuse a data type with optional constraints or allowable values. You can use domains in the Logical and Relational models. Physical—this model is associated with a relational model and defines the physical attributes for a specific database and version. Business Information—this allows you to model or document the business details that support a design. Tying these graphical and textual models together are a variety of utilities, which include: Forward and reverse engineering between the Logical and Relational models Import from various databases Export, including DDL script generation, for various databases Design Rules for verifying standards and completeness Name templates, glossary, and abbreviation files for supporting naming standards Integrated architecture SQL Developer Data Modeler is made up of a number of layers, which have a tightly synchronized relationship. The Logical model is thought of as the core of the product, providing the starting point for any design, and feeding details into other models. The following diagram shows an illustration of how the models relate to each other: The logical ERD provides the basis for one or more relational models, and each of these feeds into one or more physical models, which are in turn used for the DDL generation. You can create separate data types models and use the defined data type in either the logical or relational models. Both relational and logical models can have multiple subviews created, and each subview can have many displays created. Getting started SQL Developer Data Modeler is an independent product, and with the exception of the Data Modeler Viewer extension to SQL Developer 2.1, is not packaged with other Oracle tools. You can download it and install it in a directory of your choice, with no impact on other tools. To install, simply unzip the file. Installing and setting up the environment Getting started with SQL Developer Data Modeler is straightforward. Follow the links from the Data Modeler site on OTN, http://www.oracle.com/technology/products/database/datamodeler to the download location. You are offered a choice of files to download: For Microsoft Windows, a ZIP file with or without the JRE included For the Mac OS X, a ZIP file without the JRE included For Linux, a ZIP file without the JRE included For any of these ZIP files, extract the file contents and run the datamodeler.exe, which is in the top-level /datamodeler folder, or in the /datamodeler/bin folder. For Linux, use the datamodeler.sh executable. If the file you choose does not include a JRE, you will be prompted on startup for the location of your installed JRE. The minimum supported release is JRE 1.6 update 6.0. Oracle clients and JDBC drivers If you are designing and building a model from scratch, or have access to the DDL script file for importing models, then you do not need to have access to a database. However, if you want to import from a database, you'll need to create a database connection. In this case, there is no need for an Oracle client in your development environment because you can use the thin JDBC Driver to connect to the database. SQL Developer Data Modeler also supports the TNS alias. Therefore, if you have access to a tnsnames.ora file, or have other Oracle software installed in your environment, you can access the tnsnames file to make the database connection if and when required. Creating your first models The Data Modeler browser starts with empty Logical and Relational models. This allows you to start a new design and build a model from scratch, whether a logical model with entities and attributes, or a relational model with tables and columns. The Data Modeler also supports metadata to be imported from a variety of sources, which include: Importing metadata from: DDL scripts Data dictionary Importing from other modeling tools: Oracle Designer CA Erwin 4.x Importing other formats: VAR file XMLA (Microsoft, Hyperion) The context menu displaying the choices available is shown in the following screenshot: Once you have created and saved your models, you can open these or share them with colleagues. To open an existing model, use the menu: File | Open—browse to the location of the files, which then opens the full design with all of the saved models File | Recent Designs—opens the full design, with all of the saved models, with no need to first search for the location File | Import | Data Modeler Design—more granular, offering a choice of models saved in a set of models Recent diagramsUse File | Recent Diagrams to display a list of all diagrams you have recently worked on and saved. Using this approach saves you from needing to browse to the location of the stored files. Importing from the Data Dictionary There are many ways to start using the tool by just starting to draw any one of the model types mentioned. In the screenshot shown earlier, we highlighted the File | Import | Data Dictionary option. Using this allows you to import from Oracle 9i, Oracle 10g, Oracle Database 11g, Microsoft SQL Server 2000 and 2005, and IBM DB2 LUW Versions 7 and 8. Creating a database connection Before you can import from any database, you need to create a database connection for each database you'll connect to. Once created, you'll see all of the schemas in the database and the objects you have access to. Access the New Database Connection dialog from the File | Import wizard (seen in the following screenshot). If you have no connections, click on Add to create a new connection. For a Basic connection, you need to provide the Hostname of the database server, the Port, and SID. The connection dialog also supports TNS alias and the advanced JDBC URL. Before you can add connections for non-Oracle databases, you need to add the required JDBC drivers. To add these drivers, use Tools | General Options | Third Party JDBC Drivers.

Working with diagrams and their components You can waste away many hours laying out the elements on a diagram. Therefore, this aspect of modeling can be time consuming. However, a model serves as documentation and a communication device. Therefore, taking the time to make sure it is well annotated and clearly designed is important. Most of the controls for the models are on the context menu, allowing you to modify individual aspects of the diagram. The context menu changes depending on whether you have an object or line selected, or you're just clicking in the open space. You can also set general defaults using Tools | General Options | Diagram. In this section, we'll look at the various options available when working with the diagrams. Formatting the elements Before moving a relationship line, entity, or table, you can dramatically change the impact and readability of a large diagram just by changing the colors. This is readily demonstrated when importing from two or more schemas. Using the previous example where we imported from two schemas, open one of the subviews and select all of the tables. With the objects selected, invoke the Format Object dialog using the context menu: If this is the first time you are adjusting the colors, the dialog does not display any colors as you open it. The colors used in the diagram are the default settings. Deselect Use Default Color and click on the Background and Border Color items to select and set the new color. When you are done, click on OK and note the changes applied to the subview. Switch to the main relational model to review the impact there. The color applied to the subview is also applied to the main model as shown. This is very useful when illustrating how tables in different schemas relate to each other. For example, take the HR and OE sample schema, all of the tables related to human resources are maintained in the HR schema, while those related to the order entry system are maintained in the OE schema. You may well have applications designed around the HR schema and others tied to the OE schema, but some may involve both. In the following relational model, the OE tables are now colored green, so we're able to identify them, but we can also see where the schemas link. We can see that a CUSTOMER may deal with one EMPLOYEE and has many ORDERS: Selecting all of the tables in a modelSelect any table and click on Select Neighbors from the context menu. Select All Zones, to select all of the tables. Use this instead of Ctrl+A, which selects all tables and all lines. Changing the default format settings Instead of changing individual items or even a set of items, you can change the default color for each of the element types displayed on a diagram. The Tools | General Options | Diagram | Format provides the ability to control the color of each of the elements displayed such as tables, views, and entities: To edit any of the elements in the dialog, double-click on the object, or select the object and the edit icon. This allows you to adjust the color of the item and to format the font. You can use the font color to highlight mandatory, Unique, or Foreign Keys. Setting general diagram properties Use the same Tools | General Options | Diagram to set model properties,which include: Displaying the grid Controlling the background color of the diagram Controlling the Auto Route feature which is on by default Set display properties for certain items on each of the models, including the control of: The diagram notation for the logical model, which supports the Barker and Bachman notations The display of the relationship names for either the logical or relational models The flow names for process models For example, to display the relationship names on an Entity Relationship Diagram (as seen below), check the display property on the Tools | General Options | Model | Logical, and ensure that the Relation Cardinality properties for the relationships are also set. Creating subviews and displays Adding subviews and displays offers you alternative ways of laying out elements on a diagram and for working with subsets of items. You can create multiple subviews and displays for either logical or relational models, and remove them as easily, without impacting the main models. Adding subviews to your design You have already encountered a subview by importing from a number of schemas in the data dictionary. Subviews are not only a reflection of the different schemas in a design, but they can also represent any subset of elements in the design, allowing you to work with a smaller, more manageable set of elements. You can create a subview from the object browser by selecting: The SubViews node and using the New SubView context menu. In this case, you have a new empty diagram that you can populate by dragging tables or entities (depending on the subview in question) onto the page. Any of the model tabs and then selecting the Create SubView menu. This creates a new and empty subview. An element or elements on an existing model and using the Create SubView from selected context menu on the diagram. In this case, the new subview will contain the tables or entities you selected: The layout of the subview is not linked to the main model in any way. What is linked is how you format the items on the subview and any structural changes you make to the objects. You can continue to add new items to the subview by dragging them onto the surface from the object browser. When deleting items from the subview, you should choose whether the item is deleted: From the view (Delete View) From the complete design (Delete Object) Adding displays A display is an alternative view of a diagram, whether a main model or a subview, and is linked directly to that model. If you delete the model, the display is also deleted. Any items that you add or remove from displays are also automatically added or removed from the main model they are linked to. To create a new display, select the tab of any model and select Create Display from the context menu. The new display created is, initially, a replica of the model you selected in both layout and items available. All additional displays are listed at the bottom of the model. In the following example, the HR subview has two displays created, as highlighted at the bottom of the screenshot, the main HR display and the new Display_1. The Create Display context menu is also illustrated: Use the new display to change the layout of the model and to adjust the level of detail displayed on the diagram. A second display of the same model is useful when you want to show more or less detail on a model. You can, for example, create a display which only displays the entity or table names. Right-click in the space on a diagram and select View Details | Names Only. We'll discuss how to layout the diagram elements later. Creating a composite view If you create a number of subviews, create a new diagram showing the composite models of each of these on a single layout. This serves as a useful reminder of the number of subviews or models you have by having a thumbnail of the various layouts. Alternatively, you can add a composite view of one subview and place it on another. To create a composite view, select the model in the browser and drag it onto the diagram surface. You can drag any model onto any other diagram surface, except its own: Once you have the composite folder displayed on the diagram, display the composite model within that folder by selecting Composite View from the context menu. If the model you want to view has a selection of displays, then you can also select the display you want to see within that composite. The following screenshot shows the, subview, displaying the composite models of the HR subview, the main logical model, and both displays of the logical model: Controlling the layout When working with a large number of items in a model, it's important to keep the layout organized. A variety of tools to help with the process are explained in the following sections. Adjusting the level of detail displayed Change the amount of detail displayed in a table (or entity) using the View Details menu. It is invoked with a right-click on the white space of any diagram. The View Details menu has options for displaying: All Details Names Only Columns Datatype Keys Adjusting the width and height across the model If you have a large diagram and want to see how tables or entities relate to each other, you can create a more compact model using a display, without impacting the main model. This can be done by setting the details to display the name only and then resizing and repositioning the objects. In the following screenshot, we have set the model to display only the name of the tables. Create a more compact diagram by resizing one of the tables to a more fitting set of dimensions, select the rest, and then resize them all to the same width and height: Controlling alignment Once you have positioned the items, align them to complete the model. Use the Edit menu with the required items for top and left alignment as shown in the following screenshot: Resizing and alignmentThe first item you select is the one that drives the position for left or top alignment, and the item that controls the width and height of all subsequent items selected.

Preparing your environment Preparing your environment depends on a few factors, including the platform you are working on and whether you have an early edition of SQL Developer previously installed. First, you need to locate the software, download, and install it. Finding and downloading the software SQL Developer is available through a variety of sources as both a standalone download and as part of the Oracle Database and Oracle JDeveloper installations. SQL Developer is a free product, and you can download it from the Oracle Technology Network, http://www.oracle.com/technology/products/database/sql_developer. Use this link to reach the download for the latest standalone production release. It also includes details of the release and is regularly updated with news of preview releases and new articles. While SQL Developer is free to download and use, you are required to read and agree to the license before you can proceed with the download. The product also falls under Oracle Support contracts, if you have a Support contract for the database, which means that you can log Oracle Support tickets. Downloading and installing the Java Development Kit SQL Developer requires the Java SE Development Kit (JDK); this includes the Java Runtime Environment (JRE) and other tools, which are used by SQL Developer utilities such as the PL/SL Debugger. For Microsoft Windows, you can download and install SQL Developer with the JDK already installed. This means you'll download and unzip the product and will be ready to start, as there are no extra steps required. For the other operating systems, you'll need to download the JDK and direct SQL Developer to the path yourself. Indeed, as many other products require a JDK to be installed, you may already have one on your system. In this case, just direct the product to use an existing JDK installation. For Microsoft Windows, ensure you download SQL Developer without the JDK to make use of an existing JDK installation. The SQL Developer download site offers a selection of download choices: Microsoft Windows (with or without the JDK) Linux (without the JDK) Mac OS X (without the JDK) In each case, make your selection and download the required file. The download sites for the JDK are as follows: For Microsoft Windows and Linux: http://java.sun.com/javase/downloads/index.jsp For Mac:http://developer.apple.com/java/download/ SQL Developer is shipped with the minimum JDK required. You can download and use the latest updates to the JDK. You should be aware that some updates to the JDK are not supported. This detail is posted on the SQL Developer Downloads page for each release. Starting from SQL Developer 2.1, JDK 1.6 is the minimum JDK supported. Once you have installed the JDK, you can start SQL Developer. Installing and starting SQL Developer SQL Developer does not use an installer. All you need to do is unzip the given file into an empty folder, locate, and run the executable. Do not unzip SQL Developer into an $Oracle_Home folder or an existing SQL Developer install. Unzipping the file creates an sqldeveloper folder, which includes a selection of sub-folders and fles, including the sqldeveloper.exe executable. If your download does not include the JDK, then you'll be prompted for the full path of the java.exe. Browse to the location of the fle and select it. The path should include the full path and executable (for example, C:Program FilesJavajdk1.6.0_13binjava.exe): Working with different platforms Whether you are accessing SQL Developer as part of the Oracle Database 11g installation or as a stand-alone install, there is a selection of executables available to you. These are either platform specifc or provide additional detail while running the product. Microsoft Windows The first executable you'll find is in the root folder sqldeveloper. This is the executable more generally used. If you navigate down to sqldeveloperbin, there are two additional executables, sqldeveloper.exe and sqldeveloperW.exe. The latter is the same as the executable in the root folder. Use either of these for running SQL Developer. The additional executable is often used for debugging purposes. Use sqldeveloperbinsqldeveloper.exe to invoke SQL Developer and a separate console window which displays additional Java messages. You can use these messages when encountering errors in the product and if you want to log an issue with Oracle Support. Oracle SQL DeveloperThree steps to getting started on Microsoft Windows:Download: Download the full file, with JDK, from the Oracle Technology Network websiteUnzip: Unzip the file to an empty directoryDouble-click: Double-click on the sqldevelopersqldeveloper.exe file Alternative platforms Microsoft Windows is the predominant platform used by SQL Developer users. There is a steadily growing audience for Linux and Max OS X. As neither of these platform downloads include the JDK, you need to first access, download, and install the JDK. On starting either Linux or the Mac OS, you'll be prompted for the full path of the JDK as described. Mac OS X Download the file specific to Mac OS X and unzip it to an empty folder. This creates an sqldeveloper folder, complete with files and sub-folders. Run the sqldeveloper.sh file. Linux Use the Linux rpm command to install SQL Developer. For example, your command might look like this: rpm -Uhv sqldeveloper-1.5.54.40-1.noarch.rpm In the same way that unzip creates an sqldeveloper folder, with sub-folders and files, the rpm, command creates an sqldeveloper folder, complete with files and sub-folders. Switch to this new folder and run the sqldeveloper.sh executable. Migrating settings from a previous release On the initial startup of any release of SQL Developer, you may be asked one or two questions. The first is the location of the Java executable of the JDK as discussed. If you have installed the full release with the JDK, this question is skipped. The second question is if you want to migrate any preferences from a previous release. Regardless of whether this is the first SQL Developer install on the machine or not, the frst time you invoke SQL Developer, you are offered the choice of migrating your settings. You can migrate settings of any release from SQL Developer 1.5 and above. By default, the utility looks for the latest previous installation of the software. If you want to migrate from a different installation, select the Show All Installations button (seen above). This displays a list of all SQL Developer installations that have the system folder in the Documents and Settings system folder (for example, C:Documents and Settings<your_user>Application DataSQL Developersystem1.5.1.54.40) and includes releases from SQL Developer 1.5 and above. For releases prior to SQL Developer 1.5, the system folder was created within the SQL Developer install (for example, D:SQLDeveloperBuilds1.2.11.2.1.3213sqldevelopersqldevelopersystem).   Maintaining your environment Once you have SQL Developer installed, it is helpful to know about the environmental settings and some of the files that are created when you start the product. Knowing about the version you have installed is important if only to be able to identify this when asking questions on the forum, or when contacting Oracle Support. Verifying the current release To verify the SQL Developer release you have, select the Help | About menu once you start SQL Developer or JDeveloper. In the dialog invoked, select the Extensions tab and find the Oracle SQL Developer extension, as shown in the next screenshot. This will match the build number on the download site if you have the latest release. The screenshot shows a number of the extensions that make up SQL Developer. If your dialog does not show the Version or Status columns, you can select the column headers to resize the visible columns and bring the others into focus. Using Check for Updates SQL Developer offers a built-in patching and extensions utility, known as Check for Updates. Check for Updates is used to release: SQL Developer extensions General Oracle extensions Minor patches Third-party tools required by SQL Developer, such as the non-Oracle database drivers Third-party extensions You can control whether Check for Updates warns you about new updates using the Tools | Preferences menu. Select Extensions and then select Automatically Check for Updates. For SQL Developer extensions, if you already have SQL Developer installed and you're not secured by a firewall, you'll be alerted about new updates. You need not use the utility to get the updates, but you'll be aware of the latest release from the alert. For all other extensions, you need to start Check for Updates to see what's available. To do this, select Help | Check for Updates. In either situation, just follow the dialog to find the updates you require.

Text Search Features If you think that text search is just a basic thing and nothing more than returning results that matched words in a user query, then think again! There are many technical details that a good search implementation will give you control over to affect how well this fundamental capability works, like text analysis and relevancy ranking. But there are also a variety of ancillary features to look for that make a big difference such as result highlighting and faceting. Text analysis: This is the processing of the original text into indexed terms, and there's a lot to it. Being able to configure the tokenization of words could mean that searching for “Mac” will be found if the word “MacBook” is in the text. And then there's synonym processing so that users can search for similar words. You might want both a common language dictionary and also hand-picked ones for your data. There's the ability to smartly handle desired languages instead of the pervasive English. And then there's stemming which normalizes word variations so that for example “working” and “work” can be indexed the same. Yet another variation of text analysis is phonetic indexing to find words that sound-like the search query. Relevancy ranking: This is the logic behind ranking the search results that closest match the query. In Lucene/Solr, there are a variety of factors in an overall equation with the opportunity to adjust factors based on matching certain fields, certain documents, or using field values in a configurable equation. By comparison, the commercial Endeca platform allows configuration of a variety of matching rules that behaves more like a strict sort. Query features & syntax: From boolean logic to grouping to phrases to fuzzy-searches, to score boosting... there are a variety of queries that you might perform and combine. Many apps would prefer to hide this from users but some may wish to expose it for “advanced” searches. Result highlighting: Displaying a text snippet of a matched document containing the matched word in context is clearly very useful. We have all seen this in Google. Query spell correction (i.e. “did you mean”): Instead of unhelpfully returning no results (or very few), the search implementation should be able to try and suggest variation(s) of the search that will yield more results. This feature is customarily based on the actual indexed words and not a language dictionary. The variations might be based on the so-called edit-distance which is basically the number of alterations needed, or it might be based on phonetic matching. Faceted navigation: This is a must-have feature which enables search results to include aggregated values for designated fields that users can subsequently choose to filter the results on. It is commonly used on e-commerce sites to the left of the results to navigate products by various attributes like price ranges, vendors, etc. Term-suggest (AKA search auto-complete): As seen on Google, as you start typing a word in the search box, it suggests possible completions of the word. These are relevancy sorted and filtered to those that are also found with any words prior to the word you are typing. Sub-string indexing: In some cases, it is needed to match arbitrary sub-strings of words instead of being limited to complete words. Unlike what happens with an SQL like clause, the data is indexed in such a way for this to be quick. Geo-location search: Given the coordinates to a location on the globe with records containing such coordinates, you should be able to search for matching records from a user-specified coordinate. An extension to Solr allows a radial based search with appropriate ranking, but it is also straight-forward to box the search based on a latitude & longitude. Field/facet suggestions: The Endeca search platform can determine that your search query matches some field values used for faceting and then offer a convenient filter for them. For example, given a data set of employees, the search box could have a pop-up suggestion that the word in the search box matches a department code and then offer the choice of navigating to those matching records. This can be easier and faster than choosing facets to filter on, especially if there are a great number of facet-able fields. Solr doesn't have this feature but it would not be a stretch to implement it based on its existing foundation. Clustering: This is another aid to navigating search results besides faceting. Search result clustering will dynamically divide the results into multiple groups called clusters, based on statistical correlation of terms in common. It is a bit of an exotic feature, but is useful with lots of results with lots of text information and after any faceted navigation is done if applicable. So that's quite a list and there are other features you may find too. This should give you a list of features to look for in whatever you choose. Some features are obviously more important to you than others. How NOT to implement text search: the SQL “like” clause Perhaps “back in the day” you implemented search by simply adding like “%searchword%” in the where clause of your SQL (the author is guilty as charged!) But of course, this has serious problems such as: It is very slow, especially given a data set of decent size and any amount of load on the server. A database does a simple brute-force search. There is no concept of relevancy (e.g. a match score). A record simply matched or not. You are forced to sort on one of your columns. It is too literal. Even if the search is case insensitive, any extra punctuation can screw it up, or it may match parts of words when you only wanted to match whole words. So the bottom line is don't do it! There are smarter approaches to this problem. Probably, the only situation you would do this is if you had a particular database column holding a limited number of short values and you have it indexed. Searches should go quickly and it's very easy to implement this approach.

Supported charts Pentaho Reporting relies on JFreeChart, an open source Java chart library, for charting visualization within reports. From within Report Designer, many chart types are supported. In the chart editor, two areas of properties appear when editing a chart. The first area of properties is related to chart rendering, and the second tabbed area of properties is related to the data that populates a chart. Following is the screenshot of the chart editor within Pentaho Report Designer: All chart types receive their data from three general types of datasets. The first type is known as a Category Dataset , where the dataset series and values are grouped by categories. A series is like a sub-group. If the exact category and series appear, the chart will sum the values into a single result. The following table is a simple example of a category dataset: Category Series Sale Price Store 1 Sales Cash $14 Store 1 Sales Credit $12 Store 2 Sales Cash $100 Store 2 Sales Credit $120 Pentaho Reporting builds a Category Dataset using the CategorySetDataCollector. Also available is the PivotCategorySetCollector, which pivots the category and series data. Collector classes implement Pentaho Reporting’s Function API. The second type of dataset is known as an XY Series Dataset, which is a two dimensional group of values that may be plotted in various forms. In this dataset, the series may be used to draw different lines, and so on. Here is a simple example of an XY series dataset : Series Cost of Goods (X) Sale Price (Y) Cash 10 14 Credit 11 12 Cash 92 100 Credit 105 120 Note that X is often referred to as the domain, and Y is referred to as the range. Pentaho Reporting builds an XY Series Dataset using the XYSeriesCollector. The XYZSeriesCollector also exists for three dimensional data. The third type of dataset is known as a Time Series Dataset , which is a two dimensional group of values that are plotted based on a time and date. The Time Series Dataset is more like an XY Series than a Category Dataset, as the time scale is displayed in a linear fashion with appropriate distances between the different time references. Time Series Sale Price May 05, 2009 11:05pm Cash $14 June 07, 2009 12:42pm Credit $12 June 14, 2009 4:20pm Cash $100 June 01, 2009 1:22pm Credit $120 Pentaho Reporting builds a Time Series Dataset using the TimeSeriesCollector. Common chart rendering properties Most charts share a common set of properties. The following properties are common across most charts. Any exceptions are mentioned as part of the specific chart type. Required Property Group Property name Description name The name of the chart object within the report. This is not displayed during rendering, but must be unique in the report. A default name is generated for each chart added to the report. data-source The dataset name for the chart, which is automatically populated with the name of the dataset in the Primary DataSource panel of the chart editor. no-data-message The message to display if no data is available to render the chart. Title Property Group Property name Description chart-title The title of the chart, which is rendered in the report. chart-title-field A field representing the chart title. title-font The chart title's font family, size, and style. Options Property Group Property name Description horizontal If set to True, the chart's X and Y axis are rotated horizontally. The default value is set to False. series-colors The color in which to render each series. The default for the first three series colors are red, blue, and green. General Property Group Property name Description 3-D If set to True, renders the chart in a 3D perspective. The default value is set to False. anti-alias If set to True, renders chart fonts as anti-aliased. The default value is set to True. bg-color Sets the background around the chart to the specified color. If not set, defaults to gray. bg-image Sets the background of the chart area to the specified image. If not set, the background of the chart area defaults to white. The chart area is the area within the axes of the chart. Supported image types include PNG, JPG, and GIF file formats. show-border If set to True, displays a border around the chart. The default value is set to True. border-color Sets the border to the specified color. If not set, defaults to black. plot-border If set to False, clears the default rendering value of the chart border. plot-bg-color Sets the plot background color to the specified color. If not set, defaults to white. plot-fg-alpha Sets the alpha value of the plot foreground colors relative to the plot background. The default value is set to 1.0. plot-bg-alpha Sets the alpha value of the plot background color relative to the chart background color. The default value is set to 1.0.   Legend Property Group Property name Description show-legend If set to True, displays the legend for the chart. The default value is set to False. location The location of the legend in relation to the chart, which may be set to top, bottom, left, or right. The default location is bottom. legend-border If set to True, renders a border around the legend. The default value is set to True. legend-font The type of Java font to render the legend labels in. legend-bg-color Sets the legend background color. If not set, defaults to white. legend-font-color Sets the legend font color. If not set, defaults to black. Advanced Property Group Property name Description dependencyLevel The dependency level field informs the reporting engine what order the chart should be executed in relation to other items in the report. This is useful if you are using special functions that may need to execute prior to generating the chart. The default value is set to 0. Negative values execute before 0, and positive values execute after 0.   Common category series rendering properties The following properties appear in charts that render category information: Options Property Group Property name Description stacked If set to True, the series values will appear layered on top of one another instead of being displayed relative to one another. stacked-percent If set to True, determines the percentages of each series, and renders the bar height based on those percentages. The property stacked must be set to True for this property to have an effect. General Property Group Property name Description gridlines If set to True, displays category grid lines. This value is set to True by default. X-Axis Property Group Property name Description label-rotation If set, adjusts the inline item label rotation value. The value should be specified in degrees. If not specified, labels are rendered horizontally. You must have show-labels set to true for this value to be relevant. date-format If the item value is a date, a Java date format string may be provided to format the date appropriately. Please see Java's SimpleDateFormat JavaDoc for formatting details. numeric-format If the item value is a decimal number, a Java decimal format string may be provided to format the number appropriately. Please see Java's DecimalFormat JavaDoc for formatting details. text-format The label format used for displaying category items within the chart. This property is required if you would like to display the category item values. The following parameters may be defined in the format string to access details of the item: {0}: To access the Series Name detail of an item {1}: To access the Category detail of an item {2}: To access the Item value details of an item To display just the item value, set the format string to "{2}". x-axis-title If set, displays a label describing the category axis. show-labels If set to true, displays x-axis labels in the chart. x-axis-label-width Sets the maximum category label width ratio, which determines the maximum length each category label should render in. This might be useful if you have really long category names. x-axis-label-rotation If set, adjusts the category item label rotation value. The value should be specified in degrees. If not specified, labels are rendered horizontally. x-font The font to render the category axis title and labels in. Y-Axis Property Group Property name Description y-axis-title If set, displays a label along the value axis of the chart. label-rotation If set, determines the upward angle position of the label, where the value passed into JFreeChart is the mathematical pie over the value. Unfortunately, this property is not very flexible and you may find it difficult to use. y-tick-interval The numeric interval value to separate range ticks in the chart. y-font The font to render the range axis title in. y-sticky-0 If the range includes zero in the axis, making it sticky will force truncation of the axis to zero if set to True. The default value of this property is True. y-incl-0 If set to True, the range axis will force zero to be included in the axis. y-min The minimum value to render in the range axis. y-max The maximum value to render in the range axis. y-tick-font The font to render the range tick value in. y-tick-fmt-str The DecimalFormat string to render the numeric range tick value. enable-log-axis If set to true, displays the y-axis as a logarithmic scale. log-format If set to true, will present the logarithmic scale in a human readable view.

Ring chart The ring chart is identical to the pie chart, except that it renders as a ring versus a complete pie. In addition to sharing all the properties similar to the pie chart, it also defines the following rendering property : Options Property Group Property name Description section-depth This property defines the percentage of the radius to render the section as. The default value is set to 0.5. Ring chart example For this example, simply open the defined pie chart example and select the Ring chart type. Also, set the section-depth to 0.1, in order to generate the following effect: Multi pie chart The multi pie chart renders a group of pie charts, based on a category dataset. This meta-chart renders individual series data as a pie chart, each broken into individual categories within the individual pie charts. The multi pie chart utilizes the common properties defined above, including the category dataset properties. In addition to the standard set of properties, it also defines the following two properties: Options Property Group Property name Description label-format This label defines how each item within a chart is rendered. The default value is set to "{0}". The format string may also contain any of the following: {0}: To render the item name {1}: To render the item value {2}: To render the item percentage in relation to the entire pie chart by-row This value defaults to True. If set to False, the series and category fields are reversed, and individual charts render series information. Note that the horizontal, series-color, stacked and stacked-percent properties do not apply to this chart type. Multi pie chart example This example demonstrates the distribution of purchased item types, based on payment type. To begin, create a new report. You'll reuse the bar chart's SQL query. Now, place a new Chart element into the Report Header. Edit the chart, selecting Multi Pie as the chart type. To configure the dataset for this chart, select ITEMCATEGORY as the category-column. Set the value-columns property to QUANTITY and the series-by-field to PAYMENTTYPE. Waterfall chart The waterfall chart displays a unique stacked bar chart that spans categories. This chart is useful when comparing categories to one another. The last category in a waterfall chart normally equals the total of all the other categories to render appropriately, but this is based on the dataset, not the chart rendering. The waterfall chart utilizes the common properties defined above, including the category dataset properties. The stacked property is not available for this chart. There are no additional properties defined for the waterfall chart. Waterfall chart example In this example, you'll compare by type, the quantity of items in your inventory. Normally, the last category would be used to display the total values. The chart will render the data provided with or without a summary series, so you'll just use the example SQL query from the bar chart example. Add a Chart element to the Report Header and select Waterfall as the chart type. Set the category-column to ITEMCATEGORY, the value-columns to QUANTITY, and the series-by-value property to Quantity. Now, apply your changes and preview the results. Bar line chart The bar line chart combines the bar and line charts, allowing visualization of trends with categories, along with comparisons. The bar line chart is unique in that it requires two category datasets to populate the chart. The first dataset populates the bar chart, and the second dataset populates the line chart. The bar line chart utilizes the common properties defined above, including the category dataset properties. This chart also inherits the properties from both the bar chart, as well as the line chart. This chart also has certain additional properties, which are listed in the following table: Required Property Group Property name Description bar-data-source The name of the first dataset required by the bar line chart, which populates the bars in the chart. This value is automatically populated with the correct name. line-data-source The name of the second dataset required by the bar line chart, which populates the lines in the chart. This value is automatically populated with the correct name. Bar Options Property Group Property name Description ctgry-tick-font Defines the Java font that renders the Categories. Line Options Property Group Property name Description line-series-color Defines the color in which to render each line series. line-tick-fmt Specifies the Java DecimalFormat string for rendering the Line Axis Labels lines-label-font Defines the Java font to use when rendering line labels. line-tick-font Defines the Java font to use when rendering the Line Axis Labels. As part of the bar line chart, a second y-axis is defined for the lines. The property group Y2-Axis is available with similar properties as the standard y-axis. Bar line chart example To demonstrate the bar line chart, you'll reuse the SQL query from the area chart example. Create a new report, and add a Chart element to the Report Header. Edit the chart, and select Bar Line as the chart type. You'll begin by configuring the first dataset. Set the category-column to ITEMCATEGORY, the value-columns to COST, and the series-by-value property to Cost. To configure the second dataset, set the category-column to ITEMCATEGORY, the value-columns to SALEPRICE, and the series-by-value property to Sale Price. Set the x-axis-label-width to 2.0, and reduce the x-font size to 7. Also, set show-legend to True. You're now ready to preview the bar line chart. Bubble chart The bubble chart allows you to view three dimensions of data. The first two dimensions are your traditional X and Y dimensions, also known as domain and range. The third dimension is expressed by the size of the individual bubbles rendered. The bubble chart utilizes the common properties defined above, including the XY series dataset properties. The bubble chart also defines the following properties: Options Property Group Property name Description max-bubble-size This value defines the diameter of the largest bubble to render. All other bubble sizes are relative to the maximum bubble size. The default value is 0, so this value must be set to a reasonable value for rendering of bubbles to take place. Note that this value is based on pixels, not the domain or range values. The bubble chart defines the following additional dataset property: Required Property Group Property name Description z-value-columns This is the data source column to use for Z value, which specifies the bubble diameter relative to the maximum bubble size. Bubble chart example In this example, you need to define a three dimensional SQL query to populate the chart. You'll use inventory information, and calculate Item Category Margin: SELECT"INVENTORY"."ITEMCATEGORY","INVENTORY"."ONHAND","INVENTORY"."ONORDER","INVENTORY"."COST","INVENTORY"."SALEPRICE","INVENTORY"."SALEPRICE" - "INVENTORY"."COST" MARGINFROM"INVENTORY"ORDER BY"INVENTORY"."ITEMCATEGORY" ASC Now that you have a SQL query to work with, add a Chart element to the Report Header and select Bubble as the chart type. First, you'll populate the correct dataset fields. Set the series-by-field property to ITEMCATEGORY. Now, set the X, Y, and Z value columns to ONHAND, SALEPRICE, and MARGIN. You're now ready to customize the chart rendering. Set the x-title to On Hand, the y-title to Sales Price, the max-bubble-size to 100, and the show-legend property to True. The final result should look like this:

Data transformation with External Tables One of the main uses of the External Tables is their support of the ETL process, allowing the user to perform a data load that is transformed to the target format without an intermediate stage table. Let's read an External Table whose contents are: This data can be loaded in a single command to multiple tables. Let's create several tables with the same structure: SQL> desc amount_jan Name Null? Type ----------------- -------- ------------ REGION VARCHAR2(16) AMOUNT NUMBER(3) Now issue the command to send the data from the external table to the different tables. INSERT ALL INTO AMOUNT_JAN (REGION, AMOUNT) VALUES(COUNTRY, JAN) INTO AMOUNT_FEB (REGION, AMOUNT) VALUES(COUNTRY, FEB) INTO AMOUNT_MAR (REGION, AMOUNT) VALUES(COUNTRY, JAN) INTO AMOUNT_APR (REGION, AMOUNT) VALUES(COUNTRY, JAN) INTO AMOUNT_MAY (REGION, AMOUNT) VALUES(COUNTRY, JAN) INTO AMOUNT_JUN (REGION, AMOUNT) VALUES(COUNTRY, JAN) INTO AMOUNT_JUL (REGION, AMOUNT) VALUES(COUNTRY, JAN) INTO AMOUNT_AUG (REGION, AMOUNT) VALUES(COUNTRY, JAN) INTO AMOUNT_SEP (REGION, AMOUNT) VALUES(COUNTRY, JAN) INTO AMOUNT_OCT (REGION, AMOUNT) VALUES(COUNTRY, JAN) INTO AMOUNT_NOV (REGION, AMOUNT) VALUES(COUNTRY, JAN) INTO AMOUNT_DEC (REGION, AMOUNT) VALUES(COUNTRY, JAN)SELECT COUNTRY, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DECFROM REGION_REVENUE; In this example, we will perform a conditional insert to different tables depending on the value of the amount column. We will first create three tables, one for low, another for average, and a third for high amounts: SQL> create table low_amount( 2 region varchar2(16), 3 month number(2), 4 amount number(3));Table created.SQL> create table high_amount as select * from low_amount;Table created. Now we can read the External Table and have the data inserted conditionally to one of three mutually exclusive targets. INSERT ALL WHEN ( JAN <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '01', JAN ) WHEN ( FEB <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '02', FEB ) WHEN ( MAR <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '03', MAR ) WHEN ( APR <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '04', APR ) WHEN ( MAY <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '05', MAY ) WHEN ( JUN <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '06', JUN ) WHEN ( JUL <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '07', JUL ) WHEN ( AUG <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '08', AUG ) WHEN ( SEP <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '09', SEP ) WHEN ( OCT <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '10', OCT ) WHEN ( NOV <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '11', NOV ) WHEN ( DEC <= 500 ) THEN INTO LOW_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '12', DEC ) WHEN ( JAN > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '01', JAN ) WHEN ( FEB > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '02', FEB ) WHEN ( MAR > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '03', MAR ) WHEN ( APR > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '04', APR ) WHEN ( MAY > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '05', MAY ) WHEN ( JUN > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '06', JUN ) WHEN ( JUL > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '07', JUL ) WHEN ( AUG > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '08', AUG ) WHEN ( SEP > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '09', SEP ) WHEN ( OCT > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '10', OCT ) WHEN ( NOV > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '11', NOV ) WHEN ( DEC > 500 ) THEN INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT) VALUES ( COUNTRY, '12', DEC )SELECT COUNTRY, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DECFROM REGION_REVENUE; Extending the alert.log analysis with External Tables Reading the alert.log from the database is a useful feature which can help you to find any outstanding error messages reported in this file. create table ALERT_LOG ( text_line varchar2(512)) organization external ( type ORACLE_LOADER default directory BACKGROUND_DUMP_DEST access parameters( records delimited by newline nobadfile nodiscardfile nologfile ) location( 'alert_beta.log') ); Once the External Table has been created, the alert.log file can be queried just like any other regular table. SQL> select text_line from alert_log 2 where text_line like 'ORA-%';TEXT_LINE-----------------------------------------------------------------ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/beta/redo01.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/beta/redo02.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: '/u01/oracle/oradata/beta/redo03.log'ORA-27037: unable to obtain file status Querying the alert.log file up to this phase is useful just to see the contents of the file and look for basic ORA-% strings. This could also be achieved by using the alert.log link in the Enterprise Manager (EM). The alert.log file can be queried by means of the EM, but as this can only be viewed from the EM in an interactive mode, you can only rely on the preset alerts. If further automatic work needs to be done, then it is useful to do some more work with the alert analysis tool. A temporary table can be used to store the contents of the ALERT_LOG table, along with an extra TIMESTAMP column, so it can be queried in detail in an EM-like manner. create global temporary table TMP_ALERT_LOG ( LINE_NO NUMBER(6), TIMESTAMP DATE, TEXT_LINE VARCHAR2(512))on commit preserve rows; A bit of PLSQL programming is necessary so the ALERT_LOG file can be modified and inserted into the TMP_ALERT_LOG, (enabling further queries can be done). declarecursor alertLogCur is select ROWNUM, TEXT_LINE from ALERT_LOG;currentDate date;altertLogRec ALERT_LOG.TEXT_LINE%TYPE;testDay varchar2(10);begincurrentDate := sysdate;for alertLogInst in alertLogCur loop -- fetch row and determine if this is a date row testDay := substr(alertLogInst.text_line, 1, 3); if testDay = 'Sun' or testDay = 'Mon' or testDay = 'Tue' or testDay = 'Wed' or testDay = 'Thu' or testDay = 'Fri' or testDay = 'Sat' then -- if this is a date row, it sets the current logical record date currentDate := to_date( alertlogInst.text_line, 'Dy Mon DD HH24:MI:SS YYYY'); end if; insert into TMP_ALERT_LOG values( alertLogInst.rownum, currentDate, alertLogInst.text_line );end loop;end;/ As the contents of the alert.log end up in a temporary table, more than one DBA can query it at the same time, or restrict the DBA's accessibilities. There is no need to manage the purge and maintenance of the table after the session has ended, it can be indexed and there is little overhead by means of this procedure. Moreover, as this is a temporary object, minimum redo log information is generated. Once the external ALERT_LOG and the temporary ALERT_LOG tables have been created, it is possible to perform, not only filters by date (provided by Enterprise Manager) but also any query against the alert.log file. SELECT TIMESTAMP, TEXT_LINEFROM TMP_ALERT_LOGWHERE TIMESTAMP IN ( SELECT TIMESTAMP FROM TMP_ALERT_LOG WHERE TEXT_LINE LIKE 'ORA-%')AND TIMESTAMP BETWEEN SYSDATE-30 AND SYSDATEORDER BY LINE_NO; Further treatment can be done on this concept to look for specific error messages, analyze specific time frames and perform drill down analysis. This procedure can be extended to read the trace files or any other text file from the database. Reading the listener.log from the database One particular extension of the above procedure is to read the listener.log file. This file has a specific star-delimited field file format which can be advantageous, and eases the read by means of the Loader driver. The file format is as follows: 21-JUL-2008 00:39:50 * (CONNECT_DATA=(SID=beta)(CID=(PROGRAM=perl)(HOST=alpha.us.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.10)(PORT=8392)) * establish * beta * 021-JUL-2008 00:39:56 * (CONNECT_DATA=(SID=beta)(CID=(PROGRAM=perl)(HOST=alpha.us.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.10)(PORT=8398)) * establish * beta * 021-JUL-2008 00:40:16 * service_update * beta * 021-JUL-2008 00:41:19 * service_update * beta * 021-JUL-2008 00:44:43 * ping * 0 The file has a format that can be deduced from the above data sample: TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE As you can see this format, even though it is structured, it may have a different number of fields, so at loading time this issue must be considered. In order for us to map this table to the database, we should consider the variable number of fields to have the External Table created. We'll create a temporary table so that this doesn't create an additional transactional overhead. Now, let's create an External Table based on this format that points to $ORACLE_HOME/network/log: create directory NETWORK_LOG_DIRas '$ORACLE_HOME/network/log'; Now, let's create the external table: create table LISTENER_LOG ( TIMESTAMP date, CONNECT_DATA varchar2(2048), PROTOCOL_INFO varchar2(64), EVENT varchar2(64), SID varchar2(64), RETURN_CODE number(5))organization external ( type ORACLE_LOADER default directory NETWORK_LOG_DIR access parameters ( records delimited by NEWLINE nobadfile nodiscardfile nologfile fields terminated by "*" LDRTRIM reject rows with all null fields ( "TIMESTAMP" char date_format DATE mask "DD-MON-YYYY HH24:MI:SS ", "CONNECT_DATA", "PROTOCOL_INFO", "EVENT", "SID", "RETURN_CODE" ) ) location ('listener.log'))reject limit unlimited; The structure of interest is specified above, so there will be several rows rejected. Seeing as this file is not fully structured, you will find some non formatted information; the bad file and the log file are not meaningful in this context. Another application of the LISTENER_LOG External Table is usage trend analysis. This query can be issued to detect usage peak hours. SQL> select to_char(round(TIMESTAMP, 'HH'), 'HH24:MI') HOUR, 2 lpad('#', count(*), '#') CX 3 from listener_log 4 group by round(TIMESTAMP, 'HH') 5 order by 1;HOUR CX----- ------------------------------------------------14:00 ###15:00 ##########################16:00 ######################17:00 #####################18:00 #####################19:00 ############### Reading the listener.log file this way allows the DBA not only to keep track of the listener behavior, but also it allows a security administrator to easily spot hacking attempts. Let's find out who is trying to access the database with sqlplus.exe. SQL> select timestamp, protocol_info 2 from listener_log 3 where connect_data like '%sqlplus.exe%' 4 /TIMESTAMP PROTOCOL_INFO-------------------- --------------------------------------------------------01-SEP-2008 14:30:37 (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.101)(PORT=3651))01-SEP-2008 14:31:08 (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.101)(PORT=3666))01-SEP-2008 14:31:35 (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.101)(PORT=3681)) The use of External Tables to analyze the listener.log can be used not only to have an in-database version of the listener.log perform periodic and programmatic analysis of the listener behavior, but also to determine usage trends and correlate information with the audit team so that unauthorized connection programs can be easily and quickly spotted. Further useful applications can be found by reading the listener.log file. There are two fields that must be further parsed to get information out of them, but parsing those fields goes beyond the scope of this article. The structure that the analysis should consider is detailed next: Connect String SID: The Database Oracle SID, which is populated if the connection was performed by SID, otherwise it is NULL. CID: It contains two subfields, PROGRAM and HOST. SERVER: This field indicates the connection type, either dedicated or shared. SERVICE_NAME: This field is populated when the connection is performed by a Service instead of SID. COMMAND: The command issued by the user. SERVICE: Present only when listener commands are issued. FAILOVER_MODE: In Real Application Clusters (RAC) environments this field is used if the client performed a connection due to a failover. It shows the failover mode used. Protocol PROTOCOL: Indicates the used to perform the connection; this will be TCP most of the times. HOST: This is the client's IP Address. PORT: The port number of the oracle server used to establish the connection. Mapping XML files as External Tables XML has become a de facto information exchange format, which is why oracle has included the XML Database (XDB) feature from 9.2.0. However, it requires the data to be actually loaded into the database before it can be processed. An External Table allows the user to take a quick look at the contents of the external file prior to performing any further processing. In this example an External Table is created out of an XML file. This file is read by means of a CLOB field, and some further XDB commands can be issued against the external XML file to extract and view data. Let's create the external XML file first: create table EMPLOYEES_XML (xmlFile CLOB)organization external ( type ORACLE_LOADER default directory EXTTABDIR access parameters ( fields (xmllob char terminated by ',') column transforms (xmlFile from lobfile(xmllob)) ) location('employees.dat'))reject limit unlimited; The employees.dat file contains the file name of the XML file to load as an external CLOB file. This file, for the purpose of the demo, contains the file name: employees.xml. Now the file can be queried from the database as if it was a regular table with a single XML column. Dynamically changing the external reference When managing External Tables, there should be an easy way to redefine the external source file. It is enough to change the External Table properties by means of an ALTER TABLE command. Let's create a stored procedure that performs this task by means of a dynamically generated DDL command. This procedure, named Change_External_Table redefines the location property. Using a stored program unit is a flexible way to perform this task. create procedure change_external_table( p_table_name in varchar2, p_file_name in varchar2) isbeginexecute immediate 'alter table '|| p_table_name|| ' location ('''|| p_file_name|| ''')' ;exceptionwhen othersthenraise_application_error(sqlcode,sqlerrm) ;end ;/ Oracle 11g External Table enhancements External Tables work the same in 10g and in 11g, so there are no differences when working with these two versions. When working with Data Pump External Tables, and one single row proves defective, the data set reading operation is aborted. An enhancement in this 11g release prevents the data load aborting, thus saving reprocessing time. Summary Managing data with External Tables is a means not only for mapping external flat files as regular (but limited) tables inside the database, but also a tool to more efficiently perform administrative tasks such as programmatically processing database log files such as the alert.log or the listener.log files. It can be used to easily view external XML formatted files from inside the database without actually loading the file to the database. It can also be used as a means of unloading data in temporary external storage to exchange data among different Oracle versions. This particular feature allows the user to easily build an Oracle Datamart that allows the pre-formatting and summarization of data from the source, enabling it to be directly inserted into the target data warehouse.

By the end of this article, you should be able to: Add multiple columns to a report Group report data Add images and charts to a report Creating more elaborate reports In this section, we will modify the simple report we created in the previous article to illustrate how to add images, charts, and multiple columns to a report. We will also see how to group report data. We will perform all of these tasks graphically with iReport. Adding images to a report Adding static images to a report is very simple with iReport. Just drag the Image component from the Palette to the band where it will be rendered in the report. When we drop the image component into the appropriate band, a window pops up asking us to specify the location of the image file to display. After we select the image, we can drag it to its exact location where it will be rendered. As we can see, adding images to a report using iReport couldn't be any simpler. Adding multiple columns to a report The report we've been creating so far in this article (continued from the first part of this article, Graphical Report Design with iReport: Part 1, for database details, refer to Database for our reports section of Creating Dynamic Reports from Databases) contains over 11,000 records. It spans over 300 pages. As we can see, there is a lot of space between the text fields. Perhaps it would be a good idea to place the text fields closer together and add an additional column. This would cut the number of pages in the report by half. To change the number of columns in the report, we simply need to select the root report node in the Report Inspector window at the top left and then modify its Columns property in the Properties window at the bottom right. When we modify the Columns property, iReport automatically modifies the Column Width property to an appropriate value. We are free, of course, to modify this value if it doesn't meet our needs. As our report now contains more than one column, it makes sense to re-add the Column Header band we deleted earlier. This can be done by right-clicking on the band in the Report Inspector window and selecting Add Band. Next, we need to move the static text in the page header to the Column Header band. To move any element from one band to another, all we need to do is drag it to the appropriate band in the Report Inspector window. Next, we need to resize and reposition the text fields in the Detail band and the static text elements in the Column Header band so that they fit in the new, narrower width of the columns. Also, resize the Column Header band to avoid having too much whitespace between the elements of the Column Header and Detail bands. Our report now looks like this: We can see the resulting report by clicking on Preview. Grouping report data Suppose we are asked to modify our report so that data is divided by the state where the aircraft is registered. This is a perfect situation to apply report groups. Report groups allow us to divide report data when a report expression changes. To define a report group, we need to right-click on the root report node in the Report Inspector window, and then select Add Report Group. Then, enter the Group name and indicate whether we want to group by a field or by a report expression. In our case, we want to group the data by state field. After clicking on Next>, we need to indicate whether we want to add a group header and/or footer to our report. For aesthetic purposes, we move the static text fields in the Column Header band to the Group Header band, remove the column and page header bands, and add additional information to the Group Header band. After making all of these changes, our report preview will look like this: We can preview the report by clicking Preview.

In 2008, iReport was rewritten to take advantage of the NetBeans platform. It is freely available both as a standalone product and as a plugin to the NetBeans IDE. In this article, we will be covering the standalone version of iReport; however, the material is also applicable to the iReport NetBeans plugin. By the end of this article, you will be able to: Obtain and set up iReport Quickly create database reports by taking advantage of iReport's Report Wizard Design reports graphically with iReport Obtaining iReport iReport can be downloaded from its home page at http://jasperforge.org/projects/ireport by clicking on the Download iReport image slightly above the center of the page. Once we click on the image, we are directed to an intermediate page where we can either log in with our JasperForge account or go straight to the download page. Either logging in or clicking on the No Thanks, Download Now button takes us to the iReport download page. The standalone iReport product is in the first row of the table on the page. To download it, we simply click on the Download link in the last column. Other downloads on the page are for older versions of JasperReports, iReport NetBeans plugin, and other JasperSoft products. iReport can be downloaded as a DMG file for Macintosh computers, as a Windows installer for Windows PCs, as a source file, as a ZIP file, or as a gzipped TAR file. To install iReport, simply follow the usual application installation method for your platform. If you chose to download the ZIP or gzipped TAR file, simply extract it into any directory. A subdirectory called something like iReport-nb-3.5.1 will be created. (The exact name will depend on the version of iReport that was downloaded.) Inside this directory, you will find a bin subdirectory containing an executable shell script called ireport and a couple of Windows executables, ireport.exe and ireport_w.exe. On Windows systems, either EXE file will start iReport. The difference between the two Windows executables is that theireport.exe will display a command-line window when iReport is executed, and ireport_w.exe won't. Both versions provide exactly the same functionality. On Unix and Unix-like systems, such as Linux and Mac OS, iReport can be started by executing the ireport shell script. The following screenshot illustrates how iReport looks when it is opened for the first time: Setting up iReport iReport can help us quickly generate database reports. To do so, we need to provide it with the JDBC driver and connection information for our database. iReport comes bundled with JDBC drivers for several open source relational database systems, such as MySQL, PostgreSQL, HSQLDB, and others. If we want to connect to a different database, we need to add the JDBC driver to iReport's CLASSPATH. This can be done by clicking on Tools | Options and then selecting the Classpath tab. To add the JDBC driver to the CLASSPATH, click on the Add JAR button, and then navigate to the location of the JAR file containing the JDBC driver. Select the JAR file and click on the OK button at the bottom of the window. We won't actually add a JDBC driver, as we are using MySQL for our examples, which is one of the RDBMS systems supported out of the box by iReport. The information just provided is for the benefit of readers using an RDBMS system that is not supported out of the box. Before we can create reports that use an RDBMS as a datasource, we need to create a database connection. In order to do so, we need to click on the Report Datasources icon in the toolbar: After doing so, the Connections / Datasources configuration window should pop up. To add the connection, we need to click on the New button, select Database JDBC connection, and then click on the Next> button. We then need to select the appropriate JDBC driver, fill in the connection information, and click on the Save button. Before saving the database connection properties, it is a good idea to click on theTest button to make sure we can connect to the database. If we can, we should see a pop-up window like the following: After verifying that we can successfully connect to the database, we are ready to create some database reports. Creating a database report in record time iReport contains a wizard that allows us to quickly generate database reports (very useful if the boss asks for a report 15 minutes before the quitting time on a Friday!). The wizard allows us to use one of the predefined templates that are included with iReport. The included report templates are divided into two groups: templates laid out in a "columnar" manner and templates laid out in a "tabular" manner. Columnar templates generate reports that are laid out in columns, and tabular templates generate reports that are laid out like a table. In this section, we will create a report displaying all the aircraft with a horsepower of 1000 or more. To quickly create a database report, we need to go to File | New | Report Wizard. We should then enter an appropriate name and location for our report and click on Next>. Next, we need to select the datasource or database connection to use for our report. For our example, we will use the JDBC connection we configured in the previous section. We can then enter the database query we will use to create the report. Alternatively, we can use the iReport query designer to design the query. For individuals with SQL experience, in many cases it is easier to come up with the database query in a separate database client tool and then paste it in the Query text area than using the query designer. The complete query for the report is: selecta.tail_num,a.aircraft_serial,am.model as aircraft_model,ae.model as engine_modelfrom aircraft a, aircraft_models am, aircraft_engines aewhere a.aircraft_model_code = am.aircraft_model_codeand a.aircraft_engine_code = ae.aircraft_engine_codeand ae.horsepower >= 1000 The following window shows a list of all the columns selected in the query, allowing us to select which ones we would like to use as report fields: In this case, we want the data for all columns in the query to be displayed in the report. Therefore, we select all columns by clicking on the second button. We then select how we want to group the data and click on Next>. This creates a report group. In this example, we will not group the report data. The screenshot illustrates how the drop-down box contains the report fields selected in the previous step. We then select the report layout (Columnar or Tabular). In this example, we will use the Tabular Layout. After selecting the layout, we click on Next> to be presented with the last step. We then click on Finish to generate the report's JRXML template. While the template is automatically saved when it is created, the report generated by the Preview button is not automatically saved. We can then preview our report by clicking on Preview. That's it! We have created a report by simply entering a query and selecting a few options from a wizard.  

Interactive HTML report properties All reporting elements share a common set of HTML-related properties that may be used to create a dynamic report. Below is a list of properties and their uses: HTML Properties class This property sets the class attribute of the current HTML entity to the specified value. name This property sets the name attribute of the current HTML entity to the specified value. title This property sets the title attribute of the current HTML entity to the specified value. xml-id This property allows the naming of the current HTML entity, setting the id attribute, making it possible to reference in outside scripts. append-body This property allows the placement of raw HTML within the body of the HTML document, prior to the rendering of the current element. append-body-footer This property allows the placement of raw HTML within the body of the HTML document, after the rendering of the current element. append-header Defined only at the master report level, this property allows the inclusion of raw HTML within the header of the HTML document generated. This location is traditionally used to load additional CSS files, as well as external JavaScript files.

Installing the EnterpriseDB Download PostgrePlus Advanced Server 8.3 ( pgplus-advsvr-windows-83012b.exe (120MB) ) from the following site: http://www.enterprisedb.com/products/download.do. After downloading the program double click the executable file. You may need to choose a language from a list of languages. Here English has been chosen. The welcome window gets displayed as shown. Click Next. Choose the option you need. Read notes on this page to make the choice. Here, the Oracle compatibility has been chosen. Click Next or, choose a different location by browsing. Here the default location is accepted. Click Next. The window that shows up displays all the various features that are available. Pick and choose the features. Here all features are chosen. Click Next. The next window shows the links from where the JDBC drivers for connecting to Oracle and MySQL are available displayed. Click Next . In the window that shows up you need to choose the password for the Operating System UserID and Password. Read the cautionary remarks on this page. Choose Next. At this point your anti-virus program may require you to permit to run the program. McAfee is the anti-virus program on this computer. In the window that gets displayed you may need to choose the administrator's log in credentials. You may also Browse and select the Data Destination Directory. Herein the default is accepted. Click Next. In the windows that gets displayed you may choose the type of environment for which the server will be used as well as the work load for which you may be using the server. The dynamic tuning options available are: Server Utilization Development: This is a development machine and many other applications will be running on it. Stress testing should not be performed with this configuration. EnterpriseDB will use a minimal amount of memory. Mixed: Several applications will be running on this machine. Choose this option for web/application servers. Dedicated: This machine is dedicated to run EnterpriseDB and will use available memory to optimize performance. The Workload Profile Transaction Processing: The running application is a transaction intensive applications. General Purpose: The database will be used for transaction processing as well as complex queries and reporting. Reporting: The database will be used for reporting applications. For this tutorial, the Mixed option for Server Utilization and General Purpose for Workload Profile were chosen. Click on Next. The Summary page gets displayed showing all the options chosen. Click on the Install button. The window with a progress bar gets displayed. You may get a warning from the anti-virus program on your computer to allow the file to be executed. Click OK to allow install.

Introduction Business needs often necessitate data migration from a smaller, less secure database to a higher end, faster database server with a more reliable availability. A typical scenario is the migration of data from a desktop sized database such as MS Access or Fox Pro to any other higher end database servers such as MS SQL Server, Oracle, DB2 or SQL Anywhere Server. Most of the database vendors provide tools to migrate from third party to their own database servers. In his three previous articles, the author has described the built-in tools to migrate from MS Access to SQL 2000 Server, SQL Anywhere Server, and from Oracle 10G XE to SQL Anywhere server.   In an earlier article on this site, the author showed how you may connect to an MS Access 2003 database and execute SQL statements using the Oracle SQL Developer 1.2 tool. In this tutorial the author shows you how to migrate an MS Access database to an Oracle 10G XE Server delineating all the steps involved in the migration process. Oracle SQL Developer 1.2 with this latest version is sometimes called the Migration version as it supports migrating data from three vendors (MySQL, SQL Server and MS Access) to an Oracle database. In fact, it has been designed to migrate from more than one version of MS Access. This feature was not available in the version 1.1 of this tool. Overview of this Tutorial Like in the earlier article, a simple MS Access 2003 database file will be created with just one table, a query and a linked table. This database file, about 292 KB, will be migrated to Oracle 10G XE database. Oracle 10G XE, by design, can have just one database on a computer. However, you can have separate applications by having different user schemas. Oracle 10G XE comes bundled with a sample database schema and data which can be accessed by using the credentials, username hr with a password hr. For the purposes of this example a new user will be created and his authentication will be used for creating necessary migration related schemas to be stored in a repository. This will become clear as you follow the various details and the steps. Once the ‘Repository’ is created then you can begin by capturing the metadata of the source followed by converting the captured source information into Oracle specific model where a mapping between the source data and the Oracle will be accomplished. After this process, you generate the data definition language script which will create the Oracle objects such as tables, views, etc. In the final step these tables will be populated by transferring the data from the source to Oracle 10G XE. MS Access 2003 Source An empty MS Access database file TestMigration.mdb is created in the default directory, My Documents. An Employees table will be imported, an Orders table will be linked and a TestQuery based on selecting a few columns of Employees table will be created. The Employees table and the Orders table may be found in the Northwind Database that ships with most of the MS Access versions. Creating a New User in Oracle 10G XE As described in the overview, the MS Access Database will be migrated to a User schema in Oracle 10G XE, but this requires reating this schema. Only a user with DBA privileges can create a new user. Open the Homepage of the Oracle 10G XE Server. Login with the credentials you supplied while installing the software where the user is system and the password is what you chose at that time, as shown in the next figure.   This gives you access to several of the tools that you can use to administer as well as work with database objects. Click on the icon for Administration and follow the drop-downs till you get to the menu item, Create User, as shown in the next figure. Create a new user MigrateAccess with some password that you choose and confirm. Keep the account status unlocked. This uses the default tablespace called USERS. The default user privilege does not include the DBA role but for this example, the DBA is also included by placing a check mark in this selection. Also several other system wide privileges are also granted. Please follow steps described in the earlier article for the details. The next figure shows all the details filled in. After this when you click the Create button you will have created the user, MigrateAccess. When you click the button Create, you will notice that the ‘bread crumb’ will change to Manage Database Users. You will notice that the new user MigrateAccess has been added to the list of users, as shown in the next figure. As no expiry was set for this user in the previous screen, you can notice that there is no expiry shown in the following screen. Now if you logout (remember you logged in as SYSTEM) and login with the new credentials, MigrateAccess/[chosen password] you can access all the tools on the database. Of course, all the objects (tables, views, etc) will be empty. Creating the Repository to Store Schemas Migration using this tool requires an Oracle database schema to store the Meta data it collects about the source. You will create a connection from the Oracle SQL Developer to the Oracle 10 XE, in which, you just finished creating a new user schema. This user’s schema is where the repository contents will be stored. Making a connection to the Oracle Right click on the Connections node, and from the drop-down menu select New Connection. This brings up the New / Select Database Connection (this has been described in the earlier referenced article) window. It comes up with the default connection to an Oracle database. It even recognizes the local Oracle 10G XE, capturing all its details as shown. You need to provide a Connection Name, a Username and a Password. The connection name is your choice (herein called conMigrate) and the user name and password is the same that was used while creating the new user MigrateAccess. When you click on the button ‘Test’, a (success) status message will be posted to this form above the Help button, as shown in the next figure after a little while, preceded by a little progress window. Now click on the OK button on the New / Select Database Connection window. This adds the conMigrate connection to the list of Connections as shown in the next figure. Notice that objects are all empty as we discussed earlier. Create Repository Click on the main menu item Migrate. From the drop-down, click on Repository Management –> Create Repository as shown in the next figure. This brings up the Create Repository window showing the connection conMigrate as shown in the next figure. You may connect or disconnect this from the tool as long as the authentication information is available. Now click on the Create button. This brings up the Installing Repository window which reports the various objects installed and finally shows a message “Repository Built Successfully” as shown in the next figure. Click on the Close button on this window. Now login to the Oracle 10G XE with the credentials for the user MigrateAccess, and click on the object browser. Now you see all the Tables, Views, etc in the repository as shown. You will notice that either two more windows, named captured and converted models appear below the Connections node in Oracle SQL Developer, or if they are not found in the Connections node, you may find in the submenu of the main menu, View. The next figure shows the submenus of the View menu. Connect to the Source Database Right click on the connection node and establish a new connection so that you can connect to the source database, conTestMigration as shown in the next figure. When you click the Test button you will see a message that gets posted to the screen indicating the connection was a success. Click on the Connect button. This adds the conTestMigrate connection to the list of Connections in the navigator window.

MySQL Query Browser, one of the open source MySQL GUI tools from MySQL AB, is used for building MySQL database queries visually. In MySQL Query Browser, you build database queries using just your mouse—click, drag and drop! MySQL Query Browser has plenty of visual query building functions and features. This article shows two examples, building Join and Master-detail queries. These examples will demonstrate some of these functions and features. Join Query A pop-up query toolbar will appear when you drag a table or column from the Object Browser’s Schemata tab to the Query Area. You drop the table or column on the pop-up query toolbar’s button to build your query. The following example demonstrates the use of the pop-up query toolbar to build a join query that involves three tables and two types of join (equi and left outer). Drag and drop the product table from the Schemata to Add Table(s) button. A SELECT query on the product table is written in the Query Area. Drag and drop the item table from Schemata to the JOIN Table(s) button on the Pop-up Query Toolbar. The two tables are joined on the foreign-key, product_code. If no foreign-key relationship exists, the drag and drop won’t have any effect. Drag and drop the order table from Schemata to the LEFT OUTER JOIN button on the Pop-up Query Toolbar. Maximize query area by pressing F11. You get a larger query area, and your lines are sequentially numbered (for easier identification). Move the FROM clause to its next line, by putting your cursor just before the FROM word and press Enter. Similarly, move the ON clause to its next line. Now, you can see all lines completely, and that the item table is left join to the order table on their foreign-key relationship column, the order_number column. As of now our query is SELECT *, i.e. selecting all columns from all tables. Let’s now select the columns we’d like to show at the query’s output. For example, drag and drop the order_number from the item table, product_name from the product table, and then quantity from the item table. (If necessary, expand the table folders to see their columns). The sequence of the selecting the columns is reflected in the SELECT clause (from left to right). Note that you can’t select column from the left join of the order table (if you try, nothing will happen) Next, add an additional condition. Drag and drop the amount column on the WHERE button in the Pop-up Query Toolbar. The column is added, with an AND, in the WHERE clause of the query. Type in its condition value, for example, > 1000. To finalize our query, drag and drop product_name on the ORDER button, and then, order_number (from item table, not order table) on the GROUP button. You’ll see that the GROUP BY and ORDER clauses are ordered correctly, i.e. the GROUP BY clause first before the ORDER BY, regardless of your drag & drop sequence. To test your query, click the Execute button. Your query should run without any error, and display its output in the query area (below the query).