Course (3-3) Oracle PL/SQL Part 2
- 15. Data Retrieval
- 15.1. Cursor Basics
- 15.1.1. Some Data Retrieval Terms
- 15.1.2. Typical Query Operations
- 15.1.3. Introduction to Cursor Attributes
- 15.1.4. Referencing PL/SQL Variables in a Cursor
- 15.1.5. Choosing Between Explicit and Implicit Cursors
- 15.2. Working with Implicit Cursors
- 15.2.1. Implicit Cursor Examples
- 15.2.2. Error Handling with Implicit Cursors
- 15.2.3. Implicit SQL Cursor Attributes
- 15.3. Working with Explicit Cursors
- 15.3.1. Declaring Explicit Cursors
- 15.3.2. Opening Explicit Cursors
- 15.3.3. Fetching from Explicit Cursors
- 15.3.4. Column Aliases in Explicit Cursors
- 15.3.5. Closing Explicit Cursors
- 15.3.6. Explicit Cursor Attributes
- 15.3.7. Cursor Parameters
- 15.4. SELECT...FOR UPDATE
- 15.4.1. Releasing Locks with COMMIT
- 15.4.2. The WHERE CURRENT OF Clause
- 15.5. Cursor Variables and REF CURSORs
- 15.5.1. Why Cursor Variables?
- 15.5.2. Similarities to Static Cursors
- 15.5.3. Declaring REF CURSOR Types
- 15.5.4. Declaring Cursor Variables
- 15.5.5. Opening Cursor Variables
- 15.5.6. Fetching from Cursor Variables
- 15.5.7. Rules for Cursor Variables
- 15.5.8. Passing Cursor Variables as Arguments
- 15.5.9. Cursor Variable Restrictions
- 15.6. Cursor Expressions
- 15.6.1. Using Cursor Expressions
- 15.6.2. Restrictions on Cursor Expressions
- 15.1. Cursor Basics
- 16. Dynamic SQL and Dynamic PL/SQL
- 16.1. NDS Statements
- 16.1.1. The EXECUTE IMMEDIATE Statement
- 16.1.2. The OPEN FOR Statement
- 16.1.3. About the Four Dynamic SQL Methods
- 16.2. Binding Variables
- 16.2.1. Argument Modes
- 16.2.2. Duplicate Placeholders
- 16.2.3. Passing NULL Values
- 16.3. Working with Objects and Collections
- 16.4. Dynamic PL/SQL
- 16.4.1. Build Dynamic PL/SQL Blocks
- 16.4.2. Replace Repetitive Code with Dynamic Blocks
- 16.5. Recommendations for NDS
- 16.5.1. Use Invoker Rights for Shared Programs
- 16.5.2. Anticipate and Handle Dynamic Errors
- 16.5.3. Use Binding Rather Than Concatenation
- 16.5.4. Minimize the Dangers of Code Injection
- 16.6. When to Use DBMS_SQL
- 16.6.1. Parse Very Long Strings
- 16.6.2. Obtain Information About Query Columns
- 16.6.3. Meet Method 4 Dynamic SQL Requirements
- 16.6.4. Minimize Parsing of Dynamic Cursors
- 16.7. Oracle Database 11g New Features
- 16.7.1. DBMS_SQL.TO_REFCURSOR Function
- 16.7.2. DBMS_SQL.TO_CURSOR Function
- 16.7.3. Enhanced Security for DBMS_SQL
- 16.1. NDS Statements
- 17. Procedures, Functions, and Parameters
- 17.1. Modular Code
- 17.2. Procedures
- 17.2.1. Calling a Procedure
- 17.2.2. The Procedure Header
- 17.2.3. The Procedure Body
- 17.2.4. The END Label
- 17.2.5. The RETURN Statement
- 17.3. Functions
- 17.3.1. Structure of a Function
- 17.3.2. The RETURN Datatype
- 17.3.3. The END Label
- 17.3.4. Calling a Function
- 17.3.5. Functions Without Parameters
- 17.3.6. The Function Header
- 17.3.7. The Function Body
- 17.3.8. The RETURN Statement
- 17.4. Parameters
- 17.4.1. Defining Parameters
- 17.4.2. Actual and Formal Parameters
- 17.4.3. Parameter Modes
- 17.4.4. Explicit Association of Actual and Formal Parameters in PL/SQL
- 17.4.5. The NOCOPY Parameter Mode Qualifier
- 17.4.6. Default Values
- 17.5. Local or Nested Modules
- 17.5.1. Benefits of Local Modularization
- 17.5.2. Scope of Local Modules
- 17.5.3. Sprucing Up Your Code with Local Modules
- 17.6. Module Overloading
- 17.6.1. Benefits of Overloading
- 17.6.2. Restrictions on Overloading
- 17.6.3. Overloading with Numeric Types
- 17.7. Forward Declarations
- 17.8. Advanced Topics
- 17.8.1. Calling Your Function From Inside SQL
- 17.8.2. Table Functions
- 17.8.3. Deterministic Functions
- 17.9. Go Forth and Modularize!
- 18. Packages
- 18.1. Why Packages?
- 18.1.1. Demonstrating the Power of the Package
- 18.1.2. Some Package-Related Concepts
- 18.1.3. Diagramming Privacy
- 18.2. Rules for Building Packages
- 18.2.1. The Package Specification
- 18.2.2. The Package Body
- 18.2.3. Initializing Packages
- 18.3. Rules for Calling Packaged Elements
- 18.4. Working with Package Data
- 18.4.1. Global Within a Single Oracle Session
- 18.4.2. Global Public Data
- 18.4.3. Packaged Cursors
- 18.4.4. Serializable Packages
- 18.5. When to Use Packages
- 18.5.1. Encapsulate Data Access
- 18.5.2. Avoid Hardcoding Literals
- 18.5.3. Improve Usability of Built-in Features
- 18.5.4. Group Together Logically Related Functionality
- 18.5.5. Cache Static Session Data
- 18.6. Packages and Object Types
- 18.1. Why Packages?
- 19. Triggers
- 19.1. DML Triggers
- 19.1.1. DML Trigger Concepts
- 19.1.2. Creating a DML Trigger
- 19.1.3. DML Trigger Example: No Cheating Allowed!
- 19.1.4. Multiple Triggers of the Same Type
- 19.1.5. Who Follows Whom
- 19.1.6. Mutating Table Errors
- 19.1.7. Compound Triggers: Putting It All In One Place
- 19.2. DDL Triggers
- 19.2.1. Creating a DDL Trigger
- 19.2.2. Available Events
- 19.2.3. Available Attributes
- 19.2.4. Working with Events and Attributes
- 19.2.5. Dropping the Undroppable
- 19.2.6. The INSTEAD OF CREATE Trigger
- 19.3. Database Event Triggers
- 19.3.1. Creating a Database Event Trigger
- 19.3.2. The STARTUP Trigger
- 19.3.3. The SHUTDOWN Trigger
- 19.3.4. The LOGON Trigger
- 19.3.5. The LOGOFF Trigger
- 19.3.6. The SERVERERROR Trigger
- 19.4. INSTEAD OF Triggers
- 19.4.1. Creating an INSTEAD OF Trigger
- 19.4.2. The INSTEAD OF INSERT Trigger
- 19.4.3. The INSTEAD OF UPDATE Trigger
- 19.4.4. The INSTEAD OF DELETE Trigger
- 19.4.5. Populating the Tables
- 19.4.6. INSTEAD OF Triggers on Nested Tables
- 19.5. AFTER SUSPEND Triggers
- 19.5.1. Setting Up for the AFTER SUSPEND Trigger
- 19.5.2. Looking at the Actual Trigger
- 19.5.3. The ORA_SPACE_ERROR_INFO Function
- 19.5.4. The DBMS_RESUMABLE Package
- 19.5.5. Trapped Multiple Times
- 19.5.6. To Fix or Not to Fix?
- 19.6. Maintaining Triggers
- 19.6.1. Disabling, Enabling, and Dropping Triggers
- 19.6.2. Creating Disabled Triggers
- 19.6.3. Viewing Triggers
- 19.6.4. Checking the Validity of Triggers
- 19.1. DML Triggers
- 20. Managing PL/SQL Code
- 20.1. Managing Code in the Database
- 20.1.1. Overview of Data Dictionary Views
- 20.1.2. Display Information About Stored Objects
- 20.1.3. Display and Search Source Code
- 20.1.4. Use Program Size to Determine Pinning Requirements
- 20.1.5. Obtain Properties of Stored Code
- 20.1.6. Analyze and Modify Trigger State Through Views
- 20.1.7. Analyze Argument Information
- 20.1.8. Analyze Identifier Usage (Oracle Database 11g’s PL/Scope)
- 20.2. Managing Dependencies and Recompiling Code
- 20.2.1. Analyzing Dependencies with Data Dictionary Views
- 20.2.2. Fine-Grained Dependency (Oracle Database 11g)
- 20.2.3. Remote Dependencies
- 20.2.4. Limitations of Oracle’s Remote Invocation Model
- 20.2.5. Recompiling Invalid Program Units
- 20.3. Compile-Time Warnings
- 20.3.1. A Quick Example
- 20.3.2. Enabling Compile-Time Warnings
- 20.3.3. Some Handy Warnings
- 20.4. Testing PL/SQL Programs
- 20.4.1. Typical, Tawdry Testing Techniques
- 20.4.2. General Advice for Testing PL/SQL Code
- 20.4.3. Automated Testing Options for PL/SQL
- 20.4.4. Testing with utPLSQL
- 20.4.5. Testing with Quest Code Tester for Oracle
- 20.5. Tracing PL/SQL Execution
- 20.5.1. DBMS_APPLICATION_INFO
- 20.5.2. Quest Error Manager Tracing
- 20.5.3. The DBMS_TRACE Facility
- 20.6. Debugging PL/SQL Programs
- 20.6.1. The Wrong Way to Debug
- 20.6.2. Debugging Tips and Strategies
- 20.7. Protecting Stored Code
- 20.7.1. Restrictions on and Limitations of Wrapping
- 20.7.2. Using the Wrap Executable
- 20.7.3. Dynamic Wrapping with DBMS_DDL
- 20.7.4. Guidelines for Working with Wrapped Code
- 20.8. Introduction to Edition-Based Redefinition (Oracle Database 11g Release 2)
- 20.1. Managing Code in the Database
- 21. Optimizing PL/SQL Performance
- 21.1. Tools to Assist in Optimization
- 21.1.1. Analyzing Memory Usage
- 21.1.2. Identifying Bottlenecks in PL/SQL Code
- 21.1.3. Calculating Elapsed Time
- 21.1.4. Choosing the Fastest Program
- 21.1.5. Avoiding Infinite Loops
- 21.1.6. Performance-Related Warnings
- 21.2. The Optimizing Compiler
- 21.2.1. Insights on How the Optimizer Works
- 21.2.2. Runtime Optimization of Fetch Loops
- 21.3. Data Caching Techniques
- 21.3.1. Package-Based Caching
- 21.3.2. Deterministic Function Caching
- 21.3.3. Function Result Cache (Oracle Database 11g)
- 21.3.4. Caching Summary
- 21.4. Bulk Processing for Multirow SQL
- 21.4.1. High Speed Querying with BULK COLLECT
- 21.4.2. High Speed DML with FORALL
- 21.5. Improving Performance With Pipelined Table Functions
- 21.5.1. Replacing Row-Based Inserts with Pipelined Function-Based Loads
- 21.5.2. Tuning Merge Operations with Pipelined Functions
- 21.5.3. Asynchronous Data Unloading with Parallel Pipelined Functions
- 21.5.4. Performance Implications of Partitioning and Streaming Clauses in Parallel Pipelined Functions
- 21.5.5. Pipelined Functions and the Cost-Based Optimizer
- 21.5.6. Tuning Complex Data Loads with Pipelined Functions
- 21.5.7. A Final Word on Pipelined Functions
- 21.6. Specialized Optimization Techniques
- 21.6.1. Using the NOCOPY Parameter Mode Hint
- 21.6.2. Using the Right Datatype
- 21.7. Stepping Back for the Big Picture on Performance
- 21.1. Tools to Assist in Optimization
- 22. I/O and PL/SQL
- 22.1. Displaying Information
- 22.1.1. Enabling DBMS_OUTPUT
- 22.1.2. Write Lines to the Buffer
- 22.1.3. Read the Contents of the Buffer
- 22.2. Reading and Writing Files
- 22.2.1. The UTL_FILE_DIR Parameter
- 22.2.2. Work with Oracle Directories
- 22.2.3. Open Files
- 22.2.4. Is the File Already Open?
- 22.2.5. Close Files
- 22.2.6. Read from Files
- 22.2.7. Write to Files
- 22.2.8. Copy Files
- 22.2.9. Delete Files
- 22.2.10. Rename and Move Files
- 22.2.11. Retrieve File Attributes
- 22.3. Sending Email
- 22.3.1. Oracle Prerequisites
- 22.3.2. Configuring Network Security
- 22.3.3. Send a Short (32,767 or Less) Plaintext Message
- 22.3.4. Include “Friendly” Names in Email Addresses
- 22.3.5. Send a Plaintext Message of Arbitrary Length
- 22.3.6. Send a Message with a Short (< 32,767) Attachment
- 22.3.7. Send a Small File (< 32767) as an Attachment
- 22.3.8. Attach a File of Arbitrary Size
- 22.4. Working with Web-Based Data (HTTP)
- 22.4.1. Retrieve a Web Page in “Pieces”
- 22.4.2. Retrieve a Web Page into a LOB
- 22.4.3. Authenticate Using HTTP Username/Password
- 22.4.4. Retrieve an SSL-Encrypted Web Page (Via HTTPS)
- 22.4.5. Submit Data to a Web Page via GET or POST
- 22.4.6. Disable Cookies or Make Cookies Persistent
- 22.4.7. Retrieve Data from an FTP Server
- 22.4.8. Use a Proxy Server
- 22.5. Other Types of I/O Available in PL/SQL
- 22.5.1. Database Pipes, Queues, and Alerts
- 22.5.2. TCP Sockets
- 22.5.3. Oracle’s Built-in Web Server
- 22.1. Displaying Information
- 23. Application Security and PL/SQL
- 23.1. Security Overview
- 23.2. Encryption
- 23.2.1. Key Length
- 23.2.2. Algorithms
- 23.2.3. Padding and Chaining
- 23.2.4. The DBMS_CRYPTO Package
- 23.2.5. Encrypting Data
- 23.2.6. Encrypting LOBs
- 23.2.7. SecureFiles
- 23.2.8. Decrypting Data
- 23.2.9. Performing Key Generation
- 23.2.10. Performing Key Management
- 23.2.11. Cryptographic Hashing
- 23.2.12. Using Message Authentication Codes
- 23.2.13. Using Transparent Data Encryption (TDE)
- 23.2.14. Transparent Tablespace Encryption
- 23.3. Row-Level Security
- 23.3.1. Why Learn About RLS?
- 23.3.2. A Simple RLS Example
- 23.3.3. Using Dynamic Policies
- 23.3.4. Using Column-Sensitive RLS
- 23.3.5. RLS Debugging
- 23.4. Application Contexts
- 23.4.1. Using Application Contexts
- 23.4.2. Security in Contexts
- 23.4.3. Contexts as Predicates in RLS
- 23.4.4. Identifying Non-Database Users
- 23.5. Fine-Grained Auditing
- 23.5.1. Why Learn About FGA?
- 23.5.2. A Simple FGA Example
- 23.5.3. Access How Many Columns?
- 23.5.4. Checking the Audit Trail
- 23.5.5. Using Bind Variables
- 23.5.6. Using Handler Modules
- 24. PL/SQL Architecture
- 24.1. Who (or What) is DIANA?
- 24.2. How Does Oracle Execute PL/SQL Code?
- 24.2.1. An Example
- 24.2.2. Compiler Limits
- 24.3. The Default Packages of PL/SQL
- 24.4. Execution Authority Models
- 24.4.1. The Definer Rights Model
- 24.4.2. The Invoker Rights Model
- 24.4.3. Combining Rights Models
- 24.5. Conditional Compilation
- 24.5.1. Examples of Conditional Compilation
- 24.5.2. The Inquiry Directive
- 24.5.3. The $IF Directive
- 24.5.4. The $ERROR Directive
- 24.5.5. Synchronizing Code with Packaged Constants
- 24.5.6. Program-Specific Settings with Inquiry Directives
- 24.5.7. Working with Postprocessed Code
- 24.6. PL/SQL and Database Instance Memory
- 24.6.1. PGA, UGA, and CGA
- 24.6.2. Cursors, Memory, and More
- 24.6.3. Tips on Reducing Memory Use
- 24.6.4. What to Do if You Run Out of Memory
- 24.7. Native Compilation
- 24.7.1. When to Run Interpreted Mode
- 24.7.2. When to Go Native
- 24.7.3. Native Compilation and Database Release
- 24.8. What You Need to Know
- 25. Globalization and Localization in PL/SQL
- 25.1. Overview and Terminology
- 25.2. Unicode Primer
- 25.2.1. National Character Set Datatypes
- 25.2.2. Character Encoding
- 25.2.3. Globalization Support Parameters
- 25.2.4. Unicode Functions
- 25.3. Character Semantics
- 25.4. String Sort Order
- 25.4.1. Binary Sort
- 25.4.2. Monolingual Sort
- 25.4.3. Multilingual Sort
- 25.5. Multilingual Information Retrieval
- 25.5.1. IR and PL/SQL
- 25.6. Date/Time
- 25.6.1. Timestamp Datatypes
- 25.6.2. Date/Time Formatting
- 25.7. Currency Conversion
- 25.8. Globalization Development Kit for PL/SQL
- 25.8.1. UTL_118N Utility Package
- 25.8.2. UTL_LMS Error-Handling Package
- 25.8.3. GDK Implementation Options
- 26. Object-Oriented Aspects of PL/SQL
- 26.1. Introduction to Oracle’s Object Features
- 26.2. Object Types by Example
- 26.2.1. Creating a Base Type
- 26.2.2. Creating a Subtype
- 26.2.3. Methods
- 26.2.4. Invoking Supertype Methods in Oracle Database 11g
- 26.2.5. Storing, Retrieving, and Using Persistent Objects
- 26.2.6. Evolution and Creation
- 26.2.7. Back to Pointers?
- 26.2.8. Generic Data: The ANY Types
- 26.2.9. I Can Do It Myself
- 26.2.10. Comparing Objects
- 26.3. Object Views
- 26.3.1. A Sample Relational System
- 26.3.2. Object View with a Collection Attribute
- 26.3.3. Object Subview
- 26.3.4. Object View with Inverse Relationship
- 26.3.5. INSTEAD OF Triggers
- 26.3.6. Differences Between Object Views and Object Tables
- 26.4. Maintaining Object Types and Object Views
- 26.4.1. Data Dictionary
- 26.4.2. Privileges
- 26.5. Concluding Thoughts from a (Mostly) Relational Developer
- 27. Calling Java from PL/SQL
- 27.1. Oracle and Java
- 27.2. Getting Ready to Use Java in Oracle
- 27.2.1. Installing Java
- 27.2.2. Building and Compiling Your Java Code
- 27.2.3. Setting Permissions for Java Development and Execution
- 27.3. A Simple Demonstration
- 27.3.1. Finding the Java Functionality
- 27.3.2. Building a Custom Java Class
- 27.3.3. Compiling and Loading into Oracle
- 27.3.4. Building a PL/SQL Wrapper
- 27.3.5. Deleting Files from PL/SQL
- 27.4. Using loadjava
- 27.5. Using dropjava
- 27.6. Managing Java in the Database
- 27.6.1. The Java Namespace in Oracle
- 27.6.2. Examining Loaded Java Elements
- 27.7. Using DBMS_JAVA
- 27.7.1. LONGNAME: Converting Java Long Names
- 27.7.2. GET_, SET_, and RESET_COMPILER_OPTION: Getting and Setting (a Few) Compiler Options
- 27.7.3. SET_OUTPUT: Enabling Output from Java
- 27.7.5. EXPORT_SOURCE, EXPORT_RESOURCE, and EXPORT_CLASS: Exporting Schema Objects
- 27.8. Publishing and Using Java in PL/SQL
- 27.8.1. Call Specs
- 27.8.2. Some Rules for Call Specs
- 27.8.3. Mapping Datatypes
- 27.8.4. Calling a Java Method in SQL
- 27.8.5. Exception Handling with Java
- 27.8.6. Extending File I/O Capabilities
- 27.8.7. Other Examples
- 28. External Procedures
- 28.1. Introduction to External Procedures
- 28.1.1. Example: Invoking an Operating System Command
- 28.1.2. Architecture of External Procedures
- 28.2. The Oracle Net Configuration
- 28.2.1. Specifying the Listener Configuration
- 28.2.2. Security Characteristics of the Configuration
- 28.3. Setting Up Multithreaded Mode
- 28.4. Creating an Oracle Library
- 28.5. Writing the Call Specification
- 28.5.1. The Call Spec: Overall Syntax
- 28.5.2. Parameter Mapping: The Example Revisited
- 28.5.3. Parameter Mapping: The Full Story
- 28.5.4. More Syntax: The PARAMETERS Clause
- 28.5.5. PARAMETERS Properties
- 28.6. Raising an Exception from the Called C Program
- 28.7. Nondefault Agents
- 28.8. Maintaining External Procedures
- 28.8.1. Dropping Libraries
- 28.8.2. Data Dictionary
- 28.8.3. Rules and Warnings
- 28.1. Introduction to External Procedures
Online LIVE Training Programming Course offered by INCAPROG ONLINE - www.incaprog.com - contacto@incaprog.com - Phone : (954) 727-3141