PL/SQL Developer 12.0 User’s Guide - ComponentSource

PL/SQL Developer 12.0 User's Guide

November 2016

PL/SQL Developer 12.0 User's Guide

3

Contents

CONTENTS ........................................................................................................................................... 3

1. INTRODUCTION...................................................................................................................... 9

2. INSTALLATION ..................................................................................................................... 12

2.1

SYSTEM REQUIREMENTS.................................................................................................... 12

2.2

INTERACTIVE INSTALLATION............................................................................................. 12

2.3

SILENT INSTALLATION....................................................................................................... 12

2.4

UNINSTALLING PL/SQL DEVELOPER ................................................................................ 12

3. LOGGING ON TO AN ORACLE DATABASE.................................................................... 13

4. WRITING PROGRAMS......................................................................................................... 14

4.1

CREATING A PROGRAM...................................................................................................... 14

4.2

SAVING A PROGRAM.......................................................................................................... 15

4.3

MODIFYING A PROGRAM ................................................................................................... 16

4.4

COMPILING A PROGRAM .................................................................................................... 16

4.5

COMPARING A PROGRAM................................................................................................... 17

4.6

COMPILER PREFERENCES................................................................................................... 18

5. TESTING PROGRAMS.......................................................................................................... 19

5.1

CREATING A TEST SCRIPT ................................................................................................. 19

5.2

EXECUTING A TEST SCRIPT ............................................................................................... 20

5.3

VARIABLE TYPES............................................................................................................... 21

5.4

SAVING TEST SCRIPTS....................................................................................................... 22

5.5

TRACKING RUN-TIME ERRORS ........................................................................................... 23

5.6

PACKAGE STATES & JAVA SESSION STATES....................................................................... 23

5.7

VIEWING RESULT SETS ...................................................................................................... 23

5.8

VIEWING DBMS_OUTPUT ................................................................................................... 23

5.9

VIEWING HTP OUTPUT ...................................................................................................... 24

5.10

DEBUGGING ...................................................................................................................... 24

5.11

TRACING EXECUTION ........................................................................................................ 29

5.12

REGRESSION TESTING........................................................................................................ 30

5.13

CREATING STANDARD TESTS ............................................................................................ 30

6. OPTIMIZING .......................................................................................................................... 31

6.1

USING THE EXPLAIN PLAN WINDOW................................................................................. 31

6.2

AUTOMATIC STATISTICS.................................................................................................... 33

6.3

THE PL/SQL PROFILER ..................................................................................................... 34

6.4

SQL TRACE....................................................................................................................... 34

7. AD HOC SQL........................................................................................................................... 36

7.1

USING THE SQL WINDOW ................................................................................................. 36

7.2

RESULT GRID MANIPULATION............................................................................................ 38

7.3

QUERY BY EXAMPLE MODE .............................................................................................. 43

7.4

LINKED QUERIES............................................................................................................... 44

7.5

SUBSTITUTION VARIABLES ................................................................................................ 45

7.6

UPDATING THE DATABASE ................................................................................................ 46

7.7

VIEWING AND EDITING XMLTYPE COLUMNS .................................................................. 47

7.8

COMPARING RECORDS....................................................................................................... 47

7.9

DIRECT QUERY EXPORT .................................................................................................... 48

7.10

COMMENT DIRECTIVES...................................................................................................... 48

7.11

SAVING SQL SCRIPTS ....................................................................................................... 49

7.12

CREATING STANDARD QUERIES ........................................................................................ 49

4

PL/SQL Developer 12.0 User's Guide

8. THE COMMAND WINDOW................................................................................................. 51

8.1

ENTERING SQL STATEMENTS AND COMMANDS ................................................................ 51

8.2

DEVELOPING COMMAND FILES .......................................................................................... 52

8.3

SUPPORTED COMMANDS.................................................................................................... 52

9. CREATING AND MODIFYING NON-PL/SQL OBJECTS................................................ 56

9.1

THE TABLE DEFINITION EDITOR......................................................................................... 56

9.2

THE SEQUENCE DEFINITION EDITOR .................................................................................. 69

9.3

THE SYNONYM DEFINITION EDITOR ................................................................................... 69

