Course (3-3) Oracle PL/SQL Part 2

zoom

pre 1 of 1 next

Location: New York - Map

Date posted: June 21, 2013

Price: Please contact

Ad ID: 91756

Views: 18

Reply by email

close X
  • 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
  • 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
  • 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
  • 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
  • 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)
  • 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
  • 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
  • 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

Online LIVE Training Programming Course offered by INCAPROG ONLINE - www.incaprog.com - contacto@incaprog.com - Phone : (954) 727-3141

  • Share this ad: pictute pictute pictute pictute
  • Print: pictute
  • Add to favorites: pictute
  • Report: pictute