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.
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.
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.
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.
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 10: 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.
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.
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.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text are shown as follows: " To see the value of the bind variable in the worksheet shown, enter the SQL*Plus PRINT command. For example, PRINT DEPTNUM."
A block of code is set as follows:
SELECT DEPARTMENT_NAME FROM DEPARTMENTS WHERE DEPARTMENT_NAME = &DNAME;
When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT_ID
FROM EMPLOYEES
WHERE department_id =
:DEPARTMENT_ID
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "Select the Output SQL option, and select the required language from the drop-down list".
Note
Tips and tricks appear like this.
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.
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.
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 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.
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.