44,39 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
As 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.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 454
Veröffentlichungsjahr: 2009
Ähnliche
Table of Contents
Oracle SQL Developer 2.1
Sue Harper
Oracle SQL Developer 2.1
Copyright © 2009 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: November 2009
Production Reference: 1171109
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 978-1-847196-26-2
www.packtpub.com
Cover Image by Sue Harper (<[email protected]>)
Credits
Author
Sue Harper
Reviewers
Barry McGillin
Michael Wickham
Dermot O'Neill
Joyce Scapicchio
Kris Rice
Raghvendra Saboo
Vadim Tropashko
Acquisition Editor
James Lumsden
Development Editor
Ved Prakash Jha
Technical Editor
Hithesh Uchil
Indexer
Monica Ajmera
Editorial Team Leader
Akshara Aware
Project Team Leader
Priya Mukherji
Project Coordinator
Leena Purkait
Proofreader
Joel T. Johnson
Graphics
Nilesh R. Mohite
Production Coordinator
Adline Swetha Jesuthas
Cover Work
Adline Swetha Jesuthas
About the Author
Sue Harper is a product manager for Oracle SQL Developer, SQL Developer Data Modeler, and SQL Developer Migrations working in the Database Development Tools group at Oracle. She has a BSc in Mathematics and Physics from the University of the Witwatersrand, Johannesburg, and a post graduate teaching diploma. It was this teaching qualification that opened the doors to working with Oracle products in South Africa, where Sue started as an instructor in 1992, teaching SQL, PL/SQL, and SQL Forms and Reports. Before long, she'd added Oracle Designer to her repertoire and soon left for the UK to join the Oracle Designer curriculum development team. Sue was a course writer and then an Oracle Designer product manager, traveling extensively, meeting customers, presenting at conferences, and running train-the-trainer classes. Later, she became a product manager for Oracle JDeveloper working with the database and modeling tools and helping the Oracle Designer community learn about the new tools, before moving to the Database Development Tools group.
Based at home, Sue works with her team scattered around the world and with customers. She runs web-based training sessions and writes supporting material for the products. She frequently attends and presents at conferences and has a number of blogs, which she contributes to sporadically.
Sue lives in West London, where she enjoys walking her dog in the extensive local parks. As a walker, her favorite stomping ground is the Drakensberg in South Africa. With the 'berg' too many miles away, any local hills will do. As a keen photographer, she now enjoys the long distance trails in the UK and is often out in the countryside with her camera.
Acknowledgement
I'd like to thank the people who have helped me complete this book. In particular Barry McGillin, who reviewed each chapter for technical errors, and Michael Wickham, who joined Barry, to add his technical insight when my writing became more sporadic and the chapters backed up! The editorial staff and publishing team at Packt who patiently accepted each new delay to the schedule, while gently reminding me of the ever-encroaching deadlines.
I'd like to thank my friends who patiently listened each time I said I couldn't go out, "because I was writing…" even those in different countries who were also on the receiving end of my tales that life had become seven-days SQL Developer!
I wasn't planning on writing a book. Working with the product, the team, and customers keeps me busy enough, but there seemed to be more demand for a source of answers to the many questions that bubble up to the surface from forums and events, that it seemed a good thing to do. Believe it or not, I enjoyed it. This would not have been possible without the support of my team, which I am very happy to be a part of. Despite the distances, we are in regular contact, whether chatting to team members in Ireland or Northern Ireland, India or Bulgaria, or in any of the various States of America. We know that we can pick up the phone or dash off a quick instant messenger note and have a chat or ask a question. In particular, I'd like to thank my manager, Kris Rice, and Mike Hichwa who runs the group. Without their guidance and support, the team wouldn't work the way it does, a rare gift when managing a remote and culturally disparate group. Kris in particular, who has his hands full helping me keep things in perspective and constantly tries to help me maintain the balance.
About the Reviewers
Barry McGillin is the development manager for Oracle SQL Developer and has been with Oracle over 15 years. He is married to Lisa with two children, Rebecca and Katie-Ellen. Barry holds an honors degree in Computing and Information Systems from the University of Ulster and lives in Omagh, Northern Ireland. He has worked across most business areas in Oracle, from product development support and consulting, specializing in core database technology, and database migration, before working with Sue Harper and Kris Rice on Oracle SQL Developer. Barry has worked on several books as a Reviewer and Technical Advisor over the years, culminating in this work with Sue.
I'd like to thank Sue for asking me to review this book. She has a passion for completeness and accuracy, which has helped us create better tools for our customers. I'd also like to thank my family, my wife Lisa for patience, love and support, and the tea and coffee. And lastly, my two little ladies, Rebecca and Katie-Ellen who constantly challenge us to answer the questions of life and force us to be better as a result of trying to.
Michael Wickham is the Global Technical Lead for SQL Developer at Oracle Technical Support. He has a Bachelors Degree and Masters Degree in Computer Science from Colorado Technical University, an Associates Degree in Electronics and an Associates Degree in Aviation.
Michael may be reached at <[email protected]>
Other interests are Videography and Hydroponics.
I would like to thank Sue Harper who has authored this book. For the years of assistance that she has given me and for all she has done for the advancement of Oracle Tools such as Oracle Designer, SQL Developer, SQL Developer Data Modeler, and others.
Preface
Oracle SQL Developer is a relatively new product included in the range of Oracle products. It was first introduced to the world in September 2005, by its code name Project Raptor. Raptor is a name many still cling to, despite being replaced very quickly with the full product name, Oracle SQL Developer (and referred to in the rest of the book as SQL Developer). The first production version was released in early 2006 and had many Oracle customers both skeptical and excited.
SQL Developer is a Graphical User Interface (GUI) for browsing and working with databases. Primarily developed for the Oracle database, it also provides a GUI for a selection of popular non-Oracle databases. As technology has evolved, many developers are looking for modern tools to assist them in daily tasks. A clean, easy to navigate interface is essential and many tools are judged on looks, performance, and functionality.
Initially billed as a light-weight, clean, easy to install GUI for the Oracle database, SQL Developer is gaining momentum in the market and broadening its scope in its offering.
The skeptics mentioned are ever concerned that Oracle is not really interested in the product, backing up this concern with the fact that SQL Developer is free. Disappointing as it is that "free" is equated with "poor quality", the last couple of years have considerably stilled many of these dissenting voices and the market using SQL Developer continues to increase. Time will tell.
What this book covers
The book is designed to allow you to dip into any chapter that is your current area of focus. Having said that, if you have never used SQL Developer before, are new to Oracle and SQL, or have never used a GUI tool before, we recommend that you read the introduction, which gets you started slowly. You can then work through the first few chapters in order. Once you have covered the first three chapters, you should be on your way.
As the chapters progress, while they do not necessarily get more complex, they do rely to some extent on existing knowledge. The latter portion of the book is not considered to be more complex, but instead focuses on areas used by specific target audiences, such as Oracle Application Express or migrating from non-Oracle databases.
We have loosely divided the book into the following three sections.
I: Getting started
The first few chapters introduce SQL Developer. Use them to get to know the layout of the product and the essential features it provides. For those new to Oracle, or application developers who spend the day using other development tools, these first few chapters may be the only chapters needed.
In Chapter 1: Getting Started with Oracle SQL Developer, we provide general information about SQL Developer, where you can find it, how to install it, and how to get started. Using a simple example, you quickly walk through the product, touching a variety of areas to get a feel for the navigation, the layout, and some of the features available. We introduce the environment, how to navigate and manage the interface, the database, and schemas used in this book. The chapter includes tips for customizing the environment to suit your preferences.
In Chapter 2: Browsing and Editing Database Objects and Data, you will create, edit, and update database objects, such as tables, views, sequences, and instance data (the data in those tables), using data grids. You'll also learn how to access and use the utilities, wizards, and dialogs, which SQL Developer provides, to work with data and database objects. Lastly, you will use SQL commands and scripts, accessing the code formatter and other assistants that separate a GUI tool from a command–line environment.
In Chapter 3: Working with the SQL Worksheet, you see that the SQL Worksheet provides a scratch pad for all SQL, PL/SQL, and SQL*Plus statements and commands. In this chapter, you learn to work with the SQL Worksheet and the commands it supports, how to use code insight, templates, snippets, and formatting code. You learn how to manage the environment and to work with multiple worksheets. We introduce the Query Builder and how to write, test, and run SQL queries using the Query Builder.
In Chapter 4: The Power of SQL Reports, you look at the variety of SQL reports provided by SQL Developer, why they are useful in daily tasks, and how to run and use them. You'll learn how to create your own reports, such as master-detail or drill-down reports, and share your user-defined reports.
II: Delving a little deeper
These chapters are intended for all developers, perhaps more focused on those of you who spend your days working with database objects and data. While not strictly advanced features, there are areas here that can be more involved, and knowledge of how the tool works will complement your existing knowledge of developing and working with code.
In Chapter 5: Working with PL/SQL, we start by reviewing various PL/SQL structures. This chapter covers creating, editing, compiling, and debugging PL/SQL code. You'll see how the various search tools work, and use code templates and snippets. You'll learn how to refactor PL/SQL code, and search using the Oracle Database 11g PL/Scope feature.
Chapter 6: SQL and PL/SQL Tuning Tools. Although not designed to teach you how to optimize your code, this chapter shows you the different utilities that SQL Developer provides to help you see problem areas and work at fixing them. Utilities include using Explain Plan, reviewing trace files, and auto trace.
In Chapter 7: Managing Files, you'll see that SQL Developer provides an integrated file navigator that allows you to browse the file system and open files from within the tool. This chapter introduces the file navigator and the features it supports. Working in conjunction with the file navigator, SQL Developer integrates with open source version control systems. This chapter discusses the alternatives and shows you how to place your code under version control and work within a team sharing code, by comparing and merging code.
In Chapter 8: Importing, Exporting, and Working with Data, you'll learn how SQL Developer provides a number of utilities for moving or copying both the data structures and the instance data between schemas and databases. This chapter introduces you to the schema copy, diff, and export wizards and related export and import utilities.
III: Focus areas
The last few chapters are focused on specific audiences. We don't specifically cover complex features, but there are aspects of some of these features that require knowledge beyond the scope of this book.
In Chapter 9: Database Connections and JDBC Drivers, we'll look at the various types of database connections available, how to set them up, and when to use them. We'll look at various authentication methods, such as LDAP, OS authentication or strong authentication methods like Kerberos, and compare Basic, TNS, and JDBC connections.
This chapter also looks at setting up the required JDBC drivers and creating connections to non-Oracle databases. The chapter closes with a section on managing your connections.
Chapter 1: Introducing SQL Developer Data Modeler. Whether you just want a diagram of how your tables connect, or you want to build an entity relationship diagram, transform that to a relational model, and generate the DDL script to create the objects in your database, the SQL Developer Data Modeler provides the tools you need. This chapter introduces some of the modeling utilities available in SQL Developer Data Modeler. You'll also learn about the integration points with SQL Developer and the Data Modeler Viewer in SQL Developer.
In Chapter 11: Extending SQL Developer, you'll see that SQL Developer is an extensible tool. This means you, as an end user of the tool, can add in your own features. These may be small, providing just a single input or output field, and only require XML and SQL knowledge, while others are more extensive and require Java skills. This chapter reviews the various ways you can extend SQL Developer using SQL and XML.
In Chapter 12: Working with Application Express, you'll see how SQL Developer connects to and provides utilities for interacting with Application Express (Oracle APEX). In this chapter, features you'll learn to use include setting up the environment to work with Oracle APEX, importing, browsing, and deploying applications. You'll also see how to tune and refactor SQL and PL/SQL code.
In Chapter 13: Working with SQL Developer Migrations, you'll understand how SQL Developer helps you connect to and browse non-Oracle or third-party databases. This chapter looks at preparing the SQL Developer environment, and browsing the supported third-party databases. The focus of the chapter is on migrating from a third-party database to Oracle, from setting up the repository, through the capture and conversion phases, and ending with data migration. You'll also learn to translate pieces of code, such as translating T-SQL to PL/SQL.
Standards and assumptions
Unless otherwise stated, the environment used in the examples and referred to throughout the book is Oracle Database 11g Release 1, installed on Microsoft Windows XP. We use the shipped Oracle sample schemas, in addition to using the SYSTEM and SYS users.
Oracle offers a free database, Oracle Express Edition 10g (XE), which you can download and use for many of the examples. Some of the discussion is related to new Oracle Database 11g functionality that is not available on any Oracle Database 10g release, including XE. In the case where the feature is Oracle Database 11g specific, the text will indicate this. However, the text does not list anomalies for each database version.
This book was written using and is based on Oracle SQL Developer 2.1. Screenshots and menu items all refer to SQL Developer 2.1 and were accurate at the time of writing, completed just prior to the production release of the software. Minor differences are inevitable due to changes in the later stages of product development prior to production. You can also use the book, and much of the content, as a guide for working with earlier releases of SQL Developer. Where they are significant, we make note of the differences in features that have changed from earlier releases.
Who this book is for
This book provides you with in-depth detail about all aspects of using SQL Developer to assist you in your day-to-day database tasks and activities. It will also make you more productive in some of the more mundane tasks, while providing you with an extensible environment to support your ongoing needs.
No attempt is made to teach Oracle, SQL, or PL/SQL, or to suggest best practices. However, if you are new to the environment, SQL Developer should ably assist you in getting familiar with the database, SQL, and PL/SQL. We'll show you how to create data models and extend existing data designs. We do not teach data modeling and design. Additionally, no part of the book is focused on using non-Oracle databases, except when browsing or importing objects from these for the purpose of migration.
If you have ever accessed an Oracle Database for information on any database objects you are working with, such as tables, constraints, data, or PL/SQL code, whether you are building applications, doing data analysis, or just working with database structures, then you can use SQL Developer for these tasks and this book might be for you! If you are adept with SQL and PL/SQL, know your way around the database, and you're a command-line user, you might be intrigued and we hope that you can dip into this book on an ad hoc basis to get you started and on the road to a "GUI life". We're aware that you'll never fully move from the command-line, but you may well find that using the utilities SQL Developer provides, frees up some of your time to build a few extensions, providing an interface for the utilities you almost certainly already have! For those of you familiar with any of the many GUI tools available today, this book can help you with the details and specifics that make SQL Developer popular.
In general, you'll get more out of the book if you know SQL, PL/SQL, and have a general familiarity with Oracle concepts. There is excellent Oracle Documentation available online, so you can access those if we move into an area that is new to you.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an email to <[email protected]>, and mention the book title via the subject of your message.
If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or email <[email protected]>.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book on, see our author guide on www.packtpub.com/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Note
Downloading the example code for the book
Visit http://www.packtpub.com/files/code/6262_Code.zip to directly download the example code.
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration, and help us to improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the let us know link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata added to any list of existing errata. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or web site name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
Questions
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.
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:
In each case, make your selection and download the required file.
The download sites for the JDK are as follows:
http://java.sun.com/javase/downloads/index.jsp
For Mac OS X:http://developer.apple.com/java/download/
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, D:\SQLDeveloper\Builds\1.2.1\1.2.1.3213\sqldeveloper\sqldeveloper\system).
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:
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.
You can initially elect to see just the third-party updates, or all updates available, by selecting all options, as shown in the following screenshot:
The database drivers for some of the non-Oracle databases are located in Third Party SQL Developer Extensions. The Third Party update center also includes a selection of customer developed SQL Developer extensions. The customer extensions are developed, supported, and updated by the customer involved, and are not tested, certified, or supported by Oracle.
As with all software downloads from the Internet, you are required to read and accept the license agreements. The Check for Updates utility directs you to the appropriate licenses, before downloading the software. If the updates are from Oracle, you will need to provide your Oracle Technology Network sign-on details.
Check for Updates is only used to apply patches to your install. Starting with SQL Developer 1.5.1, the team released patches which are applied to upgrade the product in place. These patches fixed one or two bugs and did not constitute a new download, or even a full install. For all other releases, you need to do a full install as described earlier.
Managing the system folder and other files
SQL Developer maintains a series of files as you work with the product. These files are created and stored in the \Documents and Settings\<your_user>\Application Data\SQL Developer folder. On Linux, these files are all stored in a folder ~/.sqldeveloper/.
Deleting this folder is equivalent to returning a SQL Developer installation to its factory settings. The files at this highest level are:
These files are used by each of the SQL Developer installations you have. For example, you may elect to have the latest installation in addition to a number of earlier releases. Having several different releases of SQL Developer on one machine is acceptable, as the installations have no impact on each other, except that they do share these files.
Once you have started SQL Developer, a folder with sub-folders and files is created in the \Documents and Settings\<your_user>\Application Data\SQL Developer folder. The top-level folder is labeled systemx.x.x.x.x (for example system1.5.0.54.40). The systemx.x.x.x.x folder contains all of the other preferences and the settings that pertain to the specific release in use. In this case, deleting the system folder is almost equivalent to resetting SQL Developer to its factory settings, except that any user defined reports, SQL history, and code templates are not lost.
Sharing preferences
Preferences are set for your local environment and are therefore not shared globally between teams. However, you can export the SQL Formatter preferences set. This allows you to share the settings between team members and ensure that you all code to the same settings. To export your SQL Formatter settings, select Tools | Preferences and expand the Database node in the tree. Select SQL Formatter, you can now export or import previous saved settings.
Alternative installations of SQL Developer
We have been discussing the installation and management of the independent release of SQL Developer available on the Oracle Technology Network. SQL Developer is also available as part of the Oracle Database and Oracle JDeveloper installations.
Oracle JDeveloper
Most of SQL Developer is integrated into Oracle JDeveloper, which means you need to install JDeveloper to access and use the SQL Developer components. Having SQL Developer as part of JDeveloper means that, if you are building Java applications and working with the Fusion Middleware platform, you can access and work with the Oracle Database without an additional install of SQL Developer. JDeveloper does not consume all of the extensions for SQL Developer (for example, extensions like Migrations and Versioning are not included).
Note
Oracle JDeveloper 11g includes SQL Developer 1.5.6.
Oracle Database 11g
SQL Developer is also shipped with the Oracle Database. Initially, Oracle Database 11g Release 1. SQL Developer is installed by default when you install the database. Once the installation is complete, locate the sqldeveloper directory (for example, \product\11.1.0\db_1\sqldeveloper\sqldeveloper.exe) to start SQL Developer.
Be aware that Oracle database releases are less frequent than those of SQL Developer, which, by its nature and size, allows for more frequent updates. This means the version of SQL Developer shipped with the database may not be the most current release. Oracle Database 11g Release 2 is shipped with SQL Developer 1.5.5. All examples in this text are using SQL Developer 2.1. You may also update your database version less frequently than a client tool.
To upgrade the SQL Developer installation in Oracle Database 11g Release 1, you should do a full new install. As with other installs, create a new folder and unzip the latest download.
Note
Oracle Database 11g Release 1 ships with SQL Developer 1.1.3
Oracle Database 11g Release 2 ships with SQL Developer 1.5.5
Troubleshooting
It seems ominous to provide a section on troubleshooting at the start of a book! If you accept that software can get in a tangle sometimes, either if you use the product as it's not designed, or perhaps include extensions that you'd prefer not to have and the product is no longer behaving as expected, then a few hints on how to escape that tangle can be useful.
Removing extensions
If you have created your own extensions, or have downloaded and installed other extensions that you no longer require, then invoke the preferences, using the menu Tools | Preferences and select Extensions from the tree. Here you see that SQL Developer includes a number of default extensions, such as the Oracle TimesTen extension. In addition, any extension that you have included is listed here. You can deselect extensions here and the product will no longer access them. This does not delete the files installed for the extension. You will need to manually delete any files downloaded for that to happen. However, it does mean that you can restart the product and see if the extension is the root of the problem.
Resetting shortcut keys
Some users find that their keyboard shortcuts no longer work as expected. In this circumstance, you can select the menu Tools | Preferences, and then select Shortcut Keys from the tree. Click on the More Actions drop-down list and select Load Keyboard Scheme…, as shown in the following screenshot. Select Default from the dialog to reset the keyboard accelerators to the shipped settings. This also replaces any settings you have added.
Note
In releases prior to SQL Developer 2.1, the Shortcut Keys are called Accelerators. In these releases, to reset the keys, select Load Preset.
Reset the environment, do not reinstall the product
When things go wrong, users sometimes resort to deleting and reinstalling a product. This may even require downloading the files again. This is time consuming, and in the case of SQL Developer, not necessary. Assuming you have not edited any of the .jar files (it's been known to happen and not legally permitted), you can reset the product to the shipped factory settings by deleting the system folder. Before you delete the system folder, export your connections and shut down SQL Developer.
Note
Export Connections: To export your connections, select Connections, right-click and select Export Connections. Save the file to a new location.
When troubleshooting, deleting the system folder is useful. However, by deleting this folder you are also deleting all of the changes made to the preferences, your connections, and any layout changes you have made. Therefore, it is recommended that you delete the folder as a last resort, and not as a standard approach to troubleshooting.
Tip
Reset to factory settings
For Microsoft Windows, delete the \Documents and Settings\<your_user>\Application Data\SQL Developer folder to reset SQL Developer to the shipped factory settings.
For Linux, remove the ~.sqldeveloper folder and on the Mac, remove the ~/Library/Application Support/SQL Developer folder.
In addition to deleting all of the preferences set and connections created, this action also deletes user-defined reports, your SQL history, and any code templates and snippets you have created. In general, delete the lower level system folder for a less drastic reset.
A quick overview
Let's start with a walk-through of the product. This book is all about SQL Developer, using the product, and getting to know it. You may well ask yourself why there is a need for a book if we can walk through the product in twenty minutes or less. 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 book, you need access to SYSTEM and the shipped sample schemas, HR, OE, SH, PM, and IX available in Oracle Database 9i, 10g, or 11g. Specifically, this book 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