9.4

THE LIBRARY DEFINITION EDITOR ..................................................................................... 70

9.5

THE DIRECTORY DEFINITION EDITOR................................................................................. 70

9.6

THE JOB DEFINITION EDITOR ............................................................................................. 71

9.7

THE QUEUE DEFINITION EDITOR ........................................................................................ 71

9.8

THE QUEUE TABLE DEFINITION EDITOR ............................................................................. 72

9.9

THE USER DEFINITION EDITOR ........................................................................................... 73

9.10

THE ROLE DEFINITION EDITOR........................................................................................... 76

9.11

THE PROFILE DEFINITION EDITOR ...................................................................................... 76

9.12

THE DATABASE LINK DEFINITION EDITOR.......................................................................... 77

10. DBMS SCHEDULER .............................................................................................................. 78

10.1

CREATING OBJECTS ........................................................................................................... 85

10.2

EDITING OBJECTS .............................................................................................................. 85

10.3

DELETING OBJECTS ........................................................................................................... 85

10.4

VIEWING JOB RUNS ........................................................................................................... 85

10.5

VIEWING RUNNING JOBS.................................................................................................... 86

11. DIAGRAMS ............................................................................................................................. 87

11.1

CREATING A DIAGRAM ...................................................................................................... 87

11.2 ZOOMING .......................................................................................................................... 90

11.3

SAVING AND OPENING A DIAGRAM FILE ............................................................................ 90

11.4

UPDATING A DIAGRAM ...................................................................................................... 90

11.5

PRINTING A DIAGRAM ....................................................................................................... 91

11.6

EXPORTING A DIAGRAM .................................................................................................... 91

12. REPORTS ................................................................................................................................ 92

12.1

STANDARD REPORTS ......................................................................................................... 92

12.2

CUSTOM REPORTS ............................................................................................................. 93

12.3 VARIABLES ....................................................................................................................... 93

12.4

REFINING THE LAYOUT...................................................................................................... 98

12.5

THE STYLE LIBRARY....................................................................................................... 104

12.6 OPTIONS.......................................................................................................................... 105

12.7

THE REPORTS MENU........................................................................................................ 106

13. GRAPHICS ............................................................................................................................ 108

14. PROJECTS ............................................................................................................................ 110

14.1

CREATING A NEW PROJECT.............................................................................................. 110

14.2

SAVING A PROJECT .......................................................................................................... 111

14.3

ADDING FILES TO A PROJECT ........................................................................................... 111

14.4

ADDING DATABASE OBJECTS TO A PROJECT .................................................................... 111

14.5

WORKING WITH PROJECT ITEMS ...................................................................................... 112

14.6

COMPILING A PROJECT .................................................................................................... 113

15. TO-DO ITEMS ...................................................................................................................... 114

15.1

CREATING A TO-DO ITEM ............................................................................................... 115

15.2

EDITING A TO-DO ITEM................................................................................................... 115

15.3

CLOSING A TO-DO ITEM.................................................................................................. 115

PL/SQL Developer 12.0 User's Guide

5

15.4

DELETING A TO-DO ITEM................................................................................................ 115

16. THE CONNECTION LIST................................................................................................... 116

16.1

USING THE CONNECTION LIST......................................................................................... 116

16.2

DEFINING FOLDERS AND CONNECTIONS .......................................................................... 119

17. CONNECTIONS, WINDOWS, SESSIONS AND TRANSACTIONS ............................... 122

17.1

THE MAIN CONNECTION .................................................................................................. 122

17.2

WINDOW CONNECTIONS .................................................................................................. 122

17.3

SESSION MODE ................................................................................................................ 123

17.4

EXECUTION IN MULTI SESSION OR DUAL SESSION MODE................................................. 123

18. BROWSING OBJECTS ........................................................................................................ 124

18.1

USING THE OBJECT BROWSER ......................................................................................... 124

18.2

OBJECT BROWSER FILTERS ............................................................................................. 131

18.3

OBJECT BROWSER FOLDERS............................................................................................ 132

19. BROWSING FILES............................................................................................................... 134

19.1

SETTING UP THE FILE BROWSER...................................................................................... 135

