Oracle Database 10g PL SQL Programming

write Robust Pl/sql Applications create Dynamic Client/server Applications Using Pl/sql And The Comprehensive Information Contained In This Authoritative Volume. Expert Oracle Insiders Cover The Features Of Pl/sql, Explain Scripting Semantics And Syntax, And Fully Detail The Pl/sql Functionality Of Oracle Database 10 g . You’ll Learn How To Write Powerful Pl/sql Programs, Interact With Oracle Databases, Perform Complex Calculations, And Handle Error Conditions. Plus, You’ll Get Insider Programming Tips And Techniques And Real-world Examples From The Oracle Community. develop, Tune, And Debug Pl/sql Programs That Access Oracle Databases From A Wide Variety Of Environments use Pl/sql Block Structures, Variables, Datatypes, Expressions, Operators, And Statements regulate And Recover From Errors Using Exceptions And Exception Handlers deploy Subprograms, Procedures, Functions, Packages, And Triggers accomplish Intersession Communication With Dbms_pipe And Dbms_alert write Object-oriented Pl/sql Applications manage Large Sets Of Data Using Pl/sql Records And Objects work With Oracle Net Services, External Procedures, Java Libraries, And C Shared Libraries perform Object-oriented Programming Using Transient Objects, Inheritance, Type Evolution, And Attribute Chaining automate Database Tasks With Dbms_job And Dbms_scheduler And Add E-mail Functionality With Utl_smtp And Utl_mail Contents at a Glance......Page 10 Contents......Page 12 Acknowledgments......Page 22 What’s New......Page 24 How to Use This Book......Page 25 Part I - Introduction......Page 30 Introduction to PL/SQL......Page 32 Introduction to Programming Languages......Page 33 Note to Beginning Programmers......Page 34 Structured Query Language (SQL)......Page 35 Relational Database Overview......Page 36 PL/SQL vs. SQL......Page 38 PL/SQL vs. Java......Page 40 PL/SQL History and Features......Page 41 Anonymous Blocks......Page 44 Object Types......Page 45 Native Compilation......Page 46 Scope......Page 47 Assumptions......Page 48 Examples......Page 49 Summary......Page 50 Using SQL*Plus and JDeveloper......Page 52 SQL*Plus......Page 53 Connecting to the Instance......Page 54 Testing the Connection......Page 55 Using SQL*Plus......Page 57 Running a Script from a File......Page 60 Output to the Screen Using SQL*Plus and PL/SQL......Page 62 Installing JDeveloper......Page 63 Working with PL/SQL in JDeveloper......Page 65 Summary......Page 72 PL/SQL Basics......Page 74 The Basic Structure......Page 75 Anonymous Blocks......Page 78 Named Blocks......Page 81 Nested Blocks......Page 91 Triggers......Page 92 Lexical Units......Page 93 Character/String......Page 106 NUMBER Data Type......Page 110 Boolean......Page 112 Date/Time......Page 113 Reference......Page 116 Using Variables......Page 118 %ROWTYPE......Page 119 Variable Scope......Page 120 Bind Variables......Page 122 Hiding Code......Page 126 Expressions......Page 129 Assignment Operator......Page 130 Controlling Program Flow......Page 131 Conditional Evaluation......Page 132 Circular Execution......Page 139 Sequential Navigation using GOTO......Page 142 Summary......Page 144 Using SQL with PL/SQL......Page 146 Transaction Processing......Page 147 Transactions and Locking......Page 148 Autonomous Transactions......Page 154 Set Transaction......Page 157 SQL SELECT Statement......Page 159 Pattern Matching......Page 163 Information Retrieval......Page 167 Cursors......Page 171 How Cursors Work......Page 172 Explicit Cursors......Page 175 Implicit Cursors......Page 182 Cursor Variables......Page 183 Cursor Subqueries......Page 185 Open Cursors......Page 186 DML and DDL......Page 187 Pre-Compilation......Page 188 Manipulating Data with DML......Page 189 DELETE......Page 192 Introduction to Dynamic SQL......Page 193 ROWID......Page 194 ROWNUM......Page 198 Numeric Functions......Page 201 Date Functions......Page 203 Conversion Functions......Page 204 Error Functions......Page 205 Other Functions......Page 207 Summary......Page 208 Records......Page 210 Working with Records......Page 211 Defining Record Types......Page 212 Defining and Using Record Types as Formal Parameters......Page 227 Defining and Using Object Types as Parameters......Page 231 Defining and Using Record Types as Return Values......Page 234 Defining and Using Object Types as Return Values......Page 236 Verifying Work with Record Types......Page 239 Summary......Page 241 Collections......Page 242 Working with Collections......Page 243 Working with Varrays......Page 246 Working with Nested Tables......Page 266 Working with Associative Arrays......Page 290 Oracle 10gCollection API......Page 311 COUNT Method......Page 316 DELETE Method......Page 317 EXISTS Method......Page 319 EXTEND Method......Page 322 FIRST Method......Page 324 LIMIT Method......Page 326 PRIOR Method......Page 328 TRIM Method......Page 329 Summary......Page 331 Error Handling......Page 332 What Is an Exception?......Page 333 Declaring Exceptions......Page 335 Raising Exceptions......Page 338 Handling Exceptions......Page 339 The EXCEPTION_INIT Pragma......Page 347 Using RAISE_APPLICATION_ERROR......Page 348 Exceptions Raised in the Executable Section......Page 352 Exceptions Raised in the Declarative Section......Page 355 Exceptions Raised in the Exception Section......Page 356 Exception Guidelines......Page 358 Scope of Exceptions......Page 359 Avoiding Unhandled Exceptions......Page 360 Masking Location of the Error......Page 361 Exceptions and Transactions......Page 362 Summary......Page 363 Creating Procedures, Functions, and Packages......Page 364 Procedures and Functions......Page 365 Subprogram Creation......Page 366 Subprogram Parameters......Page 371 The CALL Statement......Page 393 Package Specification......Page 396 Package Body......Page 398 Packages and Scope......Page 400 Overloading Packaged Subprograms......Page 403 Package Initialization......Page 407 Summary......Page 409 Using Procedures, Functions, and Packages......Page 410 Stored Subprograms and the Data Dictionary......Page 411 Local Subprograms......Page 414 Stored vs. Local Subprograms......Page 421 Considerations of Stored Subprograms and Packages......Page 422 Subprogram Dependencies......Page 423 Package Run-Time State......Page 435 Privileges and Stored Subprograms......Page 441 Single-Valued Functions......Page 453 Multiple-Valued Functions......Page 464 Native Compilation......Page 468 UNKEEP......Page 469 ABORTED_REQUEST_THRESHOLD......Page 470 Summary......Page 471 Database Triggers......Page 472 Types of Triggers......Page 473 DML Triggers......Page 474 Instead-of Triggers......Page 475 System Triggers......Page 477 Creating DML Triggers......Page 478 Creating Instead-of Triggers......Page 490 Creating System Triggers......Page 498 Other Trigger Issues......Page 508 Triggers and the Data Dictionary......Page 515 Mutating Tables......Page 517 Mutating Table Example......Page 520 Workaround for the Mutating Table Error......Page 521 Summary......Page 524 Part II - Advanced PL/ SQL Features......Page 526 Intersession Communication......Page 528 Requiring Permanent or Semipermanent Structures......Page 529 Not Requiring Permanent or Semipermanent Structures......Page 530 Introducing the DBMS_PIPE Package......Page 531 Defining the DBMS_PIPE Package......Page 534 Working with the DBMS_PIPE Package......Page 538 Introducing the DBMS_ALERT Package......Page 559 Defining the DBMS_ALERT Package......Page 560 Working with the DBMS_ALERT Package......Page 563 Summary......Page 571 External Routines......Page 572 Introducing External Procedures......Page 573 Architecture......Page 574 Oracle Net Services Configuration......Page 577 Defining the Multithreaded External Procedure Agent......Page 585 Working with a C Shared Library......Page 589 Working with a Java Shared Library......Page 597 Troubleshooting the Shared Library......Page 604 Configuration of the Listener or Environment......Page 605 Configuration of the Shared Library or PL/SQL Library Wrapper......Page 609 Summary......Page 610 Dynamic SQL......Page 612 Introducing Dynamic SQL......Page 614 Working with Native Dynamic SQL......Page 615 Working with DDL and DML Without Bind Variables......Page 617 Working with DML and a Known List of Bind Variables......Page 627 Working with DQL......Page 630 Working with the Oracle DBMS_SQL Built-in Package......Page 638 Working with DDL and DML Without Bind Variables......Page 647 Working with DML and a Known List of Bind Variables......Page 651 Working with DQL......Page 660 Summary......Page 663 Introduction to Objects......Page 664 Data and Procedural Abstraction......Page 665 Object Type Overview......Page 666 Object Type Specification......Page 667 Object Type Body......Page 674 Object Type Inheritance......Page 681 Dynamic Method Dispatch......Page 689 Attribute Chaining......Page 692 Type Evolution......Page 695 Summary......Page 701 Objects in the Database......Page 702 Object Tables......Page 703 Column Objects......Page 711 Object Views......Page 712 Object Tables......Page 714 Accessing Column Objects......Page 718 Accessing Object Views......Page 720 Object Related Functions and Operators......Page 722 Type Evolution......Page 734 Summary......Page 738 Large Objects......Page 740 Introduction to Large Objects......Page 741 Types of LOBs......Page 742 LOB Structure......Page 746 Internal LOB Storage......Page 747 External LOB Storage......Page 751 Temporary LOB Storage......Page 752 Migrating from LONGs to LOBs......Page 753 SQL for Internal Persistent LOBs......Page 754 External LOB ... BFILE......Page 758 DBMS_LOB......Page 759 Returning Clause......Page 787 Summary......Page 794 Scheduling Tasks......Page 796 Introducing DBMS_JOB......Page 797 SUBMIT......Page 799 BROKEN......Page 803 RUN......Page 805 CHANGE......Page 806 REMOVE......Page 808 Using DBMS_SCHEDULER......Page 809 Migrating from DBMS_JOB......Page 814 Summary......Page 817 Part III - Appendixes......Page 818 PL/SQL Reserved Words......Page 820 Table of Reserved Words......Page 821 Guide to Supplied Packages......Page 824 SYS-Owned Built-in Packages......Page 825 DBMS_ADVISOR......Page 826 DBMS_APPLICATION_INFO......Page 827 DBMS_BACKUP_RESTORE......Page 828 DBMS_CRYPTO......Page 829 DBMS_DDL......Page 830 DBMS_DESCRIBE......Page 831 DBMS_FGA......Page 832 DBMS_FLASHBACK......Page 833 DBMS_JAVA......Page 834 DBMS_LOB......Page 835 DBMS_METADATA......Page 836 DBMS_ODCI......Page 837 DBMS_OUTLN......Page 838 DBMS_OUTPUT......Page 839 DBMS_PROPAGATION_ADM......Page 840 DBMS_REFRESH......Page 841 DBMS_RESOURCE_MANAGER/ DBMS_RESOURCE_MANAGER_PRIVS......Page 842 DBMS_ROWID......Page 843 DBMS_SCHEMA_COPY......Page 844 DBMS_SHARED_POOL......Page 845 DBMS_SQLTUNE......Page 846 DBMS_STATS......Page 847 DBMS_TRANSACTION......Page 848 DBMS_WARNING......Page 849 DBMS_XPLAN......Page 850 UTL_DBWS......Page 851 UTL_HTTP......Page 852 UTL_LMS......Page 853 UTL_REF......Page 854 UTL_URL......Page 855 CTXSYS-Owned Built-in Packages......Page 856 CTX_CLS......Page 857 CTX_DDL......Page 858 CTX_DOC......Page 860 CTX_OUTPUT......Page 862 CTX_QUERY......Page 863 CTX_REPORT......Page 864 CTX_THES......Page 865 Index......Page 868 Publisher's Note: Products purchased from Third Party sellers are not guaranteed by the publisher for quality, authenticity, or access to any online entitlements included with the product. The essential desktop reference for PL/SQL revised and expanded for Oracle Database 10g Create dynamic client/server applications using PL/SQL and the comprehensive information contained in this authoritative volume. Expert Oracle insiders cover the features of PL/SQL, explain scripting semantics and syntax, and fully detail the PL/SQL functionality of Oracle Database 10 g . Youll learn how to write powerful PL/SQL programs, interact with Oracle databases, perform complex calculations, and handle error conditions. "This fifth edition is principally rewritten, and includes all new examples and coverage of advanced topics like object oriented programming with PL/SQL, external routines, job scheduling, and more. This book covers both introductory and advanced material, and provides complete coverage of the PL/SQL language including new 10g features"--Introduction