Oracle SQL Developer 2.1
Description
- Includes the latest features to enhance productivity and simplify database development
- Covers reporting, testing, and debugging concepts
- Meet the new powerful Data Modeling tool ñ Oracle SQL Developer Data Modeler
- Detailed code examples and screenshots for easy learning
Book DescriptionAs technology rapidly evolves, many developers are looking for valuable tools to assist them with their daily tasks. When dealing with databases, a clean, easy-to-navigate interface for working and browsing is essential. Oracle SQL Developer is a graphical user interface that makes life much easier by allowing you to browse database objects, run SQL statements and scripts, and create, edit, and debug PL/SQL statements in the most efficient way. It enhances productivity and simplifies your database development tasks. Although the SQL Developer journey looks simple and easy, there are many areas that can go undiscovered, leaving you just scratching the surface. It's easy to get started and master this powerful tool with this book to hand. It will provide you with in-depth details about all aspects of using SQL Developer to assist you in your day-to-day database tasks and activities. You will learn to utilize SQL Developer's extensible environment to support your ongoing needs This book offers detailed instructions for installing, configuring, and effectively using Oracle SQL Developer. You will learn how to utilize every feature of this development tool and make the most out of it. While none of the tasks are complex, the book progresses from the easy, most commonly used features, such as browsing objects and writing queries in the SQL Worksheet, to the more involved and possibly less frequently used features, such as Tuning and Testing SQL and PL/SQL, and adding User Extensions, and finally to those features used by a smaller more targeted audience, such as Migrations, Oracle APEX, and the Data Modeler. Throughout the book there are tips and suggestions gathered as a result of working with the current SQL Developer user base. This book will also show you how to assess the health of your database with built-in as well as customized reports. By the end of the book you will be confident in making the best use of SQL Developer, and be able to set up and maintain a productive environment for quick and easy database development.What you will learn - Build complex queries based on a number of tables using visual Query Builder
- Assess the health of your database, data structure of your application, and data in that application with built-in as well as user-defined reports
- Create, compile, and debug PL/SQL code and explore available features to facilitate writing PL/SQL code
- Integrate your SQL Developer with open source version control systems CVS and Subversion, which allow checking out of files from a repository, editing, and checking them back in
- Enter and execute your SQL, PL/SQL, and SQL*Plus statements with the SQL Worksheet interface
- Produce easily replicable scripts that copy and move data from one database instance to another, or from one schema to another
- Create advanced database connections using a variety of connection and authentication types available for Oracle as well as non-Oracle databases
- Create, review, and update database schema designs with SQL Developer Data Modeler
- Augment your environment with features that are specific to your needs by extending your SQL Developer with XML structured user-defined extensions
- Monitor and manage your Application Express applications by integrating with SQL Developer
- Set up an easy and quick migration environment for your database schema by using the migration repository
- Browse and review non-Oracle databases, before using the migration environment to migrate and consolidate databases on the Oracle platform
Who this book is forThis book is for Oracle developers who want to ease their database development, and enhance their productivity using Oracle SQL Developer. You should have a programming knowledge of SQL and PL/SQL, and a general familiarity with Oracle database concepts.
All prices
More details
Other editions
Additional editions
Person
Sue Harper was a senior school mathematics and science teacher by profession. It was this teaching qualification that opened the door to Oracle in South Africa, where Sue started as a SQL, PL/SQL, SQL Forms, and Reports instructor in 1992. Before long she'd added Oracle Designer to her repertoire and soon left for the UK, to join the Designer curriculum development team. Sue was a course writer and then product manager for Oracle Designer for many years, traveling extensively meeting customers and training Oracle staff and instructors. Sue was later product manager for Oracle JDeveloper working with database and modeling tools in that product. Sue is currently product manager for Oracle SQL Developer, SQL Developer Data Modeler, and SQL Developer Migrations. Based at home, Sue works with her team, scattered around the world and customers, running web-based training sessions or helping with individual queries. She frequently attends conferences where she presents SQL Developer material and works with customers on their queries. Sue lives in West London, where she enjoys walking with her camera and her dog in the extensive local parks and further afield throughout the UK.
Content
- Cover
- Copyright
- Credits
- About the Author
- About the Reviewers
- Table of Contents
- Preface
- Chapter 1: Getting Started with SQL Developer
- Preparing your environment
- Finding and downloading the software
- Downloading and installing the Java Development Kit
- Installing and starting SQL Developer
- Working with different platforms
- Migrating settings from a previous release
- Maintaining your environment
- Verifying the current release
- Using Check for Updates
- Managing the system folder and other files
- Sharing preferences
- Alternative installations of SQL Developer
- Oracle JDeveloper
- Oracle Database 11g
- Troubleshooting
- A quick overview
- Sample schemas
- Creating your first connection
- Using basic commands in the SQL Worksheet
- Browsing and updating data
- Running reports
- Navigating around SQL Developer
- Managing SQL Developer windows
- Tiling windows
- Splitting documents
- Maximizing detail
- Resetting the window layout
- Finding more help
- Summary
- Chapter 2: Browsing and Editing Database Objects and Data
- Browsing objects
- Working with the Connections navigator
- Opening connections
- Working with objects
- Filtering objects
- Display editors
- General display editors
- Working with the display editors
- Using the SQL display editor
- Working with the data grids
- Controlling the column display
- Sorting the data
- Filtering columns
- More data grid context menus
- Count Rows and the Single Record View
- Working with Updating Data Grids
- Updating data
- Reviewing other database object nodes
- Accessing objects you don't own
- Recycle Bin
- Creating and updating database objects
- Creating new objects
- Creating tables
- Creating views
- Reviewing a few specific objects
- Editing objects: Putting context menus to work
- Editing objects
- Diving into context menus
- Using context menus as utilities
- Summary
- Chapter 3: Working with the SQL Worksheet
- Introducing the SQL Worksheet
- Controlling the environment
- Opening SQL Worksheets
- Working with multiple worksheets
- Switching connections
- Getting started
- Writing and executing commands
- Writing your first statements
- Running statements
- Run script
- Using SQL*Plus commands
- Supporting SQL*Plus
- Running scripts
- Reviewing unsupported SQL*Plus commands
- Working with SQL
- Dragging and dropping tables to create queries
- Formatting code
- Managing the case
- Formatting SQL for use in other languages
- Working with code completion insight
- Including code snippets
- Using the File navigator
- Opening files
- Using extra features in the worksheet
- SQL History
- DBMS Output
- OWA Output
- Using the Query Builder
- Building an SQL query
- Selecting the tables, column, and joins
- Viewing the Results
- Adding the WHERE clause
- Returning to the SQL Worksheet
- Summary
- Chapter 4: The Power of SQL Reports
- Introducing SQL Developer reports
- Who can run reports?
- When do you use reports?
- Running a report
- Using bind variables
- Privileges required for running reports
- Switching users
- Browsing shipped reports
- Running data dictionary reports
- Getting to know the data dictionary
- About the database
- Reviewing Privileges and Security reports
- Assisting with quality assurance
- Using the PL/SQL reports
- Running ASH and AWR reports
- Other categories
- Migration reports
- Application Express reports
- Data Modeler reports
- Running reports from other menus
- Monitor sessions
- Managing the database
- Real-time SQL monitoring
- Creating your own reports
- Getting started
- Creating folders
- Storing reports
- Creating general reports
- Building general tabular reports
- Adding bind variables
- Drilling down through reports
- Creating a drill-down report
- Master-detail reports
- Creating master-detail reports
- Creating sibling details
- Adding charts
- Building other graphical reports
- Other reports styles
- Sharing reports
- Copying and reusing reports
- Importing and exporting
- Sharing reports through user defined extensions
- Summary
- Chapter 5: Working with PL/SQL
- Creating PL/SQL code
- Writing PL/SQL in the SQL Worksheet
- Using code insight
- Using code snippets and code templates
- Creating and compiling PL/SQL program units
- Working with triggers
- Using the Create Trigger dialog
- Viewing trigger details
- Controlling triggers
- Adding triggers that populate columns
- Adding functions or procedures
- Editing program units
- Working with errors
- Testing and executing program units
- Creating packages
- Creating the body
- Editing code
- Refactoring code
- Searching for code
- Finding DB Object
- Debugging PL/SQL
- Debugging PL/SQL code
- Using the debugging mechanism in SQL Developer
- Remote debugging
- Summary
- Chapter 6: SQL and PL/SQL Tuning Tools
- Support for tuning code in the SQL Worksheet
- Working with EXPLAIN PLAN
- Controlling the Explain Plan output
- Execution plan details
- Using Autotrace
- Additional performance tuning tools
- Using SQL reports
- Running the Top SQL reports
- Monitoring your environment
- Inspecting SQL trace files
- Profiling PL/SQL
- Getting started
- Preparing the environment
- Reviewing the output
- Summary
- Chapter 7: Managing Files
- Introducing source code control
- Overview
- Ways of working
- The repository is the point of truth
- SQL Developer integration
- Subversion (SVN)
- Concurrent Versions System (CVS)
- Other version control systems
- Getting started
- Invoking the Files navigator
- Browsing and editing files
- Reviewing the file editors
- Editing other file types
- Working with the file history
- Introducing the Versioning Navigator
- Managing general version control preferences
- Setting up the repository
- Creating connections to a version repository
- Browsing files in the repository
- Working with files under version control
- Placing files under version control
- Importing files into the repository
- Refreshing the repository
- Understanding revision numbers
- Checking out files
- Saving files
- Checking files in
- Comparing and merging code
- Creating patches
- Summary
- Chapter 8: Importing, Exporting, and Working with Data
- Exporting data
- Exporting instance data
- Setting up the export file
- Exporting SQL DML
- Exporting to HTML
- Supporting export for SQL*Loader
- Exporting to Microsoft Excel
- Exporting to XML
- Exporting DDL (Metadata)
- Exporting table DDL
- Selecting multiple tables for DDL export
- Using the Database Export wizard to export DDL and data
- Starting the export wizard
- Selecting objects for generation
- Specifying objects
- Specifying data
- Running the script
- Importing data
- Importing data from SQL script files
- Importing data from XLS and CSV files
- Creating a table on XLS import
- Using the Database Copy wizard
- Comparing the database copy alternatives
- Running the Database Copy wizard
- Comparing schemas
- Summary
- Chapter 9: Database Connections and JDBC Drivers
- Working with Oracle connections
- Using alternative Oracle connection types
- Reviewing the Basic connection
- Accessing the tnsnames.ora file
- Accessing LDAP server details
- Creating advanced connections with JDBC URLs
- Connecting to Oracle TimesTen
- Reviewing JDBC drivers
- Oracle JDBC thin driver (Type IV driver)
- Oracle JDBC thick driver (Type II driver)
- SQL Developers shipped drivers
- Using different authentication methods
- OS Authentication
- Using Proxy authentication
- Using Kerberos authentication
- Using RADIUS authentication
- Creating non-Oracle database connections
- Setting up JDBC drivers
- Using Check for Updates
- Manually adding JDBC drivers
- Creating connections to the third-party databases
- Connecting to IBM DB2
- Microsoft Access
- Connecting to Sybase Adaptive Server or Microsoft SQL Server
- Connecting to MySQL
- Organizing your connections
- Creating folders
- Working with folders
- Managing folders
- Exporting and importing connections
- Summary
- Chapter 10: Introducing SQL Developer Data Modeler
- Oracle SQL Developer Data Modeler
- Feature overview
- Integrated architecture
- Getting started
- Installing and setting up the environment
- Oracle clients and JDBC drivers
- Creating your first models
- Importing from the Data Dictionary
- Creating a database connection
- Using the import wizard
- Reviewing the results
- Saving designs
- Working with diagrams and their components
- Formatting the elements
- Changing the default format settings
- Setting general diagram properties
- Creating subviews and displays
- Adding subviews to your design
- Adding displays
- Creating a composite view
- Controlling the layout
- Adjusting the level of detail displayed
- Adjusting the width and height across the model
- Controlling alignment
- Working with lines
- Managing lines with elbows
- Managing straight lines
- Analysis, design, and generation
- Flow of work
- Starting with analysis (top down)
- Importing existing models (bottom up)
- Building the relational model
- Logical models
- Creating an ERD
- Supporting alternate notations
- Creating constraints, domains, and setting default values
- Working with domains
- Creating domains
- Assigning domain valid values to an attribute or column
- Setting valid values at attribute or column level
- Introducing forward and reverse engineering
- Forward engineering
- General engineering dialog features
- Maintaining the model layout
- Reverse engineering models
- Creating relational models
- Working with the relational model
- Setting naming standards templates
- Building the physical model
- Importing a schema from the data dictionary
- Creating a new physical model
- Adding new database sites
- Reviewing physical properties
- Propagate properties
- Generating the DDL
- Reviewing and applying Design Rules
- Integration with Oracle SQL Developer
- Creating a new model
- Creating and running reports
- Setting up the reporting schema
- Summary
- Chapter 11: Extending SQL Developer
- Introducing extensibility
- Extensibility in SQL Developer
- Who builds extensions?
- Why extend?
- SQL Developer XML extension types
- Adding an XML extension
- Sharing user-defined reports
- Adding display editors
- Examples of display editors
- Building the XML file for a display editor
- Working with context menus
- Adding a context menu to the connections menus
- Passing parameters
- Creating a utility using context menus
- Including the user-defined extension for context menus
- Adding new nodes to the Connections navigator
- Including user-defined extensions for a navigator node
- Adding a new tree to the navigator
- Adding multiple nodes
- Reviewing an example
- Adding support for dimensions
- Working with extensions
- Controlling existing extensions
- Adding in new Java extensions
- Removing extensions
- Sharing extensions
- Summary
- Chapter 12: Working with Application Express
- Setting the scene
- Setting up in Application Express
- Creating a workspace and database schema
- Creating an Application Express user
- Browsing applications in SQL Developer
- Creating a connection in SQL Developer
- Browsing and working with schema objects
- Browsing the applications
- Mapping objects in SQL Developer to Application Express
- Tuning SQL and PL/SQL code using SQL Developer
- Working with Region Source SQL
- Tuning with Explain Plan
- Working with PL/SQL code
- Replacing the anonymous block in Application Express
- Managing applications in SQL Developer
- Importing applications
- Modifying applications
- Deploying applications
- Controlling services
- Reporting on applications using SQL Developer
- Summary
- Chapter 13: Working with SQL Developer Migrations
- Introducing SQL Developer Migrations
- An overview of the migration process
- Offline or online migration choices
- Supported third-party databases
- Setting up your environment
- Setting up the JDBC drivers
- Creating third-party connections
- Accessing non-Oracle databases
- Browsing database objects
- Using the SQL Worksheet
- Managing the repository
- Creating the repository
- Associating a repository with a user
- Planning database connections
- Setting up the source database connection
- Setting up the target database connection
- Migrating
- Using Quick Migrate
- The migration
- Verifying the results
- Delving into a complex migration
- Preparing for the migration
- Capturing the model
- Converting the model
- Generating the scripts
- Executing the script
- Populating the target tables with data
- Offline migrations
- Additional migration activities
- Migration reports
- Summary
- Index
Chapter 1. Getting Started with SQL Developer
This book is divided into chapters that focus on the different areas or functionality in SQL Developer. The progression through the chapters is from the more frequently used features to those less frequently used. This initial chapter is all about preparing your environment, installation, and getting started.
SQL Developer is easy to set up and use, so there is very little setup required to follow the examples in this book. The best way to learn is by practice, and for that you'll need a computer with access to an Oracle database and SQL Developer. This chapter, and indeed the rest of the book, assumes you have a computer with Microsoft Windows, Linux, or Mac OS X installed, and that you have access to an Oracle database. It focuses on the alternative installations available for SQL Developer, where to find the product, and how to install it. Once your environment is set up, you can follow a quick product walk-through to familiarize yourself with the landscape. You'll create a few connections, touch on the various areas available (such as the SQL Worksheet and Reports navigator), and learn about the control of the windows and general product layout.
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:
- For Mac OS X:
Note
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.
Note
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 files, 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 file and select it. The path should include the full path and executable (for example, C:\Program Files\Java\jdk1.6.0_13\bin\java.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 specific 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 \sqldeveloper\bin, 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 \sqldeveloper\bin\sqldeveloper.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.
Tip
Oracle SQL Developer Three steps to getting started on Microsoft Windows:
Download: Download the full file, with JDK, from the Oracle Technology Network web site.
Unzip: Unzip the file to an empty directory.
Double-click: Double-click on the \sqldeveloper\sqldeveloper.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 double-click to unzip the file. This creates an icon for SQL Developer on your desktop. Double-click to run the application.
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 first 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 Data\SQL Developer\system1.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,...
