Oracle SQL Developer User’s Guide Oracle® User's

User Manual:

Open the PDF directly: View PDF PDF.
Page Count: 194 [warning: Documents this large are best viewed by clicking the View PDF Link!]

Oracle® SQL Developer
User's Guide
Release 1.5
E12152-08
July 2014
Provides conceptual and usage information about Oracle
SQL Developer, a graphical tool that enables you to browse,
create, edit, and delete (drop) database objects; run SQL
statements and scripts; edit and debug PL/SQL code;
manipulate and export data; migrate third-party databases to
Oracle; view metadata and data in third-party databases; and
view and create reports.
Note: This book is for an old release of SQL Developer. You
are encouraged to use the latest available release. See
http://www.oracle.com/technetwork/developer-tools/sq
l-developer/
for information about downloads and
documentation.
Oracle SQL Developer User's Guide, Release 1.5
E12152-08
Copyright © 2006, 2014, Oracle and/or its affiliates. All rights reserved.
Primary Author: Chuck Murray
This software and related documentation are provided under a license agreement containing restrictions on
use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your
license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,
transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse
engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is
prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If
you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it
on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software,
any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users
are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and
agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and
adaptation of the programs, including any operating system, integrated software, any programs installed on
the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to
the programs. No other rights are granted to the U.S. Government.
This software or hardware is developed for general use in a variety of information management
applications. It is not developed or intended for use in any inherently dangerous applications, including
applications that may create a risk of personal injury. If you use this software or hardware in dangerous
applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other
measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages
caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of
their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks
are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD,
Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced
Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products,
and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly
disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle
Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your
access to or use of third-party content, products, or services.
iii
Contents
Preface ............................................................................................................................................................... xiii
Audience..................................................................................................................................................... xiii
Documentation Accessibility................................................................................................................... xiii
Related Documents ................................................................................................................................... xiii
Conventions ............................................................................................................................................... xiii
Third-Party License Information............................................................................................................. xiv
1 SQL Developer Concepts and Usage
1.1 Installing and Getting Started with SQL Developer.............................................................. 1-2
1.2 SQL Developer User Interface................................................................................................... 1-2
1.2.1 Menus for SQL Developer.................................................................................................. 1-6
1.2.2 Restoring the Original "Look and Feel"............................................................................ 1-9
1.3 Database Objects ......................................................................................................................... 1-9
1.3.1 Applications (Application Express 3.0.1 and Later) .................................................... 1-10
1.3.2 Cache Groups (Oracle TimesTen In-Memory Database)............................................ 1-10
1.3.3 Database Links (Public and Private).............................................................................. 1-11
1.3.4 Directories.......................................................................................................................... 1-11
1.3.5 Functions............................................................................................................................ 1-11
1.3.6 Indexes................................................................................................................................ 1-12
1.3.7 Java Sources ....................................................................................................................... 1-12
1.3.8 Materialized Views........................................................................................................... 1-12
1.3.9 Materialized View Logs................................................................................................... 1-12
1.3.10 Packages ............................................................................................................................. 1-13
1.3.11 Procedures ......................................................................................................................... 1-13
1.3.12 Queues................................................................................................................................ 1-14
1.3.13 Queue Tables ..................................................................................................................... 1-14
1.3.14 Recycle Bin......................................................................................................................... 1-14
1.3.15 Replication Schemes (Oracle TimesTen In-Memory Database)................................. 1-14
1.3.16 Sequences........................................................................................................................... 1-15
1.3.17 Synonyms (Public and Private) ...................................................................................... 1-15
1.3.18 Tables.................................................................................................................................. 1-15
1.3.18.1 Flashback Table Support .......................................................................................... 1-16
1.3.19 Triggers .............................................................................................................................. 1-17
1.3.20 Types................................................................................................................................... 1-17
1.3.21 Users (Other Users) .......................................................................................................... 1-17
iv
1.3.22 Views .................................................................................................................................. 1-17
1.3.23 XML Schemas .................................................................................................................... 1-17
1.3.24 Captured and Converted Database Objects (for Migration) ...................................... 1-18
1.4 Database Connections ............................................................................................................. 1-18
1.4.1 Using Folders to Group Connections ............................................................................ 1-20
1.4.2 Sharing of Connections.................................................................................................... 1-20
1.4.3 Advanced Security for JDBC Connection to the Database ......................................... 1-20
1.4.4 Connections with Operating System (OS) Authentication......................................... 1-21
1.4.5 Connections with Proxy Authentication....................................................................... 1-21
1.5 Entering and Modifying Data ................................................................................................ 1-21
1.6 Running and Debugging Functions and Procedures ......................................................... 1-23
1.6.1 Using Bookmarks When Editing Functions and Procedures..................................... 1-25
1.6.2 Remote Debugging........................................................................................................... 1-26
1.6.3 Displaying SQL Trace (.trc) Files.................................................................................... 1-26
1.6.4 Using the PL/SQL Hierarchical Profiler ....................................................................... 1-26
1.6.5 Setting Expression Watches ............................................................................................ 1-27
1.7 Using the SQL Worksheet....................................................................................................... 1-27
1.7.1 SQL*Plus Statements Supported and Not Supported in SQL Worksheet................ 1-30
1.7.2 Script Runner..................................................................................................................... 1-31
1.7.3 Execution Plan................................................................................................................... 1-31
1.7.4 Autotrace Pane .................................................................................................................. 1-32
1.7.5 DBMS Output Pane .......................................................................................................... 1-32
1.7.6 OWA Output Pane ........................................................................................................... 1-32
1.7.7 SQL History ....................................................................................................................... 1-33
1.7.8 Gauges: In the SQL Worksheet and User-Defined Reports........................................ 1-33
1.8 Using Snippets to Insert Code Fragments............................................................................ 1-34
1.8.1 User-Defined Snippets ..................................................................................................... 1-34
1.9 Using Find DB Object to Find Database Objects ................................................................. 1-35
1.10 Using Extended Search ........................................................................................................... 1-35
1.11 Using Versioning ..................................................................................................................... 1-36
1.11.1 About CVS and SQL Developer ..................................................................................... 1-37
1.11.1.1 Pending Changes (CVS) ........................................................................................... 1-37
1.11.2 About Subversion and SQL Developer ......................................................................... 1-37
1.12 SQL Developer Reports........................................................................................................... 1-38
1.12.1 About Your Database reports ......................................................................................... 1-39
1.12.2 All Objects reports ............................................................................................................ 1-39
1.12.3 Application Express reports............................................................................................ 1-40
1.12.4 ASH and AWR reports..................................................................................................... 1-40
1.12.5 Charts reports.................................................................................................................... 1-40
1.12.6 Database Administration reports ................................................................................... 1-40
1.12.7 Data Dictionary reports ................................................................................................... 1-41
1.12.8 Jobs reports ........................................................................................................................ 1-41
1.12.9 PL/SQL reports................................................................................................................. 1-41
1.12.10 Security reports ................................................................................................................. 1-41
1.12.11 Streams reports ................................................................................................................. 1-42
1.12.12 Table reports...................................................................................................................... 1-42
1.12.13 XML reports....................................................................................................................... 1-44
v
1.12.14 Migration reports.............................................................................................................. 1-44
1.12.15 User Defined reports ........................................................................................................ 1-44
1.12.15.1 User-Defined Report Example: Chart..................................................................... 1-44
1.12.15.2 User-Defined Report Example: Dynamic HTML.................................................. 1-46
1.13 SQL Developer Preferences.................................................................................................... 1-46
1.13.1 Environment...................................................................................................................... 1-46
1.13.2 Accelerators (Keyboard Shortcuts) ................................................................................ 1-47
1.13.3 Code Editor........................................................................................................................ 1-47
1.13.4 Compare and Merge......................................................................................................... 1-49
1.13.5 Database ............................................................................................................................. 1-50
1.13.6 Debugger............................................................................................................................ 1-54
1.13.7 Extensions .......................................................................................................................... 1-54
1.13.8 File Types ........................................................................................................................... 1-55
1.13.9 Global Ignore List ............................................................................................................. 1-55
1.13.10 Migration............................................................................................................................ 1-55
1.13.11 Versioning.......................................................................................................................... 1-58
1.13.12 Web Browser and Proxy .................................................................................................. 1-61
1.14 Location of User-Related Information .................................................................................. 1-61
1.15 Oracle TimesTen In-Memory Database Support................................................................. 1-62
1.16 Using the Help.......................................................................................................................... 1-63
1.17 For More Information.............................................................................................................. 1-63
2 Migrating Third-Party Databases
2.1 Migration Quick Start................................................................................................................. 2-2
2.1.1 Standard Migration ............................................................................................................. 2-2
2.1.2 Quick Migration................................................................................................................... 2-3
2.2 Overview of Migration............................................................................................................... 2-5
2.2.1 How Migration Works........................................................................................................ 2-5
2.2.2 Migration Implemented as SQL Developer Extensions................................................. 2-6
2.3 Preparing a Migration Plan ....................................................................................................... 2-6
2.3.1 Task 1: Determining the Requirements of the Migration Project ................................. 2-6
2.3.2 Task 2: Estimating Workload............................................................................................. 2-8
2.3.3 Task 3: Analyzing Operational Requirements ................................................................ 2-9
2.3.4 Task 4: Analyzing the Application.................................................................................... 2-9
2.3.5 Task 5: Planning the Migration Project.......................................................................... 2-10
2.4 Before You Start Migrating: General Information............................................................... 2-10
2.4.1 Creating a Database User for the Migration Repository............................................. 2-11
2.4.2 Requirements for Creating the Destination Oracle Objects........................................ 2-11
2.5 Before You Start Migrating: Source-Specific Information.................................................. 2-12
2.5.1 Before Migrating From Microsoft SQL Server or Sybase Adaptive Server.............. 2-12
2.5.2 Before Migrating From Microsoft Access...................................................................... 2-13
2.5.2.1 Creating Microsoft Access XML Files..................................................................... 2-15
2.5.3 Before Migrating From MySQL...................................................................................... 2-15
2.6 Capturing the Source Database ............................................................................................. 2-15
2.6.1 Online Capture.................................................................................................................. 2-16
2.6.2 Offline Capture ................................................................................................................. 2-16
2.7 Creating and Customizing the Converted Model............................................................... 2-17
vi
2.7.1 Correcting Errors in the Converted Model................................................................... 2-17
2.8 Generating the DDL for the Oracle Schema Objects........................................................... 2-17
2.9 Migrating the Data................................................................................................................... 2-18
2.9.1 Transferring the Data Offline.......................................................................................... 2-18
2.9.1.1 Creating Data Files From Microsoft SQL Server or Sybase Adaptive Server... 2-18
2.9.1.2 Creating Data Files From Microsoft Access........................................................... 2-19
2.9.1.3 Creating Data Files From MySQL........................................................................... 2-19
2.9.1.4 Populating the Destination Database Using the Data Files................................. 2-20
2.10 Making Queries Case Insensitive .......................................................................................... 2-23
2.11 Testing the Oracle Database................................................................................................... 2-23
2.11.1 Testing Methodology ....................................................................................................... 2-24
2.11.2 Testing the Oracle Database............................................................................................ 2-24
2.11.2.1 Guidelines for Creating Tests .................................................................................. 2-26
2.11.2.2 Example of a Unit Test Case .................................................................................... 2-27
2.12 Deploying the Oracle Database ............................................................................................. 2-27
2.12.1 Choosing a Rollout Strategy............................................................................................ 2-27
2.12.1.1 Phased Approach ...................................................................................................... 2-28
2.12.1.2 Big Bang Approach ................................................................................................... 2-28
2.12.1.3 Parallel Approach...................................................................................................... 2-28
2.12.2 Deploying the Destination Database ............................................................................. 2-28
2.13 Using Migration Reports ........................................................................................................ 2-29
2.14 SQL Developer User Interface for Migration....................................................................... 2-30
2.14.1 Migration Menu ................................................................................................................ 2-32
2.14.2 Other Menus: Migration Items ....................................................................................... 2-33
2.14.3 Migration Preferences ...................................................................................................... 2-33
2.14.4 Migration Log Panes ........................................................................................................ 2-33
2.14.5 Using the Translation Scratch Editor ............................................................................. 2-33
3 Tutorial: Creating Objects for a Small Database
3.1 Create a Table (BOOKS)............................................................................................................. 3-2
3.2 Create a Table (PATRONS) ....................................................................................................... 3-3
3.3 Create a Table (TRANSACTIONS)........................................................................................... 3-4
3.4 Create a Sequence ....................................................................................................................... 3-6
3.5 Insert Data into the Tables......................................................................................................... 3-7
3.6 Create a View............................................................................................................................... 3-8
3.7 Create a PL/SQL Procedure...................................................................................................... 3-8
3.8 Debug a PL/SQL Procedure ..................................................................................................... 3-9
3.9 Use the SQL Worksheet for Queries ..................................................................................... 3-11
3.10 Script for Creating and Using the Library Tutorial Objects .............................................. 3-12
4 Dialog Boxes for Creating/Editing Objects
4.1 Add Extension............................................................................................................................. 4-1
4.2 Branch/Tag.................................................................................................................................. 4-1
4.3 Check for Updates ...................................................................................................................... 4-2
4.4 Check Out from CVS .................................................................................................................. 4-2
4.5 Check Out from Subversion ...................................................................................................... 4-3
4.6 Choose Directory......................................................................................................................... 4-3
vii
4.7 Confirm Drop Application ........................................................................................................ 4-3
4.8 Confirm Running SQL ............................................................................................................... 4-3
4.9 Connection Has Uncommitted Changes ................................................................................. 4-3
4.10 Create/Edit New Object (New Gallery).................................................................................. 4-4
4.11 Create/Edit CVS Connection.................................................................................................... 4-4
4.12 Create/Edit/Select Database Connection............................................................................... 4-5
4.13 Rename Model (Migration) ....................................................................................................... 4-8
4.14 Rename Database Item (Migration) ......................................................................................... 4-9
4.15 Select Connection........................................................................................................................ 4-9
4.16 Connection Information............................................................................................................. 4-9
4.17 No Connection Found ................................................................................................................ 4-9
4.18 Connection Rename Error ......................................................................................................... 4-9
4.19 New Folder (Connections)......................................................................................................... 4-9
4.20 Continue After Pause .............................................................................................................. 4-10
4.21 Select Library ............................................................................................................................ 4-10
4.22 Create Library........................................................................................................................... 4-10
4.23 Import Data............................................................................................................................... 4-10
4.24 Export/Import Connection Descriptors............................................................................... 4-11
4.25 Create/Edit Database Link..................................................................................................... 4-11
4.26 Create/Edit Index .................................................................................................................... 4-12
4.27 Create Filter............................................................................................................................... 4-13
4.28 Create/Edit Materialized View Log...................................................................................... 4-13
4.29 Create PL/SQL Package ......................................................................................................... 4-14
4.30 Create PL/SQL Subprogram (Function or Procedure) ...................................................... 4-14
4.31 Create Remote Directory......................................................................................................... 4-15
4.32 Create/Edit Sequence.............................................................................................................. 4-15
4.33 Create SQL File......................................................................................................................... 4-16
4.34 Create/Edit Subversion Connection..................................................................................... 4-16
4.35 Create Subversion Repository................................................................................................ 4-16
4.36 Create/Edit Synonym ............................................................................................................. 4-17
4.37 Create Table (quick creation) ................................................................................................. 4-17
4.38 Create/Edit Table (with advanced options) ........................................................................ 4-18
4.39 Storage Options........................................................................................................................ 4-27
4.40 Create Trigger........................................................................................................................... 4-28
4.41 Create Type (User-Defined) ................................................................................................... 4-29
4.42 Create/Edit User...................................................................................................................... 4-29
4.43 Create/Edit User Defined Report.......................................................................................... 4-30
4.44 Create/Edit User Defined Report Folder ............................................................................. 4-32
4.45 Create/Edit View..................................................................................................................... 4-32
4.46 Create XML Schema ................................................................................................................ 4-37
4.47 Configure Extension ................................................................................................................ 4-38
4.48 Configure File Type Associations.......................................................................................... 4-38
4.49 Custom Filters .......................................................................................................................... 4-38
4.50 Database Copy (Schema Objects) .......................................................................................... 4-38
4.51 Database Schema Differences................................................................................................. 4-39
4.52 DDL Panel for Creating or Editing an Object ...................................................................... 4-40
4.53 Debugger - Attach to JPDA .................................................................................................... 4-40
viii
4.54 Deploy or Import Application ............................................................................................... 4-40
4.55 Describe Object Window ........................................................................................................ 4-41
4.56 Edit Value (Table Column Data) ........................................................................................... 4-41
4.57 Enter Bind Values .................................................................................................................... 4-41
4.58 Error Writing to Export File ................................................................................................... 4-41
4.59 Export (Database Objects and Data) ..................................................................................... 4-42
4.60 Export: Advanced Data Filter ................................................................................................ 4-44
4.61 Export Error .............................................................................................................................. 4-44
4.62 Export Table Data .................................................................................................................... 4-44
4.63 External Locator Configuration ............................................................................................. 4-45
4.64 External Tools ........................................................................................................................... 4-45
4.65 Create/Edit External Tool ...................................................................................................... 4-45
4.66 Choose Offline Options........................................................................................................... 4-46
4.67 Feature Required...................................................................................................................... 4-47
4.68 Filter ........................................................................................................................................... 4-47
4.69 Insert Macro.............................................................................................................................. 4-47
4.70 Externally Modified Files........................................................................................................ 4-47
4.71 Filter Object Types ................................................................................................................... 4-48
4.72 Filter Schemas........................................................................................................................... 4-48
4.73 Filter Error................................................................................................................................. 4-48
4.74 Find/Replace Text ................................................................................................................... 4-48
4.75 Find Result ................................................................................................................................ 4-49
4.76 Generate Oracle DDL .............................................................................................................. 4-49
4.77 Generate Offline Data Move Files ......................................................................................... 4-49
4.78 Go to Bookmark ....................................................................................................................... 4-49
4.79 Go to Line Number.................................................................................................................. 4-50
4.80 Go to Line Number: Error....................................................................................................... 4-50
4.81 Import to CVS........................................................................................................................... 4-50
4.82 Load Preset Key Mappings..................................................................................................... 4-51
4.83 Log In to CVS............................................................................................................................ 4-51
4.84 Modify Value............................................................................................................................ 4-51
4.85 Data Move Details.................................................................................................................... 4-51
4.86 New Procedure (Refactoring)................................................................................................. 4-52
4.87 No Object Found ...................................................................................................................... 4-52
4.88 No Object Selected ................................................................................................................... 4-52
4.89 Open File ................................................................................................................................... 4-52
4.90 Oracle-Only Report.................................................................................................................. 4-52
4.91 Oracle Proxy Authentication.................................................................................................. 4-52
4.92 Paste ........................................................................................................................................... 4-53
4.93 Privilege Warning for Migration ........................................................................................... 4-53
4.94 Query Builder ........................................................................................................................... 4-53
4.95 Recent Files ............................................................................................................................... 4-54
4.96 Create Repository..................................................................................................................... 4-54
4.97 Delete or Truncate Repository ............................................................................................... 4-54
4.98 Capture Microsoft Access Exporter XML............................................................................. 4-54
4.99 Rename Local Variable............................................................................................................ 4-54
4.100 Rename Procedure................................................................................................................... 4-55
ix
4.101 Select Current Repository ....................................................................................................... 4-55
4.102 Cannot Capture Table ............................................................................................................. 4-55
4.103 Reset Expired Password.......................................................................................................... 4-55
4.104 Revision Lister .......................................................................................................................... 4-55
4.105 Run/Debug/Profile PL/SQL................................................................................................. 4-55
4.106 Create/Edit Breakpoint........................................................................................................... 4-56
4.107 Save/Save As............................................................................................................................ 4-57
4.108 Save Files ................................................................................................................................... 4-57
4.109 Unable to Save Files................................................................................................................. 4-57
4.110 Save Style Settings ................................................................................................................... 4-57
4.111 Schema Differences Source or Destination Error ................................................................ 4-57
4.112 Script Execution Failed............................................................................................................ 4-57
4.113 Script Generation Complete ................................................................................................... 4-57
4.114 Set Data Mapping .................................................................................................................... 4-58
4.115 Add/Edit Rule.......................................................................................................................... 4-58
4.116 Set Encoding ............................................................................................................................. 4-58
4.117 Set Pause Continue .................................................................................................................. 4-58
4.118 Sign In (checking for updates) ............................................................................................... 4-59
4.119 Single Record View.................................................................................................................. 4-59
4.120 Save Snippet (User-Defined) .................................................................................................. 4-59
4.121 Edit Snippets (User-Defined) ................................................................................................. 4-60
4.122 Unable to Open File ................................................................................................................. 4-60
4.123 Unsupported Database Version............................................................................................. 4-60
4.124 Windows ................................................................................................................................... 4-60
x
xi
List of Figures
1–1 SQL Developer Main Window.................................................................................................. 1-3
2–1 SQL Developer Migration Architecture .................................................................................. 2-6
2–2 V-model with a Database Migration..................................................................................... 2-24
2–3 Main Window for a Database Migration.............................................................................. 2-31
xii
xiii
Preface
This guide provides conceptual and usage information about Oracle SQL Developer, a
graphical tool that enables you to browse, create, edit, and delete (drop) database
objects; run SQL statements and scripts; edit and debug PL/SQL code; manipulate and
export data; and view and create reports.
Audience
This guide is intended for those using the Oracle SQL Developer tool.
Documentation Accessibility
For information about Oracle's commitment to accessibility, visit the Oracle
Accessibility Program website at
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For
information, visit
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are
hearing impaired.
Related Documents
For information about installing Oracle SQL Developer, see the Oracle Database SQL
Developer Installation Guide.
To download free release notes, installation documentation, white papers, or other
collateral, go to the Oracle Technology Network (OTN) at
http://www.oracle.com/technetwork/
If you already have a user name and password for OTN, then you can go directly to
the documentation section of the OTN Web site at
http://www.oracle.com/technology/documentation
Conventions
The following text conventions are used in this document:
Convention Meaning
boldface Boldface type indicates graphical user interface elements associated
with an action, or terms defined in text or the glossary.
italic Italic type indicates book titles, emphasis, or placeholder variables for
which you supply particular values.
monospace Monospace type indicates commands within a paragraph, URLs, code
in examples, text that appears on the screen, or text that you enter.
xiv
Third-Party License Information
Oracle SQL Developer contains third-party code. Oracle is required to provide the
following notices. Note, however, that the Oracle program license that accompanied
this product determines your right to use the Oracle program, including the
third-party software, and the terms contained in the following notices do not change
those rights.
Apache Regular Expression Package 2.0
Licensed under the Apache License, Version 2.0 (the "License"); you may not use this
file except in compliance with the License. You may obtain a copy of the License at:
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under
the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR
CONDITIONS OF ANY KIND, either express or implied. See the License for the
specific language governing permissions and limitations under the License.
Antlr v 2.7.3
http://www.antlr.org/rights.html
OracleAS TopLink uses Antlr for EJB QL parsing. Antlr (ANother Tool for Language
Recognition), is a language tool that provides a framework for constructing
recognizers, compilers, and translators from grammatical descriptions containing C++
or Java actions. The ANTLR parser and translator generator is fully in the public
domain.
JGoodies Looks and Forms
Copyright © 2003 JGoodies Karsten Lentzsch. All rights reserved.
Redistribution and use in source and binary forms, with or without modification, are
permitted provided that the following conditions are met:
Redistributions of source code must retain the above copyright notice, this list of
conditions and the following disclaimer.
Redistributions in binary form must reproduce the above copyright notice, this list
of conditions and the following disclaimer in the documentation and/or other
materials provided with the distribution.
Neither the name of JGoodies Karsten Lentzsch nor the names of its contributors
may be used to endorse or promote products derived from this software without
specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND
CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
xv
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR
CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.
TMate Subversion Software
Copyright © 2004-2005 TMate Software. All rights reserved. This product includes
software developed by TMate Software (http://www.tmatesoft.com/).
Redistribution and use in source and binary forms, with or without modification, are
permitted provided that the following conditions are met:
Redistributions of source code must retain the above copyright notice, this list of
conditions and the following disclaimer.
Redistributions in binary form must reproduce the above copyright notice, this list
of conditions and the following disclaimer in the documentation and/or other
materials provided with the distribution.
Redistributions in any form must be accompanied by information on how to
obtain complete source code for the software that uses SVNKit and any
accompanying software that uses the software that uses SVNKit. The source code
must either be included in the distribution or be available for no more than the
cost of distribution plus a nominal fee, and must be freely redistributable under
reasonable conditions. For an executable file, complete source code means the
source code for all modules it contains. It does not include source code for
modules or files that typically accompany the major components of the operating
system on which the executable file runs.
Redistribution in any form without redistributing source code for software that
uses SVNKit is possible only when such redistribution is explicitly permitted by
TMate Software. Please, contact TMate Software at support@svnkit.com to get
such permission.
THIS SOFTWARE IS PROVIDED BY TMATE SOFTWARE ``AS IS'' AND ANY
EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE, OR NON-INFRINGEMENT, ARE DISCLAIMED.
IN NO EVENT SHALL TMATE SOFTWARE BE LIABLE FOR ANY DIRECT,
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
xvi
1
SQL Developer Concepts and Usage 1-1
1 SQL Developer Concepts and Usage
Note: This book is for an old release of SQL Developer. You are
encouraged to use the latest available release. See
http://www.oracle.com/technetwork/developer-tools/sq
l-developer/ for information about downloads and
documentation.
Oracle SQL Developer is a graphical version of SQL*Plus that gives database
developers a convenient way to perform basic tasks. You can browse, create, edit, and
delete (drop) database objects; run SQL statements and scripts; edit and debug
PL/SQL code; manipulate and export data; and view and create reports.
You can connect to any target Oracle database schema using standard Oracle database
authentication. Once connected, you can perform operations on objects in the
database.
You can connect to schemas for selected third-party (non-Oracle) databases, such as
MySQL, Microsoft SQL Server, Sybase Adaptive Server, and Microsoft Access, and
view metadata and data in these databases; and you can migrate third-party databases
to Oracle.
This chapter contains the following major sections:
Section 1.1, "Installing and Getting Started with SQL Developer"
Section 1.2, "SQL Developer User Interface"
Section 1.3, "Database Objects"
Section 1.4, "Database Connections"
Section 1.5, "Entering and Modifying Data"
Section 1.6, "Running and Debugging Functions and Procedures"
Section 1.7, "Using the SQL Worksheet"
Section 1.8, "Using Snippets to Insert Code Fragments"
Section 1.9, "Using Find DB Object to Find Database Objects"
Section 1.10, "Using Extended Search"
Section 1.11, "Using Versioning"
Section 1.12, "SQL Developer Reports"
Section 1.13, "SQL Developer Preferences"
Section 1.14, "Location of User-Related Information"
Installing and Getting Started with SQL Developer
1-2 Oracle SQL Developer User's Guide
Section 1.15, "Oracle TimesTen In-Memory Database Support"
Section 1.16, "Using the Help"
Section 1.17, "For More Information"
1.1 Installing and Getting Started with SQL Developer
To install and start SQL Developer, you simply download a ZIP file and unzip it into a
desired parent directory or folder, and then type a command or double-click a file
name. You should read the Oracle Database SQL Developer Installation Guide before you
perform the installation. After you have read the installation guide, the basic steps are:
1. Unzip the SQL Developer kit into a directory (folder) of your choice. This
directory location will be referred to as <sqldeveloper_install>.
Unzipping the SQL Developer kit causes a directory named sqldeveloper to be
created under the <sqldeveloper_install> directory. It also causes many files
and folders to be placed in and under that directory.
If Oracle Database (Release 11 or later) is also installed, a version of SQL
Developer is also included and is accessible through the menu system under
Oracle. This version of SQL Developer is separate from any SQL Developer kit
that you download and unzip on your own, so do not confuse the two, and do not
unzip a kit over the SQL Developer files that are included with Oracle Database.
Suggestion: Create a shortcut for the SQL Developer executable file that you
install, and always use it to start SQL Developer.
2. To start SQL Developer, go to the sqldeveloper directory under the
<sqldeveloper_install> directory, and do one of the following:
On Linux and Mac OS X systems, run sh sqldeveloper.sh.
On Windows systems, double-click sqldeveloper.exe.
If you are asked to enter the full pathname for java.exe, click Browse and find
java.exe. For example, on a Windows system the path might have a name similar
to C:\Program Files\Java\jdk1.6.0_06\bin\java.exe.
3. If you want to become familiar with SQL Developer concepts before using the
interface, read the rest of this chapter before proceeding to the next step.
4. Create at least one database connection (or import some previously exported
connections), so that you can view and work with database objects, use the SQL
Worksheet, and use other features.
To create a new database connection, right-click the Connections node in the
Connections navigator, select New Connection, and complete the required entries
in the Create/Edit/Select Database Connection dialog box.
5. If you want to get started quickly with SQL Developer, do the short tutorial in
Chapter 3, "Tutorial: Creating Objects for a Small Database", or work with your
existing database objects.
1.2 SQL Developer User Interface
The SQL Developer window generally uses the left side for navigation to find and
select objects, and the right side to display information about selected objects.
Figure 1–1 shows the main window.
SQL Developer User Interface
SQL Developer Concepts and Usage 1-3
Figure 1–1 SQL Developer Main Window
Note: This text explains the default interface. However, you can
customize many aspects of the appearance and behavior of SQL
Developer by setting preferences (see Section 1.13). If you ever need to
restore the default interface, see Section 1.2.2, "Restoring the Original
"Look and Feel"".
Note: For migration of third-party databases to Oracle, see also
Section 2.14, "SQL Developer User Interface for Migration".
The menus at the top contain standard entries, plus entries for features specific to SQL
Developer (see Section 1.2.1, "Menus for SQL Developer"), as shown in the following
figure.
You can use shortcut keys to access menus and menu items: for example Alt+F for the
File menu and Alt+E for the Edit menu; or Alt+H, then Alt+S for Help, then Full Text
Search. You can also display the File menu by pressing the F10 key.
Icons under the menus perform various actions, including the following:
New creates a new a new database object (see Section 4.10, "Create/Edit New
Object (New Gallery)").
Open opens a file (see Section 4.89, "Open File").
Save saves any changes to the currently selected object.
SQL Developer User Interface
1-4 Oracle SQL Developer User's Guide
Save All saves any changes to all open objects.
Back moves to the pane that you most recently visited. (Or use the drop-down
arrow to specify a tab view.)
Forward moves to the pane after the current one in the list of visited panes. (Or
use the drop-down arrow to specify a tab view.)
Open SQL Worksheet opens the SQL Worksheet (see Using the SQL Worksheet).
If you do not use the drop-down arrow to specify the database connection to use,
you are asked to select a connection.
The left side of the SQL Developer window has tabs and panes for the Connections
and Reports navigators, icons for performing actions, and a hierarchical tree display
for the currently selected navigator, as shown in the following figure.
The Connections navigator lists database connections that have been created. To
create a new database connection, import an XML file with connection definitions, or
export or edit current connections, right-click the Connections node and select the
appropriate menu item. (For more information, see Section 1.4, "Database
Connections".)
The Files navigator (marked by a folder icon; not shown in the preceding figure)
displays your local file system using a standard hierarchy of folders and files. You can
double-click or drag and drop files to open them, and you can edit and save the files.
For example, if you open a .sql file, it is displayed in a SQL Worksheet window. The
Files navigator is especially useful if you are using versioning with SQL Developer
(see Section 1.11, "Using Versioning").
The Reports navigator lists informative reports provided by SQL Developer, such as a
list of tables without primary keys for each database connection, as well as any
user-defined reports. (For more information, see Section 1.12, "SQL Developer
Reports".)
Icons under the Connections tab (above the metadata tree) perform the following
actions on the currently selected object:
Refresh queries the database for the current details about the selected object (for
example, a connection or just a table).
Apply Filter restricts the display of objects using a filter that you specify. For
example, you can right-click the Tables node and specify a filter of EM% to see
only tables that start with EM and to have the Tables node label be changed to
SQL Developer User Interface
SQL Developer Concepts and Usage 1-5
Tables (EM%) . To remove the effects of applying a filter, right-click the node and
select Clear Filter.
The metadata tree in the Connections pane displays all the objects (categorized by
object type) accessible to the defined connections. To select an object, expand the
appropriate tree node or nodes, then click the object.
The right side of the SQL Developer window has tabs and panes for objects that you
select or open, as shown in the following figure, which displays information about a
table named BOOKS. (If you hold the mouse pointer over the tab label -- BOOKS in
this figure -- a tooltip displays the object’s owner and the database connection.)
For objects other than subprograms, icons provide the following options:
Freeze View (the pin) keeps that object’s tab and information in the window when
you click another object in the Connections navigator; a separate tab and display
are created for that other object. If you click the pin again, the object’s display is
available for reuse.
Edit displays a dialog box for editing the object.
Refresh updates the display by querying the database for the latest information.
Actions displays a menu with actions appropriate for the object. The actions are
the same as when you right-click an object of that type in the Connections
navigator, except the Actions menu does not include Edit.
To switch among objects, click the desired tabs; to close a tab, click the X in the tab. If
you make changes to an object and click the X, you are asked if you want to save the
changes.
For tables and views, this information is grouped under tabs, which are labeled near
the top. For example, for tables the tabs are Columns, Data (for seeing and modifying
the data itself), Indexes, Constraints, and so on; and you can click a column heading
under a tab to sort the grid rows by the values in that column. For most objects, the
tabs include SQL, which displays the SQL statement for creating the object.
You can export data from a detail pane or from the results of a SQL Worksheet
operation or a report by using the context menu and selecting Export.
The Messages - Log area is used for feedback information as appropriate (for example,
results of an action, or error or warning messages). If this area is not already visible,
you can display is by clicking View and then Log.
The Compiler - Log area is used for any messages displayed as a result of a Compile
or Compile for Debug operation.
SQL Developer User Interface
1-6 Oracle SQL Developer User's Guide
1.2.1 Menus for SQL Developer
This topic explains menu items that are specific to SQL Developer.
Edit menu
Extended Paste: Displays the Paste dialog box, in which you select a clipboard item
(from potentially many) to be pasted into the current location.
Duplicate Selection: When you have selected text while editing a function or
procedure, creates a copy of the selected text at the current location.
Wrap Selection: When you have selected text while editing a function or procedure,
wraps the selected text.
View menu
Contains options that affect what is displayed in the SQL Developer interface.
Connections: Displays the Connections navigator.
Files: Displays the Files navigator, which is marked by a folder icon. You can use the
Files navigator to browse, open, edit, and save files that are accessible from the local
system.
Reports: Displays the Reports navigator (see Section 1.12, "SQL Developer Reports").
Captured Models: Displays the Captured Models navigator (see Section 2.14, "SQL
Developer User Interface for Migration").
Converted Models: Displays the Converted Models navigator (see Section 2.14, "SQL
Developer User Interface for Migration").
Find DB Object: Displays the Find Database Object pane (see Section 1.9, "Using Find
DB Object to Find Database Objects").
Versioning Navigator: Displays the Versioning navigator (see Section 1.11, "Using
Versioning").
Log: Displays the Messages - Log pane, which can contain errors, warnings, and
informational messages.
Debugger: Displays panes related to debugging (see Section 1.6, "Running and
Debugging Functions and Procedures").
Run Manager: Displays the Run Manager pane, which contains entries for any active
debugging sessions.
SQL History: Displays information about SQL statements that you have executed. You
can select statements and append them to or overwrite statements on the worksheet
(see Section 1.7.7, "SQL History").
Snippets: Displays snippets (see Section 1.8, "Using Snippets to Insert Code
Fragments").
Status Bar: Controls the display of the status bar at the bottom of the SQL Developer
window.
Toolbars: Controls the display of the main toolbar (under the SQL Developer menus)
and the Connections navigator toolbar.
Refresh: Updates the current display for any open connections using the current
objects in the affected database or databases.
SQL Developer User Interface
SQL Developer Concepts and Usage 1-7
Navigate menu
Contains options for navigating to panes and in the execution of subprograms.
Back: Moves to the pane that you most recently visited.
Forward: Moves to the pane after the current one in the list of visited panes.
Toggle Bookmark: If you are editing a function or procedure, creates or removes a
bookmark (see Section 1.6.1, "Using Bookmarks When Editing Functions and
Procedures").
Remove Bookmarks from File: Removes bookmarks from the currently active editing
window for a function or procedure (see Section 1.6.1, "Using Bookmarks When
Editing Functions and Procedures").
Remove All Bookmarks: Removes bookmarks from open editing windows for
functions and procedures (see Section 1.6.1, "Using Bookmarks When Editing
Functions and Procedures").
Go to Bookmark: Displays a dialog box so that you can go to a specified bookmark
(see Section 1.6.1, "Using Bookmarks When Editing Functions and Procedures").
Go to Next Bookmark: Goes to the next bookmark in the currently active editing
window for a function or procedure (see Section 1.6.1, "Using Bookmarks When
Editing Functions and Procedures").
Go to Previous Bookmark: Goes to the previous bookmark in the currently active
editing window for a function or procedure (see Section 1.6.1, "Using Bookmarks
When Editing Functions and Procedures").
Go to Line: Goes to the specified line number and highlights the line in the editing
window for the selected function or procedure. (To display line numbers, enable Show
Line Numbers under the Code Editor: Line Gutter preferences.)
Go to Last Edit: Goes to the last line that was edited in the editing window for a
function or procedure.
Go to Recent Files: Displays the Recent Files dialog box, in which you can specify a
function or procedure to go to.
Run menu
Contains options relevant when a function or procedure is selected or when it is open
for debugging.
Run [name]: Starts execution of the specified function or procedure.
Debug [name]: Starts execution of the specified function or procedure in debug mode.
The remaining items on the Debug menu match commands on the debugging toolbar,
which is described in Section 1.6, "Running and Debugging Functions and
Procedures".
Source menu
Contains options for use when editing functions and procedures.
Completion Insight: Causes a pop-up window to be displayed when you are typing
PL/SQL code, listing items from which you can select for autocompletion. See the
Code Editor options under Section 1.13, "SQL Developer Preferences".
Toggle Line Comments: Inserts and removes comment indicators at the start of
selected code lines.
Indent Block: Moves the selected statements to the right.
SQL Developer User Interface
1-8 Oracle SQL Developer User's Guide
Unindent Block: Moves the selected statements to the left.
Versioning menu
Contains options related to support for the CVS and Subversion version management
and source control system; see Section 1.11, "Using Versioning" for more information.
The commands on the Versioning menu depend on whether the Version System is
specified as CVS, Subversion, or none.
Migration menu
Contains options related to migrating third-party databases to Oracle; see
Section 2.14.1, "Migration Menu" for more information.
Tools menu
Invokes SQL Developer tools.
Database Copy: Enables you to copy objects from one database schema to another (see
the Database Copy (Schema Objects) interface).
Database Export: Enables you to export some or all objects of one or more object types
for a database connection to a file containing SQL statements to create these objects
and optionally to export table data (see the Export (Database Objects and Data)
interface).
Database Diff: Enables you to compare two schemas to find differences between
objects of the same type and name (for example, tables named CUSTOMERS) in two
different schemas, and optionally to update the objects in the destination schema to
reflect differences in the source schema (see the Database Schema Differences
interface).
Monitor Sessions: Displays the status of one or more sessions, using information from
the V$RSRC_SESSION_INFO view, which shows how the session has been affected by
the Oracle Database Resource Manager. For more information about session
monitoring, see Oracle Database Administrator's Guide.
Monitor SQL: Displays information about any query currently executing and queries
that are done executing for a selected connection. To see detailed information about a
query, right-click its row and select Show SQL Details. The information is especially
useful for real-time monitoring of long-running SQL statements. Cursor statistics (such
as CPU times and IO times) and execution plan statistics (such as number of output
rows, memory, and temporary space used) are updated close to real-time during
statement execution. (Internally, this feature calls the DBMS_SQLTUNE.REPORT_
SQL_MONITOR subprogram.)
SQL Worksheet: Displays a worksheet in which you can enter and execute SQL and
PL/SQL statements using a specified connection (see Section 1.7, "Using the SQL
Worksheet").
External Tools: Displays the External Tools dialog box, with information about
user-defined external tools that are integrated with the SQL Developer interface. From
this dialog box can add external tools (see Section 4.65, "Create/Edit External Tool").
The Tools menu also contains items for any user-defined external tools.
Preferences: Enables you to customize the behavior of SQL Developer (see
Section 1.13, "SQL Developer Preferences").
Database Objects
SQL Developer Concepts and Usage 1-9
Help menu
Displays help about SQL Developer and enables you to check for SQL Developer
updates.
Search: Displays the Help Center window.
Table of Contents: Displays the Help Center window.
Tip of the Day: Displays a suggestion for efficient use of SQL Developer.
Check for Updates: Checks for any updates to the selected optional SQL Developer
extensions, as well as any mandatory SQL Developer extensions. The available
updates may include the JTDS JDBC Driver for Microsoft SQL Server and the MySQL
JDBE Driver, which enable you to create connections to third-party databases. (If the
system you are using is behind a firewall, see the SQL Developer user preferences for
Web Browser and Proxy.)
About: Displays version-related information about SQL Developer and its
components.
1.2.2 Restoring the Original "Look and Feel"
If you have made changes to the SQL Developer user interface ("look and feel"), such
as accidentally repositioning navigators and panes, you can restore the interface to the
way it was after SQL Developer was installed by following these steps:
1. If you are running SQL Developer, exit.
2. Create a backup copy of the folder or directory where your SQL Developer user
information is stored, in case you want to restore any old user-defined reports,
snippets, code templates, or SQL history. The default location is:
Windows: C:\Documents and Settings\<user-name>\Application Data\SQL
Developer
Linux or Mac OS X: ~/.sqldeveloper
If you have specified a nondefault location for your SQL Developer user
information (see Section 1.14), create the backup copy of that folder or directory
instead.
(If you do not want to use any old information or settings, you can skip creating a
backup copy.)
3. Delete the original (not the backup) folder or directory where your user
information is stored (explained in step 2).
4. Start SQL Developer.
This creates a folder or directory where your user information is stored (explained
in step 2), which has the same content as when SQL Developer was installed.
1.3 Database Objects
You can create, edit, and delete (drop) most types of objects in an Oracle database by
using the context menu (right-click, or Shift+F10) in the Connections navigator or by
clicking the Actions button in the detail pane display. For some objects, you can do
other operations, as appropriate for the object type.
Note: The actions available from context menus and Actions buttons
depend on the Oracle Database release number for the specified
database connection. If an action mentioned in the text is not available
with a connection, it may be that the feature was not available in that
release of Oracle Database.
Database Objects
1-10 Oracle SQL Developer User's Guide
You can search for specific objects associated with an Oracle database connection by
clicking View, then Find DB Object. For more information, see Section 1.9, "Using
Find DB Object to Find Database Objects".
If you have connected to any third-party (non-Oracle) databases, such as MySQL,
Microsoft SQL Server, Sybase Adaptive Server, or Microsoft Access, you can view their
objects using the Connections navigator. (For information about connecting to
third-party databases, see the SQL Developer user preferences for Database: Third
Party JDBC Drivers.)
1.3.1 Applications (Application Express 3.0.1 and Later)
Effective with Oracle Application Express 3.0.1, if you use SQL Developer to connect
to a schema that owns any Application Express applications, the Connections
navigator has an Application Express node. You can click an application name to
display tabs (Application, Pages, LOVs, Lists, Templates, Breadcrumbs, and so on)
with information about the application.
You can perform the following operations on an Application Express application by
right-clicking the application name in the Connections navigator and selecting an item
from the menu:
Import Application: Imports an application from a specified file and installs the
application.
Deploy Application: Deploys an application into a specified target schema.
Drop: Deletes the application.
Modify Application: Enables you to change the alias, name (Rename), status,
global notification, and proxy server for the application.
Export DDL: Saves the DDL statements to create the application (or the selected
component) to a file, a .zip file, a worksheet, or the system clipboard.
The following operations are available only by right-clicking the Application Express
node in the Connections navigator and selecting an item from the menu:
Start EPG: Starts the embedded PL/SQL gateway for Application Express.
Displays a dialog box for executing the following statements: BEGIN DBMS_
EPG.map_dad('APEX', '/apex/*'); end;
Stop EPG: Stops the embedded PL/SQL gateway for Application Express.
Displays a dialog box for executing the following statements: BEGIN DBMS_
EPG.unmap_dad('APEX'); end;
1.3.2 Cache Groups (Oracle TimesTen In-Memory Database)
A cache group describes a collection of in-memory database tables that map to all or a
subset of the tables in an Oracle database. A cache group can consist of all or a subset
of the rows and columns in these tables. Multiple cache groups can be used to cache
different sets of related tables in the Oracle database.
Database Objects
SQL Developer Concepts and Usage 1-11
1.3.3 Database Links (Public and Private)
A database link is a database object in one database that enables you to access objects
on another database. The other database need not be an Oracle Database system;
however, to access non-Oracle systems you must use Oracle Heterogeneous Services.
After you have created a database link, you can use it to refer to tables and views in
the other database. The Connections navigator has a Database Links node for all
database links (public and private) owned by the user associated with the specified
connection, and a Public Database Links node for all public database links on the
database associated with the connection. For help with specific options in creating a
database link, see Section 4.25, "Create/Edit Database Link".
You can perform the following operations on a database link by right-clicking the
database link name in the Connections navigator and selecting an item from the menu:
Test Database Link: Validates the database link.
Drop: Deletes the database link.
1.3.4 Directories
A directory object specifies an alias for a directory (called a folder on Windows
systems) on the server file system where external binary file LOBs (BFILEs) and
external table data are located. To create a directory (that is, a directory object), you can
use SQL Developer or the SQL statement CREATE DIRECTORY.
You can use directory names when referring to BFILEs in your PL/SQL code and OCI
calls, rather than hard coding the operating system path name, for management
flexibility. All directories are created in a single namespace and are not owned by an
individual schema. You can secure access to the BFILEs stored within the directory
structure by granting object privileges on the directories to specific users.
1.3.5 Functions
A function is a type of PL/SQL subprogram, which is a programming object that can
be stored and executed in the database server, and called from other programming
objects or applications. (Functions return a value; procedures do not return a value.)
For help with specific options in creating a PL/SQL subprogram, see Section 4.30,
"Create PL/SQL Subprogram (Function or Procedure)".
You can perform the following operations on a function by right-clicking the function
name in the Connections navigator and selecting an item from the menu:
Open: Displays the function text so that you can view and edit it.
Compile: Performs a PL/SQL compilation of the function.
Compile for Debug: Performs a PL/SQL compilation of the procedure, with
PL/SQL library units compiled for debugging.
Run: Displays the Run/Debug/Profile PL/SQL dialog box, and then executes the
function in normal (not debug) mode.
Debug: Displays the Run/Debug/Profile PL/SQL dialog box, and then executes
the function in debug mode.
Profile (for an Oracle Database Release 11.1 or later connection): Displays the
Run/Debug/Profile PL/SQL dialog box, and then executes the function and
collects execution statistics.
Rename: Renames the function.
Database Objects
1-12 Oracle SQL Developer User's Guide
Drop: Deletes the function.
1.3.6 Indexes
An index is a database object that contains an entry for each value that appears in the
indexed column(s) of the table or cluster and provides direct, fast access to rows.
Indexes are automatically created on primary key columns; however, you must create
indexes on other columns to gain the benefits of indexing. For help with specific
options in creating an index, see Section 4.26, "Create/Edit Index".
You can perform the following operations on an index by right-clicking the index
name in the Connections navigator and selecting an item from the menu:
Drop: Deletes the index.
Rebuild Index: Re-creates the index or one of its partitions or subpartitions. If the
index is unusable, a successful rebuild operation makes the index usable. For a
function-based index, rebuilding also enables the index; however, if the function
on which the index is based does not exist, the rebuild operation fails.
Rename Index: Changes the name of the index.
Make Unusable: Prevents the index from being used by Oracle in executing
queries. An unusable index must be rebuilt, or dropped and re-created, before it
can be used again.
Coalesce: Merges the contents of index blocks, where possible, to free blocks for
reuse.
Compute Statistics: For a function-based index, collects statistics on both the
index and its base table using the DBMS_STATS package. Such statistics will
enable Oracle Database to correctly decide when to use the index.
Export DDL: Saves the DDL statement to create the index to a file, a SQL
Worksheet, or the system clipboard.
1.3.7 Java Sources
Java sources can be created and managed in the database. You can create a Java source
object by right-clicking the Java node in the Connections navigator, selecting Load
Java, and specifying the Java source name and source code. (A CREATE OR REPLACE
AND RESOLVE JAVA SOURCE statement is executed using the information you
specify.) For information about Java concepts and stored procedures, see Oracle
Database Java Developer's Guide.
1.3.8 Materialized Views
A materialized view is a database object that contains the results of a query. The FROM
clause of the query can name tables, views, and other materialized views. Collectively
these objects are called master tables (a replication term) or detail tables (a data
warehousing term). This reference uses "master tables" for consistency. The databases
containing the master tables are called the master databases. For help with specific
options in creating a materialized view, see Section 4.45, "Create/Edit View", especially
the View Information or Materialized View Properties pane.
1.3.9 Materialized View Logs
A materialized view log is a table associated with the master table of a materialized
view. When DML changes are made to master table data, Oracle Database stores rows
Database Objects
SQL Developer Concepts and Usage 1-13
describing those changes in the materialized view log and then uses the materialized
view log to refresh materialized views based on the master table. This process is called
incremental or fast refresh. Without a materialized view log, Oracle Database must
reexecute the materialized view query to refresh the materialized view. This process is
called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.
1.3.10 Packages
A package is an object that contains subprograms, which are programming objects that
can be stored and executed in the database server, and called from other programming
objects or applications. A package can contain functions or procedures, or both. For
help with specific options in creating a package, see Section 4.29, "Create PL/SQL
Package".
You can perform the following operations on a package by right-clicking the package
name in the Connections navigator and selecting an item from the menu:
Edit: Displays a read-only tabbed view of information about the package.
Open: Opens the package in a window, where you can modify the content and
other information.
Run: Lets you select a member in the package and run it.
Compile: Performs a PL/SQL compilation of the members in the package.
Compile for Debug: Performs a PL/SQL compilation of the members in the
package, with PL/SQL library units compiled for debugging
Order Members By: Orders the members of the package by location in the source,
by name, or by type and by name within each type.
Drop Package: Deletes the package.
Create Body: Displays a pane in which you can enter text for the package body.
Grant: Lets you grant privileges on the package
Revoke: Lets you revoke privileges on the package.
Save Package Spec and Body: Saves the package specification and body to a file
that you specify.
Export DDL: Saves the DDL statement to create the package to a file, a SQL
Worksheet, or the system clipboard.
1.3.11 Procedures
A procedure is a type of PL/SQL subprogram, which is a programming object that can
be stored and executed in the database server, and called from other programming
objects or applications. (Procedures do not return a value; functions return a value.)
For help with specific options in creating a PL/SQL subprogram, see Section 4.30,
"Create PL/SQL Subprogram (Function or Procedure)".
You can perform the following operations on a procedure by right-clicking the
procedure name in the Connections navigator and selecting an item from the menu:
Open: Displays the procedure text so that you can view and edit it.
Run: Displays the Run/Debug/Profile PL/SQL dialog box, and then executes the
procedure in normal (not debug) mode.
Compile: Performs a PL/SQL compilation of the procedure.
Database Objects
1-14 Oracle SQL Developer User's Guide
Compile for Debug: Performs a PL/SQL compilation of the procedure, with
PL/SQL library units compiled for debugging.
Debug: Displays the Run/Debug/Profile PL/SQL dialog box, and then executes
the procedure in debug mode.
Profile (for an Oracle Database Release 11.1 or later connection): Displays the
Run/Debug/Profile PL/SQL dialog box, and then executes the procedure and
collects execution statistics.
Grant: Lets you grant privileges on the procedure
Revoke: Lets you revoke privileges on the procedure.
Drop: Deletes the procedure.
Compile Dependants: Performs a PL/SQL compilation of the procedure and any
relevant dependent subprograms (see the Dependencies tab).
Export DDL: Saves the DDL statement to create the procedure to a file, a SQL
Worksheet, or the system clipboard.
1.3.12 Queues
A queue is an object in which messages are enqueued and dequeued. Queues are
managed by Oracle Streams Advanced Queueing (AQ). For information about using
queues, see Oracle Streams Advanced Queuing User's Guide.
1.3.13 Queue Tables
A queue table is a table that holds messages to be used with Oracle Streams Advanced
Queueing (AQ). For information about using queue tables, see Oracle Streams Advanced
Queuing User's Guide, especially the information about managing queue tables in the
chapter describing the Oracle Streams AQ administrative interface.
1.3.14 Recycle Bin
The Recycle bin (applicable only to Oracle Database Release 10g and later) holds
objects that have been dropped (deleted). The objects are not actually deleted until a
commit operation is performed. Before the objects are actually deleted, you can
"undelete" them by selecting them in the Recycle bin and selecting Undrop from the
context menu.
You can perform the following operations on an object in the Recycle bin by
right-clicking the object name in the Recycle bin in the Connections navigator and
selecting an item from the menu:
Purge: Removes the object from the Recycle bin and deletes it.
Flashback to Before Drop: Moves the object from the Recycle bin back to its
appropriate place in the Connections navigator display.
1.3.15 Replication Schemes (Oracle TimesTen In-Memory Database)
A replication scheme is a configuration, using SQL statements and a transaction-based
log, whereby committed changes are copied from their source to one or more
subscriber databases. The goal is to enable high efficiency and low overhead during
the replication.
Database Objects
SQL Developer Concepts and Usage 1-15
1.3.16 Sequences
Sequences are used to generate unique integers. You can use sequences to
automatically generate primary key values. For help with specific options in creating
and editing a sequence, see Section 4.32, "Create/Edit Sequence".
1.3.17 Synonyms (Public and Private)
Synonyms provide alternative names for tables, views, sequences, procedures, stored
functions, packages, materialized views, Java class database objects, user-defined
object types, or other synonyms. The Connections navigator has a Synonyms node for
all synonyms (public and private) owned by the user associated with the specified
connection, and a Public Synonyms node for all public synonyms on the database
associated with the connection. For help with specific options in creating and editing a
synonym, see Section 4.36, "Create/Edit Synonym".
1.3.18 Tables
Tables are used to hold data. Each table typically has multiple columns that describe
attributes of the database entity associated with the table, and each column has an
associated data type. You can choose from many table creation options and table
organizations (such as partitioned tables, index-organized tables, and external tables),
to meet a variety of enterprise needs. To create a table, you can do one of the following:
Create the table quickly by adding columns and specifying frequently used
features. To do this, do not check the Advanced box in the Create Table dialog box.
For help with options for creating a table using this quick approach, see Create
Table (quick creation).
Create the table by adding columns and selecting from a larger set of features. To
do this, check the Advanced box in the Create Table dialog box. For help with
options for creating a table with advanced features, see Create/Edit Table (with
advanced options).
Create the table automatically from a Microsoft Excel worksheet. To do this,
right-click Tables under a connection in the Connections navigator, and select
Import Data. When asked for the file, select a file of type .xls or .csv.
You can perform the following operations on a table by right-clicking the table name in
the Connections navigator and selecting an item from the menu:
Edit: Displays the Create/Edit Table (with advanced options) dialog box.
Tab l e : Table actions include Rename, Copy (create a copy using a different name),
Drop (delete the table), Truncate (delete existing data without affecting the table
definition), Lock (set the table lock mode: row share, exclusive, and so on),
Comment (descriptive comment explaining the use or purpose of the table),
Parallel (change the default degree of parallelism for queries and DML on the
table), No Parallel (specify serial execution), and Count Rows (return the number
of rows).
Column: Column actions include Comment (descriptive comment about a
column), Add, Drop, and Normalize.
Constraint: Includes options for adding, dropping, enabling, and disabling
constraints.
Index: Options include Create (create an index on specified columns), Create Text
(create an Oracle Text index on a column), Create Text (create a function-based
index on a column), and Drop.
Database Objects
1-16 Oracle SQL Developer User's Guide
Constraint: Options include Enable or Disable Single, Drop (delete a constraint),
Add Check (add a check constraint), Add Foreign Key, and Add Unique.
Privileges: If you are connected as a database user with sufficient privileges, you
can Grant or Revoke privileges on the table to other users.
Statistics: Options include Gather Statistics (compute exact table and column
statistics and store them in the data dictionary) and Validate Structure (verifies the
integrity of each data block and row, and for an index-organized table also
generates the optimal prefix compression count for the primary key index on the
table). Statistics are used by the Oracle Database optimizer to choose the execution
plan for SQL statements that access analyzed objects.
Storage: Options include Shrink Table (shrink space in a table, for segments in
tablespaces with automatic segment management) and Move Table (to another
tablespace). The Shrink Table options include Compact (only defragments the
segment space and compacts the table rows for subsequent release, but does not
readjust the high water mark and does not release the space immediately) and
Cascade (performs the same operations on all dependent objects of the table,
including secondary indexes on index-organized tables).
Trigg e r : Options include Create, Create PK from Sequence (create a before-insert
trigger to populate the primary key using values from a specified sequence),
Enable or Disable All, Enable or Disable Single, and Drop (delete the trigger).
Import Data: Enables you to import data from a Microsoft Excel worksheet (.xls or
.csv file).
Export Data: Enables you to export some or all of the table data to a file or to the
system clipboard, in any of the following formats: XML (XML tags and data), CSV
(comma-separated values including a header row for column identifiers), SQL
Insert (INSERT statements), or SQL Loader (SQL*Loader control file). After you
select a format, the Export Table Data dialog box is displayed.
You can perform the following operations on a column in a table by right-clicking the
column name in the Connections navigator and selecting an item from the menu:
Rename: Renames the column.
Drop: Deletes the column (including all data in that column) from the table.
Comment: Adds a descriptive comment about the column.
Encrypt (for Oracle Database Release 10.2 and higher, and only if the Transparent
Data Encryption feature is enabled for the database): Displays a dialog box in
which you specify a supported encryption algorithm to be used for encrypting all
data in the column. Current data and subsequently inserted data are encrypted.
Decrypt (for Oracle Database Release 10.2 and higher, and only if the Transparent
Data Encryption feature is enabled for the database): Decrypts data in the column
that had been encrypted, and causes data that is subsequently inserted not to be
encrypted.
Normalize: Creates a new table using the distinct values in the specified column.
You must specify names for the new table and its primary key column, as well as a
sequence name and trigger name.
1.3.18.1 Flashback Table Support
For tables in Oracle Database Release 11.1 and later, the table display includes the
Flashback tab, which provides a view of the modified and original data in the table. If
you have appropriate privileges, you can click the Undo SQL subtab to select and
Database Objects
SQL Developer Concepts and Usage 1-17
review the syntax required to undo changes. For information about using the
Flashback Table feature, see Oracle Database Backup and Recovery User's Guide.
1.3.19 Triggers
Triggers are stored PL/SQL blocks associated with a table, a schema, or the database,
or anonymous PL/SQL blocks or calls to a procedure implemented in PL/SQL or Java.
Oracle Database automatically executes a trigger when specified conditions occur. For
help with specific options in creating a trigger, see Section 4.40, "Create Trigger".
1.3.20 Types
A data type associates a fixed set of properties with the values that can be used in a
column of a table or in an argument of a function or procedure. These properties cause
Oracle Database to treat values of one data type differently from values of another
data type. Most data types are supplied by Oracle, although users can create data
types.
For help with specific options in creating a user-defined type, see Section 4.41, "Create
Type (User-Defined)".
1.3.21 Users (Other Users)
Database users are accounts through which you can log in to the database. In the
Connections navigator, you can see the Other Users in the database associated with a
connection, but the database objects that you are allowed to see for each user are
determined by the privileges of the database user associated with the current database
connection.
If you are connected as a user with the DBA role, you can create a database user by
right-clicking Other Users and selecting Create User, and you can edit an existing
database user by right-clicking the user under Other Users and selecting Edit User.
For help on options in creating and editing users, see Create/Edit User.
1.3.22 Views
Views are virtual tables (analogous to queries in some database products) that select
data from one or more underlying tables. Oracle Database provides many view
creation options and specialized types of views (such as materialized views, described
in Section 1.3.8, "Materialized Views"), to meet a variety of enterprise needs. For help
with specific options in creating and editing a view, see Create/Edit View.
You can perform the following operations on a view by right-clicking the view name
in the Connections navigator and selecting an item from the menu:
Edit: Displays the Create/Edit View dialog box.
Drop: Deletes the view.
Compile: Recompiles the view, to enable you to locate possible errors before run
time. You may want to recompile a view after altering one of its base tables to
ensure that the change does not affect the view or other objects that depend on it.
1.3.23 XML Schemas
XML schemas are schema definitions, written in XML, that describe the structure and
various other semantics of conforming instance XML documents. For conceptual and
Database Connections
1-18 Oracle SQL Developer User's Guide
usage information about XML schemas, see Oracle XML DB Developer's Guide in the
Oracle Database documentation library.
You can edit an XML schema by right-clicking the XML schema name in the
Connections navigator and selecting Edit from the menu.
1.3.24 Captured and Converted Database Objects (for Migration)
If you are migrating a third-party database to Oracle, the Captured Models and
Converted Models navigators can display models that include database objects, such
as tables and procedures. A captured object represents an object in the captured
third-party database, and a converted object represents an Oracle model of that object
as it will be created in the Oracle database.
The context menu for each captured object includes Convert to Oracle, which creates a
corresponding converted object. The context menu for each converted object includes
Generate, which creates the corresponding Oracle Database object. (The context
menus will contain other items as appropriate for the object.)
For information about the related Oracle Database objects, see the following:
Section 1.3.5, "Functions"
Section 1.3.6, "Indexes"
Section 1.3.11, "Procedures"
Section 1.3.16, "Sequences"
Section 1.3.18, "Tables"
Section 1.3.19, "Triggers"
Section 1.3.21, "Users (Other Users)"
Section 1.3.22, "Views"
1.4 Database Connections
A connection is a SQL Developer object that specifies the necessary information for
connecting to a specific database as a specific user of that database. You must have at
least one database connection (existing, created, or imported) to use SQL Developer.
You can connect to any target Oracle database schema using standard Oracle database
authentication. Once connected, you can perform operations on objects in the
database. You can also connect to schemas for selected third-party (non-Oracle)
databases, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, and
Microsoft Access, and view metadata and data.
When you start SQL Developer and whenever you display the database connections
dialog box, SQL Developer automatically reads any connections defined in the
tnsnames.ora file on your system, if that file exists. By default, tnsnames.ora is located
in the $ORACLE_HOME/network/admin directory, but it can also be in the directory
specified by the TNS_ADMIN environment variable or registry value or (on Linux
systems) the global configuration directory. On Windows systems, if the tnsnames.ora
file exists but its connections are not being used by SQL Developer, define TNS_
ADMIN as a system environment variable. For information about the tnsnames.ora
file, see the "Local Naming Parameters (tnsnames.ora)" chapter in Oracle Database Net
Services Reference.
Database Connections
SQL Developer Concepts and Usage 1-19
You can create additional connections (for example, to connect to the same database
but as different users, or to connect to different databases). Each database connection is
listed in the Connections navigator hierarchy.
To create a new database connection, right-click the Connections node and select New
Database Connection. Use the dialog box to specify information about the connection
(see Section 4.12, "Create/Edit/Select Database Connection"). You can also create a
new database connection by selecting an existing connection in that dialog box,
changing the connection name, changing other connection attributes as needed, and
clicking Save or Connect.
To edit the information about an existing database connection, right-click the
connection name in the Connections navigator display and select Properties. Use the
dialog box to modify information about the connection, but do not change the
connection name. (See Section 4.12, "Create/Edit/Select Database Connection".)
To organize connection groups using folders, see Section 1.4.1, "Using Folders to
Group Connections".
To export information about the existing database connections into an XML file that
you can later use for importing connections, right-click Connections in the
Connections navigator display and select Export Connections. Use the dialog box to
specify the connections to be exported (see Section 4.24, "Export/Import Connection
Descriptors").
To import connections that had previously been exported (adding them to any
connections that may already exist in SQL Developer), right-click Connections in the
Connections navigator display and select Import Connections. Use the dialog box to
specify the connections to be imported (see Section 4.24, "Export/Import Connection
Descriptors").
To perform limited database management operations if you are connected AS
SYSDBA, right-click the connection name in the Connections navigator display and
select Manage Database. You can click to refresh the read-only display of memory
(SGA and PGA) and tablespace information. If a listener is running with a static
listener configured for the database, you can also click to start and stop the database.
To perform remote debugging if you are using the Sun Microsystem's Java Platform
Debugger Architecture (JPDA) and you would like the debugger to listen so that a
debuggee can attach to the debugger, right-click the connection name in the
Connections navigator display and select Remote Debug. Use the dialog box to
specify remote debugging information (see Section 4.53, "Debugger - Attach to JPDA").
To estimate or compute statistics for objects in a database schema, right-click the
connection name in the Connections navigator display and select Gather Schema
Statistics. Statistics are used to optimize SQL execution.
To generate documentation (in HTML format (comparable to Javadoc for Java classes)
about a schema, right-click the connection name in the Connections navigator display
and select Generate DB Doc. To view the generated documentation, open the
index.html file in the output directory that you specified.
To rename a connection, right-click the connection name in the Connections navigator
display and select Rename Connection.
To delete a connection (that is, delete it from SQL Developer, not merely disconnect
from the current connection), right-click the connection name in the Connections
navigator display and select Delete. Deleting a connection does not delete the user
associated with that connection.
Database Connections
1-20 Oracle SQL Developer User's Guide
To connect using an existing connection, expand its node in the Connections navigator,
or right-click its name and select Connect. A SQL Worksheet window is also opened
for the connection (see Section 1.7, "Using the SQL Worksheet"). To create a separate
unshared worksheet for a connection, use Ctrl+Shift+N.
To disconnect from the current connection, right-click its name in the Connections
navigator and select Disconnect.
To specify a preference for using an OCI (thick, Type 2) driver (if available) instead of a
JDBC (thin) driver for basic and TNS (network alias) database connections, enable the
Use OCI/Thick driver option under the Database: Advanced Parameters user
preferences.
1.4.1 Using Folders to Group Connections
You can use folders in the Connections navigator to organize connections into groups:
for example, one folder for connections on your local system, another for connections
on the test system, and another for connections on the production system.
To create a folder to hold connections, right-click the name in the Connections
navigator of a connection to be added to the folder, select Add to Folder and then New
Folder, and specify the folder name (such as Local Connections).
To add more connections to a folder, right-click the name in the Connections navigator
of a connection to be added to the folder, and select Add to Folder and then the name
of the folder into which to add the connection.
To move a connection from one folder to another folder, right-click the connection
name under its current folder, select Add to Folder, and then either the name of the
destination folder or New Folder to move the connection to a new folder to be created.
To remove a connection from the folder, right-click the connection name under the
folder and select Remove from Folder. (This does not delete the connection; it is
moved to the top level in the Connections navigator hierarchy display.)
To remove a folder, right-click the folder name select Remove Folder. (This does not
delete any connections that are in the folder; these connections are moved to the top
level in the Connections navigator hierarchy display.)
To rename a folder, right-click the folder name, select Rename Folder, and specify the
new name.
1.4.2 Sharing of Connections
By default, each connection in SQL Developer is shared when possible. For example, if
you open a table in the Connections navigator and two SQL Worksheets using the
same connection, all three panes use one shared connection to the database. In this
example, a commit operation in one SQL Worksheet commits across all three panes. If
you want a dedicated session, you must duplicate your connection and give it another
name. Sessions are shared by name, not connection information, so this new
connection will be kept separate from the original.
1.4.3 Advanced Security for JDBC Connection to the Database
You are encouraged to use Oracle Advanced Security to secure a JDBC connection to
the database. Both the JDBC OCI and the JDBC Thin drivers support at least some of
the Oracle Advanced Security features. If you are using the OCI driver, you can set
relevant parameters in the same way that you would in any Oracle client setting. The
JDBC Thin driver supports the Oracle Advanced Security features through a set of
Entering and Modifying Data
SQL Developer Concepts and Usage 1-21
Java classes included with the JDBC classes in a Java Archive (JAR) file and supports
security parameter settings through Java properties objects.
For more information about using Oracle Advanced Security, see Oracle Database JDBC
Developer's Guide and Reference.
1.4.4 Connections with Operating System (OS) Authentication
When you create a connection to an Oracle database that is using operating system
(OS) authentication, you can omit the user name and password; that is, specify a
connection name and all the other necessary information, except do not specify a user
name or password. For information about using external authentication, including the
use of the OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT database
initialization parameters, see Oracle Database Security Guide.
If you omit the user name and password trying to create a connection to a system that
is not configured for external authentication, an error message is displayed.
1.4.5 Connections with Proxy Authentication
Proxy authentication enables one JDBC connection to act as a proxy for other JDBC
connections. If you use the Proxy Connection option when you create a database
connection, the connection will be used to connect as the specified user for the
connection, but authenticated using the user name and either the password or
distinguished name of the proxy user. For information about using a middle tier server
for proxy authentication, see Oracle Database Security Guide.
For example, to create connection for a user named PROXY_USER but connecting
using the user name and password of existing database user SCOTT, follow these
steps.
1. Create the proxy user and grant it the appropriate privileges:
CREATE USER proxy_user IDENTIFIED BY <password>;
ALTER USER proxy_user GRANT CONNECT THROUGH scott AUTHENTICATED USING PASSWORD;
GRANT create session TO proxy_user;
. . .<Grant other privileges as needed.>
2. Create a new database connection. For example: connection name = proxy_conn,
user name = scott, password = <password for scott>.
3. Enable (check) Proxy Connection.
4. In the Oracle Proxy Connection dialog box, select User Name for Proxy Type
5. For Proxy User, enter PROXY_USER; and for Proxy Password, enter the password
for the PROXY_USER database user.
6. Click OK to close the Oracle Proxy Connection dialog box.
7. Complete any other necessary connection information, and click Connect to create
the connection.
In this example, when you connect using the proxy_conn connection, the user name
and password for user SCOTT are used to connect to the database, but the connection
sees the database objects owned by the PROXY_USER schema.
1.5 Entering and Modifying Data
You can use SQL Developer to enter data into tables and to edit and delete existing
table data. To do any of these operations, select the table in the Connections navigator,
Entering and Modifying Data
1-22 Oracle SQL Developer User's Guide
then click the Data tab in the table detail display. The following figure shows the Data
pane for a table named BOOKS, with a filter applied to show only books whose rating
is 10, and after the user has clicked in the Title cell for the first book.
Icons and other controls under the Data tab provide the following options:
Freeze View (the pin) keeps that object’s tab and information in the window when
you click another object in the Connections navigator; a separate tab and display
are created for that other object. If you click the pin again, the object’s display is
available for reuse.
Refresh queries the database to update the data display. If a filter is specified, the
refresh operation uses the filter.
Insert Row adds an empty row after the selected row, for you to enter new data.
Delete Selected Row(s) marks the selected rows for deletion. The actual deletion
does not occur until you commit changes.
Commit Changes ends the current transaction and makes permanent all changes
performed in the transaction.
Rollback Changes undoes any work done in the current transaction.
Sort displays a dialog box for selecting columns to sort by. For each column, you
can specify ascending or descending order, and you can specify that null values be
displayed first.
Filter enables you to enter a SQL predicate (WHERE clause text without the
WHERE keyword) for limiting the display of data. For example, to show only
rows where the RATING column value is equal to 10, specify: rating = 10
Actions displays a menu with actions relevant to the table.
When you enter a cell in the grid, you can directly edit the data for many data types,
and for all data types you can click the ellipsis (...) button to edit the data. For binary
data you cannot edit the data in the cell, but must use the ellipsis button.
In the data grid, the context menu (right-click) includes the following commands:
Single Record View displays the Single Record View dialog box, which enables
you to edit data for a table or view, one record at a time.
Auto-fit All Columns adjusts the width of all columns according to your
specification (by column header, by column data, or best fit).
Auto-fit Selected Columns adjusts the width of the selected columns according to
your specification (by column header, by column data, or best fit).
Count Rows displays the number of rows in the table.
Publish to Apex (if Application Express is installed) creates a small Application
Express application based on the data. It displays a dialog box in which you
Running and Debugging Functions and Procedures
SQL Developer Concepts and Usage 1-23
specify the following for the application to be created: workspace, application
name, theme, page name, and SQL statement for generating the report.
Export Data enables you to export some or all of the table data to a file or to the
system clipboard, in any of the following formats: XML (XML tags and data), CSV
(comma-separated values including a header row for column identifiers), SQL
Insert (INSERT statements), or SQL Loader (SQL*Loader control file). After you
select a format, the Export Table Data dialog box is displayed.
You can copy and paste data between table grid cells and cells in a Microsoft Excel
worksheet.
To copy table data to the clipboard, click the column header (for all column data) or
select specific cells and press Ctrl+C; to copy the column header text along with the
table data, press Ctrl+Shift+C.
To sort the display of rows by values within a column, double-click the column
header; to switch between ascending and descending sorting, double-click the
up/down arrow in the column header.
In the Data pane for a table or view, you can split the display vertically or horizontally
to see two (or more) parts independently by using the split box (thin blue rectangle),
located to the right of the bottom scroll bar and above the right scroll bar.
In the Data pane, the acceptable format or formats for entering dates may be different
from the date format required by SQL*Plus.
1.6 Running and Debugging Functions and Procedures
You can use SQL Developer to run and debug PL/SQL subprograms (functions and
procedures).
To run a subprogram, click its name in the Connections navigator; then either
right-click and select Run, or click the Edit icon and then click the Run icon above
its source listing.
To debug a subprogram, click its name in the Connections navigator. If the
procedure in its current form has not already been compiled for debug, right-click
and select Compile for Debug. Then click the Edit icon and click the Debug icon
above its source listing.
In both cases, a code editing window is displayed. The following figure shows the
code editing window being used to debug a procedure named LIST_A_RATING2,
which is used for tutorial purposes in Section 3.8, "Debug a PL/SQL Procedure".
Running and Debugging Functions and Procedures
1-24 Oracle SQL Developer User's Guide
In the code editing window, under the tab with the name of the subprogram, is a
toolbar, and beneath it is the text of the subprogram, which you can edit. You can set
and unset breakpoints for debugging by clicking to the left of the thin vertical line
beside each statement with which you want to associate a breakpoint. (When a
breakpoint is set, a red circle is displayed.)
The toolbar under the tab for the subprogram name has a toolbar that includes the
icons shown in the following figure.
Run starts normal execution of the subprogram, and displays the results in the
Running - Log tab.
Debug starts execution of the subprogram in debug mode, and displays the
Debugging - Log tab, which includes the debugging toolbar for controlling the
execution.
Compile performs a PL/SQL compilation of the subprogram.
Compile for Debug performs a PL/SQL compilation of the subprogram so that it
can be debugged.
The Debugging - Log tab under the code text area contains the debugging toolbar and
informational messages. The debugging toolbar has the icons shown in the following
figure.
Running and Debugging Functions and Procedures
SQL Developer Concepts and Usage 1-25
Find Execution Point goes to the execution point (the next line of source code to
be executed by the debugger).
Step Over bypasses the next subprogram (unless the subprogram has a
breakpoint) and goes to the next statement after the subprogram. If the execution
point is located on a subprogram call, it runs that subprogram without stopping
(instead of stepping into it), then positions the execution point on the statement
that follows the call. If the execution point is located on the last statement of a
subprogram, Step Over returns from the subprogram, placing the execution point
on the line of code that follows the call to the subprogram from which you are
returning.
Step Into executes a single program statement at a time. If the execution point is
located on a call to a subprogram, Step Into steps into that subprogram and places
the execution point on its first statement. If the execution point is located on the
last statement of a subprogram, Step Into returns from the subprogram, placing
the execution point on the line of code that follows the call to the subprogram from
which you are returning.
Step Out leaves the current subprogram and goes to the next statement.
Step to End of Method goes to the last statement of the current subprogram.
Resume continues execution.
Pause halts execution but does not exit, thus allowing you to resume execution.
Terminate halts and exits the execution. You cannot resume execution from this
point; instead, to start running or debugging from the beginning of the
subprogram, click the Run or Debug icon in the Source tab toolbar.
The Breakpoints tab displays breakpoints, both system-defined and user-defined.
The Smart Data tab displays information about variables, using your Debugger: Smart
Data preferences. You can also specify these preferences by right-clicking in the Smart
Data window and selecting Preferences.
The Data tab displays information about variables, using your Debugger: Data
preferences. You can also specify these preferences by right-clicking in the Data
window and selecting Preferences.
The Watches tab displays information about watches (see Section 1.6.5, "Setting
Expression Watches").
If the function or procedure to be debugged is on a remote system, see also
Section 1.6.2, "Remote Debugging".
1.6.1 Using Bookmarks When Editing Functions and Procedures
When you are editing a long function or procedure, you may find it convenient to
create bookmarks in the code so that you can easily navigate to points of interest.
To create or remove a bookmark, click Navigate, then Toggle Bookmark. When a
bookmark is created, an icon appears to the left of the thin vertical line.
To go to a specific bookmark, click Navigate, then Go to Bookmark. To go to the next
or previous bookmark, click Navigate, then Go to Next Bookmark or Go to Previous
Bookmark, respectively.
To remove all bookmarks from the currently active editing window for a function or
procedure or from all open editing windows, click Navigate, then Remove
Bookmarks from File or Remove All Bookmarks, respectively.
Running and Debugging Functions and Procedures
1-26 Oracle SQL Developer User's Guide
You can also go to a specific line or to your last edit by clicking Navigate, then Go to
Line or Go to Last Edit, respectively.
1.6.2 Remote Debugging
To debug a function or procedure for a connection where the database is on a different
host than the one on which you are running SQL Developer, you can perform remote
debugging. Remote debugging involves many of the steps as for local debugging;
however, do the following before you start the remote debugging:
1. Use an Oracle client such as SQL*Plus to issue the debugger connection command.
Whatever client you use, make sure that the session which issues the debugger
connection commands is the same session which executes your PL/SQL program
containing the breakpoints. For example, if the name of the remote system is
remote1, use the following SQL*Plus command to open a TCP/IP connection to
that system and the port for the JDWP session:
EXEC DBMS_DEBUG_JDWP.CONNECT_TCP('remote1', '4000');
The first parameter is the IP address or host name of the remote system, and the
second parameter is the port number on that remote system on which the
debugger is listening.
2. Right-click the connection for the remote database, select Remote Debug, and
complete the information in the Debugger - Attach to JPDA dialog box.
Then, follow the steps that you would for local debugging (for example, see
Section 3.8, "Debug a PL/SQL Procedure").
1.6.3 Displaying SQL Trace (.trc) Files
If you have any SQL Trace (.trc) output files, you can display them in SQL Developer
as an alternative to using the TKPROF program to format the contents of the trace file.
To open a .trc file in SQL Developer and see an attractive, effective display of the
information, click File, then Open, and specify the file; or drag the file’s name or icon
into the SQL Developer window.
You can then examine the information in the List View, Statistics View, and History
panes, with each pane including options for filtering and controlling the display.
For information about SQL Trace and TKPROF, see Oracle Database Performance Tuning
Guide.
1.6.4 Using the PL/SQL Hierarchical Profiler
For an Oracle Database Release 11.1 or later connection, you can use the PL/SQL
hierarchical profiler to identify bottlenecks and performance-tuning opportunities in
PL/SQL applications. Profiling consists of the two steps: running the PL/SQL module
in profiling mode, and analyzing the reports. In addition, some one-time setup work is
required the first time you use profiling in SQL Developer.
To initiate profiling, right-click the name of the function or procedure in the
Connections navigator hierarchy and select Profile, or click the Profile button on the
PL/SQL source editor toolbar. After the function or procedure is run in profiling
mode, the profiler reports are located at the Execution Profiles tab of the object viewer
window. You can review subprogram-level execution summary information, such as:
Number of calls to the subprogram
Time spent in the subprogram itself (function time or self time)
Using the SQL Worksheet
SQL Developer Concepts and Usage 1-27
Time spent in the subprogram itself and in its descendent subprograms (subtree
time)
Detailed parent-children information, including all subprograms that a given
subprogram called (that is, children of the given subprogram)
For more information about using the PL/SQL hierarchical profiler, see Oracle Database
Advanced Application Developer's Guide.
1.6.5 Setting Expression Watches
A watch enables you to monitor the changing values of variables or expressions as
your program runs. After you enter a watch expression, the Watches window displays
the current value of the expression. As your program runs, the value of the watch
changes as your program updates the values of the variables in the watch expression.
A watch evaluates an expression according to the current context which is controlled
by the selection in the Stack window. If you move to a new context, the expression is
reevaluated for the new context. If the execution point moves to a location where any
of the variables in the watch expression are undefined, the entire watch expression
becomes undefined. If the execution point returns to a location where the watch
expression can be evaluated, the Watches window again displays the value of the
watch expression.
To open the Watches window, click View, then Debugger, then Watches.
To add a watch, right-click in the Watches window and select Add Watch. To edit a
watch, right-click in the Watches window and select Edit Watch.
1.7 Using the SQL Worksheet
You can use the SQL Worksheet to enter and execute SQL, PL/SQL, and SQL*Plus
statements. You can specify any actions that can be processed by the database
connection associated with the worksheet, such as creating a table, inserting data,
creating and editing a trigger, selecting data from a table, and saving that data to a file.
You can display a SQL Worksheet by right-clicking a connection in the Connections
navigator and selecting Open SQL Worksheet, by selecting Tool s and then SQL
Worksheet, or by clicking the Use SQL Worksheet icon under the menu bar. In the
Select Connection dialog box, select the database connection to use for your work with
the worksheet. You can also use that dialog box to create and edit database
connections. (You can have a SQL Worksheet window open automatically when you
open a database connection by enabling the appropriate SQL Developer user
preference under Database Connections.)
To create a separate unshared worksheet for a connection, use Ctrl+Shift+N.
The SQL Worksheet has the user interface shown in the following figure:
Using the SQL Worksheet
1-28 Oracle SQL Developer User's Guide
SQL Worksheet toolbar (under the SQL Worksheet tab): Contains icons for the
following operations:
Execute Statement executes the statement at the mouse pointer in the Enter SQL
Statement box. The SQL statements can include bind variables and substitution
variables of type VARCHAR2 (although in most cases, VARCHAR2 is
automatically converted internally to NUMBER if necessary); a pop-up box is
displayed for entering variable values.
Run Script executes all statements in the Enter SQL Statement box using the Script
Runner. The SQL statements can include substitution variables (but not bind
variables) of type VARCHAR2 (although in most cases, VARCHAR2 is
automatically converted internally to NUMBER if necessary); a pop-up box is
displayed for entering substitution variable values.
Commit writes any changes to the database, and ends the transaction; also clears
any output in the Results and Script Output panes.
Rollback discards any changes without writing them to the database, and ends
the transaction; also clears any output in the Results and Script Output panes.
Cancel stops the execution of any statements currently being executed.
Monitor SQL Status (Oracle Database Release 11.1 and later only) calls the
real-time SQL monitoring feature of Oracle Database, enabling you to monitor the
performance of SQL statements while they are executing.
Execute Explain Plan generates the execution plan for the statement (internally
executing the EXPLAIN PLAN statement). To see the execution plan, click the
Explain tab. For more information, see Section 1.7.3, "Execution Plan".
Autotrace generates trace information for the statement. To see the execution plan,
click the Autotrace tab. For more information, see Section 1.7.3, "Execution Plan".
Clear erases the statement or statements in the Enter SQL Statement box.
To the right of these icons is a drop-down list for changing the database
connection to use with the worksheet.
Using the SQL Worksheet
SQL Developer Concepts and Usage 1-29
The context menu (right-click, or Shift+F10) includes the preceding SQL Worksheet
toolbar operations, plus the following operations:
Print File prints the contents of the Enter SQL Statement box.
Cut, Copy, Paste, and Select All have the same meanings as for normal text
editing operations.
Query Builder opens the Query Builder dialog box, where you can create a
SELECT statement by dragging and dropping table and view names and by
graphically specifying columns and other elements of the query.
Refactoring enables you to do the following on selected text: switch character case
(to upper/lower/initcap), extract the sequence of PL/SQL statements to a
procedure, or rename the local variable.
Format formats the SQL statement (capitalizing the names of statements, clauses,
keywords, and so on).
Popup Describe, if the name of a database object is completely selected, displays a
window with tabs and information appropriate for that type of object (see
Section 4.55, "Describe Object Window").
Save Snippet opens the Save Snippet (User-Defined) dialog box with the selected
text as the snippet text.
Enter SQL Statement: The statement or statements that you intend to execute. For
multiple statements, each non-PL/SQL statement must be terminated with either a
semicolon or (on a new line) a slash (/), and each PL/SQL statement must be
terminated with a slash (/) on a new line. SQL keywords are automatically
highlighted. To format the statement, right-click in the statement area and select
Format SQL.
You can drag some kinds of objects from the Connections navigator and drop them
into the Enter SQL Statement box:
If you drag and drop a table or view, a SELECT statement is constructed with all
columns in the table or view. You can then edit the statement, for example,
modifying the column list or adding a WHERE clause.
If you drag and drop a function or procedure, a snippet-like text block is
constructed for you to edit when including that object in a statement.
To view details for any object, you can select its name in the Enter SQL Statement box
and select Popup Describe from the context menu (or press Shift+F4). For example, if
you select a table name and press Shift+F4, information about Columns, Constraints,
Grants, and so on is displayed; or if you select a procedure name and press Shift+F4,
information about Code, Grants, Dependencies, References, and Details is displayed.
Tab s display panes with the following information:
Results: Displays the results of the most recent Execute Statement operation.
Explain: Displays the output if you clicked the Explain Execution Plan icon (see
Section 1.7.3, "Execution Plan").
Script Output: Displays the output if you clicked the Run Script icon (see
Section 1.7.2, "Script Runner").
DBMS Output: Displays the output of DBMS_OUTPUT package statements (see
Section 1.7.5, "DBMS Output Pane").
OWA Output: Displays Oracle Web Agent (MOD_PLSQL) output (see
Section 1.7.6, "OWA Output Pane").
Using the SQL Worksheet
1-30 Oracle SQL Developer User's Guide
To toggle the relative heights of the Enter SQL Statement area and the area for tabs and
display panes, press Ctrl+Alt+L. You can also manually adjust the heights.
1.7.1 SQL*Plus Statements Supported and Not Supported in SQL Worksheet
The SQL Worksheet supports some SQL*Plus statements. SQL*Plus statements must
be interpreted by the SQL Worksheet before being passed to the database; any
SQL*Plus that are not supported by the SQL Worksheet are ignored and not passed to
the database.
The following SQL*Plus statements are supported by the SQL Worksheet:
@
@@
acc[ept]
conn[ect]
cl[ear]
def[ine]
desc[ribe]
doc[ument]
exec[ute]
exit (Stops execution and reinstates the specified connection)
ho[st]
pau[se]
pro[mpt]
quit (Stops execution and reinstates the specified connection)
rem[ark]
set pau[se] {ON | OFF}
sta[rt]
timi[ng]
undef[ine]
whenever
xquery
The following SQL*Plus statements are not supported by the SQL Worksheet:
a[ppend]
archive
attr[ibute]
bre[ak]
bti[tle]
c[hange]
col[ulmn]
comp[ute]
copy
del
disc[onnect]
ed[it]
get
help
i[nput]
l[ist]
newpage
oradebug
passw[ord]
print
r[un]
recover
repf[ooter]
reph[eader]
sav[e]
Using the SQL Worksheet
SQL Developer Concepts and Usage 1-31
sho[w]
shu[tdown]
spo[ol]
startup
store
tti[tle]
var[iable]
1.7.2 Script Runner
The script runner emulates a limited set of SQL*Plus features. You can often enter SQL
and SQL*Plus statements and execute them by clicking the Run Script icon. The Script
Output pane displays the output.
The SQL*Plus features available in the script runner include @, @@, CONNECT, EXIT,
QUIT, UNDEFINE, WHENEVER, and substitution variables. For example, to run a
script named c:\myscripts\mytest.sql, type @c:\myscripts\mytest in the Enter SQL
Statement box, and click the drop-down next to the Execute Statement icon and select
Run Script.
The following considerations apply to using the SQL Developer script runner:
You cannot use bind variables. (The Execute SQL Statement feature does let you
use bind variables of type VARCHAR2, NUMBER, and DATE.)
For substitution variables, the syntax &&variable assigns a permanent variable
value, and the syntax &variable assigns a temporary (not stored) variable value.
For EXIT and QUIT, commit is the default behavior, but you can specify rollback.
In either case, the context is reset: for example, WHENEVER command
information and substitution variable values are cleared.
DESCRIBE works for most, but not all, object types for which it is supported in
SQL*Plus.
For SQL*Plus commands that are not supported, a warning message is displayed.
SQL*Plus comments are ignored.
For XMLType data, data in the column is displayed as "SYS.XMLDATA" if the
database connection uses a JDBC Thin driver, but the expanded XML values are
displayed if the connection uses an OCI (thick, Type 2) driver.
If you have SQL*Plus available on your system, you may want to use it instead of the
script runner.
1.7.3 Execution Plan
The Execute Explain Plan icon generates the execution plan, which you can see by
clicking the Explain tab. The execution plan is the sequence of operations that will be
performed to execute the statement. An execution plan shows a row source tree with
the hierarchy of operations that make up the statement. For each operation, it shows
the ordering of the tables referenced by the statement, access method for each table
mentioned in the statement, join method for tables affected by join operations in the
statement, and data operations such as filter, sort, or aggregation.
In addition to the row source tree, the plan table displays information about
optimization (such as the cost and cardinality of each operation), partitioning (such as
the set of accessed partitions), and parallel execution (such as the distribution method
of join inputs). For more information, see the chapter about using EXPLAIN PLAN in
Oracle Database Performance Tuning Guide.
Using the SQL Worksheet
1-32 Oracle SQL Developer User's Guide
1.7.4 Autotrace Pane
The Autotrace pane displays trace-related information when you execute the SQL
statement by clicking the Autotrace icon. Most of the specific information displayed is
determined by the SQL Developer Preferences for Database: Autotrace Parameters.
If you cancel a long-running statement, partial execution statistics are displayed.
This information can help you to identify SQL statements that will benefit from
tuning. For example, you may be able to optimize predicate handling by transitively
adding predicates, rewriting predicates using Boolean algebra principles, moving
predicates around in the execution plan, and so on. For more information about
tracing and autotrace, see the chapter about tuning in SQL*Plus User's Guide and
Reference.
To use the autotrace feature, the database user for the connection must have the
SELECT_CATALOG_ROLE privilege.
1.7.5 DBMS Output Pane
The PL/SQL DBMS_OUTPUT package enables you to send messages from stored
procedures, packages, and triggers. The PUT and PUT_LINE procedures in this
package enable you to place information in a buffer that can be read by another
trigger, procedure, or package. In a separate PL/SQL procedure or anonymous block,
you can display the buffered information by calling the GET_LINE procedure. The
DBMS Output pane is used to display the output of that buffer. This pane contains
icons and other controls for the following operations:
Enable/Disable DBMS Output: Toggles the SET SERVEROUTPUT setting
between ON and OFF. Setting server output ON checks for any output that is
placed in the DBMS_OUTPUT buffer, and any output is displayed in the pane.
Clear: Erases the contents of the pane.
Save: Saves the contents of the pane to a file that you specify.
Print: Prints the contents of the pane.
Buffer Size: For databases before Oracle Database 10.2, limits the amount of data
that can be stored in the DBMS_OUTPUT buffer. The buffer size can be between 1
and 1000000 (1 million).
Poll: The interval (in seconds) at which SQL Developer checks the DBMS_
OUTPUT buffer to see if there is data to print. The poll rate can be between 1 and
15.
1.7.6 OWA Output Pane
OWA (Oracle Web Agent) or MOD_PLSQL is an Apache (Web Server) extension
module that enables you to create dynamic Web pages from PL/SQL packages and
stored procedures. The OWA Output pane enables you to see the HTML output of
MOD_PLSQL actions that have been executed in the SQL Worksheet. This pane
contains icons for the following operations:
Enable/Disable OWA Output: Enables and disables the checking of the OWA
output buffer and the display of OWA output to the pane.
Clear: Erases the contents of the pane.
Save: Saves the contents of the pane to a file that you specify.
Print: Prints the contents of the pane.
Using the SQL Worksheet
SQL Developer Concepts and Usage 1-33
1.7.7 SQL History
You can click View, then SQL History (or press F8) to view a dockable window with
SQL statements and scripts that you have executed, and optionally select one or more
statements to have them either replace the statements currently on the SQL Worksheet
or be added to the statements currently on the SQL Worksheet.
You can click on a column heading to sort the rows by the values in that column.
The SQL history list will not contain any statement that can include a password. Such
statements include (but are not necessarily limited to) CONNECT, ALTER USER, and
CREATE DATABASE LINK.
Append: Appends the selected statement or statements to any statements currently on
the SQL Worksheet. You can also append the selected statement or statements by
dragging them from the SQL History window and dropping them at the desired
location on the SQL Worksheet.
Replace: Replaces any statements currently on the SQL Worksheet with the selected
statement or statements.
Clear: Removes all statements from the SQL history.
Filter: If you type a string in the text box and click Filter, only SQL statements
containing that string are displayed.
1.7.8 Gauges: In the SQL Worksheet and User-Defined Reports
You can use graphical gauges to display query results in the SQL Worksheet and in
user-defined reports. In both cases, you need to specify the name of the value column
for the gauge data, and minimum and maximum values on the gauge, and the values
to be shown as low and high on the gauge (usually between the minimum and
maximum values). In the SQL Worksheet, the required structure for the value to be
selected is:
'SQLDEV:GAUGE:<min>:<max>:<low>:<high>:' || <value-column>
For example, to display the last name and the salary in gauge format, where the gauge
shows from 1000 to 30000 with below 10000 as low and above 18000 as high, for
employees with ID numbers less than a number to be specified, connect to the
supplied HR schema and execute the following query:
SELECT last_name, 'SQLDEV:GAUGE:1000:30000:10000:18000:' || salary
FROM employees WHERE employee_id < :employee_id
If you specify 104 as the bind variable value, the output appears as shown in the
following figure:
Using Snippets to Insert Code Fragments
1-34 Oracle SQL Developer User's Guide
For a user-defined gauge report, the query must specify only the value column, the
minimum and maximum values, and the low and high values, and optionally a
WHERE clause. The required structure for the query (before any optional WHERE
clause) is:
SELECT <value-column>, <min>, <max>, <low>, <high> FROM <table-name>
For example, to create a report of salaries in gauge dial format, with the same values
and WHERE clause as in the preceding query, right-click on User Defined Reports in
the Reports navigator and select Add Report. In the Add Report dialog box, specify a
report name; for Style, select Gauge; and for SQL, enter the following:
SELECT salary, 1000, 30000, 10000, 18000 FROM employees
WHERE employee_id < :EMPLOYEE_ID;
Click the Chart Details tab near the bottom of the box; for Chart Type, select DIAL; for
Query Based, select true; and click Apply.
Use the Reports navigator to view the newly created user-defined report. For
Connection, specify one that connects to the HR sample schema. For the bind variable
value, specify 104. The report shows four semicircular dials, each with a label
containing the salary amount and a "needle" pointing to an appropriate place on the
dial.
1.8 Using Snippets to Insert Code Fragments
Snippets are code fragments, such as SQL functions, Optimizer hints, and
miscellaneous PL/SQL programming techniques. Some snippets are just syntax, and
others are examples. You can insert and edit snippets when you are using the SQL
Worksheet or creating or editing a PL/SQL function or procedure.
To display snippets, from the View menu, select Snippets. In the snippets window (on
the right side), use the drop-down to select a group (such as Aggregate Functions or
Character Functions). In most cases, the fragments in each group do not represent all
available objects in that logical grouping, or all formats and options of each fragment
shown. For complete and detailed information, see the Oracle Database
documentation.
A Snippets button is placed in the right window margin, so that you can display the
snippets window if it becomes hidden.
To insert a snippet into your code in a SQL Worksheet or in a PL/SQL function or
procedure, drag the snippet from the snippets window and drop it into the desired
place in your code; then edit the syntax so that the SQL function is valid in the current
context. To see a brief description of a SQL function in a tooltip, hold the pointer over
the function name.
For example, you could type SELECT and then drag CONCAT(char1, char2) from the
Character Functions group. Then, edit the CONCAT function syntax and type the rest
of the statement, such as in the following:
SELECT CONCAT(title, ' is a book in the library.') FROM books;
1.8.1 User-Defined Snippets
You can create and edit snippets. User-defined snippets are intended mainly to enable
you to supplement the Oracle-supplied snippets, although you are also permitted to
replace an Oracle-supplied snippet with your own version.
Using Extended Search
SQL Developer Concepts and Usage 1-35
When you create a user-defined snippet, you can add it to one of the Oracle-supplied
snippet categories (such as Aggregate Functions) or to a category that you create. If
you add a snippet to an Oracle-supplied category and if your snippet has the same
name as an existing snippet, your snippet definition replaces the existing one. (If you
later upgrade to a new version of SQL Developer and if you choose to preserve your
old settings, your old user-defined snippets will replace any Oracle-supplied snippets
of the same name in the new version of SQL Developer.)
To create a snippet, do any of the following:
Open the Snippets window and click the Add User Snippets icon.
Select text for the snippet in the SQL Worksheet window, right-click, and select
Save Snippet.
Click the Add User Snippet icon in the Edit Snippets (User-Defined) dialog box.
To edit an existing user-defined snippet, click the Edit User Snippets icon in the
Snippets window.
Information about user-defined snippets is stored in a file named UserSnippets.xml
under the directory for user-specific information. For information about the location of
this information, see Section 1.14, "Location of User-Related Information".
1.9 Using Find DB Object to Find Database Objects
You can use the Find DB Object feature to find database objects associated with an
Oracle database connection and to open editing panes to work with those objects. To
move to the Find Database Object pane or to display it if it is not visible, from the
View menu, select Find DB Object.
The following figure shows the Find Database Object pane with results from a search
for all objects associated with a connection named hr_conn that start with EMPLOYEE.
(The pane may be displayed on the right side of the SQL Developer window or at the
bottom.)
To find objects for an Oracle connection, click Search, select the connection name, enter
an object name or a string containing one or more wildcard characters, and press the
Enter key. To view or edit one of the objects, double-click its name in the Find Database
Object pane.
You can detach, move, and dock the Find Database Object pane by clicking and
holding the tab, and dragging and positioning the pane.
1.10 Using Extended Search
You can use the extended feature to find various types of objects (tables, columns,
declarations within functions or procedures, and so on) associated with an Oracle
database connection and to open editing panes to work with those objects. (Thus,
Using Versioning
1-36 Oracle SQL Developer User's Guide
extended search is more comprehensive than the Find DB Object feature.) To move to
the Extended Search pane or to display it if it is not visible, from the View menu, select
Extended Search.
The following figure shows the Extended Search pane with results from a search for
columns associated with a connection named stacd05_hr that start with EM. (The pane
may be displayed on the right side of the SQL Developer window or at the bottom.)
Connection: Database connection to use for the search.
Name: An object name or a string containing one or more wildcard characters. For
example: EM% for all names starting with EM.
Type: Type of object for which to restrict the search.
Usage: Usage of the object. May or may not be relevant, depending on the type of
object.
Click the Lookup icon to display objects that meet the specified criteria. To view or
edit one of the objects (or the parent object that contains the specified object),
double-click its name in the Extended Search pane
You can detach, move, and dock the Extended Search pane by clicking and holding the
tab, and dragging and positioning the pane.
1.11 Using Versioning
SQL Developer provides integrated support for the following versioning and source
control systems: CVS (Concurrent Versions System) and Subversion. The SQL
Developer documentation does not provide detailed information about the concepts
and operations of such systems; it assumes that you know them or can read about
them in the product documentation.
For the CVS manual (by Per Cederqvist and others), see
http://ximbiot.com/cvs/manual/.
For information about Subversion, see http://subversion.tigris.org/. For
Subversion documentation, see http://svnbook.red-bean.com/.
Using Versioning
SQL Developer Concepts and Usage 1-37
To access the versioning features of SQL Developer, use the Versioning menu.
If you create any versioning system repositories, you can use the hierarchical display
in the Files navigator, which is marked by a folder icon. (If that navigator is not visible,
click View, then Files.)
1.11.1 About CVS and SQL Developer
CVS repositories can be created on a local PC or remote server. There can be more than
one CVS repository. You need to create one or more CVS repositories.
Source files are held in a CVS repository. The source files in a CVS repository are
grouped into modules. If you have new files, a wizard in SQL Developer will help you
import them into the CVS repository and place them under version control. A copy is
made of your original files and placed in a subdirectory (.backup) of the one from
which you imported them.
Files to be worked on are checked out from the CVS repository. This makes a local
copy of the files. You can see the contents of the CVS repository in the SQL Developer
CVS Navigator and open read-only versions of files. You can then decide which files
you want to check out and work on.
CVS creates a new directory populated with the copy of the source files. You can see
the files in the System Navigator. You can also open them from here.
Source files have a status, depending on what operations have been carried out on
them. A preference lets you choose whether the version control status of a file is
shown in the System Navigator.
1.11.1.1 Pending Changes (CVS)
The Pending Changes window is displayed if you click Versioning, then CVS, then
Pending Changes, or when you initiate an action that changes the local source control
status of a file. This window shows files that have been added, modified or removed
(locally or remotely), files whose content conflicts with other versions of the same file
files that have not been added to source control files that are being watched, and files
for which editors have been obtained. You can use this information to detect conflicts
and to resolve them where possible.
The Outgoing pane shows changes made locally, the Incoming pane shows changes
made remotely, and the Candidates pane shows files that have been created locally but
not yet added to source control. You can double-click file names to edit them, and you
can use the context menu to perform available operations.
1.11.2 About Subversion and SQL Developer
Before you can work with a Subversion repository through SQL Developer, you must
create a connection to it. When you create a local Subversion repository, a connection
to it is automatically created, and this can be seen in the Subversion Navigator. You
can subsequently edit the connection details.
Existing files must be imported into the Subversion repository to bring them under
version control. Files are then checked out from the Subversion repository to a local
folder known as the "Subversion working copy". Files created in (or moved into) SQL
Developer must be stored in the Subversion working copy.
Files newly created within SQL Developer must be added to version control. Changed
and new files are made available to other users by committing them to the SQL
Developer repository. The Subversion working copy can be updated with the contents
of the Subversion repository to incorporate changes made by other users.
SQL Developer Reports
1-38 Oracle SQL Developer User's Guide
1.12 SQL Developer Reports
SQL Developer provides many reports about the database and its objects. You can also
create your own user-defined reports. To display reports, click the Reports tab on the
left side of the window (see SQL Developer User Interface). If this tab is not visible,
select View and then Reports.
Individual reports are displayed in tabbed panes on the right side of the window; and
for each report, you can select (in a drop-down control) the database connection for
which to display the report. For reports about objects, the objects shown are only those
visible to the database user associated with the selected database connection, and the
rows are usually ordered by Owner. The detail display pane for a report includes the
following icons at the top:
Freeze View (the pin) keeps that report in the SQL Developer window when you
click another report in the Reports navigator; a separate tab and detail view pane
are created for that other report. If you click the pin again, the report’s detail view
pane is available for reuse.
Run Report updates the detail view pane display by querying the database for the
latest information.
Run Report in SQL Worksheet displays the SQL statement used to retrieve the
information for a report in a SQL Worksheet pane, where you can view, edit, and
run the statement (see Section 1.7, "Using the SQL Worksheet").
The time required to display specific reports will vary, and may be affected by the
number and complexity of objects involved, and by the speed of the network
connection to the database.
For most reports that contain names of database objects, you can double-click the
object name in the report display pane (or right-click the object name and select Go To)
to display that object in a detail view pane, just as if you had selected that object using
the Connections navigator.
To export a report into an XML file that can be imported later, right-click the report
name in the Reports navigator display and select Export. To import a report that had
previously been exported, select the name of the report folder name (such as a
user-defined folder) in which to store the imported report, right-click, and select
Import.
You can create a shared report from an exported report by clicking Tools, then
Preferences, and using the Database: User-Defined Extensions pane to add a row with
Type as REPORT and Location specifying the exported XML file. The next time you
restart SQL Developer, the Reports navigator will have a Shared Reports folder
containing that report.
Reports are grouped in the following categories:
About Your Database reports list release information about the database associated
with the connection.
All Objects reports list information about all objects accessible to the user associated
with the specified database connection, not just objects owned by the user.
Application Express reports list information about Oracle Application Express 3.0.1 (or
later) applications, pages, schemas, UI defaults, and workspaces.
ASH and AWR reports list information provided by the Active Session History (ASH)
and Automated Workload Repository (AWR) features.
Database Administration reports list usage information about system resources.
SQL Developer Reports
SQL Developer Concepts and Usage 1-39
Data Dictionary reports list information about the data dictionary views that are
accessible in the database. Examples of data dictionary views are ALL_OBJECTS and
USER_TABLES.
Jobs reports list information about jobs running on the database.
PL/SQL reports list information about your PL/SQL objects and allow you to search
the source of those objects.
Security reports list privilege-related information about the database.
Streams reports list information about stream rules.
Table reports list information about tables owned by the user associated with the
specified connection. These reports can help you to better understand the metadata
and data. The table reports include Quality Assurance reports that indicate possible
logical design flaws and sources of run-time performance problems.
XML reports list information about XML objects.
User Defined reports are any customized reports that you have created.
Bind Variables for Reports
For some reports, you are prompted for bind variables before the report is generated.
These bind variables enable you to further restrict the output. The default value for all
bind variables is null, which implies no further restrictions. To specify a bind variable,
select the variable name and type an entry in the Value field. Any bind variable values
that you enter are case insensitive, all matches are returned where the value string
appears anywhere in the name of the relevant object type.
1.12.1 About Your Database reports
The About Your Database reports list release information about the database
associated with the selected connection. The reports include Version Banner (database
settings) and National Language Support Parameters (NLS_xxx parameter values for
globalization support).
1.12.2 All Objects reports
All Objects reports list information about objects visible to the user associated with the
database connection.
All Objects: For each object, lists the owner, name, type (table, view, index, and so on),
status (valid or invalid), the date it was created, and the date when the last data
definition language (DDL) operation was performed on it. The Last DDL date can help
you to find if any changes to the object definitions have been made on or after a
specific time.
Collection Types: Lists information about for each collection type. The information
includes the type owner, element type name and owner, and type-dependent specific
information.
Dependencies: For each object with references to it, lists information about references
to (uses of) that object.
Invalid Objects: Lists all objects that have a status of invalid.
Object Count by Type: For each type of object associated with a specific owner, lists
the number of objects. This report might help you to identify users that have created
an especially large number of objects, particularly objects of a specific type.
SQL Developer Reports
1-40 Oracle SQL Developer User's Guide
Public Database Links: Lists all public database links.
Public Synonyms: Lists all public synonyms.
1.12.3 Application Express reports
If you select a connection for a schema that owns any Oracle Application Express 3.0.1
(or later) applications, the Application Express reports list information about
applications, pages, schemas, UI defaults, and workspaces. For information about
Oracle Application Express, see the documentation for that product.
1.12.4 ASH and AWR reports
The ASH and AWR reports list information provided by the Active Session History
(ASH) and Automated Workload Repository (AWR) features, which require special
licensing. For information about using AWR, including how to use ASH reports, see
the information about automatic performance statistics in Oracle Database Performance
Tuning Guide.
1.12.5 Charts reports
Charts reports include a chart showing the distribution of objects of various object
types (number of tables, indexes, and so on).
1.12.6 Database Administration reports
Database Administration reports list usage information about system resources. This
information can help you to manage storage, user accounts, and sessions efficiently.
(The user for the database connection must have the DBA role to see most Database
Administration reports.)
All Tables: Contains the reports that are also grouped under Table reports, including
Quality Assurance reports.
Cursors: Provide information about cursors, including cursors by session (including
open cursors and cursor details.
Database Parameters: Provide information about all database parameters or only
those parameters that are not set to their default values.
Locks: Provide information about locks, including the user associated with each.
Sessions: Provide information about sessions, selected and ordered by various criteria.
Storage: Provide usage and allocation information for tablespaces and data files.
Top SQ L : Provide information about SQL statements, selected and ordered by various
criteria. This information might help you to identify SQL statements that are being
executed more often than expected or that are taking more time than expected.
Users: Provide information about database users, selected and ordered by various
criteria. For example, you can find out which users were created most recently, which
user accounts have expired, and which users use object types and how many objects
each owns.
Waits and Events: Provide information about waits and events, selected by criteria
related to time and other factors. For Events in the Last x Minutes, specify the number
of minutes in the Enter Bind Values dialog box.
SQL Developer Reports
SQL Developer Concepts and Usage 1-41
1.12.7 Data Dictionary reports
Data Dictionary reports list information about the data dictionary views that are
accessible in the database. Examples of data dictionary views are ALL_OBJECTS and
USER_TABLES.
Dictionary View Columns: For each Oracle data dictionary view, lists information
about the columns in the view.
Dictionary Views: Lists each Oracle data dictionary view and (in most cases) a
comment describing its contents or purpose.
1.12.8 Jobs reports
Jobs reports list information about jobs running on the database.
All Jobs: Lists information about all jobs running on the database. The information
includes the start time of its last run, current run, and next scheduled run.
DBA Jobs: Lists information about each job for which a DBA user is associated with
the database connection. The information includes the start time of its last run, current
run, and next scheduled run.
Your Jobs: Lists information about each job for which the user associated with the
database connection is the log user, privilege user, or schema user. The information
includes the start time of its last run, current run, and next scheduled run.
1.12.9 PL/SQL reports
PL/SQL reports list information about PL/SQL packages, function, and procedures,
and about types defined in them.
Program Unit Arguments: For each argument (parameter) in a program unit, lists the
program unit name, the argument position (1, 2, 3, and so on), the argument name,
and whether the argument is input-only (In), output-only (Out), or both input and
output (In/Out).
Search Source Code: For each PL/SQL object, lists the source code for each line, and
allows the source to be searched for occurrences of the specified variable.
Unit Line Counts: For each PL/SQL object, lists the number of source code lines. This
information can help you to identify complex objects (for example, to identify code
that may need to be simplified or divided into several objects).
1.12.10 Security reports
Security reports list information about users that have been granted privileges, and in
some cases about the users that granted the privileges. This information can help you
(or the database administrator if you are not a DBA) to understand possible security
issues and vulnerabilities, and to decide on the appropriate action to take (for
example, revoking certain privileges from users that do not need those privileges).
Auditing: Lists information about audit policies.
Encryption: Lists information about encrypted columns.
Grants and Privileges: Includes the following reports:
Column Privileges: For each privilege granted on a specific column in a specific
table, lists the user that granted the privilege, the user to which the privilege was
granted, the table, the privilege, and whether the user to which the privilege was
granted can grant that privilege to other users.
SQL Developer Reports
1-42 Oracle SQL Developer User's Guide
Object Grants: For each privilege granted on a specific table, lists the user that
granted the privilege, the user to which the privilege was granted, the table, the
privilege, and whether the user to which the privilege was granted can grant that
privilege to other users.
Role Privileges: For each granted role, lists the user to which the role was granted,
the role, whether the role was granted with the ADMIN option, and whether the
role is designated as a default role for the user.
System Privileges: For each privilege granted to the user associated with the
database connection, lists the privilege and whether it was granted with the
ADMIN option.
Policies: Lists information about policies.
Public Grants: Lists information about privileges granted to the PUBLIC role.
1.12.11 Streams reports
Streams reports list information about stream rules.
All Stream Rules: Lists information about all stream rules. The information includes
stream type and name, rule set owner and name, rule owner and name, rule set type,
streams rule type, and subsetting operation.
Your Stream Rules: Lists information about each stream rule for which the user
associated with the database connection is the rule owner or rule set owner. The
information includes stream type and name, rule set owner and name, rule owner and
name, rule set type, streams rule type, and subsetting operation.
1.12.12 Table repor ts
Table reports list information about tables owned by the user associated with the
specified connection. This information is not specifically designed to identify problem
areas; however, depending on your resources and requirements, some of the
information might indicate things that you should monitor or address.
For table reports, the owner is the user associated with the database connection.
Columns: For each table, lists each column, its data type, and whether it can contain a
null value. Also includes Datatype Occurrences: For each table owner, lists each data
type and how many times it is used.
Comments for tables and columns: For each table and for each column in each table,
lists the descriptive comments (if any) associated with it. Also includes a report of
tables without comments. If database developers use the COMMENT statement when
creating or modifying tables, this report can provide useful information about the
purposes of tables and columns
Constraints: Includes the following reports related to constraints:
All Constraints: For each table, lists each associated constraint, including its type
(unique constraint, check constraint, primary key, foreign key) and status (enabled
or disabled).
Check Constraints: For each check constraint, lists information that includes the
owner, the table name, the constraint name, the constraint status (enabled or
disabled), and the constraint specification.
Enabled Constraints and Disabled Constraints: For each constraint with a status
of enabled or disabled, lists the table name, constraint name, constraint type
(unique constraint, check constraint, primary key, foreign key), and status. A
SQL Developer Reports
SQL Developer Concepts and Usage 1-43
disabled constraint is not enforced when rows are added or modified; to have a
disabled constraint enforced, you must edit the table and set the status of the
constraint to Enabled (see the appropriate tabs for the Create/Edit Table (with
advanced options) dialog box).
Foreign Key Constraints: For each foreign key constraint, lists information that
includes the owner, the table name, the constraint name, the column that the
constraint is against, the table that the constraint references, and the constraint in
the table that is referenced.
Primary Key Constraints: For primary key constraint, lists information that
includes the owner, the table name, the constraint name, the constraint status
(enabled or disabled), and the column name.
Unique Constraints: For each unique constraint, lists information that includes
the owner, the table name, the constraint name, the constraint status (enabled or
disabled), and the column name.
Indexes: Includes information about all indexes, indexes by status, indexes by type,
and unused indexes.
Organization: Specialized reports list information about partitioned tables, clustered
tables, and index-organized tables.
Quality Assurance: (See Quality Assurance reports.)
Statistics: For each table, lists statistical information, including when it was last
analyzed, the total number of rows, the average row length, and the table type. In
addition, specialized reports order the results by most rows and largest average row
length.
Storage: Lists information about the table count by tablespace and the tables in each
tablespace.
Trigg e r s : Lists information about all triggers, disabled triggers, and enabled triggers.
User Synonyms: Displays information about either all user synonyms or those user
synonyms containing the string that you specify in the Enter Bind Variables dialog box
(uncheck Null in that box to enter a string).
User Tables: Displays information about either all tables or those tables containing the
string that you specify in the Enter Bind Variables dialog box (uncheck Null in that box
to enter a string).
Quality Assurance reports
Quality assurance reports are table reports that identify conditions that are not
technically errors, but that usually indicate flaws in the database design. These flaws
can result in various problems, such as logic errors and the need for additional
application coding to work around the errors, as well as poor performance with
queries at run time.
Tables without Primary Keys: Lists tables that do not have a primary key defined. A
primary key is a column (or set of columns) that uniquely identifies each row in the
table. Although tables are not required to have a primary key, it is strongly
recommended that you create or designate a primary key for each table. Primary key
columns are indexed, which enhances performance with queries, and they are
required to be unique and not null, providing some "automatic" validation of input
data. Primary keys can also be used with foreign keys to provide referential integrity.
Tables without Indexes: Lists tables that do not have any indexes. If a column in a
table has an index defined on it, queries that use the column are usually much faster
SQL Developer Reports
1-44 Oracle SQL Developer User's Guide
and more efficient than if there is no index on the column, especially if there are many
rows in the table and many different data values in the column.
Tables with Unindexed Foreign Keys: Lists any foreign keys that do not have an
associated index. A foreign key is a column (or set of columns) that references a
primary key: that is, each value in the foreign key must match a value in its associated
primary key. Foreign key columns are often joined in queries, and an index usually
improves performance significantly for queries that use a column. If an unindexed
foreign key is used in queries, you may be able to improve run-time performance by
creating an index on that foreign key.
1.12.13 XML reports
XML reports list information about XML objects.
XML Schemas: For each user that owns any XML objects, lists information about each
object, including the schema URL of the XSD file containing the schema definition.
1.12.14 Migration reports
Migration reports list information related to migrating third-party databases to Oracle.
For more information, see Section 2.13, "Using Migration Reports".
1.12.15 User Defined reports
User Defined reports are any reports that are created by SQL Developer users. To
create a user-defined report, right-click the User Defined node under Reports and
select Add Report. A dialog box is displayed in which you specify the report name
and the SQL query to retrieve information for the report (see Section 4.43, "Create/Edit
User Defined Report").
You can organize user-defined reports in folders, and you can create a hierarchy of
folders and subfolders. To create a folder for user-defined reports, right-click the User
Defined node or any folder name under that node and select Add Folder (see
Section 4.44, "Create/Edit User Defined Report Folder").
Information about user-defined reports, including any folders for these reports, is
stored in a file named UserReports.xml under the directory for user-specific
information. For information about the location of this information, see Section 1.14,
"Location of User-Related Information".
For examples of creating user-defined reports, see:
Section 1.12.15.1, "User-Defined Report Example: Chart"
Section 1.12.15.2, "User-Defined Report Example: Dynamic HTML"
Section 1.7.8, "Gauges: In the SQL Worksheet and User-Defined Reports"
1.12.15.1 User-Defined Report Example: Chart
This example creates a report displayed as a chart. It uses the definition of the
EMPLOYEES table from the HR schema, which is a supplied sample schema.
Right-click on User Defined Reports and select Add Report. In the Add Report dialog
box, specify a report name; for Style, select Chart; and for SQL, enter the following:
select m.department_id, e.last_name, e.salary
from employees m, employees e
where e.employee_id = m.employee_id
order by 1
SQL Developer Reports
SQL Developer Concepts and Usage 1-45
The preceding query lists the last name and salary of each employee in each
department, grouping the results by department ID (10, 20, 30, ... 110). Note that the
expected syntax for the SQL statement for a chart report is:
SELECT <group>,<series>,<value> FROM <table(s)>
Click the Chart Details tab near the bottom of the box; for Chart Type, select BAR_
VERT_STACK (bar chart, stacked vertically); and click Apply.
Use the Reports navigator to view the newly created user-defined report. For
Connection, specify one that connects to the HR sample schema.
The report is displayed as a chart, part of which is shown in the following illustration.
For example, as you can see, department 50 has mainly employees with the lowest
salaries, and department 90 consists of the three highest-paid employees.
SQL Developer Preferences
1-46 Oracle SQL Developer User's Guide
1.12.15.2 User-Defined Report Example: Dynamic HTML
This example creates a report using one or more PL/SQL DBMS_OUTPUT statements,
so that the report is displayed as dynamic HTML.
Right-click on User Defined Reports and select Add Report. In the Add Report dialog
box, specify a report name; for Style, select plsql-dbms_output; and for SQL, enter the
following:
begin
dbms_output.put_line ('<H1> This is Level-1 Heading </H1>');
dbms_output.put_line ('<H2> This is a Level-2 Heading </H2>');
dbms_output.put_line ('<p> This is regular paragraph text. </p>');
end;
Click Apply.
Use the Reports navigator to view the newly created user-defined report. For
Connection, specify any from the list. (This report does not depend on a specific
connection of table.).
The report is displayed as formatted HTML output.
1.13 SQL Developer Preferences
You can customize many aspects of the SQL Developer interface and environment by
modifying SQL Developer preferences according to your preferences and needs. To
modify SQL Developer preferences, select Tools, then Preferences.
Information about SQL Developer preferences is stored under the directory for
user-specific information. For information about the location of this information, see
Section 1.14, "Location of User-Related Information".
Most preferences are self-explanatory, and this topic explains only those whose
meaning and implications are not obvious. Some preferences involve performance or
system resource trade-offs (for example, enabling a feature that adds execution time),
and other preferences involve only personal aesthetic taste. The preferences are
grouped in the following categories.
1.13.1 Environment
The Environment pane contains options that affect the startup and overall behavior
and appearance of SQL Developer. You can specify that certain operations be
performed automatically at specified times, with the trade-off usually being the extra
time for the operation as opposed to the possibility of problems if the operation is not
performed automatically (for example, if you forget to perform it when you should).
The undo level (number of previous operations that can be undone) and navigation
level (number of open files) values involve slight increases or decreases system
resource usage for higher or lower values.
Automatically Reload Externally Modified Files: If this option is checked, any files
open in SQL Developer that have been modified by an external application are
updated when you switch back to SQL Developer, overwriting any changes that you
might have made. If this option is not checked, changes that you make in SQL
Developer overwrite any changes that might have been made by external applications.
Silently Reload When File Is Unmodified: If this option is checked, you are not asked
if you want to reload files that have been modified externally but not in SQL
Developer. If this option is not checked, you are asked if you want to reload each file
that has been modified externally, regardless of whether it has been modified in SQL
Developer.
SQL Developer Preferences
SQL Developer Concepts and Usage 1-47
Environment: Dockable Windows
The Dockable Windows pane configures the behavior of dockable windows and the
shapes of the four docking areas of SQL Developer: top, bottom, left, and right.
Dockable Windows Always on Top: If this option is checked, dockable windows
always remain visible in front of other windows.
Windows Layout: Click the corner arrows to lengthen or shorten the shape of each
docking area.
Environment: Local History
The Local History pane controls whether information about editing operations on files
opened within SQL Developer is kept. If local history is enabled, you can specify how
long information is retained and the maximum number of revisions for each file.
Environment: Log
The Log pane configures the colors of certain types of log messages and the saving of
log messages to log files.
Save Logs to File: If this option is checked, all output to the Messages - Log window is
saved to log files, where the file name reflects the operation and a timestamp. You are
also asked to specify a Log Directory; and if the specified directory does not already
exist, it is created. Note that if you save log information to files, the number of these
files can become large.
Maximum Log Lines: The maximum number of lines to store in each log file.
1.13.2 Accelerators (Keyboard Shortcuts)
The Accelerators pane enables you to view and customize the accelerator key
mappings (keyboard shortcuts) for SQL Developer.
Category: Select All or a specific category (Code Editor, Database, Debug, Edit, and so
on), to control which actions are displayed.
Actions: The actions for the selected category. When you select an action, any existing
accelerator key mappings are displayed.
Accelerators: Any existing key mappings for the selected action. To remove an existing
key mapping, select it and click Remove.
New Accelerator: The new accelerator key to be associated with the action. Press and
hold the desired modifier key, then press the other key. For example, to associate
Ctrl+J with an action, press and hold the Ctrl key, then press the j key. If any actions
are currently associated with that accelerator key, they are listed in the Current
Assignment box.
Current Assignment: A read-only display of the current action, if any, that is mapped
to the accelerator key that you specified in the New Accelerator box.
Load Preset: Displays the Load Preset Key Mappings dialog box, where you can load a
set of predefined key mappings (including the SQL Developer defaults) for certain
systems and external editing applications. If you load any preset key mappings that
conflict with changes that you have made, your changes are overwritten.
1.13.3 Code Editor
The Code Editor pane contains general options that affect the appearance and
behavior of SQL Developer when you edit functions, procedures, and packages.
SQL Developer Preferences
1-48 Oracle SQL Developer User's Guide
Code Editor: Bookmarks
The Bookmarks pane contains options that determine the persistence and search
behavior for bookmarks that you create when using the code editor.
Code Editor: Caret Behavior
The Caret Behavior pane contains options that determine the shape, color, and
blinking characteristics of the caret (cursor) in the code editor.
Code Editor: Code Insight
The Code Insight pane contains options for the logical completion (autocomplete
options) of keywords and names while you are coding in the SQL Worksheet.
When you press Ctrl+Space, code insight provides a context-sensitive popup window
that can help you select parameter names. Completion insight provides you with a list
of possible completions at the insertion point that you can use to auto-complete code
you are editing. This list is based on the code context at the insertion point. To exit
code insight at any time, press Esc.
You can enable or disable both completion and parameter insight, as well as set the
time delay for the popup windows.
Code Editor: Code Insight: Completion
The Code Insight: Completion pane contains options for refining the behavior when
matching items are found. For more information, see the explanation for Code Editor:
Code Insight.
Code Editor: Display
The Display pane contains general options for the appearance and behavior of the
code editor.
Text Anti-Aliasing allows smooth-edged characters where possible.
Code Folding Margin allows program blocks in procedures and functions to be
expanded and collapsed in the display.
Visible Right Margin renders a right margin that you can set to control the length of
lines of code.
Automatic Brace Matching controls the highlighting of opening parentheses and
brackets and of blocks when a closing parenthesis or bracket is typed.
Code Editor: Fonts
The Fonts pane specifies text font options for the code editor.
Display Only Fixed-Width Fonts: If this option is checked, the display of available
font names is restricted to fonts where all characters have the same width.
(Fixed-width fonts are contrasted with proportional-width fonts.)
Code Editor: Line Gutter
The Line Gutter pane specifies options for the line gutter (left margin of the code
editor).
Show Line Numbers: If this option is checked, lines are numbered. (To go to a line
number while you are using the SQL Worksheet, press Ctrl+G.)
Enable Line Selection by Click-Dragging: If this option is checked, you can select
consecutive lines in the editor by clicking in the gutter and dragging the cursor
without releasing the mouse button.
SQL Developer Preferences
SQL Developer Concepts and Usage 1-49
Code Editor: Printing
The Printing pane specifies options for printing the contents of the code editor. The
Preview pane sample display changes as you select and deselect options.
Code Editor: Printing HTML
The Printing HTML pane specifies options for printing HTML files from the code
editor.
Code Editor: Save Actions
The Save Actions pane specifies actions to be performed automatically during a save
operation.
Code Editor: Syntax Colors
The Syntax Colors pane specifies colors for different kinds of syntax elements.
Code Editor: Undo Behavior
The Undo Behavior pane specifies options for the behavior of undo operations
(Ctrl+Z, or Edit, then Undo). Only consecutive edits of the same type are considered; for
example, inserting characters and deleting characters are two different types of
operation.
Allow Navigation-Only Changes to be Undoable: If this option is checked,
navigation actions with the keyboard or mouse can be undone. If this option is not
checked, navigation actions cannot be undone, and only actual changes to the text can
be undone.
1.13.4 Compare and Merge
The Compare and Merge pane defines options for comparing and merging two source
files. For more information, see, see Comparing Source Files.
For each type of option, you can specify a Maximum File Size (KB): the maximum
size of the file (number of kilobytes) for which the operation will be performed.
Ignore Whitespace: If this option is enabled, leading and trailing tabs and letter
spacing are ignored when comparing files. Carriage returns are not ignored. Enabling
this option makes comparing two files easier when you have replaced all the space
with hard tabs, or vice versa. Otherwise, every line in the two documents might be
shown as different in the Compare window.
Show Character Differences: If this option is enabled, characters that are present in
one file and not in another are highlighted. Red highlighting indicates a character that
has been removed. Green highlighting indicates a character that has been added. The
highlighting is shown only when you click into a comparison block that contains
character differences.
Enable XML Compare: If this option is enabled, XML files can be compared.
Enable XML Merge: If this option is enabled, XML files can be merged.
Reformat Result: If this option is enabled, merged XML files can be reformatted.
Validate Result (May require Internet access): If this option is enabled, merged XML
files will be validated.
Comparing Source Files
You can compare source files in the following ways:
SQL Developer Preferences
1-50 Oracle SQL Developer User's Guide
A file currently being edited with its saved version: Place the focus on the current
version open in the editor, then select the History tab in the editor window. The
saved file opens side by side with the file in the editor buffer.
One file with another file outside the project: Place the focus on the file in the
editor to be compared; from the main menu, choose File, then Compare With
Other File; in the Select File to Compare With dialog, navigate to the file and click
Open.
Two files within the same project: In the navigator, select the two files to be
compared; then from the main menu, choose File, then Compare With Each
Other.
1.13.5 Database
The Database pane sets properties for the database connection.
Validate date and time default values: If this option is checked, date and time
validation is used when you open tables.
Default path for storing export: Default path of the directory or folder under which to
store output files when you perform an export operation. To see the current default for
your system, click the Browse button next to this field.
Run startup script on each new database connection: If this option is checked, the
script specified in the next field is executed whenever a connection is opened to an
Oracle database.
Filename for startup script: File name for the startup script to run when an Oracle
database connection is opened. You can click Browse to specify the location. The
default location is the default path for scripts (see the Database: Worksheet Parameters
preferences pane).
Database: Advanced Parameters
The Advanced Parameters pane specifies options such as the SQL array fetch size and
display options for null values.
You can also specify Kerberos thin driver configuration parameters, which enables you
to create database connections using Kerberos authentication and specifying the user
name and password. For more information, see the Kerberos Authentication
explanation on the Oracle tab in the Create/Edit/Select Database Connection dialog
box. For information about configuring Kerberos authentication, see Oracle Database
Advanced Security Administrator's Guide.
Use OCI/Thick driver: If this option is checked, and if an OCI (thick, Type 2) driver is
available, that driver will be used instead of a JDBC (thin) driver for basic and TNS
(network alias) database connections.
Kerberos Thin Config: Config File: Kerberos configuration file (for example,
krb5.conf).
Kerberos Thin Config: Credential Cache File: Kerberos credential cache file (for
example, krb5_cc_cache).
Database: Autotrace Parameters
The Autotrace Parameters pane specifies information to be displayed on the Autotrace
pane in the SQL Worksheet.
SQL Developer Preferences
SQL Developer Concepts and Usage 1-51
Database: General Export Parameters
Custom Export Delimiter: The character to be used as the delimiter when you export
table data in CSV format. This option enables you to use a character other than the
default comma (,) as the CSV delimiter.
Database: NLS Parameters
The NLS Parameters pane specifies values for globalization support parameters, such
as the language, territory, sort preference, and date format. These parameter values are
used for SQL Developer session operations, such as for statements executed using the
SQL Worksheet and for the National Language Support Parameters report. Specifying
values in this preferences pane does not apply those values to the underlying database
itself. To change the database settings, you must change the appropriate initialization
parameters and restart the database.
Note that SQL Developer does not use default values from the current system for
globalization support parameters; instead, SQL Developer, when initially installed, by
default uses parameter values that include the following:
NLS_LANG,"AMERICAN"
NLS_TERR,"AMERICA"
NLS_CHAR,"AL32UTF8"
NLS_SORT,"BINARY"
NLS_CAL,"GREGORIAN"
NLS_DATE_LANG,"AMERICAN"
NLS_DATE_FORM,"DD-MON-RR"
Database: ObjectViewer Parameters
The ObjectViewer Parameters pane specifies whether to freeze object viewer windows,
and display options for the output. The display options will affect the generated DDL
on the SQL tab. The Data Editor Options affect the behavior when you are using the
Data tab to edit table data.
Data Editor Options
Post Edits on Row Change: If this option is checked, posts DML changes when you
perform edits using the Data tab (and the Set Auto Commit On option determines
whether or not the changes are automatically committed). If this option is not checked,
changes are posted and committed when you press the Commit toolbar button.
Set Auto Commit On (available only if Post Edit on Row Changes is enabled): If this
option is checked, DML changes are automatically posted and committed when you
perform edits using the Data tab.
Clear persisted table column widths, order, sort, and filter settings: If you click
Clear, then any customizations in the Data tab display for table column widths, order,
sort, and filtering are not saved for subsequent openings of the tab, but instead the
default settings are used for subsequent openings.
Database: PL/SQL Compiler Options
The PL/SQL Compiler Options pane specifies options for compilation of PL/SQL
subprograms.
Generate PL/SQL Debug Information: If this option is checked, PL/SQL debug
information is included in the compiled code; if this option is not checked, this debug
information is not included. The ability to stop on individual code lines and debugger
access to variables are allowed only in code compiled with debug information
generated.
SQL Developer Preferences
1-52 Oracle SQL Developer User's Guide
Types of messages: You can control the display of informational, severe, and
performance-related messages. (The ALL type overrides any individual specifications
for the other types of messages.) For each type of message, you can specify any of the
following:
No entry (blank): Use any value specified for ALL; and if none is specified, use the
Oracle default.
Enable: Enable the display of all messages of this category.
Disable: Disable the display of all messages of this category.
Error: Enable the display of only error messages of this category.
Optimization Level: 1, 2, or 3, reflecting the number and type of changes made
(lowest impact to highest impact). Higher levels usually result in better performance,
but longer compilation time.
PLScope Identifiers: Specifies the amount of PL/Scope identifier data to collect and
use (All or None).
Database: Reports
The Reports pane specifies options relating to SQL Developer reports.
Close all reports on disconnect: If this option is checked, all reports for any database
connection are automatically closed when that connection is disconnected.
Database: SQL Editor Code Templates
The SQL Editor Code Templates pane enables you to view, add, and remove templates
for editing SQL and PL/SQL code. Code templates assist you in writing code more
quickly and efficiently by inserting text for commonly used statements. You can then
modify the inserted text.
To insert the contents of a code template in the SQL Worksheet, put the cursor at the
point where the template is to be inserted, type the ID associated with the template,
and then press Ctrl+Shift+T. (Note: Ctrl+Shift+T is the accelerator assigned in the
default keymap, but you can assign another mapping.)
To enclose a SELECT statement in a FOR loop, select (highlight) the SELECT statement
and press Ctrl+Shift+T.
Add Template: Adds an empty row in the code template display. Enter an ID value,
then move to the Template cell; you can enter template content in that cell, or click the
ellipsis (...) button to open the code editor to enter the template content.
Remove Template: Deletes the selected code template.
Database: SQL Formatter
The SQL Formatter pane controls how statements in the SQL Worksheet are formatted
when you click Format SQL. The options include whether to insert space characters or
tab characters when you press the Tab key (and how many characters), uppercase or
lowercase for keywords and identifiers, whether to preserve or eliminate empty lines,
and whether comparable items should be placed or the same line (if there is room) or
on separate lines.
Database: Third Party JDBC Drivers
The Third Party JDBC Drivers pane specifies drivers to be used for connections to
third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, or Sybase
Adaptive Server. (You do not need to add a driver for connections to Microsoft Access
SQL Developer Preferences
SQL Developer Concepts and Usage 1-53
databases.) To add a driver, click Add Entry and select the path for the driver (for
example, a file with a name similar to mysql-connector-java-5.0.4-bin.jar,
in a directory under the one into which you unzipped the download for the MySQL
driver; or jtds-1.2.jar, which is included in the jtds-1.2-dist.zip download,
for Microsoft SQL Server or Sybase Adaptive Server).
Alternative: As an alternative to using this preference, you can click
Help, then Check for Updates to install the JTDS JDBC Driver for
Microsoft SQL Server and the MySQL JDBE Driver as extensions.
To find a specific third-party JDBC driver, see the appropriate Web site (for example,
http://www.mysql.com for the MySQL Connector/J JDBC driver for MySQL, or
http://jtds.sourceforge.net/ for the jTDS driver for Microsoft SQL Server
and Sybase Adaptive Server). For MySQL, use the MySQL 5.0 driver, not 5.1 or later,
with SQL Developer release 1.5.
You must specify a third-party JDBC driver or install a driver using the Check for
Updates feature before you can create a database connection to a third-party database
of that associated type. (See the tabs for creating connections to third-party databases
in the Create/Edit/Select Database Connection dialog box.)
Database: User-Defined Extensions
The User-Defined Extensions pane specifies user-defined extensions that have been
added. You can use this pane to add extensions that are not available through the
Check for Updates feature. (For more information about extensions and checking for
updates, see Section 1.13.7, "Extensions".)
One use of the Database: User-Defined Extensions pane is to create a Shared Reports
folder and to include an exported report under that folder: click Add Row, specify
Type as REPORT, and for Location specify the XML file containing the exported report.
The next time you restart SQL Developer, the Reports navigator will have a Shared
Reports folder containing that report
Database: Worksheet Parameters
Autocommit in SQL Worksheet: If this option is checked, a commit operation is
automatically performed after each INSERT, UPDATE, or DELETE statement executed
using the SQL Worksheet. If this option is not checked, a commit operation is not
performed until you execute a COMMIT statement.
Open a worksheet on connect: If this option is checked, a SQL Worksheet window for
the connection is automatically opened when you open a database connection. If this
option is not checked, you must use the Open SQL Worksheet right-click command or
toolbar icon to open a SQL Worksheet.
Close all worksheets on disconnect: If this option is checked, all SQL Worksheet
windows for any database connection are automatically closed when that connection
is disconnected.
Max rows to print in a script: Limits the number of rows displayed.
Default path to look for scripts: The default directory where SQL Developer looks
when you run a script (using @).
Save bind variables to disk on exit: If this option is checked, bind variables that you
enter when running a script are saved on disk for reuse. If you do not want bind
variable values stored on disk (for security or other reasons), be sure not to check this
option.
SQL Developer Preferences
1-54 Oracle SQL Developer User's Guide
Drag and Drop Effects: Determines the type of SQL statement created in the SQL
Worksheet when you drag an object from the Connections navigator into the SQL
Worksheet. The SQL Developer preference sets the default, which you can override in
the Drag and Drop Effects dialog box.
The type of statement (INSERT, DELETE, UPDATE, or SELECT) applies only for object
types for which such a statement is possible. For example, SELECT makes sense for a
table, but not for a trigger. For objects for which the statement type does not apply, the
object name is inserted in the SQL Worksheet.
1.13.6 Debugger
The Debugger pane contains general options for the SQL Developer debugger. Other
panes contain additional specific kinds of debugger options.
Debugger: Breakpoints
The Breakpoints pane sets the columns to appear in the Breakpoints pane and the
scope of each breakpoint.
Debugger: Breakpoints: Default Actions
The Breakpoints: Default Actions pane sets defaults for actions to occur at breakpoints.
These actions are the same as on the Actions tab in the Create/Edit Breakpoint dialog
box.
Debugger: Data
The Data pane enables you to control the columns to appear in the debugger Data
pane and aspects of how the data is displayed.
Debugger: Inspector
The Inspector pane enables you to control the columns to appear in the debugger
Inspector pane and aspects of how the data is displayed.
Debugger: Smart Data
The Smart Data pane enables you to control the columns to appear in the debugger
Smart Data pane and aspects of how the data is displayed.
Debugger: Stack
The Stack pane enables you to control the columns to appear in the debugger Stack
pane and other options.
Debugger: Watches
The Watches pane enables you to control the columns to appear in the debugger
Watches pane and aspects of how the data is displayed.
1.13.7 Extensions
The Extensions pane determines which optional extensions SQL Developer uses when
it starts. (SQL Developer also uses some mandatory extensions, which users cannot
remove or disable.) If you change any settings, you must exit SQL Developer and
restart it for the new settings to take effect.
For Versioning Support, the settings (selected or not, and configuration options if
selected) affect whether the Versioning menu is displayed and the items on that menu.
SQL Developer Preferences
SQL Developer Concepts and Usage 1-55
Extensions to Use: Controls the specific optional SQL Developer extensions to use at
startup.
Check for Updates: Checks for any updates to the selected optional SQL Developer
extensions, as well as any mandatory extensions. (If the system you are using is behind
a firewall, see the SQL Developer user preferences for Web Browser and Proxy.)
Automatically Check for Updates: If this option is checked, SQL Developer
automatically checks for any updates to the selected optional SQL Developer
extensions and any mandatory extensions at startup.
1.13.8 File Types
The File Types pane determines which file types and extensions will be opened by
default by SQL Developer. The display shows each file extension, the associated file
type, and a check mark if files with that extension are to be opened by SQL Developer
be default, such as when a user double-clicks the file name.
Details area at bottom: You can modify the file type, content type (text or binary), and
whether to open files with this extension automatically by SQL Developer.
To have files with a specific extension be opened by default by SQL Developer, click
the file extension in the list, then check Open with SQL Developer in the Details area.
This overrides any previous application association that may have been in effect for
that file extension.
To add a file extension, click Add and specify the file extension (including the period).
After adding the extension, you can modify its associated information by selecting it
and using the Details area.
1.13.9 Global Ignore List
The Global Ignore List pane specifies filters that determine which files and file types
will not be used in any processing.
New Filter: A file name or file type that you want to add to the list of files and file
types (in the Filter box) that SQL Developer will ignore during all processing (if the
filter is enabled, or checked). You can exclude a particular file by entering its complete
file name, such as mumble.txt, or you can exclude all files of the same type by
entering a construct that describes the file type, such as *.txt.
Add: Adds the new filter to the list in the Filter box.
Remove: Deletes the selected filter from the list in the Filter box.
Restore Defaults: Restores the contents of the Filter box to the SQL Developer
defaults.
Filter: Contains the list of files and file types. For each item, if it is enabled (checked),
the filter is enforced and the file or file type is ignored by SQL Developer; but if it is
disabled (unchecked), the filter is not enforced.
1.13.10 Migration
The Migration pane contains options that affect the behavior of SQL Developer when
you migrate schema objects and data from third-party databases to an Oracle
database.
Default Repository: Migration repository to be used for storing the captured models
and converted models. For information about migrating third-party databases to
Oracle, including how to create a migration repository, see Chapter 2.
SQL Developer Preferences
1-56 Oracle SQL Developer User's Guide
Migration: Data Move Options
The Data Move Options pane contains options that affect the behavior when you
migrate data from third-party databases to Oracle Database tables generated by the
migration.
Online for all. Offline for MySQL, SQL Server, and Sybase Adaptive Server:
Options that can be used for online data migration for all supported third-party
databases, and for offline data migration for MySQL, SQL Server, and Sybase
Adaptive Server.
Representation for 0 Length String: The value to which Oracle converts zero-length
strings in the source data. Can be a space (' ') or a null value (NULL). Specific notes:
For Microsoft Access offline migrations, a null value and a space are considered
the same.
For Sybase offline migrations, '' is considered the same as a space (' ').
For MySQL offline migrations, a null value is exported as 'NULL', which is
handled as type VARCHAR2. You can specify another escape character by using
the --fields-escaped-by option with the mysqldump command (for example,
specifying \N for null or \\ for \). For information about the mysqldump
command, see Section 2.9.1.3, "Creating Data Files From MySQL".
For MySQL offline migrations, the data is exported to a file named table-name.txt;
so if you are moving data from two or more tables with the same name but in
different schemas, rename files as needed so that they are all unique, and modify
the SQL*Loader .ctl file accordingly.
Online: The online data move options determine the results of files created when you
click Migration, then Migrate Data.
Number of Parallel Data Move Streams: The number of internal connections created
for simultaneous movement of data from the source database to the Oracle tables.
Higher values may shorten the total time required, but will use more database
resources during that time.
Number of Rows to Commit After: During the data move operation, Oracle pauses to
perform an automatic internal commit operation after each number of rows that you
specify are moved from the source database to Oracle tables.
Lower values will cause a successful move operation to take more time; but if a failure
occurs, it is likely that more source records will exist in the Oracle tables and that if the
move operation is resumed, fewer source records will need to be moved. Higher
values will cause a successful move operation to take less time; but if a failure occurs,
it is likely that fewer source records will exist in the Oracle tables and that is the move
operation is resumed, more source records will need to be moved.
Offline: The offline data move options determine the results of files created when you
click Migration, then Generate Scripts, then Generate Data Move Scripts.
Offline Data Script Directory: Default location for scripts for offline data move
operations.
End of Column Delimiter: String to indicate end of column.
End of Row Delimiter: String to indicate end of row.
Date Mask: Format mask for dates.
Timestamp Mask: Format mask for timestamps.
SQL Developer Preferences
SQL Developer Concepts and Usage 1-57
Migration: Generation Options
The Generation Options pane contains options that determine the results of files
created when you click Migration, then Generate Scripts, then Generate Oracle DDL.
One single file, A file per object, or A file per database: Determines how many files
are created and their relative sizes. Having more files created might be less convenient,
but may allow more flexibility with complex migration scenarios. (See also the
Maximum Number of Lines option.)
Output Directory: Default location in which the files will be created.
Implement ’CREATE’ as ’CREATE OR REPLACE’: Causes CREATE statements in
source database objects to be implemented using CREATE OR REPLACE when the
Oracle syntax allows this.
Generate Comments: Generates comments in the Oracle SQL statements.
Generate Controlling Script: Generates a "master" script for running all the required
files.
Maximum Number of Lines: Sets a maximum number of lines for each file; you then
specify the number.
Least Privilege Schema Migration: For migrating schema objects in a converted
model to Oracle, causes CREATE USER, GRANT, and CONNECT statements not to be
generated in the output scripts. You must then ensure that the scripts are run using a
connection with sufficient privileges. You can select this option if the database user
and connection that you want to use to run the scripts already exist, or if you plan to
create them.
Generate Data Move User: For data move operations, creates an additional database
user with extra privileges to perform the operation. It is recommended that you delete
this user after the operation. This option is provided for convenience, and is suggested
unless you want to perform least privilege migrations or unless you want to grant
privileges manually to a user for the data move operations. This option is especially
recommended for multischema migrations, such as when not all tables belong to a
single user.
Generate Failed Objects: Causes objects that failed to be converted to be included in
the generation script, so that you can make any desired changes and then run the
script. If this option is not checked, objects that failed to be converted are not included
in the generation script.
Generate Stored Procedure for Migrate Blobs Offline: Causes a stored procedure
named CLOBtoBLOB_sqldeveloper (with execute access granted to public) to be
created if the schema contains a BLOB (binary large object); this procedure is
automatically called if you perform an offline capture. If this option is not checked,
you will need to use the manual workaround described in Section 2.9.1.4, "Populating
the Destination Database Using the Data Files". (After the offline capture, you can
delete the CLOBtoBLOB_sqldeveloper procedure or remove execute access from
public.)
Migration: Identifier Options
The Identifier Options pane contains options that apply to object identifiers during
migrations.
Prepended to All Identifier Names (Microsoft Access, Microsoft SQL Server, and
Sybase Adaptive Server migrations only): A string to be added at the beginning of the
name of migrated objects. For example, if you specify the string as XYZ_, and if a
SQL Developer Preferences
1-58 Oracle SQL Developer User's Guide
source table is named EMPLOYEES, the migrated table will be named XYZ_
EMPLOYEES. (Be aware of any object name length restrictions if you use this option.)
Is Quoted Identifier On (Microsoft SQL Server and Sybase Adaptive Server
migrations only): If this option is enabled, quotation marks (double-quotes) can be
used to refer to identifiers (for example, SELECT "Col 1" from "Table 1"); if this option
is not enabled, quotation marks identify string literals. Important: The setting of this
option must match the setting in the source database to be migrated, as explained in
Section 2.5.1, "Before Migrating From Microsoft SQL Server or Sybase Adaptive
Server".
Migration: Translation Options
The Translation Options pane contains options that relate to conversion of stored
procedures and functions from their source database format to Oracle format.
Default Source Date Format: Default data format mask for dates in the source data.
Translation Diff Viewer: Several options affect the display when you use the
translation differences viewer feature.
1.13.11 Versioning
Versioning preferences affect the behavior of the version control and management
systems that you can use with SQL Developer. You can specify preferences for CVS
and Subversion. For information about using versioning with SQL Developer, see
Section 1.11, "Using Versioning".
Versioning: CVS
The CVS pane specifies options for use with CVS (Concurrent Versions System).
CVS Client: Internal to Oracle SQL Developer (installed with SQL Developer) or
External Executable (separately installed CVS client, for which you must specify the
name or path).
Name on System Path: Name of the CVS server executable. The default (cvs) is
correct for most installations. This option assumes that the name of the CVS server
executable is on the system path.
Path from Environment: Location of the CVS server executable, especially if there
is more than one on the system path. The selection area will list all instances of the
CVS server executable known to the local system. You may have more than one
version of CVS installed: this option lets you specify which of them to use with
SQL Developer.
Other Path: Location of the CVS server executable, if it is not on the system path at
all.
Run CVS in Edit/Watch Mode: If this option is enabled, you coordinate access to files
by declaring an editor for them through CVS, after which they may be modified. Only
those files that you check out after changing this preference will be affected. If this
option is disabled, the edit and watch commands on the Versioning menu are disabled.
State Overlay Scheme: Scheme for the icons displayed alongside folder and file names
in the navigators to indicate their versioning status.
Versioning: CVS: Commands
The CVS: Commands pane sets options for CVS source control. Some options are not
available when using the internal CVS client.
SQL Developer Preferences
SQL Developer Concepts and Usage 1-59
Enable Advanced Controls: If this option is enabled, advanced CVS controls are
shown in dialog boxes. If you find that you use only basic CVS features, you might
wish to use SQL Developer without advanced controls, to reduce complexity and save
screen space.
Global Options: Run Quietly: If this option is enabled, informational messages are
suppressed.
Global Options: Do not Log Commands: If this option is enabled, CVS commands are
not logged in the repository command history.
Global Options: Encrypt: If this option is enabled, all communication between the
client and the server is encrypted. Encryption support is not available in CVS by
default; it must be enabled using a special configuration option when you build CVS.
Set Compression Level (z): If this option is enabled, you can set the compression level
for files sent between client and server. The level can be set from Minimum (high
speed, low compression) to Maximum (low speed, high compression).
Keyword Substitution Mode: CVS uses keyword substitution modes to insert revision
information into files when they are checked out or updated. This option controls the
mode of replacement for keyword substitution in versioned files:
Automatic: The default, recommended option.
Keyword-Only Mode: Generates only keyword names in keyword strings and
omits their values. This option is useful for disregarding differences due to
keyword substitution when comparing different revisions of a file.
Keyword-Value Mode: Generates keyword strings using the default form.
Keyword-Value-Locker Mode: Like the keyword-value mode, except that the
name of the locker is always inserted if the given revision is currently locked.
Old-Contents Mode: Generates the old keyword string, present in the working file
just before it was checked in.
Value-Only Mode: Generates only keyword values for keyword strings. This can
help generate files in programming languages where it is hard to strip keyword
delimiters from a string. However, further keyword substitution cannot be
performed once the keyword names are removed, so this option should be used
with care.
On Commit: Use Comment Templates: If this option is enabled, your commit
comments will be entered through template forms. The forms are set up by the CVS
system administrator. There may be different forms for different circumstances and
installations, and it may be that none of them are suitable for your commit comments.
In this case, this preference lets you disable the use of all forms.
On Commit: Automatically Add Files: If this option is enabled, local files are added to
the CVS repository whenever you perform a commit action.
Create Backup Files on Remove: If this option is enabled, backup copies are made of
files that are removed through actions of the source control system.
Versioning: CVS: General
The CVS: General pane specifies environment settings and the operation timeout.
Use Navigator State Overlay Icons: If this option is enabled, state overlay icons are
used. State overlay icons are small symbols associated with object names in the
navigators. They indicate the state of version-controlled files (for example, "up to
date").
SQL Developer Preferences
1-60 Oracle SQL Developer User's Guide
Use Navigator State Overlay Labels: If this option is enabled, state overlay labels are
used. State overlay labels are tooltips associated with object names in the navigators.
Automatically Make Files Editable: If this option is enabled, an editor is
automatically used on a data file when you start to change it. (If you edit a file
unintentionally, immediately use Versioning, then Unedit to revert.)
Operation Timeout: Maximum time allowed for CVS operations to complete.
Versioning: CVS: Navigator Labels
The CVS: Navigator Labels pane specifies formatting for CVS information appears on
navigator nodes and tool tips. For a full explanation of keyword substitution modes,
see the CVS documentation.
Versioning: CVS: Version Tools
The CVS: Version Tools pane specifies options for the pending changes window and
the merge editor.
Use Outgoing Changes Commit Dialog: Enables you to make optimum use of limited
screen space when the Pending Changes window is open. You can save screen space
by not showing the Comments area of the Pending Changes window, but you might
still want to add comments before a commit action. You can choose the circumstances
under which the Commit dialog is opened: always, only when the Comments area of
the Pending Changes window is hidden, or never.
Incoming Changes Timer Interval: The frequency at which the change status of files is
checked.
Merge Editor: Specifies whether files are merged locally or at the server.
Versioning: Subversion
The Subversion pane specifies the Subversion client to use with SQL Developer.
Versioning: Subversion: General
The Subversion: General pane specifies environment settings and the operation
timeout.
Use Navigator State Overlay Icons: If this option is enabled, state overlay icons are
used. State overlay icons are small symbols associated with object names in the
navigators. They indicate the state of version-controlled files (for example, "up to
date").
Use Navigator State Overlay Labels: If this option is enabled, state overlay labels are
used. State overlay labels are tooltips associated with object names in the navigators.
Automatically Make Files Editable: If this option is enabled, an editor is
automatically used on a data file when you start to change it. (If you edit a file
unintentionally, immediately use Versioning, then Unedit to revert.)
Operation Timeout: Maximum time allowed for Subversion operations to complete.
Versioning: Subversion: Version Tools
The Subversion: Version Tools pane specifies options for the pending changes window
and the merge editor.
Use Outgoing Changes Commit Dialog: Enables you to make optimum use of limited
screen space when the Pending Changes window is open. You can save screen space
by not showing the Comments area of the Pending Changes window, but you might
still want to add comments before a commit action. You can choose the circumstances
Location of User-Related Information
SQL Developer Concepts and Usage 1-61
under which the Commit dialog is opened: always, only when the Comments area of
the Pending Changes window is hidden, or never.
Incoming Changes Timer Interval: The frequency at which the change status of files is
checked.
Merge Editor: Specifies whether files are merged locally or at the server.
1.13.12 Web Browser and Proxy
The Web Browser and Proxy pane settings are relevant only when you use the Check
for Updates feature (click Help, then Check for Updates), and only if your system is
behind a firewall.
Browser Command Line: To specify a Web browser other than your default browser,
specify the executable file to start that browser. To use your default browser, leave this
field blank.
Use HTTP Proxy Server: Check your Web browser options or preferences for the
appropriate values for these fields.
1.14 Location of User-Related Information
SQL Developer stores user-related information in several places, with the specific
location depending on the operating system and certain environment specifications.
User-related information includes user-defined reports, user-defined snippets, SQL
Worksheet history, code templates, and SQL Developer user preferences. In most cases,
your user-related information is stored outside the SQL Developer installation
directory hierarchy, so that it is preserved if you delete that directory and install a new
version.
The user-related information is stored in or under the following location:
On Windows systems: the HOME environment variable location, if defined;
otherwise the SQLDEVELOPER_USER_DIR location, if defined; otherwise as
indicated in the following table
On Linux and Mac OS X systems: the SQLDEVELOPER_USER_DIR location, if
defined; otherwise as indicated in the following table
The following table shows the typical default locations (under a directory or in a file)
for specific types of resources on different operating systems. (Note the period in the
name of any directory named .sqldeveloper.)
Table 1–1 Default Locations for User-Related Information
Resource Type System (Windows, Linux, or Mac OS X)
User-defined
reports
Windows: C:\Documents and Settings\<user-name>\Application Data\SQL
Developer\UserReports.xml
Linux or Mac OS X: ~/.sqldeveloper/UserReports.xml
User-defined
snippets
Windows: C:\Documents and Settings\<user-name>\Application
Data\SQL Developer\UserSnippets.xml
Linux: ~/.sqldeveloper/ UserSnippets.xml
Mac OS X: /Users/<Your user>/Library/Application Support/
SQLDeveloper/UserSnippets.xml
Oracle TimesTen In-Memory Database Support
1-62 Oracle SQL Developer User's Guide
To specify a nondefault SQLDEVELOPER_USER_DIR location, do either of the
following:
Set the SQLDEVELOPER_USER_DIR environment variable to specify another
directory path.
Edit the <sqldeveloper_
install>\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf file
and substitute the desired directory path for SQLDEVELOPER_USER_DIR in the
following line:
SetUserHomeVariable SQLDEVELOPER_USER_DIR
If you want to prevent other users from accessing your user-specific SQL Developer
information, you must ensure that the appropriate permissions are set on the directory
where that information is stored or on a directory above it in the path hierarchy. For
example, on a Windows system you may want to ensure that the SQL Developer
folder and the \<user-name>\Application Data\SQL Developer folder under
Documents and Settings are not shareable; and on a Linux or Mac OS X system
you may want to ensure that the ~/.sqldeveloper directory is not world-readable.
1.15 Oracle TimesTen In-Memory Database Support
When you connect to an Oracle TimesTen In-Memory Database, the available types of
objects that you can work with include several that apply to an Oracle Database, and
the following that are specific to TimesTen:
Cache groups
Replication schemes
To create a connection to a TimesTen database, use the TimesTen tab in the
Create/Edit/Select Database Connection dialog box.
For usage and reference information about TimesTen, see the online documentation
that is included with the TimesTen installation. For additional information, go to:
http://www.oracle.com/technology/products/timesten/
SQL history Windows: C:\Documents and Settings\<user-name>\Application Data\SQL
Developer\SqlHistory.xml
Linux: ~/.sqldeveloper/ SqlHistory.xml
Mac OS X: /Users/<Your user>/Library/Application Support/
SQLDeveloper/ SqlHistory.xml
Code templates Windows: C:\Documents and Settings\<user-name>\Application
Data\SQL Developer\ CodeTemplate.xml
Linux: ~/.sqldeveloper/ CodeTemplate.xml
Mac OS X: /Users/<Your user>/Library/Application Support/
SQLDeveloper/ CodeTemplate.xml
SQL Developer
user preferences
Windows: C:\Documents and Settings\<user-name>\Application
Data\SQL Developer\systemn.n.n.n.n
Linux or Mac OS X: ~/.sqldeveloper/systemn.n.n.n.n
Table 1–1 (Cont.) Default Locations for User-Related Information
Resource Type System (Windows, Linux, or Mac OS X)
For More Information
SQL Developer Concepts and Usage 1-63
1.16 Using the Help
SQL Developer provides a Help menu and context-sensitive help (click the Help
button or press the F1 key in certain contexts). Much of the help content is also in
Oracle Database SQL Developer User's Guide, which is in the SQL Developer
Documentation Library.
Help is displayed in the Help Center window, which has a Contents pane on the left, a
Search box at the top right, and a help topic display pane under the Search box. You
can move the horizontal divider to change the pane sizes (for example, to make the
Contents pane narrower, to allow more room for the help topic content). You can also
resize and reposition the Help Center window.
For Search, you can click the icon (binoculars) to see search options: case sensitive
(Match case) or case insensitive; and whether to match topics based on all specified
words, any specified words, or a Boolean expression.
The Keep on Top button toggles whether the Help Center window is kept on top of
the display when you switch focus (click) back in the SQL Developer window.
To print a help topic, display it in the topic display pane and click the Print icon at the
top of the pane.
To increase or decrease the size of the font in the help topic viewer, click the Change
Font Size (A) icon in the Help Center topic display area toolbar, then select Increase
Font Size of Decrease Font Size. This setting is preserved only for the duration of the
current help pane or window; therefore, you may want to keep the Help Center
window open after setting the help text font to your preferred size.
1.17 For More Information
For more information about SQL Developer and related topics, you may find the
following resources helpful:
SQL Developer home page (OTN), which includes links for downloads, white
papers, tutorials, viewlets (demonstrations), blogs, a discussion forum, and other
sources of information:
http://www.oracle.com/technology/products/database/sql_
developer/
PL/SQL page on OTN: http://www.oracle.com/technology/tech/pl_
sql/
Oracle Accessibility site: http://www.oracle.com/accessibility/
Oracle Corporate site: http://www.oracle.com/
For More Information
1-64 Oracle SQL Developer User's Guide
2
Migrating Third-Party Databases 2-1
2 Migrating Third-Party Databases
Note: The migration capabilities in SQL Developer represent an
evolution of the Oracle Migration Workbench product.
Migration is the process of copying the schema objects and data from a source
third-party (non-Oracle) database, such as MySQL, Microsoft SQL Server, Sybase
Adaptive Server, or Microsoft Access, to an Oracle database. You can perform the
migration in an efficient, largely automated way.
Thus, you have two options for working with third-party databases in SQL Developer:
Creating database connections so that you can view schema objects and data in
these databases
Migrating these databases to Oracle, to take advantage of the full range of Oracle
Database features and capabilities
This chapter contains the following major sections:
Section 2.1, "Migration Quick Start"
Section 2.2, "Overview of Migration"
Section 2.3, "Preparing a Migration Plan"
Section 2.4, "Before You Start Migrating: General Information"
Section 2.5, "Before You Start Migrating: Source-Specific Information"
Section 2.6, "Capturing the Source Database"
Section 2.7, "Creating and Customizing the Converted Model"
Section 2.8, "Generating the DDL for the Oracle Schema Objects"
Section 2.9, "Migrating the Data"
Section 2.10, "Making Queries Case Insensitive"
Section 2.11, "Testing the Oracle Database"
Section 2.12, "Deploying the Oracle Database"
Section 2.13, "Using Migration Reports"
Section 2.14, "SQL Developer User Interface for Migration"
Migration Quick Start
2-2 Oracle SQL Developer User's Guide
2.1 Migration Quick Start
To migrate a third-party database to Oracle, the basic actions are: prepare for the
migration, create or select associate a migration repository, capture the source
database, convert the captured database, generate and run DDL for the new Oracle
schema objects, and optionally move data from the source database to the new
database.
There are two mechanisms for migrating third-party databases to Oracle: standard
migration and quick migration.
2.1.1 Standard Migration
Standard migration involves capturing, converting, generating the database, and
performing the data move in several distinct steps. This is the recommended approach
when performing a migration. Any issues during these phases can be manually
resolved and all objects can be inspected or modified to suit your needs.
Standard Migration: Prepare for Migration
1. Prepare for the migration by reading the appropriate related topics in Chapter 2,
"Migrating Third-Party Databases".
2. Create a migration repository in a new or existing Oracle connection. You may
find it simple and convenient to create separate a Oracle database user and
connection for migration work. Then, select the connection and create the
repository. For example:
a. Create an Oracle user named MIGRATIONS with default tablespace USER
and temporary tablespace TEMP; and grant it at least RESOURCE, CREATE
SESSION, and CREATE VIEW privileges. (For multischema migrations, you
must grant the RESOURCE role with the ADMIN option; and you must also
grant this user the CREATE ROLE, CREATE USER, and ALTER ANY
TRIGGER privileges, all with the ADMIN option.)
b. Create a database connection named Migration_Repository that connects to
the MIGRATIONS user.
c. Right-click the Migration_Repository connection, and select Migration
Repository, then Associate Migration Repository to create the repository.
3. Create and open a database connection for the third-party database. (For
migrations other than from Microsoft Access, you should set the third party JDBC
driver preference before creating the connection.)
For example, create a database connection named Sales_Access to the Microsoft
Access database named sales.mdb, and connect to it.
Standard Migration: Capture Source Schema Objects
There are two ways to capture source schema objects: online and offline. Online
capture which is suitable in most cases, so it is described here.
To perform online capture, right-click the connection name in the Connections
navigator and select Capture database-type (for example, Capture MySQL, Capture
Microsoft Access, Capture Microsoft SQL Server, or Capture Sybase Adaptive Server).
Selecting Capture Microsoft Access automatically invokes the Microsoft Access
exporter tool to create XML files for migrating the schema and the table data.
However, if you want to run the exporter tool manually (for example, to control
certain options), click Migration, then Microsoft Access Exporter, then the item for
Migration Quick Start
Migrating Third-Party Databases 2-3
your version of Microsoft Access. Follow the steps for the exporter tool, which has its
own online help.
After the capture, the Captured Models navigator displays an expandable node for the
captured objects (for example, sales (Access) for the captured sales.mdb objects, as
shown in the figure in Section 2.14, "SQL Developer User Interface for Migration").
Standard Migration: Convert Captured Objects
To convert the captured objects to Oracle-format objects, right-click the appropriate
node in the Captured Objects navigator and select Convert to Oracle Model, and
accept the defaults for data mappings (or specify selected mappings if you need to).
After the conversion, the Converted Models navigator displays an expandable node
for the converted objects (for example, Converted sales (Access)).
Standard Migration: Generate Oracle Database Objects
1. Generate a SQL*Plus script that creates the DDL statements to create the Oracle
database objects that correspond to the source database objects: right-click the
appropriate node in the Captured Models navigator and select Generate. A SQL
Worksheet window opens containing the SQL*Plus statements.
2. In the SQL Worksheet window that was just opened, select (in the drop-down list
on the right) an Oracle database connection in which to run the script (next step).
3. Examine the generated SQL*Plus statements, and optionally make any changes.
For example, if the database user to own the generated objects already exists (as it
will if you are following these quick-step instructions), delete or modify the
CREATE USER and related statements.
4. Click the Run Script button in the SQL Worksheet window to run the script.
5. In the Connections navigator, create a connection to the user that was just created.
In the Connections navigator, you should now see the new database objects
corresponding to the objects in the third-party database that you migrated.
Standard Migration: Move Data to Oracle Database
If you want, you can migrate (move) any existing data from the source database to the
Oracle database. You have two options for data migration: online or offline.
Online data move: Click Migration, then Migrate Data. In the dialog box, specify
the Source Connection, the Target Connection, and the Converted Model. This
method uses JDBC and therefore is constrained by the third-party
implementations. This method is suitable for moving small data sets.
Offline data move: Click Migration, then Script Generation, then Generate Data
Move Scripts; specify the converted model and a directory into which to generate
the files that you will use for unloading the data from the source database and for
importing into Oracle using SQL*Loader. This method is designed for moving
large volumes of data.
2.1.2 Quick Migration
Quick migration is a simplified approach that uses a wizard. It provides a quick
solution when migrating a simple database; however, for more control of the
migration process, you should use Standard Migration.
Migration Quick Start
2-4 Oracle SQL Developer User's Guide
Quick Migration: Prepare for Migration
1. Prepare for the migration by reading the appropriate related topics in Chapter 2,
"Migrating Third-Party Databases".
2. Create a migration repository in a new or existing Oracle connection. You may
find it simple and convenient to create separate a Oracle database user and
connection for migration work. Then, select the connection and create the
repository. For example:
a. Create an Oracle user named MIGRATIONS with default tablespace USER
and temporary tablespace TEMP; and grant it at least RESOURCE, CREATE
SESSION, and CREATE VIEW privileges. (For multischema migrations, you
must grant the RESOURCE role with the ADMIN option; and you must also
grant this user the CREATE ROLE, CREATE USER, and ALTER ANY
TRIGGER privileges, all with the ADMIN option.)
b. Create a database connection named Migration_Repository that connects to
the MIGRATIONS user.
c. Right-click the Migration_Repository connection, and select Migration
Repository, then Associate Migration Repository to create the repository.
3. Create an Oracle user whose schema is to be used as the destination for the objects
to be migrated, or use an existing Oracle user and schema. Grant sufficient
privileges to this user.
For example, if you plan to migrate a Microsoft Access database named sales.mdb,
you might create an Oracle user named SALES, in whose schema the Oracle
database objects will be generated.
4. Create and open an Oracle connection for the schema that you created or selected
in the preceding step.
For example, create an Oracle connection named Sales_Oracle to the schema
associated with user SALES, and connect to it.
5. Create and open a database connection for the third-party database. (For
migrations other than from Microsoft Access, you should set the third party JDBC
driver preference before creating the connection.)
For example, create a database connection named Sales_Access to the Microsoft
Access database named sales.mdb, and connect to it.
Quick Migration: Migrate Using the Wizard
1. Click Migration, then Quick Migrate.
2. For Source Connection, select the connection for the third-party database to be
migrated. For example: Sales_Access
3. For Target Connection, select the connection for the Oracle Database schema to
which the third-party database is to be migrated. For example: Sales_Oracle
4. For Repository, use the selected existing repository; or if no repository exists,
allow SQL Developer to create a migration repository in the schema of the target
connection.
5. Click Verify to start the pre-migration check.
6. After the pre-migration check completes satisfactorily, specify the Migration Type:
Migrate Tables, Migrate Tables and Data, or Migrate Everything (all objects).
7. Click Finish in the Summary pane to perform the migration.
Overview of Migration
Migrating Third-Party Databases 2-5
The specific operations performed depend on the migration type and the type of
third-party database being migrated. For example, for a Microsoft Access
database, the Exporter for Microsoft Access tool is automatically invoked. Do not
interrupt any of the migration operations.
If any issues arise during the migration, the quick migration will stop. To proceed with
migration, follow the Standard Migration approach, which will help identify the issues
and allow you to modify the appropriate objects.
2.2 Overview of Migration
An Oracle database provides you with better scalability, reliability, increased
performance, and better security than third-party databases. For this reason,
organizations migrate from their current database, such as Microsoft SQL Server,
Sybase Adaptive Server, or Microsoft Access, to an Oracle database. Although
database migration can be complicated, SQL Developer enables you to simplify the
process of migrating a third-party database to an Oracle database.
SQL Developer captures information from the source database and displays it in the
captured model, which is a representation of the structure of the source database. This
representation is stored in a migration repository, which is a collection of schema
objects that SQL Developer uses to store migration information.
The information in the repository is used to generate the converted model, which is a
representation of the structure of the destination database as it will be implemented in
the Oracle database. You can then use the information in the captured model and the
converted model to compare database objects, identify conflicts with Oracle reserved
words, and manage the migration progress. When you are ready to migrate, you
generate the Oracle schema objects, and then migrate the data.
SQL Developer contains logic to extract data from the data dictionary of the source
database, create the captured model, and convert the captured model to the converted
model.
Using SQL Developer to migrate a third-party database to an Oracle database provides
the following benefits:
Reduces the effort and risks involved in a migration project
Enables you to migrate an entire third-party database, including triggers and
stored procedures
Enables you to see and compare the captured model and converted model and to
customize each if you wish, so that you can control how much automation there is
in the migration process
Provides feedback about the migration through reports
2.2.1 How Migration Works
The components of SQL Developer work together to migrate a third-party database to
an Oracle database. Figure 2–1, "SQL Developer Migration Architecture" shows how
SQL Developer reads the information from the source database and creates the Oracle
database schema objects. SQL Developer uses the information stored in the migration
repository to migrate to the Oracle schema. You can make changes to the captured
model or the converted model, or both, before migrating. The information in the
converted model is used to complete the migration, that is, to generate the database
objects in the destination Oracle schema.
Preparing a Migration Plan
2-6 Oracle SQL Developer User's Guide
Figure 2–1 SQL Developer Migration Architecture
Source
Database
SQL Developer Captured
Model
Converted
Model
Migration
Repository
Destination
Oracle
Schema
2.2.2 Migration Implemented as SQL Developer Extensions
Migration support is implemented in SQL Developer as a set of extensions. If you
want, you can disable migration support or support for migrating individual
third-party databases.
To view the installed extensions, and to enable or disable individual extensions, click
Tools, then Preferences, then Extensions. Note that SQL Developer ships which all
extensions and third-party database "plugins" available at the time of release, so to
begin migrations other than for Microsoft Access, only the third-party drivers need be
installed.
2.3 Preparing a Migration Plan
This topic describes the process of how to create a migration project plan. It identifies
the sections to include in the migration plan, describes how to determine what to
include for each section, and explains how to avoid the risks involved in a migration
project. This information includes:
Task 1: Determining the Requirements of the Migration Project
Task 2: Estimating Workload
Task 3: Analyzing Operational Requirements
Task 4: Analyzing the Application
Task 5: Planning the Migration Project
2.3.1 Task 1: Determining the Requirements of the Migration Project
In this task, you identify which databases you want to migrate and applications that
access that database. You also evaluate the business requirements and define testing
criteria.
To determine the requirements of the migration project:
1. Define the scope of the project.
Preparing a Migration Plan
Migrating Third-Party Databases 2-7
There are several choices you must make about the third-party database and
applications that access that database in order to define the scope of the migration
project. To obtain a list of migration issues and dependencies, you should consider
the following
What third-party databases are you migrating?
What is the version of the third-party database?
What is the character set of the third-party database?
What source applications are affected by migrating the third-party database to
an Oracle database?
What is the third-party application language?
What version of the application language are you using?
In the scope of the project, you should have identified the applications you
must migrate. Ensure that you have included all the necessary applications
that are affected by migrating the database
What types of connectivity issues are involved in migrating to an Oracle
database?
Do you use connectivity software to connect the applications to the
third-party database? Do you need to modify the connectivity software to
connect the applications to the Oracle database?
What version of the connectivity software do you use? Can you use this
same version to connect to the Oracle database?
Are you planning to rewrite the applications or modify the applications to
work with an Oracle database?
2. Use Table 2–1 to determine whether you have a complex or simple source database
environment. Identify the requirements based on the specific scenario.
If the migration project is a simple scenario, you may not have to complete all of
the tasks listed in this guide. You make decisions based on your specific
environment. For example, if you have a complex scenario, you may require extra
testing based on the complexity of the application accessing the database.
Table 2–1 Complex and Simple Scenarios
Complex Scenario Simple Scenario
More than one of the following:
Large database (greater than 25 GB)
Data warehouse
Large applications (greater than 100
forms, reports, and batch jobs)
Database is used by multiple lines of
business
Distributed deployment
Large user base (greater than 100)
High availability requirement (such as
a 24 X 7 X 365 environment)
Contains the following:
Small database (less than 25 GB)
Simple online transaction processing
(OLTP)
Small application (less than 100 forms,
reports, and batch jobs)
Database is used by one
department
Centralized deployment
Small user base (less than 100)
Average availability (business hours)
3. Determine whether the destination database requires additional hardware and
rewriting of backup schedules.
Preparing a Migration Plan
2-8 Oracle SQL Developer User's Guide
4. Define testing and acceptance criteria.
Define tests to measure the accuracy of the migration. You then use the acceptance
criteria to determine whether the migration was successful. The tests that you
develop from the requirements should also measure stability, evaluate
performance, and test the applications. You must decide how much testing is
necessary before you can deploy the Oracle database and applications into a
production environment.
5. Create a requirements document with a list of requirements for the migration
project.
The requirements document should have clearly defined tasks and number each
specific requirement, breaking these into sub-requirements where necessary.
2.3.2 Task 2: Estimating Workload
In this task, you use SQL Developer to make calculated decisions on the amount of
work that can be automated and how much is manual.
To estimate the workload:
1. Capture the captured model, create the converted model, and migrate to the
destination database.
You can analyze the source database through the captured model and a preview of
the destination database through the converted model. After you have captured
the source database, analyze the captured data contained in the captured model
and the converted model. Ensure the content and structure of the migration
repository is correct and determine how much time the entire process takes.
2. Use the Migration Log pane to evaluate the capture and migration process,
categorize the total number of database objects, and identify the number of objects
that can be converted and migrated automatically.
The migration log provides information about the actions that have occurred and
record any warnings and errors. They identify the changes that have been made to
the converted model so that you can evaluate if you should make changes to the
applications that access the destination database.
3. Evaluate and categorize the issues that occurred. The migration log can help by
providing information about:
Tables that did not load when you captured the source database
Stored procedures, views, and triggers that did not parse when you created
the converted model
Syntax that requires manual intervention
Database objects that were not created successfully when you migrated the
destination database
Data that did not migrate successfully when you migrated the destination
database
4. For each error or warning in the migration log, evaluate the following:
Number of times an issue occurred
T