19.2

USING THE FILE BROWSER .............................................................................................. 136

20. PREFERENCES .................................................................................................................... 137

20.1 20.2 20.3 20.4 20.5 20.6 20.7 20.8 20.9 20.10 20.11 20.12 20.13 20.14 20.15 20.16 20.17 20.18 20.19 20.20 20.21 20.22 20.23 20.24 20.25 20.26 20.27 20.28 20.29 20.30 20.31 20.32 20.33

ORACLE ? CONNECTION .................................................................................................. 138 ORACLE ? OPTIONS ......................................................................................................... 139 ORACLE ? COMPILER ...................................................................................................... 141 ORACLE ? DEBUGGER ..................................................................................................... 143 ORACLE ? OUTPUT .......................................................................................................... 144 ORACLE ? TRACE ............................................................................................................ 145 ORACLE ? PROFILER........................................................................................................ 145 ORACLE ? LOGON HISTORY ............................................................................................ 147 ORACLE ? HINTS ............................................................................................................. 148 USER INTERFACE ? OPTIONS ........................................................................................... 150 USER INTERFACE ? OBJECT BROWSER ............................................................................ 152 USER INTERFACE ? EDITOR ............................................................................................. 153 USER INTERFACE ? FONTS............................................................................................... 157 USER INTERFACE ? CODE ASSISTANT ............................................................................. 158 USER INTERFACE ? KEY CONFIGURATION....................................................................... 159 USER INTERFACE ? APPEARANCE.................................................................................... 160 USER INTERFACE ? NLS OPTIONS ................................................................................... 162 WINDOW TYPES ? PROGRAM WINDOW ........................................................................... 163 WINDOW TYPES ? SQL WINDOW.................................................................................... 165 WINDOW TYPES ? TEST WINDOW ................................................................................... 168 WINDOW TYPES ? PLAN WINDOW .................................................................................. 168 TOOLS ? DIFFERENCES .................................................................................................... 169 TOOLS ? DATA GENERATOR............................................................................................ 169 TOOLS ? TO-DO LIST....................................................................................................... 170 TOOLS ? RECALL STATEMENT......................................................................................... 170 FILES ? DIRECTORIES ...................................................................................................... 171 FILES ? EXTENSIONS ....................................................................................................... 173 FILES ? FORMAT.............................................................................................................. 174 FILES ? BACKUP .............................................................................................................. 175 FILES ? HTML/XML....................................................................................................... 176 OTHER ? PRINTING .......................................................................................................... 177 OTHER ? UPDATES & NEWS ............................................................................................ 178 PREFERENCE SETS ........................................................................................................... 179

6

PL/SQL Developer 12.0 User's Guide

21. TOOLS ................................................................................................................................... 181

21.1 21.2 21.3 21.4 21.5 21.6 21.7 21.8 21.9 21.10 21.11 21.12 21.13 21.14 21.15

OBJECT BROWSER ........................................................................................................... 181 FIND DATABASE OBJECTS............................................................................................... 181 COMPILE INVALID OBJECTS ............................................................................................ 182 EXPORT TABLES.............................................................................................................. 183 IMPORT TABLES .............................................................................................................. 186 EXPORT USER OBJECTS................................................................................................... 188 TEXT IMPORTER .............................................................................................................. 189 ODBC IMPORTER............................................................................................................ 192 DATA GENERATOR.......................................................................................................... 194 COMPARE USER OBJECTS................................................................................................ 198 COMPARE TABLE DATA .................................................................................................. 200 EVENT MONITOR............................................................................................................. 202 SESSIONS......................................................................................................................... 203 USER DEFINED TOOLS..................................................................................................... 205 TEST MANAGER .............................................................................................................. 209

22. THE EDITOR ........................................................................................................................ 213

22.1 22.2 22.3 22.4 22.5 22.6 22.7 22.8 22.9 22.10 22.11 22.12 22.13 22.14 22.15 22.16 22.17 22.18 22.19 22.20 22.21

