You are previewing Oracle SQL Developer 2.1.
O'Reilly logo
Oracle SQL Developer 2.1

Book Description

Database design and development using this feature-rich, powerful user-extensible interface

  • Install, configure, customize, and manage your SQL Developer environment

  • 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 Modele

  • Detailed code examples and screenshots for easy learning

In Detail

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.

Table of Contents

  1. Oracle SQL Developer 2.1
  2. Credits
  3. About the Author
  4. Acknowledgement
  5. About the Reviewers
  6. Preface
    1. What this book covers
      1. I: Getting started
      2. II: Delving a little deeper
      3. III: Focus areas
    2. Standards and assumptions
    3. Who this book is for
    4. Conventions
    5. Reader feedback
    6. Customer support
      1. Errata
      2. Piracy
      3. Questions
  7. 1. Getting Started with SQL Developer
    1. Preparing your environment
      1. Finding and downloading the software
        1. Downloading and installing the Java Development Kit
      2. Installing and starting SQL Developer
        1. Working with different platforms
          1. Microsoft Windows
          2. Alternative platforms
            1. Mac OS X
            2. Linux
        2. Migrating settings from a previous release
      3. Maintaining your environment
        1. Verifying the current release
      4. Using Check for Updates
        1. Managing the system folder and other files
        2. Sharing preferences
      5. Alternative installations of SQL Developer
        1. Oracle JDeveloper
        2. Oracle Database 11g
        3. Troubleshooting
          1. Removing extensions
          2. Resetting shortcut keys
          3. Reset the environment, do not reinstall the product
    2. A quick overview
      1. Sample schemas
      2. Creating your first connection
      3. Using basic commands in the SQL Worksheet
      4. Browsing and updating data
      5. Running reports
    3. Navigating around SQL Developer
      1. Managing SQL Developer windows
        1. Tiling windows
        2. Splitting documents
        3. Maximizing detail
      2. Resetting the window layout
    4. Finding more help
    5. Summary
  8. 2. Browsing and Editing Database Objects and Data
    1. Browsing objects
      1. Working with the Connections navigator
        1. Opening connections
      2. Working with objects
        1. Filtering objects
          1. Schema level filtering
      3. Display editors
        1. General display editors
        2. Working with the display editors
        3. Using the SQL display editor
      4. Working with the data grids
        1. Controlling the column display
        2. Sorting the data
        3. Filtering columns
        4. More data grid context menus
          1. Highlighting data
        5. Count Rows and the Single Record View
          1. Save Grid as Report
        6. Working with Updating Data Grids
          1. Sorting and filtering data
        7. Updating data
          1. Working with LOBS in the data grid
          2. Using flashback
      5. Reviewing other database object nodes
        1. Working with PL/SQL objects
        2. Accessing objects you don't own
          1. Other users
          2. Synonyms and filters
        3. Recycle Bin
    2. Creating and updating database objects
      1. Creating new objects
        1. Creating tables
          1. Early decisions
          2. Adding constraints
          3. Creating column sequences
          4. Additional properties
            1. Storage
            2. Partitions
        2. Creating views
          1. Writing the SQL query
          2. Using the Quick-pick objects
          3. Building the query using elements in the tree
        3. Reviewing a few specific objects
          1. Creating new users
          2. Setting the privilege to create new objects
      2. Editing objects: Putting context menus to work
        1. Editing objects
        2. Diving into context menus
        3. Using context menus as utilities
          1. Normalizing data
          2. Triggers and sequences
    3. Summary
  9. 3. Working with the SQL Worksheet
    1. Introducing the SQL Worksheet
      1. Controlling the environment
      2. Opening SQL Worksheets
      3. Working with multiple worksheets
      4. Switching connections
      5. Getting started
    2. Writing and executing commands
      1. Writing your first statements
        1. Running statements
        2. Run script
      2. Using SQL*Plus commands
        1. Supporting SQL*Plus
          1. Bind variables
          2. The advantage of using bind variables
            1. Bind variables and performance
            2. Security and bind variables
        2. Running scripts
        3. Reviewing unsupported SQL*Plus commands
      3. Working with SQL
        1. Dragging and dropping tables to create queries
        2. Formatting code
        3. Managing the case
        4. Formatting SQL for use in other languages
        5. Working with code completion insight
          1. Controlling code completion insight in the SQL Worksheet
        6. Including code snippets
          1. Managing the Snippets window
          2. Adding code snippets
          3. Creating code templates
      4. Using the File navigator
        1. Opening files
      5. Using extra features in the worksheet
        1. SQL History
        2. DBMS Output
        3. OWA Output
    3. Using the Query Builder
      1. Building an SQL query
        1. Selecting the tables, column, and joins
        2. Viewing the Results
        3. Adding the WHERE clause
        4. Returning to the SQL Worksheet
    4. Summary
  10. 4. The Power of SQL Reports
    1. Introducing SQL Developer reports
      1. Who can run reports?
      2. When do you use reports?
      3. Running a report
        1. Using bind variables
        2. Privileges required for running reports
        3. Switching users
    2. Browsing shipped reports
      1. Running data dictionary reports
        1. Getting to know the data dictionary
        2. About the database
        3. Reviewing Privileges and Security reports
        4. Assisting with quality assurance
        5. Using the PL/SQL reports
        6. Running ASH and AWR reports
      2. Other categories
        1. Migration reports
        2. Application Express reports
        3. Data Modeler reports
      3. Running reports from other menus
        1. Monitor sessions
        2. Managing the database
        3. Real-time SQL monitoring
    3. Creating your own reports
      1. Getting started
        1. Creating folders
        2. Storing reports
      2. Creating general reports
        1. Building general tabular reports
        2. Adding bind variables
        3. Drilling down through reports
        4. Creating a drill-down report
      3. Master-detail reports
        1. Creating master-detail reports
        2. Creating sibling details
        3. Adding charts
        4. Building other graphical reports
          1. Creating pie chart reports
          2. Using gauges in reporting
        5. Other reports styles
          1. Using the Script style report
          2. Using PL/SQL in a report
    4. Sharing reports
      1. Copying and reusing reports
      2. Importing and exporting
      3. Sharing reports through user defined extensions
    5. Summary
  11. 5. Working with PL/SQL
    1. Creating PL/SQL code
      1. Writing PL/SQL in the SQL Worksheet
        1. Using code insight
        2. Using code snippets and code templates
    2. Creating and compiling PL/SQL program units
      1. Working with triggers
        1. Using the Create Trigger dialog
          1. Creating INSTEAD OF triggers
          2. Creating system triggers on the database or schema
        2. Viewing trigger details
        3. Controlling triggers
        4. Adding triggers that populate columns
      2. Adding functions or procedures
        1. Editing program units
        2. Working with errors
        3. Testing and executing program units
      3. Creating packages
        1. Creating the body
          1. Reviewing a package
          2. Testing packages
    3. Editing code
      1. Refactoring code
      2. Searching for code
        1. Finding DB Object
    4. Debugging PL/SQL
      1. Debugging PL/SQL code
        1. Using the debugging mechanism in SQL Developer
          1. Using the Oracle debugging packages
          2. Debugging
            1. Navigating through the code
        2. Remote debugging
          1. Preparing for remote debugging
          2. Remote debugging with Application Express
    5. Summary
  12. 6. SQL and PL/SQL Tuning Tools
    1. Support for tuning code in the SQL Worksheet
      1. Working with EXPLAIN PLAN
        1. Controlling the Explain Plan output
        2. Execution plan details
      2. Using Autotrace
    2. Additional performance tuning tools
      1. Using SQL reports
        1. Running the Top SQL reports
      2. Monitoring your environment
      3. Inspecting SQL trace files
    3. Profiling PL/SQL
      1. Getting started
      2. Preparing the environment
      3. Reviewing the output
    4. Summary
  13. 7. Managing Files
    1. Introducing source code control
      1. Overview
        1. Ways of working
        2. The repository is the point of truth
      2. SQL Developer integration
        1. Subversion (SVN)
        2. Concurrent Versions System (CVS)
        3. Other version control systems
    2. Getting started
      1. Invoking the Files navigator
        1. Browsing and editing files
        2. Reviewing the file editors
        3. Editing other file types
        4. Working with the file history
      2. Introducing the Versioning Navigator
        1. Managing general version control preferences
        2. Setting up the repository
          1. Working with the Subversion repository
          2. Adding CVS repositories
        3. Creating connections to a version repository
        4. Browsing files in the repository
    3. Working with files under version control
      1. Placing files under version control
        1. Importing files into the repository
          1. Working with directories
          2. Performing a Check Out after import
        2. Refreshing the repository
        3. Understanding revision numbers
      2. Checking out files
        1. Identifying checked out files
        2. Tracking the working copy
        3. Saving files
          1. Pending changes preferences
        4. Checking files in
          1. Updating the working copy
          2. Reverting changes
          3. Committing changes to the repository
        5. Comparing and merging code
        6. Creating patches
    4. Summary
  14. 8. Importing, Exporting, and Working with Data
    1. Exporting data
      1. Exporting instance data
        1. Setting up the export file
        2. Exporting SQL DML
        3. Exporting to HTML
        4. Supporting export for SQL*Loader
        5. Exporting to Microsoft Excel
        6. Exporting to XML
      2. Exporting DDL (Metadata)
        1. Exporting table DDL
        2. Selecting multiple tables for DDL export
      3. Using the Database Export wizard to export DDL and data
        1. Starting the export wizard
        2. Selecting objects for generation
        3. Specifying objects
        4. Specifying data
        5. Running the script
    2. Importing data
      1. Importing data from SQL script files
      2. Importing data from XLS and CSV files
      3. Creating a table on XLS import
    3. Using the Database Copy wizard
      1. Comparing the database copy alternatives
      2. Running the Database Copy wizard
    4. Comparing schemas
    5. Summary
  15. 9. Database Connections and JDBC Drivers
    1. Working with Oracle connections
      1. Using alternative Oracle connection types
        1. Reviewing the Basic connection
        2. Accessing the tnsnames.ora file
        3. Accessing LDAP server details
        4. Creating advanced connections with JDBC URLs
        5. Connecting to Oracle TimesTen
      2. Reviewing JDBC drivers
        1. Oracle JDBC thin driver (Type IV driver)
        2. Oracle JDBC thick driver (Type II driver)
        3. SQL Developers shipped drivers
          1. Fixing connection errors
      3. Using different authentication methods
        1. OS Authentication
          1. Setting the database environment variables
          2. Creating a connection using OS Authentication
        2. Using Proxy authentication
        3. Using Kerberos authentication
          1. Implementing Kerberos authentication in SQL Developer
          2. Kerberos authentication using the thin JDBC driver
          3. Kerberos authentication using the thick driver
            1. Set up the sqlnet.ora file
            2. Setting up the user
            3. Preparing the authentication ticket
            4. Connecting to Oracle using Kerberos, SQLNET, and SQL Developer
        4. Using RADIUS authentication
    2. Creating non-Oracle database connections
      1. Setting up JDBC drivers
        1. Using Check for Updates
        2. Manually adding JDBC drivers
      2. Creating connections to the third-party databases
        1. Connecting to IBM DB2
        2. Microsoft Access
        3. Connecting to Sybase Adaptive Server or Microsoft SQL Server
        4. Connecting to MySQL
      3. Organizing your connections
        1. Creating folders
        2. Working with folders
        3. Managing folders
        4. Exporting and importing connections
    3. Summary
  16. 10. Introducing SQL Developer Data Modeler
    1. Oracle SQL Developer Data Modeler
      1. Feature overview
      2. Integrated architecture
    2. Getting started
      1. Installing and setting up the environment
        1. Oracle clients and JDBC drivers
      2. Creating your first models
      3. Importing from the Data Dictionary
        1. Creating a database connection
        2. Using the import wizard
        3. Reviewing the results
        4. Saving designs
    3. Working with diagrams and their components
      1. Formatting the elements
        1. Changing the default format settings
        2. Setting general diagram properties
      2. Creating subviews and displays
        1. Adding subviews to your design
        2. Adding displays
        3. Creating a composite view
      3. Controlling the layout
        1. Adjusting the level of detail displayed
        2. Adjusting the width and height across the model
        3. Controlling alignment
      4. Working with lines
        1. Managing lines with elbows
        2. Managing straight lines
    4. Analysis, design, and generation
      1. Flow of work
        1. Starting with analysis (top down)
        2. Importing existing models (bottom up)
        3. Building the relational model
      2. Logical models
        1. Creating an ERD
          1. Creating entities
          2. Adding attributes
          3. Working with relationships
          4. Creating arcs
        2. Supporting alternate notations
          1. Supporting subtypes and supertypes
      3. Creating constraints, domains, and setting default values
        1. Working with domains
        2. Creating domains
          1. Using domains to implement check constraints
        3. Assigning domain valid values to an attribute or column
        4. Setting valid values at attribute or column level
          1. Adding a default value
      4. Introducing forward and reverse engineering
        1. Forward engineering
        2. General engineering dialog features
        3. Maintaining the model layout
        4. Reverse engineering models
      5. Creating relational models
        1. Working with the relational model
          1. Creating a table and adding columns
          2. Including Foreign Key constraints
        2. Setting naming standards templates
          1. Applying the templates to the model
          2. Updating the object names prefix
      6. Building the physical model
        1. Importing a schema from the data dictionary
        2. Creating a new physical model
        3. Adding new database sites
        4. Reviewing physical properties
        5. Propagate properties
      7. Generating the DDL
        1. Reviewing and applying Design Rules
          1. Generating the DDL script
          2. Updating an existing schema
    5. Integration with Oracle SQL Developer
      1. Creating a new model
      2. Creating and running reports
        1. Setting up the reporting schema
    6. Summary
  17. 11. Extending SQL Developer
    1. Introducing extensibility
      1. Extensibility in SQL Developer
      2. Who builds extensions?
      3. Why extend?
    2. SQL Developer XML extension types
      1. Adding an XML extension
      2. Sharing user-defined reports
      3. Adding display editors
        1. Examples of display editors
          1. Include subpartitions
          2. Use restricted queries
          3. Add display editors for new navigator nodes
        2. Building the XML file for a display editor
      4. Working with context menus
        1. Adding a context menu to the connections menus
        2. Passing parameters
        3. Creating a utility using context menus
        4. Including the user-defined extension for context menus
      5. Adding new nodes to the Connections navigator
        1. Including user-defined extensions for a navigator node
        2. Adding a new tree to the navigator
        3. Adding multiple nodes
    3. Reviewing an example
      1. Adding support for dimensions
        1. Adding a dimensions navigator
        2. Adding dimension display editors
        3. Adding context menus
    4. Working with extensions
      1. Controlling existing extensions
        1. Adding in new Java extensions
        2. Removing extensions
      2. Sharing extensions
    5. Summary
  18. 12. Working with Application Express
    1. Setting the scene
      1. Setting up in Application Express
        1. Creating a workspace and database schema
        2. Creating an Application Express user
      2. Browsing applications in SQL Developer
        1. Creating a connection in SQL Developer
        2. Browsing and working with schema objects
        3. Browsing the applications
          1. Drilling down into lists of values
          2. Focusing on pages
        4. Mapping objects in SQL Developer to Application Express
      3. Tuning SQL and PL/SQL code using SQL Developer
        1. Working with Region Source SQL
        2. Tuning with Explain Plan
        3. Working with PL/SQL code
        4. Replacing the anonymous block in Application Express
      4. Managing applications in SQL Developer
        1. Importing applications
        2. Modifying applications
        3. Deploying applications
        4. Controlling services
      5. Reporting on applications using SQL Developer
    2. Summary
  19. 13. Working with SQL Developer Migrations
    1. Introducing SQL Developer Migrations
      1. An overview of the migration process
      2. Offline or online migration choices
      3. Supported third-party databases
    2. Setting up your environment
      1. Setting up the JDBC drivers
      2. Creating third-party connections
      3. Accessing non-Oracle databases
        1. Browsing database objects
        2. Using the SQL Worksheet
      4. Managing the repository
        1. Creating the repository
        2. Associating a repository with a user
      5. Planning database connections
        1. Setting up the source database connection
        2. Setting up the target database connection
    3. Migrating
      1. Using Quick Migrate
        1. The migration
        2. Verifying the results
      2. Delving into a complex migration
        1. Preparing for the migration
          1. Offline versus online migration
        2. Capturing the model
          1. Reviewing the captured model
          2. Updating the model
          3. Converting objects at a more granular level
        3. Converting the model
          1. Setting the conversion data mapping
          2. Reviewing the logs
          3. Translating procedural code
        4. Generating the scripts
        5. Executing the script
        6. Populating the target tables with data
        7. Offline migrations
    4. Additional migration activities
      1. Migration reports
    5. Summary