Go to main content
2/89
List of Examples
1-1 PL/SQL Block Structure
1-2 PL/SQL Variable Declarations
1-3 Assigning Values to Variables with the Assignment Operator
1-4 Using SELECT INTO to Assign Values to Variables
1-5 Assigning Values to Variables as Parameters of a Subprogram
1-6 Using %ROWTYPE with an Explicit Cursor
1-7 Using a PL/SQL Collection Type
1-8 Declaring a Record Type
1-9 Defining an Object Type
1-10 Using the IF-THEN-ELSE and CASE Statement for Conditional Control
1-11 Using the FOR-LOOP
1-12 Using WHILE-LOOP for Control
1-13 Using the EXIT-WHEN Statement
1-14 Using the GOTO Statement
1-15 PL/SQL Procedure
1-16 Creating a Standalone PL/SQL Procedure
1-17 Invoking a Standalone Procedure from SQL*Plus
1-18 Creating a Trigger
1-19 Creating a Package and Package Body
1-20 Invoking a Procedure in a Package
1-21 Processing Query Results in a LOOP
2-1 NUMBER Literals
2-2 Using BINARY_FLOAT and BINARY_DOUBLE
2-3 Using DateTime Literals
2-4 Single-Line Comments
2-5 Multiline Comment
2-6 Declaring Variables
2-7 Declaring Constants
2-8 Assigning Default Values to Variables with DEFAULT Keyword
2-9 Declaring Variables with NOT NULL Constraint
2-10 Using %TYPE to Declare Variables of the Types of Other Variables
2-11 Using %TYPE Incorrectly with NOT NULL Referenced Type
2-12 Using %TYPE Correctly with NOT NULL Referenced Type
2-13 Using %TYPE to Declare Variables of the Types of Table Columns
2-14 Using %ROWTYPE to Declare a Record that Represents a Table Row
2-15 Declaring a Record that Represents a Subset of Table Columns
2-16 Declaring a Record that Represents a Row from a Join
2-17 Assigning One Record to Another, Correctly and Incorrectly
2-18 Using SELECT INTO for Aggregate Assignment
2-19 Using an Alias for an Expression Associated with %ROWTYPE
2-20 Duplicate Identifiers in Same Scope
2-21 Case Insensitivity of Identifiers
2-22 Using a Block Label for Name Resolution
2-23 Using a Subprogram Name for Name Resolution
2-24 Scope and Visibility of Identifiers
2-25 Qualifying a Redeclared Global Identifier with a Block Label
2-26 Qualifying an Identifier with a Subprogram Name
2-27 Label and Subprogram with Same Name in Same Scope
2-28 Block with Multiple and Duplicate Labels
2-29 Variable Initialized to NULL by Default
2-30 Assigning BOOLEAN Values
2-31 Assigning Query Results to Variables
2-32 Concatenation Operator
2-33 Operator Precedence
2-34 AND Operator
2-35 OR Operator
2-36 NOT Operator
2-37 Changing Order of Evaluation of Logical Operators
2-38 Short-Circuit Evaluation
2-39 Relational Operators
2-40 LIKE Operator
2-41 Escape Character in Pattern
2-42 BETWEEN Operator
2-43 IN Operator
2-44 Using the IN Operator with Sets with NULL Values
2-45 Using BOOLEAN Variables in Conditional Tests
2-46 Using the WHEN Clause with a CASE Statement
2-47 Using a Search Condition with a CASE Statement
2-48 NULL Value in Unequal Comparison
2-49 NULL Value in Equal Comparison
2-50 NULL Value as Argument to DECODE Function
2-51 NULL Value as Argument to NVL Function
2-52 NULL Value as Second Argument to REPLACE Function
2-53 NULL Value as Third Argument to REPLACE Function
2-54 Using Static Constants
2-55 Using DBMS_DB_VERSION Constants
2-56 Using Conditional Compilation with Database Versions
2-57 Using PRINT_POST_PROCESSED_SOURCE to Display 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 Using the 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 Assigment 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 Using Ranges with Subtypes
3-12 Type Compatibility with the NUMBER Data Type
3-13 Assigning Default Value to Subtype Variable
3-14 Subtype Constraints Inherited by Subprograms
3-15 Column Constraints Inherited by Subtypes
3-16 Implicit Conversion
4-1 Simple IF-THEN Statement
4-2 Using a Simple IF-THEN-ELSE Statement
4-3 Nested IF-THEN-ELSE Statements
4-4 Using the IF-THEN-ELSIF Statement
4-5 Extended IF-THEN Statement
4-6 Simple CASE Statement
4-7 Searched CASE Statement
4-8 Using EXCEPTION Instead of ELSE Clause in CASE Statement
4-9 EXIT Statement
4-10 Using an EXIT-WHEN Statement
4-11 CONTINUE Statement
4-12 CONTINUE-WHEN Statement
4-13 Labeled Loops
4-14 Simple FOR-LOOP Statement
4-15 Reverse FOR-LOOP Statement
4-16 Several Types of FOR-LOOP Bounds
4-17 Changing the Increment of the Counter in a FOR-LOOP Statement
4-18 Specifying a LOOP Range at Run Time
4-19 FOR-LOOP with Lower Bound > Upper Bound
4-20 Referencing Counter Variable Outside Loop
4-21 Using Existing Variable as Loop Variable
4-22 Referencing Global Variable with Same Name as Loop Counter
4-23 Referencing Outer Counter with Same Name as Inner Counter
4-24 EXIT in a FOR LOOP
4-25 EXIT with a Label in a FOR LOOP
4-26 Simple GOTO Statement
4-27 Incorrect Label Placement
4-28 Using a NULL Statement to Allow a GOTO to a Label
4-29 Using a GOTO Statement to Branch to an Enclosing Block
4-30 GOTO Statement Cannot Branch into IF Statement
4-31 Using the NULL Statement to Show No Action
4-32 Using NULL as a Placeholder When Creating a Subprogram
4-33 Using the NULL Statement in WHEN OTHER Clause
5-1 Declaring and Using an Associative Array
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 Using FIRST and LAST with a Collection
5-32 Using PRIOR and NEXT to Access Collection Elements
5-33 Using NEXT to Access Elements of a Nested Table
5-34 Using EXTEND to Increase the Size of a Collection
5-35 Using TRIM to Decrease the Size of a Collection
5-36 Using TRIM on Deleted Elements
5-37 Using the DELETE Method on a Collection
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 Using %ROWTYPE to Declare a Record
5-44 Returning a Record from a Function
5-45 Using a Record as Parameter to a Procedure
5-46 Declaring a Nested Record
5-47 Assigning Default Values to a Record
5-48 Assigning All the Fields of a Record in One Statement
5-49 Using SELECT INTO to Assign Values in a Record
5-50 Inserting a PL/SQL Record Using %ROWTYPE
5-51 Updating a Row Using a Record
5-52 Using the RETURNING INTO Clause with a Record
5-53 Using BULK COLLECT with a SELECT INTO Statement
6-1 Data Manipulation with PL/SQL
6-2 Checking SQL%ROWCOUNT After an UPDATE
6-3 Substituting PL/SQL Variables
6-4 Invoking the SQL COUNT Function in PL/SQL
6-5 Using CURRVAL and NEXTVAL
6-6 Using ROWNUM
6-7 Using SQL%FOUND
6-8 Using SQL%ROWCOUNT
6-9 Declaring a Cursor
6-10 Fetching with a Cursor
6-11 Referencing PL/SQL Variables Within Its Scope
6-12 Fetching the Same Cursor Into Different Variables
6-13 Fetching Bulk Data with a Cursor
6-14 Using %FOUND
6-15 Using %ISOPEN
6-16 Using %NOTFOUND
6-17 Using %ROWCOUNT
6-18 Using an Alias For Expressions in a Query
6-19 Using a Subquery in a Cursor
6-20 Using a Subquery in a FROM Clause
6-21 Using a Correlated Subquery
6-22 Passing Parameters to a Cursor FOR Loop
6-23 Passing Parameters to Explicit Cursors
6-24 Cursor Variable Returning a %ROWTYPE Variable
6-25 Using the %ROWTYPE Attribute to Provide the Data Type
6-26 Cursor Variable Returning a Record Type
6-27 Passing a REF CURSOR as a Parameter
6-28 Checking If a Cursor Variable is Open
6-29 Stored Procedure to Open a Ref Cursor
6-30 Stored Procedure to Open Ref Cursors with Different Queries
6-31 Cursor Variable with Different Return Types
6-32 Fetching from a Cursor Variable into a Record
6-33 Fetching from a Cursor Variable into Collections
6-34 Declaration of Cursor Variables in a Package
6-35 Using a Cursor Expression
6-36 Using COMMIT with the WRITE Clause
6-37 Using ROLLBACK
6-38 Using SAVEPOINT with ROLLBACK
6-39 reusing a SAVEPOINT with ROLLBACK
6-40 Using SET TRANSACTION to Begin a Read-only Transaction
6-41 Using CURRENT OF to Update the Latest Row Fetched from a Cursor
6-42 Fetching Across COMMITs Using ROWID
6-43 Declaring an Autonomous Function in a Package
6-44 Declaring an Autonomous Standalone Procedure
6-45 Declaring an Autonomous PL/SQL Block
6-46 Declaring an Autonomous Trigger
6-47 Using Autonomous Triggers
6-48 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 Using Bind Arguments to Guard Against SQL Injection
7-13 Using Validation Checks to Guard Against SQL Injection
7-14 Using Explicit Format Models to Guard 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 Using OUT Mode
8-6 Procedure with Default Parameter Values
8-7 Formal Parameter with Expression as Default Value
8-8 Subprogram Calls Using Positional, Named, and Mixed Notation
8-9 Overloading a Subprogram Name
8-10 Package Specification with Overloading Violation that Causes Compile-Time Error
8-11 Package Specification with Overloading Violation that Compiles Without Error
8-12 Invocation of Improperly Overloaded Subprogram
8-13 Package Specification Without Overloading Violations
8-14 Improper Invocation of Properly Overloaded Subprogram
8-15 Resolving PL/SQL Procedure Names
8-16 Creating an Object Type with AUTHID CURRENT USER
8-17 Invoking an IR Instance Methods
8-18 Invoking an External Procedure from PL/SQL
8-19 Invoking a Java Function from PL/SQL
8-20 RESTRICT_REFERENCES Pragma
8-21 Aliasing from Passing Global Variable with NOCOPY Hint
8-22 Aliasing Passing Same Parameter Multiple Times
8-23 Aliasing from Assigning Cursor Variables to Same Work Area
8-24 Declaration and Definition of Result-Cached Function
8-25 Result-Cached Function that Returns Configuration Parameter Setting
8-26
8-27 Result-Cached Function that Depends on Session-Specific Application Context
8-28 Caching One Name at a Time (Finer Granularity)
8-29 Caching Translated Names One Language at a Time (Coarser Granularity)
9-1 CREATE TRIGGER Statement
9-2 Compound Trigger
9-3 Compound Trigger Records Changes to One Table in Another Table
9-4 Compound Trigger that Avoids Mutating-Table Error
9-5 Monitoring Logons with a Trigger
9-6 Invoking a Java Subprogram from a Trigger
10-1 A Simple Package Specification Without a Body
10-2 Matching Package Specifications and Bodies
10-3 Creating the emp_admin Package
10-4 Using PUT_LINE in the DBMS_OUTPUT Package
10-5 Separating Cursor Specifications with Packages
11-1 Run-Time Error Handling
11-2 Managing Multiple Errors with a Single Exception Handler
11-3 Scope of PL/SQL Exceptions
11-4 Using PRAGMA EXCEPTION_INIT
11-5 Raising an Application Error with RAISE_APPLICATION_ERROR
11-6 Using RAISE to Raise a User-Defined Exception
11-7 Using RAISE to Raise a Predefined Exception
11-8 Scope of an Exception
11-9 Reraising a PL/SQL Exception
11-10 Raising an Exception in a Declaration
11-11 Displaying SQLCODE and SQLERRM
11-12 Continuing After an Exception
11-13 Retrying a Transaction After an Exception
11-14 Using a Locator Variable to Identify the Location of an Exception
11-15 Controlling the Display of PL/SQL Warnings
11-16 Using the DBMS_WARNING Package to Display Warnings
12-1 Nesting a Query to Improve Performance
12-2 Issuing DELETE Statements in a Loop
12-3 Issuing INSERT Statements in a Loop
12-4 Using FORALL with Part of a Collection
12-5 Using FORALL with Nonconsecutive Index Values
12-6 Using Rollbacks with FORALL
12-7 Using %BULK_ROWCOUNT with the FORALL Statement
12-8 Counting Rows Affected by FORALL with %BULK_ROWCOUNT
12-9 Bulk Operation that Continues Despite Exceptions
12-10 Retrieving Query Results with BULK COLLECT
12-11 Using the Pseudocolumn ROWNUM to Limit Query Results
12-12 Bulk-Fetching from a Cursor Into One or More Collections
12-13 Bulk-Fetching from a Cursor Into a Collection of Records
12-14 Using LIMIT to Control the Number of Rows In a BULK COLLECT
12-15 Using BULK COLLECT with the RETURNING INTO Clause
12-16 Using FORALL with BULK COLLECT
12-17 SELECT BULK COLLECT INTO Statement with Unexpected Results
12-18 Workaround for Example 12-17 Using a Cursor
12-19 Workaround for Example 12-17 Using a Second Collection
12-20 Using NOCOPY with Parameters
12-21 Assigning the Result of a Table Function
12-22 Using a Pipelined Table Function For a Transformation
12-23 Using Multiple REF CURSOR Input Variables
12-24 Using a Pipelined Table Function as an Aggregate Function
13-1 Specifying that a Subprogram Is To Be Inlined
13-2 Specifying that an Overloaded Subprogram Is To Be Inlined
13-3 Specifying that a Subprogram Is Not To Be Inlined
13-4 Applying Two INLINE Pragmas to the Same Subprogram
13-5 Creating a Serially Reusable Package
A-1 Using DBMS_DDL.CREATE_WRAPPED Procedure to Wrap a Package
B-1 Resolving Global and Local Variable Names
B-2 Using the Dot Notation to Qualify Names
Scripting on this page enhances content navigation, but does not change the content in any way.