PL/SQL Developer 8.0
User’s Guide
July 2009
PL/SQL Developer 8.0 User’s Guide 3
Contents
CONTENTS ................................................................................................................................................ 3
1. INTRODUCTION ......................................................................................................................... 9
2. INSTALLATION ......................................................................................................................... 13
2.1 SYSTEM REQUIREMENTS ....................................................................................................... 13
2.2 WORKSTATION INSTALLATION ............................................................................................. 13
2.3 SERVER BASED INSTALLATION ............................................................................................. 13
2.4 SCRIPTED INSTALLATION ...................................................................................................... 13
2.5 UNINSTALLING PL/SQL DEVELOPER ................................................................................... 14
3. WRITING PROGRAMS ............................................................................................................ 15
3.1 CREATING A PROGRAM ......................................................................................................... 15
3.2 SAVING A PROGRAM ............................................................................................................. 16
3.3 MODIFYING A PROGRAM ....................................................................................................... 17
3.4 COMPILING A PROGRAM ....................................................................................................... 17
3.5 COMPILER PREFERENCES ...................................................................................................... 18
4. TESTING PROGRAMS ............................................................................................................. 19
4.1 CREATING A TEST SCRIPT ..................................................................................................... 19
4.2 EXECUTING A TEST SCRIPT .................................................................................................. 20
4.3 VARIABLE TYPES .................................................................................................................. 21
4.4 SAVING TEST SCRIPTS .......................................................................................................... 22
4.5 TRACKING RUN-TIME ERRORS .............................................................................................. 23
4.6 PACKAGE STATES & JAVA SESSION STATES ......................................................................... 23
4.7 VIEWING RESULT SETS .......................................................................................................... 23
4.8 VIEWING DBMS_OUTPUT ...................................................................................................... 23
4.9 VIEWING HTP OUTPUT ......................................................................................................... 24
4.10 DEBUGGING .......................................................................................................................... 24
4.11 TRACING EXECUTION ............................................................................................................ 27
4.12 REGRESSION TESTING ........................................................................................................... 28
5. OPTIMIZING .............................................................................................................................. 29
5.1 USING THE EXPLAIN PLAN WINDOW .................................................................................... 29
5.2 AUTOMATIC STATISTICS ....................................................................................................... 30
5.3 THE PL/SQL PROFILER......................................................................................................... 31
5.4 SQL TRACE ........................................................................................................................... 32
6. AD HOC SQL ............................................................................................................................... 34
6.1 USING THE SQL WINDOW .................................................................................................... 34
6.2 RESULT GRID MANIPULATION ............................................................................................... 3 5
6.3 QUERY BY EXAMPLE MODE ................................................................................................. 40
6.4 LINKED QUERIES .................................................................................................................. 41
6.5 SUBSTITUTION VARIABLES ................................................................................................... 41
6.6 UPDATING THE DATABASE .................................................................................................... 42
6.7 VIEWING AND EDITING XMLTYPE COLUMNS ..................................................................... 43
6.8 DIRECT QUERY EXPORT ....................................................................................................... 43
6.9 SAVING SQL SCRIPTS ........................................................................................................... 43
6.10 CREATING STANDARD QUERIES ........................................................................................... 44
7. THE COMMAND WINDOW .................................................................................................... 45
7.1 ENTERING SQL STATEMENTS AND COMMANDS ................................................................... 45
7.2 DEVELOPING COMMAND FILES ............................................................................................. 46
7.3 SUPPORTED COMMANDS ....................................................................................................... 47
4 PL/SQL Developer 8.0 User’s Guide
8. CREATING AND MODIFYING NON-PL/SQL OBJECTS .................................................. 50
8.1 THE TABLE DEFINITION EDITOR ............................................................................................ 50
8.2 THE SEQUENCE DEFINITION EDITOR ...................................................................................... 62
8.3 THE SYNONYM DEFINITION EDITOR ...................................................................................... 62
8.4 THE LIBRARY DEFINITION EDITOR ........................................................................................ 63
8.5 THE DIRECTORY DEFINITION EDITOR .................................................................................... 63
8.6 THE JOB DEFINITION EDITOR ................................................................................................. 64
8.7 THE QUEUE DEFINITION EDITOR ............................................................................................ 64
8.8 THE QUEUE TABLE DEFINITION EDITOR ................................................................................ 65
8.9 THE USER DEFINITION EDITOR .............................................................................................. 66
8.10 THE ROLE DEFINITION EDITOR .............................................................................................. 69
8.11 THE PROFILE DEFINITION EDITOR ......................................................................................... 70
8.12 THE DATABASE LINK DEFINITION EDITOR ............................................................................. 70
9. DBMS SCHEDULER .................................................................................................................. 7 1
9.1 CREATING OBJECTS ............................................................................................................... 76
9.2 EDITING OBJECTS .................................................................................................................. 76
9.3 DELETING OBJECTS ............................................................................................................... 76
9.4 VIEWING JOB RUNS................................................................................................................ 76
9.5 VIEWING RUNNING JOBS ....................................................................................................... 77
10. DIAGRAMS ...................................................................................................................... ............ 78
10.1 CREATING A DIAGRAM .......................................................................................................... 78
10.2 ZOOMING ............................................................................................................................... 81
10.3 SAVING AND OPENING A DIAGRAM FILE ............................................................................... 81
10.4 UPDATING A DIAGRAM .......................................................................................................... 81
10.5 PRINTING A DIAGRAM ........................................................................................................... 82
10.6 EXPORTING A DIAGRAM ........................................................................................................ 82
11. REPORTS ..................................................................................................................................... 83
11.1 STANDARD REPORTS ............................................................................................................. 83
11.2 CUSTOM REPORTS ................................................................................................................. 84
11.3 VARIABLES ............................................................................................................................ 85
11.4 REFINING THE LAYOUT ......................................................................................................... 88
11.5 THE STYLE LIBRARY ............................................................................................................. 95
11.6 OPTIONS ................................................................................................................................ 95
11.7 THE REPORTS MENU .............................................................................................................. 97
12. GRAPHICS ...................................................................................................................... ............. 98
13. PROJECTS .................................................................................................................................100
13.1 CREATING A NEW PROJECT .................................................................................................100
13.2 SAVING A PROJECT ..............................................................................................................101
13.3 ADDING FILES TO A PROJECT ...............................................................................................101
13.4 ADDING DATABASE OBJECTS TO A PROJECT .......................................................................101
13.5 WORKING WITH PROJECT ITEMS .........................................................................................102
13.6 COMPILING A PROJECT ........................................................................................................103
14. TO-DO ITEMS ...........................................................................................................................104
14.1 CREATING A TO-DO ITEM ...................................................................................................105
14.2 EDITING A TO-DO ITEM ......................................................................................................105
14.3 CLOSING A TO-DO ITEM .....................................................................................................106
14.4 DELETING A TO-DO ITEM ...................................................................................................106
15. WINDOWS, DATABASE SESSIONS AND TRANSACTIONS ..........................................107
15.1 SESSION MODE ....................................................................................................................107
15.2 EXECUTION IN MULTI SESSION OR DUAL SESSION MODE ...................................................107
PL/SQL Developer 8.0 User’s Guide 5
16. BROWSING OBJ ECTS ............................................................................................................ 108
16.1 USING THE OBJECT BROWSER ............................................................................................ 108
16.2 OBJECT BROWSER FILTERS ................................................................................................ 114
16.3 OBJECT BROWSER FOLDERS ............................................................................................... 115
17. BROWSING FILES .................................................................................................................. 117
17.1 SETTING UP THE FILE BROWSER ......................................................................................... 118
17.2 USING THE FILE BROWSER ................................................................................................. 119
18. PREFERENCES ........................................................................................................................ 120
18.1 ORACLE – CONNECTION ..................................................................................................... 121
18.2 ORACLE – OPTIONS ............................................................................................................. 122
18.3 ORACLE – COMPILER .......................................................................................................... 123
18.4 ORACLE – DEBUGGER ......................................................................................................... 125
18.5 ORACLE – OUTPUT .............................................................................................................. 126
18.6 ORACLE – TRACE ................................................................................................................ 127
18.7 ORACLE – PROFILER ........................................................................................................... 127
18.8 ORACLE – LOGON HISTORY................................................................................................ 128
18.9 ORACLE – HINTS ................................................................................................................. 129
18.10 USER INTERFACE – OPTIONS .............................................................................................. 131
18.11 USER INTERFACE – TOOLBAR ............................................................................................. 133
18.12 USER INTERFACE – OBJECT BROWSER ............................................................................... 133
18.13 USER INTERFACE – EDITOR ................................................................................................ 135
18.14 USER INTERFACE – FONTS .................................................................................................. 138
18.15 USER INTERFACE – CODE ASSISTANT ................................................................................ 139
18.16 USER INTERFACE – KEY CONFIGURATION ......................................................................... 140
18.17 USER INTERFACE – APPEARANCE ....................................................................................... 1 41
18.18 USER INTERFACE – NLS OPTIONS ...................................................................................... 142
18.19 WINDOW TYPES – PROGRAM WINDOW .............................................................................. 14 3
18.20 WINDOW TYPES – SQL WINDOW ....................................................................................... 145
18.21 WINDOW TYPES – TEST WINDOW ...................................................................................... 147
18.22 WINDOW TYPES – PLAN WINDOW ..................................................................................... 147
18.23 TOOLS – DIFFERENCES ....................................................................................................... 148
18.24 TOOLS – DATA GENERATOR ............................................................................................... 148
18.25 TOOLS – TO-DO LIST .......................................................................................................... 149
18.26 TOOLS – RECALL STATEMENT ............................................................................................ 149
18.27 FILES – DIRECTORIES .......................................................................................................... 150
18.28 FILES – EXTENSIONS ........................................................................................................... 151
18.29 FILES – FORMAT ................................................................................................................. 152
18.30 FILES – BACKUP .................................................................................................................. 152
18.31 FILES – HTML/XML .......................................................................................................... 153
18.32 OTHER – PRINTING.............................................................................................................. 154
18.33 OTHER – UPDATES & NEWS ............................................................................................... 155
18.34 PREFERENCE SETS ............................................................................................................... 156
19. TOOLS ........................................................................................................................................ 158
19.1 OBJECT BROWSER ............................................................................................................... 158
19.2 FIND DATABASE OBJECTS .................................................................................................. 158
19.3 COMPILE INVALID OBJECTS ............................................................................................... 159
19.4 EXPORT TABLES ................................................................................................................. 160
19.5 IMPORT TABLES .................................................................................................................. 163
19.6 EXPORT USER OBJECTS ...................................................................................................... 165
19.7 TEXT IMPORTER .................................................................................................................. 166
19.8 ODBC IMPORTER ............................................................................................................... 169
