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 Driverstext, 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.