SEARCH
Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.
BOOK ORACLE DATABASE 12C PLSQL PROGRAMMING BY MICHAEL MCLAUGHLIN
SUMMARY
-
Items Found: 498
- PART I
- Oracle PL/SQL
- 1 Oracle PL/SQL Development Overview 3
- 2 New Features 17
- 3 PL/SQL Basics 43
- 4 Language Fundamentals 111
- 5 Control Structures 153
- 6 Collections 217
- 7 Error Management 261
- PART II
- PL/SQL Programming
- 8 Functions and Procedures 293
- 9 Packages 347
- 10 Large Objects 385
- 11 Object Types 449
- 12 Triggers 491
- 13 Dynamic SQL 545
- PART III
- Appendixes and Glossary
- A Oracle Database Primer 595
- B SQL Primer 695
- C SQL Built-in Functions 893
- D PL/SQL Built-in Packages and Types 965
- E Regular Expression Primer 999
- F Wrapping PL/SQL Code Primer 1019
- G PL/SQL Hierarchical Profiler Primer 1029
- H PL/SQL Reserved Words and Keywords 1045
- I Mastery Check Answers 1055
- Glossary 1085
- Index 1101
- PART I
- Oracle PL/SQL
- 1 Oracle PL/SQL Development Overview 3
- PL/SQL’s History and Background 4
- Oracle Development Architecture 6
- The Database 7
- The PL/SQL Language 9
- The Oracle Processing Architecture 12
- Two-Tier Model 13
- N-Tier Model 13
- Summary 15
- Mastery Check 15
- 2 New Features 17
- New SQL Features 18
- Data Catalog DIRECTORY Qualifies a LIBRARY Object 19
- Define Tables with Valid-Time (VT) Support 19
- Enhanced Oracle Native LEFT OUTER JOIN Syntax 20
- Default Values for Columns Based on Sequences 20
- Default Values for Explicit Null Insertion 22
- Identity Columns 23
- Increased Size Limits of String and Raw Types 24
- Pass Results from SQL Statements to External Programs 24
- Native SQL Support for Query Row Limits and Offsets 26
- Oracle Database Driver for MySQL Applications 29
- SQL CROSS APPLY, OUTER APPLY, and LATERAL 29
- Bequeath CURRENT_USER Views 31
- New PL/SQL Features 32
- Caching of Invoker Rights Functions 32
- Ability to White List PL/SQL Program Unit Callers 32
- Native Client API Support for PL/SQL Types 34
- New PL/SQL Package UTL_CALL_STACK 34
- DBMS_UTILITY Adds EXPAND_SQL_TEXT Subprogram 34
- DBMS_SQL Adds a New Formal Schema
- to the PARSE Procedure 35
- PL/SQL Functions in SQL WITH Clause 35
- PL/SQL-Specific Data Types Allowed in SQL 37
- Implicit REF CURSOR Parameter Binding 40
- Supporting Scripts 40
- Summary 41
- Mastery Check 41
- 3 PL/SQL Basics 43
- Block Structure 44
- Execution Block 44
- Basic Block Structure 45
- Declaration Block 48
- Exception Block 49
- Behavior of Variables in Blocks 50
- Anonymous Blocks 50
- Nested Anonymous Blocks 55
- Local Named Blocks 57
- Stored Named Blocks 60
- Basic Scalar and Composite Data Types 63
- Scalar Data Types 63
- Attribute and Table Anchoring 65
- Composite Data Types 68
- Control Structures 81
- Conditional Structures 81
- Iterative Structures 83
- Exceptions 92
- User-Defined Exceptions 93
- Dynamic User-Defined Exceptions 94
- Bulk Operations 95
- Functions, Procedures, and Packages 97
- Functions 97
- Procedures 99
- Packages 100
- Transaction Scope 106
- Single Transaction Scope 106
- Multiple Transaction Scopes 107
- Database Triggers 108
- Summary 109
- Mastery Check 109
- 4 Language Fundamentals 111
- Lexical Units 112
- Delimiters 112
- Identifiers 118
- Literals 119
- Comments 121
- Variables and Data Types 122
- Variable Data Types 123
- Scalar Data Types 126
- Large Objects (LOBs) 142
- Composite Data Types 144
- System Reference Cursors 147
- Summary 150
- Mastery Check 150
- 5 Control Structures 153
- Conditional Statements 154
- IF Statements 162
- CASE Statements 166
- Conditional Compilation Statements 169
- Iterative Statements 172
- Simple Loop Statements 172
- FOR Loop Statements 179
- WHILE Loop Statements 181
- Cursor Structures 185
- Implicit Cursors 185
- Explicit Cursors 190
- Bulk Statements 203
- BULK COLLECT INTO Statements 203
- FORALL Statements 208
- Supporting Scripts 213
- Summary 214
- Mastery Check 214
- 6 Collections 217
- Introduction to Collections 218
- Object Types: Varray and Table Collections 221
- Varray Collections 221
- Table Collections 225
- Associative Arrays 240
- Defining and Using Associative Arrays 241
- Oracle Collection API 247
- COUNT Method 250
- DELETE Method 251
- EXISTS Method 252
- EXTEND Method 253
- FIRST Method 254
- LAST Method 255
- LIMIT Method 255
- NEXT Method 256
- PRIOR Method 256
- TRIM Method 257
- Supporting Scripts 259
- Summary 259
- Mastery Check 259
- 7 Error Management 261
- Exception Types and Scope 262
- Compilation Errors 263
- Runtime Errors 266
- Exception Management Built-in Functions 274
- User-Defined Exceptions 276
- Declaring User-Defined Exceptions 276
- Dynamic User-Defined Exceptions 278
- Exception Stack Functions 281
- Supporting Scripts 287
- Summary 287
- Mastery Check 288
- PART II
- PL/SQL Programming
- 8 Functions and Procedures 293
- Function and Procedure Architecture 295
- Transaction Scope 302
- Calling Subroutines 303
- Positional Notation 304
- Named Notation 304
- Mixed Notation 304
- Exclusionary Notation 304
- SQL Call Notation 305
- Functions 306
- Function Model Choices 307
- Creation Options 308
- Pass-by-Value Functions 322
- Pass-by-Reference Functions 336
- Procedures 338
- Pass-by-Value Procedures 339
- Pass-by-Reference Procedures 342
- Supporting Scripts 345
- Summary 345
- Mastery Check 345
- 9 Packages 347
- Package Architecture 348
- Package Specification 354
- Prototype Features 355
- Serially Reusable Precompiler Directive 358
- Variables 359
- Types 361
- Components: Functions and Procedures 364
- Package Body 365
- Prototype Features 366
- Variables 368
- Types 371
- Components: Functions and Procedures 371
- Definer vs Invoker Rights Mechanics 375
- Managing Packages in the Database Catalog 378
- Finding, Validating, and Describing Packages 379
- Checking Dependencies 380
- Comparing Validation Methods: Timestamp vs Signature 381
- Summary 382
- Mastery Check 382
- 10 Large Objects 385
- Working with Internally Stored LOB Types 387
- LOB Assignments Under 32K 387
- LOB Assignments over 32K 389
- Reading Files into Internally Stored Columns 398
- Reading Local Files into CLOB or NCLOB Columns 399
- Reading Local Files into BLOB Columns 402
- Working with LOBs Through Web Pages 404
- Working with Binary Files (BFILEs) 413
- Creating and Using Virtual Directories 413
- Reading Canonical Path Names and Filenames 419
- Understanding the DBMS_LOB Package 427
- Package Constants 427
- Package Exceptions 428
- Opening and Closing Methods 429
- Manipulation Methods 430
- Introspection Methods 436
- BFILE Methods 440
- Temporary LOB Methods 441
- Security Link Methods 442
- Supporting Scripts 446
- The LONG to CLOB Script 446
- Manage LOBs from the File System 446
- Manage CLOB and BLOB LOBs Through the Web 446
- Manage BFILE LOBs Through the Web 446
- Summary 446
- Mastery Check 447
- 11 Object Types 449
- Object Basics 453
- Declaring Objects Types 453
- Implementing Object Bodies 456
- White Listing Object Types 461
- Getters and Setters 463
- Static Member Methods 465
- Comparing Objects 467
- Inheritance and Polymorphism 475
- Declaring Subclasses 477
- Implementing Subclasses 478
- Type Evolution 481
- Implementing Object Type Collections 483
- Declaring Object Type Collections 483
- Implementing Object Type Collections 483
- Supporting Scripts 487
- Summary 487
- Mastery Check 488
- 12 Triggers 491
- Introduction to Triggers 492
- Database Trigger Architecture 495
- Data Definition Language Triggers 499
- Event Attribute Functions 501
- Building DDL Triggers 512
- Data Manipulation Language Triggers 515
- Statement-Level Triggers 516
- Row-Level Triggers 518
- Compound Triggers 527
- INSTEAD OF Triggers 532
- System and Database Event Triggers 536
- Trigger Restrictions 538
- Maximum Trigger Size 538
- SQL Statements 538
- LONG and LONG RAW Data Types 539
- Mutating Tables 539
- System Triggers 540
- Supporting Scripts 541
- Summary 541
- Mastery Check 541
- 13 Dynamic SQL 545
- Dynamic SQL Architecture 547
- Native Dynamic SQL (NDS) 547
- Dynamic Statements 548
- Dynamic Statements with Inputs 550
- Dynamic Statements with Inputs and Outputs 554
- Dynamic Statements with an Unknown Number of Inputs 558
- DBMS_SQL Package 560
- Dynamic Statements 561
- Dynamic Statements with Input Variables 564
- Dynamic Statements with Variable Inputs and Fixed Outputs 566
- Dynamic Statements with Variable Inputs and Outputs 571
- DBMS_SQL Package Definition 576
- Supporting Scripts 591
- Summary 591
- Mastery Check 591
- PART III
- Appendixes and Glossary
- A Oracle Database Primer 595
- Oracle Database Architecture 596
- Starting and Stopping the
- Oracle Database 12c Server 603
- Unix or Linux Operations 604
- Microsoft Windows Operations 609
- Starting and Stopping the Oracle Listener 610
- Multiversion Concurrency Control 615
- Data Transactions 616
- DML Locking and Isolation Control 619
- Definer Rights and Invoker Rights 620
- Definer Rights 620
- Invoker Rights 621
- SQL Interactive and Batch Processing 622
- SQL*Plus Command-Line Interface 622
- Oracle SQL Developer Interface 644
- Database Administration 652
- Provisioning Users 652
- Using Database Constraints 661
- Security Hardening 670
- Data Governance 681
- SQL Tuning 684
- EXPLAIN PLAN Statement 685
- DBMS_XPLAN Package 686
- SQL Tracing 690
- Tracing Session Statements 691
- Convert Raw Trace Files to Readable Trace Files 693
- Summary 694
- B SQL Primer 695
- Oracle SQL Data Types 699
- Data Definition Language (DDL) 703
- CREATE Statement 704
- ALTER Statement 773
- RENAME Statement 791
- DROP Statement 792
- TRUNCATE Statement 794
- COMMENT Statement 795
- Data Manipulation Language (DML) 795
- ACID Compliant Transactions 795
- INSERT Statement 799
- UPDATE Statement 815
- DELETE Statement 829
- MERGE Statement 834
- Transaction Control Language (TCL) 841
- Queries: SELECT Statements 843
- Queries that Return Columns or Results from Columns 845
- Queries that Aggregate 861
- Queries that Return Columns or Results Selectively 866
- Join Results 876
- Joins that Splice Together Rows 878
- Joins that Splice Collections 888
- Summary 891
- C SQL Built-in Functions 893
- Character Functions 894
- ASCII Function 894
- ASCIISTR Function 895
- CHR Function 895
- CONCAT Function 896
- INITCAP Function 896
- INSTR Function 897
- LENGTH Function 897
- LOWER Function 898
- LPAD Function 898
- LTRIM Function 899
- REPLACE Function 899
- REVERSE Function 900
- RPAD Function 900
- RTRIM Function 901
- UPPER Function 901
- Data Type Conversion Functions 902
- CAST Function 902
- CONVERT Function 904
- TO_CHAR Function 905
- TO_CLOB Function 907
- TO_DATE Function 907
- TO_LOB Function 908
- TO_NCHAR Function 910
- TO_NCLOB Function 910
- TO_NUMBER Function 910
- Date-time Conversion Functions 911
- ADD_MONTHS Function 911
- CURRENT_DATE Function 911
- CURRENT_TIMESTAMP Function 912
- DBTIMEZONE Function 912
- EXTRACT Function 912
- FROM_TZ Function 913
- LAST_DAY Function 913
- LOCALTIMESTAMP Function 914
- MONTHS_BETWEEN Function 914
- NEW_TIME Function 915
- ROUND Function 916
- SYSDATE Function 916
- SYSTIMESTAMP Function 917
- TO_CHAR(date) Function 917
- TO_DSINTERVAL Function 918
- TO_TIMESTAMP Function 918
- TO_TIMESTAMP_TZ Function 919
- TO_YMINTERVAL Function 919
- TRUNC(date) Function 920
- TZ_OFFSET Function 920
- Collection Management Functions 921
- CARDINALITY Function 921
- COLLECT Function 921
- POWERMULTISET Function 925
- POWERMULTISET_BY_CARDINALITY Function 926
- SET Function 926
- Collection Set Operators 926
- CARDINALITY Operator 928
- EMPTY Operator 929
- MULTISET Operator 929
- MULTISET EXCEPT Operator 930
- MULTISET INTERSECT Operator 930
- MULTISET UNION Operator 931
- SET Operator 932
- SUBMULTISET OF Operator 933
- Number Functions 933
- CEIL Function 933
- FLOOR Function 934
- MOD Function 934
- POWER Function 936
- REMAINDER Function 937
- ROUND Function 938
- Error Reporting Functions 938
- SQLCODE Function 938
- SQLERRM Function 939
- Miscellaneous Functions 940
- BFILENAME Function 941
- COALESCE Function 943
- DECODE Function 944
- DUMP Function 945
- EMPTY_BLOB Function 945
- EMPTY_CLOB Function 948
- GREATEST Function 949
- LEAST Function 951
- NANVL Function 953
- NULLIF Function 953
- NVL Function 954
- SYS_CONTEXT Function 954
- TABLE Function 958
- TREAT Function 960
- USERENV Function 961
- VSIZE Function 963
- Summary 963
- D PL/SQL Built-in Packages and Types 965
- Oracle Database 11g and 12c New Packages 966
- Examples of Package Use 974
- DBMS_APPLICATION_INFO Example 974
- DBMS_COMPARISON 979
- DBMS_CRYPTO 986
- DBMS_FGA 990
- Case Study: Query Tool 991
- Supporting Scripts 997
- Summary 997
- E Regular Expression Primer 999
- Regular Expression Introduction 1000
- Character Classes 1000
- Collation Classes 1003
- Metacharacters 1004
- Metasequences 1006
- Literals 1007
- Regular Expression Implementation 1007
- REGEXP_COUNT Function 1007
- REGEXP_INSTR Function 1011
- REGEXP_LIKE Function 1013
- REGEXP_REPLACE Function 1014
- REGEXP_SUBSTR Function 1015
- Supporting Scripts 1017
- Summary 1017
- F Wrapping PL/SQL Code Primer 1019
- Limitations of Wrapping PL/SQL 1020
- Limitations of the PL/SQL wrap Utility 1021
- Limitations of the DBMS_DDL.WRAP Function 1021
- Using the wrap Command-Line Utility 1021
- Using the DBMS_DDL Command-Line Utility 1021
- WRAP Function 1022
- CREATE_WRAPPED Procedure 1026
- Summary 1028
- G PL/SQL Hierarchical Profiler Primer 1029
- Configuring the Schema 1030
- Collecting Profiler Data 1032
- Understanding Profiler Data 1035
- Reading the Raw Output 1035
- Defining the PL/SQL Profiler Tables 1037
- Querying the Analyzed Data 1039
- Using the plshprof Command-Line Utility 1040
- Supporting Scripts 1043
- Summary 1043
- H PL/SQL Reserved Words and Keywords 1045
- Summary 1053
- I Mastery Check Answers 1055
- Chapter 1 1056
- Chapter 2 1058
- Chapter 3 1060
- Chapter 4 1063
- Chapter 5 1065
- Chapter 6 1067
- Chapter 7 1069
- Chapter 8 1071
- Chapter 9 1074
- Chapter 10 1076
- Chapter 11 1078
- Chapter 12 1080
- Chapter 13 1082
- Glossary 1085
- Index 1101
