Skip Headers
Oracle® Database PL/SQL Language Reference
11
g
Release 2 (11.2)
Part Number E10472-05
Home
Book List
Contents
Index
Master Index
Contact Us
Previous
Next
View PDF
List of Examples
1-1 PL/SQL Block Structure
1-2 Processing Query Result Rows One at a Time
2-1 Delimiter with Embedded Space
2-2 Whitespace Characters Improving Source Code Readability
2-3 Identifiers Not Separated by Space or Punctuation
2-4 Datetime and Interval Literals
2-5 Single-Line Comments
2-6 Multiline Comments
2-7 Variable Declarations
2-8 Constant Declarations
2-9 Variable and Constant Declarations with Initial Values
2-10 Variable Initialized to NULL by Default
2-11 Variable Declaration with NOT NULL Constraint
2-12 Variables Initialized to NULL Values
2-13 Declaring Variable of Same Type as Database Column
2-14 Declaring Variable of Same Type as Another Variable
2-15 Declaring Record that Represents Table Row
2-16 Declaring a Record that Represents a Subset of Table Columns
2-17 Declaring a Record that Represents a Row from a Join
2-18 Scope and Visibility of Identifiers
2-19 Qualifying a Redeclared Global Identifier with a Block Label
2-20 Qualifying an Identifier with a Subprogram Name
2-21 Duplicate Identifiers in Same Scope
2-22 Declaring the Same Identifier in Two Different Units
2-23 Label and Subprogram with Same Name in Same Scope
2-24 Block with Multiple and Duplicate Labels
2-25 Assigning Values to Variables with Assignment Statement
2-26 SELECT INTO Assigns Values to Variables
2-27 Assigning Values to Variables as Parameters of a Subprogram
2-28 Assigning BOOLEAN Values
2-29 Concatenation Operator
2-30 Concatenation Operator with NULL Operands
2-31 Controlling Evaluation Order with Parentheses
2-32 Expression with Nested Parentheses
2-33 Improving Readability with Parentheses
2-34 Operator Precedence
2-35 AND Operator
2-36 OR Operator
2-37 NOT Operator
2-38 NULL Value in Unequal Comparison
2-39 NULL Value in Equal Comparison
2-40 NOT NULL Equals NULL
2-41 Changing Evaluation Order of Logical Operators
2-42 Short-Circuit Evaluation
2-43 Relational Operators in Expressions
2-44 LIKE Operator in Expression
2-45 Escape Character in Pattern
2-46 BETWEEN Operator in Expressions
2-47 IN Operator in Expressions
2-48 IN Operator with Sets with NULL Values
2-49 Equivalent BOOLEAN Expressions as Conditions in Loops
2-50 Simple CASE Expression
2-51 Simple CASE Expression with WHEN NULL
2-52 Searched CASE Expression
2-53 Searched CASE Expression with WHEN condition IS NULL
2-54 Predefined Inquiry Directives $$PLSQL_LINE and $$PLSQL_UNIT
2-55 Displaying Values of PL/SQL Compilation Parameters
2-56 PLSQL_CCFLAGS Assigns Value to Itself
2-57 Static Constants
2-58 Code for Checking Database Version
2-59 Compiling Different Code for Different Database Versions
2-60 Displaying Post-Processed Source Code
3-1 Comparing Two CHAR Values
3-2 Comparing Two VARCHAR2 Values
3-3 Comparing CHAR Value and VARCHAR2 Value
3-4 Assigning a Literal Value to a TIMESTAMP Variable
3-5 SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions
3-6 Assigning a Literal to a TIMESTAMP WITH TIME ZONE Variable
3-7 Correct Assignment to TIMESTAMP WITH LOCAL TIME ZONE
3-8 Incorrect Assignment to TIMESTAMP WITH LOCAL TIME ZONE
3-9 Assigning Literals to an INTERVAL YEAR TO MONTH Variable
3-10 Assigning Literals to an INTERVAL DAY TO SECOND Variable
3-11 Defining Subtypes
3-12 Declaring Variables of User-Defined Subtype
3-13 Ranges with Subtypes
3-14 Type Compatibility with the NUMBER Data Type
3-15 Assigning Initial Value to Subtype Variable
3-16 Subtype Constraints Inherited by Subprograms
3-17 Column Constraints Inherited by Subtypes
3-18 Implicit Conversion
4-1 IF THEN Statement
4-2 IF THEN ELSE Statement
4-3 Nested IF THEN ELSE Statements
4-4 IF THEN ELSIF Statement
4-5 IF THEN ELSIF Statement that Simulates Simple CASE Statement
4-6 Simple CASE Statement
4-7 Searched CASE Statement
4-8 EXCEPTION Instead of ELSE Clause in CASE Statement
4-9 Basic LOOP Statement with EXIT Statement
4-10 Basic LOOP Statement with EXIT WHEN Statement
4-11 Nested, Labeled Basic LOOP Statements with EXIT WHEN Statements
4-12 CONTINUE Statement in Basic LOOP Statement
4-13 CONTINUE WHEN Statement in Basic LOOP Statement
4-14 WHILE LOOP Statements
4-15 FOR LOOP Statements
4-16 Reverse FOR LOOP Statements
4-17 FOR LOOP Statement Tries to Change Index Value
4-18 Statement Outside FOR LOOP Tries to Reference Index
4-19 FOR LOOP Index with Same Name as Declared Variable
4-20 FOR LOOP References Declared Variable with Same Name as Index
4-21 Nested FOR LOOP Statements with Same Index Name
4-22 Several Types of FOR LOOP Bounds
4-23 Changing the Increment of the Counter in a FOR LOOP Statement
4-24 Specifying a LOOP Range at Run Time
4-25 FOR LOOP with Lower Bound > Upper Bound
4-26 EXIT in FOR LOOP
4-27 EXIT with Label in FOR LOOP
4-28 GOTO Statement
4-29 Incorrect Label Placement
4-30 NULL Statement Allows GOTO to Label
4-31 GOTO Statement Transfers Control to Enclosing Block
4-32 GOTO Statement Cannot Transfer Control into IF Statement
4-33 NULL Statement Showing No Action
4-34 NULL Statement as Placeholder During Subprogram Creation
4-35 NULL Statement in WHEN OTHER Clause
5-1 Declaring and Populating Associative Array Indexed by String
5-2 Declaring an Associative Array
5-3 Declaring Nested Tables, Varrays, and Associative Arrays
5-4 Declaring Collections with %TYPE
5-5 Declaring a Procedure Parameter as a Nested Table
5-6 Invoking a Procedure with a Nested Table Parameter
5-7 Specifying Collection Element Types with %TYPE and %ROWTYPE
5-8 VARRAY of Records
5-9 NOT NULL Constraint on Collection Elements
5-10 Constructor for a Nested Table
5-11 Constructor for a Varray
5-12 Collection Constructor Including Null Elements
5-13 Combining Collection Declaration and Constructor
5-14 Empty Varray Constructor
5-15 Referencing a Nested Table Element
5-16 Referencing an Element of an Associative Array
5-17 Data Type Compatibility for Collection Assignment
5-18 Assigning a Null Value to a Nested Table
5-19 Assigning Nested Tables with Set Operators
5-20 Assigning Values to VARRAYs with Complex Data Types
5-21 Assigning Values to Tables with Complex Data Types
5-22 Checking if a Collection Is Null
5-23 Comparing Two Nested Tables
5-24 Comparing Nested Tables with Set Operators
5-25 Multilevel VARRAY
5-26 Multilevel Nested Table
5-27 Multilevel Associative Array
5-28 Checking Whether a Collection Element EXISTS
5-29 Counting Collection Elements with COUNT
5-30 Checking the Maximum Size of a Collection with LIMIT
5-31 FIRST and LAST Methods
5-32 PRIOR and NEXT Methods
5-33 NEXT Method Accesses Elements of Nested Table
5-34 EXTEND Method
5-35 TRIM Method
5-36 TRIM Method on Deleted Elements
5-37 DELETE Method
5-38 Collection Exceptions
5-39 How Invalid Subscripts are Handled with DELETE(n)
5-40 Incompatibility Between Package and Local Collection Types
5-41 Declaring and Initializing a Simple Record Type
5-42 Declaring and Initializing Record Types
5-43 %ROWTYPE in Record Declaration
5-44 Returning a Record from a Function
5-45 Record as Procedure Parameter
5-46 Nested Record Declaration
5-47 Assigning Initial Values to a Record
5-48 Assigning All the Fields of a Record in One Statement
5-49 Assigning a %ROWTYPE Record to a User-Defined Record
5-50 SELECT INTO Assigns Values to Record
5-51 Testing Records for Nullity and Equality
5-52 Initializing a Table by Inserting a Record of Default Values
5-53 Updating Rows with a Record
5-54 RETURNING INTO Clause with Record
5-55 BULK COLLECT with SELECT INTO Statement
6-1 Static SQL Statements
6-2 CURRVAL and NEXTVAL Pseudocolumns
6-3 SQL%FOUND Attribute
6-4 SQL%ROWCOUNT Attribute
6-5 Explicit Cursor Declaration
6-6 Opening an Explicit Cursor
6-7 Fetching with a Cursor
6-8 Referencing PL/SQL Variables in Its Scope
6-9 Fetching the Same Cursor Into Different Variables
6-10 Fetching Bulk Data with a Cursor
6-11 %ISOPEN Attribute
6-12 %FOUND Attribute
6-13 %NOTFOUND Attribute
6-14 %ROWCOUNT Attribute
6-15 Explicit Cursor with Calculated Column that Needs Alias
6-16 Passing Parameters to a Cursor FOR LOOP
6-17 Passing Parameters to Explicit Cursors
6-18 Implicit Cursor FOR Loop
6-19 Explicit Cursor FOR LOOP
6-20 Alias for Expressions in Query
6-21 Subqueries in Cursor Declarations
6-22 Subquery in FROM Clause
6-23 Correlated Subquery
6-24 Cursor Variable Declarations
6-25 Cursor Variables Returning %ROWTYPE Variables
6-26 %ROWTYPE Attribute Provides Data Type
6-27 Cursor Variable Returning a Record Type
6-28 Cursor Variable as Parameter
6-29 Opening Cursor Variable
6-30 Stored Procedure to Open a Cursor Variable
6-31 Stored Procedure to Open Cursor Variables with Different Queries
6-32 Cursor Variable with Different Return Types
6-33 Fetching from Cursor Variable into Record
6-34 Fetching from Cursor Variable into Collections
6-35 Cursor Variable as Host Variable
6-36 Reducing Network Traffic When Passing Host Variables
6-37 Declaration of Cursor Variables in a Package
6-38 Cursor Expression
6-39 COMMIT Statement with COMMENT and WRITE Clauses
6-40 ROLLBACK Statement
6-41 SAVEPOINT and ROLLBACK Statements
6-42 Reusing a SAVEPOINT with ROLLBACK
6-43 SET TRANSACTION Statement in Read-Only Transaction
6-44 FOR UPDATE Cursor in CURRENT OF Clause of UPDATE Statement
6-45 SELECT FOR UPDATE with Multiple Tables
6-46 Trying to Fetch with FOR UPDATE Cursor After COMMIT Statement
6-47 Simulating CURRENT OF Clause with ROWID Pseudocolumn
6-48 Declaring an Autonomous Function in a Package
6-49 Declaring an Autonomous Standalone Procedure
6-50 Declaring an Autonomous PL/SQL Block
6-51 Autonomous Trigger Logs INSERT Statements
6-52 Autonomous Trigger Using Native Dynamic SQL for DDL
6-53 Invoking an Autonomous Function
7-1 Invoking a Subprogram from a Dynamic PL/SQL Block
7-2 Unsupported Data Type in Native Dynamic SQL
7-3 Uninitialized Variable for NULL in USING Clause
7-4 Native Dynamic SQL with OPEN FOR, FETCH, and CLOSE Statements
7-5 Repeated Placeholder Names in Dynamic PL/SQL Block
7-6 Switching from DBMS_SQL Package to Native Dynamic SQL
7-7 Switching from Native Dynamic SQL to DBMS_SQL Package
7-8 Setup for SQL Injection Examples
7-9 Procedure Vulnerable to Statement Modification
7-10 Procedure Vulnerable to Statement Injection
7-11 Procedure Vulnerable to SQL Injection Through Data Type Conversion
7-12 Bind Arguments Guarding Against SQL Injection
7-13 Validation Checks Guarding Against SQL Injection
7-14 Explicit Format Models Guarding Against SQL Injection
8-1 Declaring, Defining, and Invoking a Simple PL/SQL Procedure
8-2 Declaring, Defining, and Invoking a Simple PL/SQL Function
8-3 Creating Nested Subprograms that Invoke Each Other
8-4 Formal Parameters and Actual Parameters
8-5 Avoiding and Causing Implicit Conversion of Actual Parameters
8-6 Subprogram Parameter Mode OUT
8-7 Procedure with Initial Parameter Values
8-8 Formal Parameter with Expression as Initial Value
8-9 Subprogram Calls Using Positional, Named, and Mixed Notation
8-10 Overloaded Subprogram
8-11 Overload Error That Causes Compile-Time Error
8-12 Overload Error That Compiles Successfully
8-13 Invocation of Improperly Overloaded Subprogram
8-14 Properly Overloaded Subprogram
8-15 Invocation of Properly Overloaded Subprogram
8-16 Package Specification Without Overload Errors
8-17 Improper Invocation of Properly Overloaded Subprogram
8-18 Resolving PL/SQL Procedure Names
8-19 Creating an ADT with AUTHID CURRENT USER
8-20 Invoking an IR Instance Method
8-21 Invoking an External Procedure from PL/SQL
8-22 Invoking a Java Function from PL/SQL
8-23 Aliasing from Passing Global Variable with NOCOPY Hint
8-24 Aliasing Passing Same Parameter Multiple Times
8-25 Aliasing from Assigning Cursor Variables to Same Work Area
8-26 Declaration and Definition of Result-Cached Function
8-27 Result-Cached Function that Returns Configuration Parameter Setting
8-28 Function that Depends on Session-Specific Settings
8-29 Result-Cached Function that Depends on Session-Specific Application Context
8-30 Caching One Name at a Time (Finer Granularity)
8-31 Caching Translated Names One Language at a Time (Coarser Granularity)
9-1 CREATE TRIGGER Statement
9-2 INSTEAD OF Trigger
9-3 FOR EACH ROW Trigger
9-4 Compound Trigger
9-5 Compound Trigger Records Changes to One Table in Another Table
9-6 Compound Trigger for Avoiding Mutating-Table Error
9-7 Trigger for Monitoring Logons
9-8 Trigger That Invokes Java Subprogram
9-9 Trigger that Modifies LOB Columns
9-10 REFERENCING Option
9-11 Trigger with OBJECT_VALUE Pseudocolumn
9-12 Remote Exception Handling Failure
9-13 Workaround for Example 9-12
9-14 Row-Level Trigger Causes Mutating-Table Error
9-15 Statement-Level Trigger Avoids Mutating-Table Error
9-16 Update Cascade
9-17 Viewing Information About Triggers
9-18 Foreign Key Trigger for Child Table
9-19 UPDATE and DELETE RESTRICT Trigger for Parent Table
9-20 UPDATE and DELETE SET NULL Triggers for Parent Table
9-21 DELETE Cascade Trigger for Parent Table
9-22 UPDATE Cascade Trigger for Parent Table
9-23 Trigger for Complex Check Constraints
9-24 Trigger for Enforcing Security
9-25 Trigger That Derives New Column Values for Table
10-1 Serially Reusable Package Specification
10-2 Serially Reusable Package Specification and Package Body
10-3 Open Cursors in Serially Reusable Packages at Call Boundaries
10-4 Simple Package Specification Without Body
10-5 Matching Package Specification and Body
10-6 Creating emp_admin Package
10-7 DBMS_OUTPUT.PUT_LINE Procedure
10-8 Separating Cursor Specifications with Packages
10-9 Referencing Packaged Cursor
11-1 Setting Value of PLSQL_WARNINGS Compilation Parameter
11-2 DBMS_WARNING Package
11-3 Anonymous Block with Exception Handlers
11-4 Avoiding the Exception that Example 11-3 Handles
11-5 Managing Multiple Errors with a Single Exception Handler
11-6 Scope of Exceptions
11-7 PRAGMA EXCEPTION_INIT
11-8 RAISE_APPLICATION_ERROR Procedure
11-9 Raising a User-Defined Exception
11-10 Raising a Predefined Exception
11-11 Scope of an Exception
11-12 Reraising an Exception
11-13 Raising an Exception in a Declaration
11-14 Displaying SQLCODE and SQLERRM
11-15 Exception Handler
11-16 Continuing After an Exception
11-17 Retrying a Transaction After an Exception
11-18 Identifying Exception Locations with Locator Variables
12-1 Specifying that a Subprogram Is To Be Inlined
12-2 Specifying that an Overloaded Subprogram Is To Be Inlined
12-3 Specifying that a Subprogram Is Not To Be Inlined
12-4 Applying Two INLINE Pragmas to the Same Subprogram
12-5 Nesting a Query to Improve Performance
12-6 Issuing DELETE Statements in a Loop
12-7 Issuing INSERT Statements in a Loop
12-8 FORALL Statement for Part of Collection
12-9 FORALL Statement for Nonconsecutive Index Values
12-10 Rollbacks with FORALL Statement
12-11 FORALL Statement and SQL%BULK_EXCEPTIONS
12-12 FORALL Statement and SQL%BULK_ROWCOUNT
12-13 Counting Rows Affected by FORALL with SQL%BULK_ROWCOUNT
12-14 Retrieving Query Results with BULK COLLECT
12-15 Limiting Query Results with Pseudocolumn ROWNUM
12-16 Bulk-Fetching from a Cursor Into One or More Collections
12-17 Bulk-Fetching from a Cursor Into a Collection of Records
12-18 Controlling Number of BULK COLLECT Rows with LIMIT
12-19 BULK COLLECT with RETURNING INTO Clause
12-20 FORALL with BULK COLLECT
12-21 SELECT BULK COLLECT INTO Statement with Unexpected Results
12-22 Cursor Workaround for Example 12-21
12-23 Second Collection Workaround for Example 12-21
12-24 Associating a Cursor with a Dynamic SELECT Statement
12-25 NOCOPY with Parameters
12-26 Assigning the Result of a Table Function
12-27 Pipelined Table Function for Transformation
12-28 Function with Two Cursor Variable Parameters
12-29 Pipelined Table Function as Aggregate Function
A-1 Wrapping Package with DBMS_DDL.CREATE_WRAPPED Procedure
B-1 Resolving Global and Local Variable Names
B-2 Block Label for Name Resolution
B-3 Subprogram Name for Name Resolution
B-4 Dot Notation for Qualifying Names
Scripting on this page enhances content navigation, but does not change the content in any way.