SELECTION FUNCTIONS ................................................................................................... 213 COLUMN SELECTION ....................................................................................................... 213 CODE ASSISTANT ............................................................................................................ 214 RECALLING STATEMENTS................................................................................................ 215 SPECIAL COPY................................................................................................................. 216 PASTE FROM HOST LANGUAGE ........................................................................................ 217 CONTEXT SENSITIVE HELP............................................................................................... 218 DATABASE OBJECT POPUP MENU ..................................................................................... 218 EXPLAIN PLAN ................................................................................................................ 218 MACROS.......................................................................................................................... 218 BOOKMARKS ................................................................................................................... 219 COLOR MARKS ................................................................................................................ 220 CODE CONTENTS............................................................................................................. 221 CODE HIERARCHY........................................................................................................... 222 CODE FOLDING ............................................................................................................... 223 SPLIT EDITING................................................................................................................. 223 HYPERLINK NAVIGATION ................................................................................................ 224 NAVIGATION BUTTONS.................................................................................................... 224 REFACTORING ................................................................................................................. 224 SEARCH BAR ................................................................................................................... 225 WEB SEARCH .................................................................................................................. 228

23. THE LARGE DATA EDITOR ............................................................................................. 230

23.1

EDITING PLAIN TEXT ....................................................................................................... 231

23.2 EDITING RTF .................................................................................................................. 232

23.3 EDITING XML................................................................................................................. 232

23.4 EDITING HTML .............................................................................................................. 233

23.5

EDITING IMAGES ............................................................................................................. 233

23.6

EDITING HEX DATA ......................................................................................................... 234

23.7

INVOKING AN EXTERNAL VIEWER OR EDITOR.................................................................. 235

24. THE QUERY BUILDER....................................................................................................... 237

24.1

CREATING A NEW SELECT STATEMENT ............................................................................ 237

24.2

MODIFYING AN EXISTING SELECT STATEMENT................................................................ 240

24.3

MANIPULATING THE QUERY DEFINITION ......................................................................... 240

24.4

QUERY BUILDER PREFERENCES ...................................................................................... 241

PL/SQL Developer 12.0 User's Guide

7

24.5

QUERY BUILDER PLUG-INS ............................................................................................. 242

25. THE PL/SQL BEAUTIFIER ................................................................................................ 243

25.1

DEFINING THE OPTIONS ................................................................................................... 243

25.2

DEFINING THE RULES ...................................................................................................... 244

25.3

USING THE BEAUTIFIER ................................................................................................... 245

25.4

SUPPRESSING FORMATTING ............................................................................................. 245

26. THE PL/SQL CLIPBOARD ................................................................................................. 246

27. TEMPLATES......................................................................................................................... 247

27.1

THE TEMPLATE WINDOW ................................................................................................ 247

27.2

USING A TEMPLATE ......................................................................................................... 248

27.3

CREATING AND MODIFYING TEMPLATES ......................................................................... 249

28. WINDOW LIST ..................................................................................................................... 252

29. DOCKABLE AND FLOATING TOOLS............................................................................. 254

30. RIBBONS AND TOOLBARS ............................................................................................... 256

31. DOCUMENT INTERFACE.................................................................................................. 259

31.1

MULTIPLE DOCUMENT INTERFACE (MDI) ...................................................................... 259

31.2

SINGLE DOCUMENT INTERFACE (SDI) ............................................................................ 259

32. ORACLE FILE SYSTEM (OFS).......................................................................................... 260

32.1

OFS MANAGER ............................................................................................................... 260

32.2

OFS USAGE..................................................................................................................... 262

33. HELP SYSTEMS ................................................................................................................... 264

33.1

MS HELP FILES................................................................................................................ 264

33.2

HTML MANUALS............................................................................................................ 264

34. CUSTOMIZATION............................................................................................................... 267

34.1

PREFERENCES.................................................................................................................. 267

34.2

WINDOW LAYOUT ........................................................................................................... 267

34.3

ON-LINE DOCUMENTATION ............................................................................................. 267

34.4

COMMAND-LINE PARAMETERS ........................................................................................ 269

34.5

SQL, PL/SQL AND COMMAND KEYWORDS..................................................................... 272

34.6

PLUG-INS ........................................................................................................................ 272

8

PL/SQL Developer 12.0 User's Guide

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download