Ebook: Oracle Database 12c PL/SQL programming

Contents note continued: POWERMULTLSET_BY_CARDINALITY Function -- Set Function -- Collection Set Operators -- CARDINALITY Operator -- EMPTY Operator -- MULTISET Operator -- MULTISET EXCEPT Operator -- MULTISET Intersect Operator -- MULTISET Union Operator -- SET Operator -- SUBMULTISET OF Operator -- Number Functions -- CEIL Function -- FLOOR Function -- MOD Function -- POWER Function -- REMAINDER Function -- ROUND Function -- Error Reporting Functions -- SQLCODE Function -- SQLERRM Function -- Miscellaneous Functions -- BFILENAME Function -- COALESCE Function -- DECODE Function -- DUMP Function -- EMPTY_BLOB Function -- EMPTY_CLOB Function -- GREATEST Function -- LEAST Function -- NANVL Function -- NULLIF Function -- NVL Function -- SYS_CONTEXT Function -- TABLE Function -- TREAT Function -- USERENV Function -- VSIZE Function -- Summary -- D. PL/SQL Built-In Packages And Types -- Oracle Database 11g And 12C New Packages -- Examples Of Package Use.;Contents note continued: Object Types: Varray and Table Collections -- Varray Collections -- Table Collections -- Associative Arrays -- Defining and Using Associative Arrays -- Oracle Collection API -- COUNT Method -- DELETE Method -- EXISTS Method -- EXTEND Method -- FIRST Method -- LAST Method -- LIMIT Method -- NEXT Method -- PRIOR Method -- TRIM Method -- Supporting Scripts -- Summary -- Mastery Check -- 7. Error Management -- Exception Types and Scope -- Compilation Errors -- Runtime Errors -- Exception Management Built-in Functions -- User-Defined Exceptions -- Declaring User-Defined Exceptions -- Dynamic User-Defined Exceptions -- Exception Stack Functions -- Supporting Scripts -- Summary -- Mastery Check -- pt. II PL/SQL Programming -- 8. Functions and Procedures -- Function and Procedure Architecture -- Transaction Scope -- Calling Subroutines -- Positional Notation -- Named Notation -- Mixed Notation -- Exclusionary Notation -- SQL Call Notation -- Functions.;"Develop, debug, and administer robust database programs. Filled with detailed examples and expert strategies from an Oracle ACE, Oracle Database 12c PL/SQL Programming explains how to retrieve and process data, write PL/SQL statements, execute effective queries, incorporate PHP and Java, and work with dynamic SQL. Code testing, security, and object-oriented programming techniques are fully covered in this comprehensive Oracle Press guide"--;Machine generated contents note: pt. I Oracle PL/SQL -- 1. Oracle PL/SQL Development Overview -- PL/SQL's History and Background -- Oracle Development Architecture -- The Database -- The PL/SQL Language -- The Oracle Processing Architecture -- Two-Tier Model -- N-Tier Model -- Summary -- Mastery Check -- 2. New Features -- New SQL Features -- Data Catalog DIRECTORY Qualifies a LIBRARY Object -- Define Tables with Valid-Time (VT) Support -- Enhanced Oracle Native LEFT OUTER JOIN Syntax -- Default Values for Columns Based on Sequences -- Default Values for Explicit Null Insertion -- Identity Columns -- Increased Size Limits of String and Raw Types -- Pass Results from SQL Statements to External Programs -- Native SQL Support for Query Row Limits and Offsets -- Oracle Database Driver for MySQL Applications -- SQL CROSS APPLY, OUTER APPLY, and LATERAL -- Bequeath CURRENT_USER Views -- New PL/SQL Features -- Caching of Invoker Rights Functions.;Contents note continued: DBMS_APPLICATION_INFO Example -- DBMS_COMPARISON -- DBMS_CRYPTO -- DBMS_FGA -- Case Study: Query Tool -- Supporting Scripts -- Summary -- E. Regular Expression Primer -- Regular Expression Introduction -- Character Classes -- Collation Classes -- Metacharacters -- Metasequences -- Literals -- Regular Expression Implementation -- REGEXP_COUNT Function -- REGEXP_NSTR Function -- REGEXP_LIKE Function -- REGEXP_REPLACE Function -- REGEXP_SUBSTR Function -- Supporting Scripts -- Summary -- F. Wrapping PL/SQL Code Primer -- Limitations of Wrapping PL/SQL -- Limitations of the PL/SQL wrap Utility -- Limitations of the DBMS_DDL. WRAP Function -- Using the wrap Command-Line Utility -- Using the DBMS_DDL Command-Line Utility -- WRAP Function -- CREATE_WRAPPED Procedure -- Summary -- G. PL/SQL Hierarchical Profiler Primer -- Configuring the Schema -- Collecting Profiler Data -- Understanding Profiler Data -- Reading the Raw Output.;Contents note continued: Dynamic Statements -- Dynamic Statements with Input Variables -- Dynamic Statements with Variable Inputs and Fixed Outputs -- Dynamic Statements with Variable Inputs and Outputs -- DBMS_SQL Package Definition -- Supporting Scripts -- Summary -- Mastery Check -- pt. III Appendixes and Glossary -- A. Oracle Database Primer -- Oracle Database Architecture -- Starting and Stopping the Oracle Database 12c Server -- Unix or Linux Operations -- Microsoft Windows Operations -- Starting and Stopping the Oracle Listener -- Multiversion Concurrency Controls -- Data Transactions -- DML Locking and Isolation Control -- Definer Rights and Invoker Rights -- Definer Rights -- Invoker Rights -- SQL Interactive and Batch Processing -- SQL*Plus Command-Line Interface -- Oracle SQL Developer Interface -- Database Administration -- Provisioning Users -- Using Database Constraints -- Security Hardening -- Data Governance -- SQL Tuning -- EXPLAIN PLAN Statement.;Contents note continued: Function Model Choices -- Creation Options -- Pass-by-Value Functions -- Pass-by-Reference Functions -- Procedures -- Pass-by-Value Procedures -- Pass-by-Reference Procedures -- Supporting Scripts -- Summary -- Mastery Check -- 9. Packages -- Package Architecture -- Package Specification -- Prototype Features -- Serially Reusable Precompiler Directive -- Variables -- Types -- Components: Functions and Procedures -- Package Body -- Prototype Features -- Variables -- Types -- Components: Functions and Procedures -- Definer vs. Invoker Rights Mechanics -- Managing Packages in the Database Catalog -- Finding, Validating, and Describing Packages -- Checking Dependencies -- Comparing Validation Methods: Timestamp vs. Signature -- Summary -- Mastery Check -- 10. Large Objects -- Working with Internally Stored LOB Types -- LOB Assignments Under 32K -- LOB Assignments over 32K -- Reading Files into Internally Stored Columns.;Contents note continued: Functions, Procedures, and Packages -- Functions -- Procedures -- Packages -- Transaction Scope -- Single Transaction Scope -- Multiple Transaction Scopes -- Database Triggers -- Summary -- Mastery Check -- 4. Language Fundamentals -- Lexical Units -- Delimiters -- Identifiers -- Literals -- Comments -- Variables and Data Types -- Variable Data Types -- Scalar Data Types -- Large Objects (LOBs) -- Composite Data Types -- System Reference Cursors -- Summary -- Mastery Check -- 5. Control Structures -- Conditional Statements -- IF Statements -- CASE Statements -- Conditional Compilation Statements -- Iterative Statements -- Simple Loop Statements -- FOR Loop Statements -- WHILE Loop Statements -- Cursor Structures -- Implicit Cursors -- Explicit Cursors -- Bulk Statements -- BULK COLLECT INTO Statements -- FORALL Statements -- Supporting Scripts -- Summary -- Mastery Check -- 6. Collections -- Introduction to Collections.;Contents note continued: Type Evolution -- Implementing Object Type Collections -- Declaring Object Type Collections -- Implementing Object Type Collections -- Supporting Scripts -- Summary -- Mastery Check -- 12. Triggers -- Introduction to Triggers -- Database Trigger Architecture -- Data Definition Language Triggers -- Event Attribute Functions -- Building DDL Triggers -- Data Manipulation Language Triggers -- Statement-Level Triggers -- Row-Level Triggers -- Compound Triggers -- INSTEAD OF Triggers -- System and Database Event Triggers -- Trigger Restrictions -- Maximum Trigger Size -- SQL Statements -- LONG and LONG RAW Data Types -- Mutating Tables -- System Triggers -- Supporting Scripts -- Summary -- Mastery Check -- 13. Dynamic SQL -- Dynamic SQL Architecture -- Native Dynamic SQL (NDS) -- Dynamic Statements -- Dynamic Statements with Inputs -- Dynamic Statements with Inputs and Outputs -- Dynamic Statements with an Unknown Number of Inputs -- DBMS_SQL Package.;Contents note continued: LPAD Function -- LTRIM Function -- REPLACE Function -- REVERSE Function -- RPAD Function -- RTRIM Function -- UPPER Function -- Data Type Conversion Functions -- CAST Function -- CONVERT Function -- TO_CHAR Function -- TO-CLOB Function -- TO_DATE Function -- TO_LOB Function -- TO_NCHAR Function -- TO_NCLOB Function -- TO_NUMBER Function -- Date-Time Conversion Functions -- ADD_MONTHS Function -- CURRENT_DATE Function -- CURRENT_TIMESTAMP Function -- DBTIMEZONE Function -- EXTRACT Function -- FROM_TZ Function -- LAST_DAY Function -- LOCALTIMESTAMP Function -- MONTHS_BETWEEN Function -- NEW_TIME Function -- ROUND Function -- SYSDATE Function -- SYSTIMESTAMP Function -- TO_CHAR(Date) Function -- TO_DSINTERVAL Function -- TO_TIMESTAMP Function N -- TO_TLMESTAMP_TZ Function -- TO_YMINTERVAL Function -- TRUNC(Date) Function -- TZ_OFFSET Function -- Collection Management Functions -- CARDINALITY Function -- COLLECT Function -- POWERMULTISET Function.;Contents note continued: DBMS_XPLAN Package -- SQL Tracing -- Tracing Session Statements -- Convert Raw Trace Files to Readable Trace Files -- Summary -- B. SQL Primer -- Oracle SQL Data Types -- Data Definition Language (DDL) -- CREATE Statement -- ALTER Statement -- RENAME Statement -- DROP Statement -- TRUNCATE Statement -- COMMENT Statement -- Data Manipulation Language (DML) -- ACID Compliant Transactions -- INSERT Statement -- UPDATE Statement -- DELETE Statement -- MERGE Statement -- Transaction Control Language (TCL) -- Queries: SELECT Statements -- Queries that Return Columns or Results from Columns -- Queries that Aggregate -- Queries that Return Columns or Results Selectively -- Join Results -- Joins that Splice Together Rows -- Joins that Splice Collections -- Summary -- C. SQL Built-in Functions -- Character Functions -- ASCII Function -- ASCIISTR Function -- CHR Function -- CONCAT Function -- INITCAP Function -- INSTR Function -- LENGTH Function -- LOWER Function.;Contents note continued: Reading Local Files into SLOB or NCLOB Columns -- Reading Local Files into BLOB Columns -- Working with LOBs Through Web Pages -- Working with Binary Files (BFILEs) -- Creating and Using Virtual Directories -- Reading Canonical Path Names and Filenames -- Understanding the DBMS_LOB Package -- Package Constants -- Package Exceptions -- Opening and Closing Methods -- Manipulation Methods -- Introspection Methods -- BFILE Methods -- Temporary LOB Methods -- Security Link Methods -- Supporting Scripts -- The LONG to CLOB Script -- Manage LOBs from the File System -- Manage CLOB and BLOB LOBs Through the Web -- Manage BFILE LOBs Through the Web -- Summary -- Mastery Check -- 11. Object Types -- Object Basics -- Declaring Objects Types -- Implementing Object Bodies -- White Listing Object Types -- Getters and Setters -- Static Member Methods -- Comparing Objects -- Inheritance and Polymorphism -- Declaring Subclasses -- Implementing Subclasses.;Contents note continued: Ability to White List PL/SQL Program Unit Callers -- Native Client API Support for PL/SQL Types -- New PL/SQL Package UTL_CALL_STACK -- DBMS_UTILITY Adds EXPAND_SQL_TEXT Subprogram -- DBMS_SQL Adds a New Formal Schema to the PARSE Procedure -- PL/SQL Functions in SQL WITH Clause -- PL/SQL-Specific Data Types Allowed in SQL -- Implicit REF CURSOR Parameter Binding -- Supporting Scripts -- Summary -- Mastery Check -- 3. PL/SQL Basics -- Block Structure -- Execution Block -- Basic Block Structure -- Declaration Block -- Exception Block -- Behavior of Variables in Blocks -- Anonymous Blocks -- Nested Anonymous Blocks -- Local Named Blocks -- Stored Named Blocks -- Basic Scalar and Composite Data Types -- Scalar Data Types -- Attribute and Table Anchoring -- Composite Data Types -- Control Structures -- Conditional Structures -- Iterative Structures -- Exceptions -- User-Defined Exceptions -- Dynamic User-Defined Exceptions -- Bulk Operations.;"A complete update of the essential desktop reference for PL/SQL, rewritten and expanded by to highlight the massive changes in Oracle Database 12c. Written by an Oracle ACE, Oracle Database 12c PL/SQL Programming offers in-depth information on the entire PL/SQL language and its extensibility to develop applications. With full coverage of the latest features and tools, this Oracle Press guide provides examples of web applications in native PL/SQL, JSP, and PHP. The book explains how to access and modify database information, construct powerful PL/SQL statements, execute effective queries, and deploy security. A Netflix case study example is used throughout the book as the basis for all coding examples. This enables application design consideration, data types, structures, and coding approaches to build an integrated view of the PL/SQL programming language. The examples collectively provide you with both a reference to how various programming concepts work and how components fit together to solve real-world problems"--