Learn Oracle PL/SQL day to day topics

Learn Oracle PL/SQL day to day topics

• Oracle Supplied Packages
Table Storage Statistics
DBMS_STATS
Copying Statistics to Different Databases
DBMS_ALERT Procedures
DBMS_AQ and DBMS_AQADM
DBMS_DDL
DBMS_DEBUG
DBMS_PIPE
DBMS_OUTPUT
DBMS_LOGMNR
DBMS_TRACE
DBMS_SQL
DBMS_STANDARD
DBMS_SESSION
DBMS_LOB
DBMS_JOB
The Run() Procedure
DBMS_LOCK
UTL Packages

• Advanced Features of Procedures and Functions
Autonomous Transactions
Executing Autonomous Transactions
Advantages and Restrictions
Defining an Autonomous Block
Packages and Triggers
Autonomous Triggers
Autonomous PL/SQL Blocks
Transaction Visibility
Definer's and Invoker's Rights
Using the AUTHID Clause
Resolving External References
PL/SQL Wrappers
External Procedures
Alias Library Object
Call Specifications
Mapping Procedure to an External Procedure
Mapping Function to an External Procedure

• Introduction to Object Technology
Software Development
Issues with 3GL Development
Issues with Relational Database Development
Object-Relational Development
Goals of Object-Relational Development
Functional versus Object Hierarchies
Software Evolution
Type Table
Type and Class Definitions
What is an Object?
Creating a User Defined Type
Rules on Working with User Defined Types
Defining an Abstract Data Type
Inheritance
Single versus Multiple Inheritance
Polymorphism
With and Without Polymorphism
Different Object Methodologies
What is UML?
Visual Models
Attributes
Different Types of Methods

• Object-Relational Concepts
Object-Relational Databases
Object-Relational Features
Oracle Object-Relational Features
Oracle Datatypes
Abstract Datatypes
Data Dictionary Views
The CREATE TYPE Command
Object Tables
Type Methods
The Constructor Method
Working with User Defined Datatypes
Modifying Data in Object Tables
Working with Object Tables
Object Identifiers
Defining a REF
Updating REF Values
INSERTING REF Values
Dangling REFS
The DEREF Operator
ROWIDs
DBMS_ROWID Package
Different Types of Rowids
Object Methods
Different Types of Methods
Create Type Body Command
Executing Object Methods
Differences between Packages and Objects
DDL Type Commands
MAP Method
ORDER Method
MAP versus ORDER Method
STATIC Methods
Objects and Nulls
LOBS

• Collections
Records
Creating a PL/SQL Record
PL/SQL2 Tables
Table Attributes
PL/SQL8 Tables
Create a Type Specification
CAST Operator
Operator "THE"
Queries on Nested Tables
Dynamic SQL
Executing Dynamic SQL Statements in PL/SQL
Advantages of Dynamic SQL
Advantages of DBMS_SQL
DBMS_SQL Package
DBMS_SQL Example
Advantages of Native Dynamic SQL in PL/SQL
Syntax: EXECUTE IMMEDIATE Statement
Bind Variables
Defining Modes for Bind Variables
Executing DML, DCL, and DDL Statements
Executing Dynamic Multi-row Queries
Using Schema objects with Dynamic SQL
Cursor Attributes
Assigning Nulls to Bind Variables
Issues When Executing Subprograms
Debugging
Problem Analysis
Handling Exceptions
Continuing After an Exception
Using an Inner Block to Continue after an Exception
Inner Block Approach
Function Approach
Locator Variables
Temporary Tables
Storing Debugging Information
The DBMS_TRACE Package
DBMS_TRACE Constants
Managing the Volume of Tracing Information
The DBMS_DEBUG Package
Procedures in DBMS_DEBUG
Setting up a Debug Session
Performance Tuning
Working with Object Types and Collections
Bulk Binds
Bulk Collect Into Clause
Use the RETURNING Clause
NOCOPY Hint
Memory and PL/SQL
Pinning PL/SQL Programs
KEEP Procedure
What Do I to PIN?
Relative Sizes of Code to Pin
Serially Reusable Packages
Optimizing Integer Operations
Avoid NOT NULL Constraints
Rephrase Conditional Control Statements
Additional Considerations
DBMS_PROFILER
Profiler Tables
Table PLSQL_PROFILER_DATA
Additional Object-Relational Features
Type Evolution
Inheritance
Super and Subtypes
Restrictions on Overriding
Dynamic Method Dispatch
Substitutability
The TREAT Function
NOT INSTANTIABLE
Object View Hierarchies
IS OF Operator
The SYS_TYPEID Function
Object Support in Programmatic Environments
Advanced PL/SQL (I)
CASE Expressions and Statements
Searched CASE expression
The WHEN Clause
CASE Statement
Searched CASE statement
TABLE FUNCTIONS
Pipelined versus Non-pipelined
New SQL Datatypes
Performing DML Operations Inside Table Functions
Table Function with Autonomous Block
Parallelizing Table Functions
Enhancements to Bulk Operations
Bulk FETCH
Bulk FORALL with EXECUTE IMMEDIATE
Errors in DML Operation
ADVANCED QUEUEING - New Features
Java Message Service Enhancements
Advanced PL/SQL (II) and XML
Native Compilation of PL/SQL Code
Better Integration of LOB Datatypes
Multilevel Collections
MERGE STATEMENT
New PL/SQL Supplied Packages
Cursor Subquery
XML Data Type
INSERTING INTO AN XML COLUMN
Retrieving an XML Column
Updating an XML column
XMLTYPE FUNCTIONS
Common Constructs used in Xpath Expressions
DBMS_XMLGEN
SYS_XMLGEN
SYS_XMLAGG
Accessing Metadata
The DBMS_METADATA Package
DBMS_METADATA Object Types
Customizing DDL Output
GET_XML AND GET_DDL