Oracle Database 12c PL/SQL Programming PDF

 Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / ® Oracle Database 12c PL/SQL Programming Michael McLaughlin N...

 Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / ® Oracle Database 12c PL/SQL Programming Michael McLaughlin New York Chicago San Francisco Athens London Madrid Mexico City Milan New Delhi Singapore Sydney Toronto 00-FM.indd 1 12/17/13 4:07 PM  Copyright © 2014 by McGraw-Hill Education (Publisher). All rights reserved. Printed in the United States of America. Except as permitted under the Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of publisher, with the exception that the program listings may be entered, stored, and executed in a computer system, but they may not be reproduced for publication. ISBN: 978-0-07-181244-3 MHID: 0-07-181244-X e-book conversion by Cenveo® Publisher Services Version 1.0 The material in this e-book also appears in the print version of this title: ISBN: 978-0-07-181243-6, MHID: 0-07-181243-1 McGraw-Hill Education e-books are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training programs. To contact a representative, please visit the Contact Us pages at www.mhprofessional.com. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. All other trademarks are the property of their respective owners, and McGraw-Hill Education makes no claim of ownership by the mention of products that contain these marks. Screen displays of copyrighted Oracle software programs have been reproduced herein with the permission of Oracle Corporation and/or its affiliates. Information has been obtained by McGraw-Hill Education from sources believed to be reliable. However, because of the possibility of human or mechanical error by our sources, McGraw-Hill Education, or others, McGraw-Hill Education does not guarantee the accuracy, adequacy, or completeness of any information and is not responsible for any errors or omissions or the results obtained from the use of such information. Oracle Corporation does not make any representations or warranties as to the accuracy, adequacy, or completeness of any information contained in this Work, and is not responsible for any errors or omissions. TERMS OF USE This is a copyrighted work and McGraw-Hill Education (“McGraw-Hill”) and its licensors reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms. THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise. eBook 243-1cr_pg.indd 1 12/17/13 4:26 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / iii To Lisa, my eternal companion, inspiration, wife, and best friend; and to Sarah, Joseph, Elise, Ian, Ariel, Callie, Nathan, Spencer, and Christianne—our terrific, heaven-sent children. Thank you for your constant support, patience, and sacrifice that made writing yet another book possible. 00-FM.indd 3 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / iv About the Author Michael McLaughlin is a professor at BYU–Idaho in the Computer Information Technology Department of the Business and Communication College. He is also the founder of McLaughlin Software, LLC, and is active in the Utah Oracle User’s Group. He is the author of eight other Oracle Press books, such as Oracle Database 11g & MySQL 5.6 Developer Handbook, Oracle Database 11g PL/SQL Programming, and Oracle Database 11g PL/SQL Workbook. Michael has been writing PL/SQL since it was an add-on product for Oracle 6. He also writes C, C++, Java, Perl, PHP, and Python. Michael worked at Oracle Corporation for over eight years in consulting, development, and support. While at Oracle, he led the release engineering efforts for the direct path CRM upgrade of Oracle Applications 11i (11.5.8 and 11.5.9) and led PL/SQL forward compatibility testing for Oracle Applications 11i with Oracle Database 9i. He is the inventor of the ATOMS transaction architecture (U.S. Patents #7,206,805 and #7,290,056). The patents are assigned to Oracle Corporation. Prior to his tenure at Oracle Corporation, Michael worked as an Oracle developer, systems and business analyst, and DBA beginning with Oracle 6. His blog is at http://blog.mclaughlinsoftware.com. Michael lives in eastern Idaho within a two-hour drive to Caribou-Targhee National Forest, Grand Teton National Park, and Yellowstone National Park. He enjoys outdoor activities with his wife and children (six of nine of whom still live at home). About the Contributing Author John Harper currently works for the Church of Jesus Christ of Latter-day Saints as a principal database engineer. He greatly enjoys working with the data warehousing, business intelligence, and database engineers there. John’s mentors include Michael McLaughlin, Robert Freeman, Danette McGilvary, and many others who have spent considerable time becoming the experts in their industry. He is both awed and inspired by their abilities and feels lucky to be associated with them. Recently, John has had the opportunity to work closely with some of the top-notch minds in database security. He hopes to produce a series of publications focused on Oracle products such as Oracle Audit Vault and Database Firewall, and Oracle Data Redaction. John enjoys Japanese martial arts. During his teenage years and early adulthood, he took jujitsu, karate, judo, and aikido. He loves aikido and hopes to teach it one day. He would also love to learn kyudo if he can find any spare time. John lives with his wife of over 23 years in Northern Utah County, Utah. They have one adopted daughter, whom they cherish and thoroughly spoil. He has been working with databases for the past 14 years, specializing in Oracle administration, database architecture, database programming, database security, and information quality. About the Technical Editor Joseph McLaughlin is an iPhone and Ruby web developer at Deseret Book in Salt Lake City, Utah. He has extensive backend database development experience with Oracle, MySQL, and PostgreSQL. His favorite development languages are Objective-C and Ruby. Joseph is a recent graduate of BYU–Idaho with a degree in Computer Information Technology. While a college student and independent consultant, Joseph designed, developed, and deployed four mobile applications for the iPhone or iPod Touch. Aside from programming, Joseph enjoys playing basketball and watching the Boston Red Sox win, especially when they win the World Series. 00-FM.indd 4 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / v Contents at a Glance 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 v 00-FM.indd 5 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / vi vi Oracle Database 12c PL/SQL Programming 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 00-FM.indd 6 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / vii Contents Acknowledgments.................................................... xix Introduction......................................................... xxi 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 vii 00-FM.indd 7 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / viii viii Oracle Database 12c PL/SQL Programming 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 00-FM.indd 8 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / Contents ix 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 00-FM.indd 9 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / x x Oracle Database 12c PL/SQL Programming 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 00-FM.indd 10 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / Contents xi 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 00-FM.indd 11 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / xii xii Oracle Database 12c PL/SQL Programming 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 00-FM.indd 12 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / Contents xiii 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 00-FM.indd 13 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / xiv xiv Oracle Database 12c PL/SQL Programming 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 00-FM.indd 14 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / Contents xv 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 00-FM.indd 15 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / xvi xvi Oracle Database 12c PL/SQL Programming 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 00-FM.indd 16 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / Contents xvii 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 00-FM.indd 17 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / xviii xviii Oracle Database 12c PL/SQL Programming 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 00-FM.indd 18 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / xix Acknowledgments M any thanks go to Paul Carlstroem, Amanda Russell, Harry Popli, and the production team that made this book a possibility. There are many unsung heroes and heroines in the production department because they’re behind the scenes. The production department typesets, proofreads, and gives their all to make books real, and while I don’t know all their names, they deserve acknowledgment for their meticulous work. Special thanks goes to Bill McManus, the copy editor. He gave an awesome effort to keep the book consistent, well written, and well organized! Special thanks for moral and project support to Paul Carlstroem and Amanda Russell because they were critical to my success, especially as the project went beyond a year. Thanks to Sheila Cepero, who manages the Oracle Publishers Program, for her help with the Oracle Database 12c beta testing cycle, and to Lynn Snyder, who managed the Oracle Database 12c program. Thanks to John Harper who contributed elements of Appendix A and wrote Appendix D. John also acted as a second technical editor for the rest of the book, and his great eye for detail certainly contributed to the quality of the book. Thanks to Pablo Ribaldi for his contributions to Appendix A on data governance. As the Information Governance Manager, he led the LDS Church’s Information Communication Services team that won the Data Governance Best Practice Award from DebTech International LLC. Thanks to the many students and lab tutors who took an interest in this project, like Craig Christensen, Jeremy Heiner, Matthew Mason, Alan Pynes, and Jordan Smith. Also, thanks to Kent Jackson for reading elements of the book and providing suggestions for improvements, and to Steve Rigby, my department chair, for his support in the project. xix 00-FM.indd 19 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / xx This page has been intentionally left blank 00-FM.indd 20 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / xxi Introduction T his book shows you how to use the PL/SQL programming language. It is full of examples and techniques that can help you build robust database-centric applications. Appendix A shows you the basics of what you should know as an Applications DBA or developer, like starting and stopping the Oracle database and listener, using SQL*Plus as the command-line interface, SQL Developer as the free cross-platform GUI interface, and techniques for SQL tuning. Appendixes B, C, and D show you how to write SQL, use SQL built-in functions, and use PL/SQL built-in packages. The remaining appendixes show you how to use regular expression functions, obfuscate your PL/SQL code through wrapping it, use the hierarchical profiler for PL/SQL, and discover reserved and keywords. As an author, the Introduction typically is either the last thing you write or the first thing you write. Unlike my strategy for the previous edition, this time I drafted the introduction before writing anything else, and that helped me to make sure I stayed true to a planned course. As indicated in my Acknowledgments page, the production staff also helps clear up what I write, and their talent is critical to bringing a quality book into print. The introduction covers the following: The “Book Outline” section summarizes each chapter in a sentence or two, and should be worth a quick look to give you an overview of how this book is structured. The “Lexicon” section gives you the rationale for variable naming conventions in the book and provides recommended time-saving techniques you can use when debugging your code. The “Data Model and Source Code to Download” section describes the basis for the examples and tells you where to find the code that creates and seeds the sample video store database. xxi 00-FM.indd 21 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / xxii xxii Oracle Database 12c PL/SQL Programming Book Outline The book has three parts: “PL/SQL Fundamentals,” “PL/SQL Programming,” and “Appendixes and Glossary.” In the first two parts of the book, each major section of each chapter ends with a “Review Section” that lists the key points presented in that section. Also, each of the chapters in the first two parts concludes with a “Mastery Check,” containing ten true-or-false questions and five multiple-choice questions to help you ensure that you understand the material covered in the chapter. The answers are provided in Appendix I. The third part, “Appendixes and Glossary,” contains primers on Oracle Database 12c, SQL, SQL built-in functions, PL/SQL built-in packages, regular expressions, wrapping PL/SQL code, the PL/SQL hierarchical profiler, and reserved word and keywords. As mentioned, Appendix I provides the answers to the “Mastery Check” sections. A glossary follows the last appendix. Part I: PL/SQL Fundamentals Chapter 1, “Oracle PL/SQL Development Overview,” explains the history and background of PL/SQL and describes the Oracle development architecture. The history and background section explains how SQL is the primary interface, and how PL/SQL extends the behavior of SQL with a built-in imperative programming language, enables the implementation of object-relational features, and allows DBAs and developers to exploit the power of the Oracle 12c Database. The Oracle development architecture section covers how the SQL interface works as an interactive and call command-line interface (CLI), and how two-tier and n-tier models work with the Oracle Database 12c database. Chapter 2, “New Features,” introduces the Oracle Database 12c SQL and PL/SQL new features. This chapter assumes you have a background in the Oracle Database 11g features. The new SQL features cover invisible and identity columns, expanded length of the VARCHAR2 data type, and enhanced outer join operations. The new PL/SQL features cover invoker rights result cache functions, white listing PL/SQL callers, new error stack management features, embedding functions in the SQL WITH clause, and using local PL/SQL data types in embedded SQL statements. Chapter 3, “PL/SQL Basics,” explains and provides examples of basic features of the PL/SQL programming languages. This chapter covers PL/SQL block structures, behaviors of variables in blocks, basic scalar and composite data types, control structures, exceptions, bulk operations, functions, procedures, packages, transaction scopes, and database triggers. You will find examples of all basic elements of PL/SQL syntax in Chapter 3. It’s also the best place to start if you would need a review or introduction to the basics of how you write PL/SQL programs. Chapter 4, “Language Fundamentals,” covers lexical units (delimiters, identifiers, literals, and comments) and variable and data types. In Chapter 4, you learn the basic building blocks of PL/SQL program units. You also learn what data types are available and how you declare variables of these data types. The subsequent chapters assume you know what data types are available and how to declare them in anonymous and named PL/SQL blocks, which makes it an important chapter to read or pursue before digging into the core features of the PL/SQL language. 00-FM.indd 22 12/17/13 4:07 PM  Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / Introduction xxiii Chapter 5, “Control Structures,” describes the conditional statements, iterative statements, cursor structures, and bulk statements. This chapter takes a complete look at IF statements and loops. Oracle implements the IF statement or CASE statement to manage conditional logic, and simple, FOR, and WHILE loops to manage iterative statements. The discussion of loops qualifies guard and sentinel values, and safeguards for dynamic sentinel values. This chapter covers how you manage cursors in loops and how you manage bulk processing DML statements. Chapter 6, “Collections,” shows how you can work with SQL varray and table collections, as well as PL/SQL associative arrays (previously known as PL/SQL tables or index-by tables). This chapter’s discussion of varray and table collections explains how you can work with both Attribute Data Types (ADTs) and user-defined types (UDT). It also describes the differences between how to use and work with ADT and UDT variables. This chapter also covers how to work with PL/SQL-only associative arrays that use scalar data types or composite data types, which may be record types or object types. This chapter also qualifies the functions and procedures of the Oracle Collection API, and provides examples of using these functions and procedures. Chapter 7, “Error Management,” explains how you use exceptions in PL/SQL. This chapter covers exception type and scope, exception management built-in functions, user-defined exceptions, and exception stack functions. This chapter shows you how to find and solve the typical errors that can occur when writing PL/SQL programs. This chapter also shows you how to write exception handlers that manage unexpected runtime exceptions. You also learn how to manage exception stacks. Part II: PL/SQL Programming Chapter 8, “Functions and Procedures,” explains the architecture of PL/SQL functions and procedures, transaction scope, function options and implementations, and procedure implementations. The architecture section covers how pass-by-value and pass-by-reference functions and procedures work, including how to white list stand-alone functions and procedures in the Oracle Database 12c. This chapter also covers SQL positional, named, mixed, and exclusionary call notation. It also describes the various ways you can define functions, like deterministic, parallel-enabled, pipelined, and result cache functions. It shows you how to object table functions that return collections of user-defined types. This chapter also covers how you write recursive and autonomous functions. Chapter 9, “Packages,” explores how you can work with and use packages. This chapter covers package architecture, specifications, and bodies. It also compares definer rights and invoker rights mechanics, and describes how the database catalog manages the status and validity of package specifications and bodies. This chapter reviews the concepts of how to write forward-referencing stubs and how to overload functions and procedures. It also shows you how to white list package specifications. Chapter 10, “Large Objects,” shows you how to work with the BLOB, CLOB, and NCLOB internally managed data types and the BFILE externally managed data type. This chapter shows you how to create and work with character and binary large object that