PL/SQL Developer 10.0
User’s Guide
February 2013
PL/SQL Developer 10.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 .................................................................................................. 14
2.5 UNINSTALLING PL/SQL DEVELOPER ................................................................................ 14
3. LOGGING ON TO AN ORACLE DATABASE .................................................................... 15
4. WRITING PROGRAMS ......................................................................................................... 16
4.1 CREATING A PROGRAM ...................................................................................................... 16
4.2 SAVING A PROGRAM .......................................................................................................... 17
4.3 MODIFYING A PROGRAM ................................................................................................... 18
4.4 COMPILING A PROGRAM .................................................................................................... 18
4.5 COMPARING A PROGRAM ................................................................................................... 19
4.6 COMPILER PREFERENCES ................................................................................................... 20
5. TESTING PROGRAMS .......................................................................................................... 21
5.1 CREATING A TEST SCRIPT ................................................................................................. 21
5.2 EXECUTING A TEST SCRIPT ............................................................................................... 22
5.3 VARIABLE TYPES ............................................................................................................... 23
5.4 SAVING TEST SCRIPTS ....................................................................................................... 23
5.5 TRACKING RUN-TIME ERRORS ........................................................................................... 25
5.6 PACKAGE STATES & JAVA SESSION STATES ....................................................................... 25
5.7 VIEWING RESULT SETS ...................................................................................................... 25
5.8 VIEWING DBMS_OUTPUT ................................................................................................... 25
5.9 VIEWING HTP OUTPUT ...................................................................................................... 26
5.10 DEBUGGING ...................................................................................................................... 26
5.11 TRACING EXECUTION ........................................................................................................ 30
5.12 REGRESSION TESTING ........................................................................................................ 31
6. OPTIMIZING .......................................................................................................................... 32
6.1 USING THE EXPLAIN PLAN WINDOW ................................................................................. 32
6.2 AUTOMATIC STATISTICS .................................................................................................... 33
6.3 THE PL/SQL PROFILER ..................................................................................................... 34
6.4 SQL TRACE ....................................................................................................................... 35
7. AD HOC SQL ........................................................................................................................... 37
7.1 USING THE SQL WINDOW ................................................................................................. 37
7.2 RESULT GRID MANIPULATION............................................................................................ 39
7.3 QUERY BY EXAMPLE MODE .............................................................................................. 43
7.4 LINKED QUERIES ............................................................................................................... 44
7.5 SUBSTITUTION VARIABLES ................................................................................................ 45
7.6 UPDATING THE DATABASE ................................................................................................ 45
7.7 VIEWING AND EDITING XMLTYPE COLUMNS .................................................................. 46
7.8 DIRECT QUERY EXPORT .................................................................................................... 46
7.9 SAVING SQL SCRIPTS ....................................................................................................... 47
7.10 CREATING STANDARD QUERIES ........................................................................................ 47
4 PL/SQL Developer 10.0 User’s Guide
8. THE COMMAND WINDOW ................................................................................................. 48
8.1 ENTERING SQL STATEMENTS AND COMMANDS ................................................................ 48
8.2 DEVELOPING COMMAND FILES .......................................................................................... 49
8.3 SUPPORTED COMMANDS.................................................................................................... 50
9. CREATING AND MODIFYING NON-PL/SQL OBJECTS ................................................ 53
9.1 THE TABLE DEFINITION EDITOR ......................................................................................... 53
9.2 THE SEQUENCE DEFINITION EDITOR .................................................................................. 67
9.3 THE SYNONYM DEFINITION EDITOR ................................................................................... 67
9.4 THE LIBRARY DEFINITION EDITOR ..................................................................................... 68
9.5 THE DIRECTORY DEFINITION EDITOR ................................................................................. 68
9.6 THE JOB DEFINITION EDITOR ............................................................................................. 69
9.7 THE QUEUE DEFINITION EDITOR ........................................................................................ 69
9.8 THE QUEUE TABLE DEFINITION EDITOR ............................................................................. 70
9.9 THE USER DEFINITION EDITOR ........................................................................................... 71
9.10 THE ROLE DEFINITION EDITOR ........................................................................................... 74
9.11 THE PROFILE DEFINITION EDITOR ...................................................................................... 75
9.12 THE DATABASE LINK DEFINITION EDITOR .......................................................................... 75
10. DBMS SCHEDULER .............................................................................................................. 76
10.1 CREATING OBJECTS ........................................................................................................... 82
10.2 EDITING OBJECTS .............................................................................................................. 82
10.3 DELETING OBJECTS ........................................................................................................... 82
10.4 VIEWING JOB RUNS ........................................................................................................... 82
10.5 VIEWING RUNNING JOBS.................................................................................................... 83
11. DIAGRAMS ............................................................................................................................. 84
11.1 CREATING A DIAGRAM ...................................................................................................... 84
11.2 ZOOMING .......................................................................................................................... 88
11.3 SAVING AND OPENING A DIAGRAM FILE ............................................................................ 88
11.4 UPDATING A DIAGRAM ...................................................................................................... 88
11.5 PRINTING A DIAGRAM ....................................................................................................... 88
11.6 EXPORTING A DIAGRAM .................................................................................................... 88
12. REPORTS ................................................................................................................................ 89
12.1 STANDARD REPORTS ......................................................................................................... 89
12.2 CUSTOM REPORTS ............................................................................................................. 90
12.3 VARIABLES ....................................................................................................................... 91
12.4 REFINING THE LAYOUT...................................................................................................... 95
12.5 THE STYLE LIBRARY ....................................................................................................... 102
12.6 OPTIONS .......................................................................................................................... 102
12.7 THE REPORTS MENU ........................................................................................................ 104
13. GRAPHICS ............................................................................................................................ 105
14. PROJECTS ............................................................................................................................ 107
14.1 CREATING A NEW PROJECT .............................................................................................. 107
14.2 SAVING A PROJECT .......................................................................................................... 108
14.3 ADDING FILES TO A PROJECT ........................................................................................... 108
14.4 ADDING DATABASE OBJECTS TO A PROJECT .................................................................... 108
14.5 WORKING WITH PROJECT ITEMS ...................................................................................... 109
14.6 COMPILING A PROJECT .................................................................................................... 110
15. TO-DO ITEMS ...................................................................................................................... 111
15.1 CREATING A TO-DO ITEM ............................................................................................... 112
15.2 EDITING A TO-DO ITEM................................................................................................... 112
15.3 CLOSING A TO-DO ITEM .................................................................................................. 113
PL/SQL Developer 10.0 User’s Guide 5
15.4 DELETING A TO-DO ITEM ................................................................................................ 113
16. THE CONNECTION LIST ................................................................................................... 114
16.1 USING THE CONNECTION LIST ......................................................................................... 114
16.2 DEFINING FOLDERS AND CONNECTIONS .......................................................................... 116
17. CONNECTIONS, WINDOWS, SESSIONS AND TRANSACTIONS ............................... 119
17.1 THE MAIN CONNECTION .................................................................................................. 119
17.2 WINDOW CONNECTIONS .................................................................................................. 119
17.3 SESSION MODE ................................................................................................................ 120
17.4 EXECUTION IN MULTI SESSION OR DUAL SESSION MODE ................................................ 120
18. BROWSING OBJECTS ........................................................................................................ 121
18.1 USING THE OBJECT BROWSER ......................................................................................... 121
18.2 OBJECT BROWSER FILTERS ............................................................................................. 127
18.3 OBJECT BROWSER FOLDERS ............................................................................................ 128
19. BROWSING FILES ............................................................................................................... 130
19.1 SETTING UP THE FILE BROWSER ...................................................................................... 131
19.2 USING THE FILE BROWSER .............................................................................................. 132
20. PREFERENCES .................................................................................................................... 133
20.1 ORACLE – CONNECTION .................................................................................................. 134
20.2 ORACLE – OPTIONS ......................................................................................................... 135
20.3 ORACLE – COMPILER ...................................................................................................... 137
20.4 ORACLE – DEBUGGER ..................................................................................................... 138
20.5 ORACLE – OUTPUT .......................................................................................................... 139
20.6 ORACLE – TRACE ............................................................................................................ 140
20.7 ORACLE – PROFILER ........................................................................................................ 140
20.8 ORACLE – LOGON HISTORY ............................................................................................ 142
20.9 ORACLE – HINTS ............................................................................................................. 143
20.10 USER INTERFACE – OPTIONS ........................................................................................... 145
20.11 USER INTERFACE – OBJECT BROWSER ............................................................................ 146
20.12 USER INTERFACE – EDITOR ............................................................................................. 148
20.13 USER INTERFACE – FONTS ............................................................................................... 152
20.14 USER INTERFACE – CODE ASSISTANT ............................................................................. 153
20.15 USER INTERFACE – KEY CONFIGURATION....................................................................... 154
20.16 USER INTERFACE – APPEARANCE .................................................................................... 155
20.17 USER INTERFACE – NLS OPTIONS ................................................................................... 156
20.18 WINDOW TYPES – PROGRAM WINDOW ........................................................................... 158
20.19 WINDOW TYPES – SQL WINDOW .................................................................................... 160
20.20 WINDOW TYPES – TEST WINDOW ................................................................................... 162
20.21 WINDOW TYPES – PLAN WINDOW .................................................................................. 163
20.22 TOOLS – DIFFERENCES .................................................................................................... 163
20.23 TOOLS – DATA GENERATOR ............................................................................................ 164
20.24 TOOLS – TO-DO LIST....................................................................................................... 165
20.25 TOOLS – RECALL STATEMENT ......................................................................................... 165
20.26 FILES – DIRECTORIES ...................................................................................................... 166
20.27 FILES – EXTENSIONS ....................................................................................................... 168
20.28 FILES – FORMAT .............................................................................................................. 169
20.29 FILES – BACKUP .............................................................................................................. 170
20.30 FILES – HTML/XML....................................................................................................... 171
20.31 OTHER – PRINTING .......................................................................................................... 172
20.32 OTHER – UPDATES & NEWS ............................................................................................ 173
20.33 PREFERENCE SETS ........................................................................................................... 174