[PDF] Oracle Database Advanced Application Developers Guide

View - Download Oracle Database Advanced Application Developers Guide

Previous PDF

Next PDF

[1] Oracle® Database

Development Guide

12c Release 1 (12.1)

E41452-07

May 2016

Oracle Database Development Guide, 12c Release 1 (12.1)

E41452-07

Copyright © 1996, 2016, Oracle and/or its affiliates. All rights reserved.

Primary Authors: Chuck Murray, Tom Kyte

Contributing Authors: D. Adams, L. Ashdown, P. Huey, S. Moore, E. Paapanen, R. Strohm, R. Ward Contributor: The Oracle Database 12c documentation is dedicated to Mark Townsend, who was an inspiration to all who worked on this release. Contributors: D. Alpern, G. Arora, T. Chang, B. Cheng, R. Day, R. Decker, G. Doherty, D. Elson, A.

Ganesh, M. Hartstein, Y. Hu, J. Huang, C. Iyer, N. Jain, V. Krishnaswamy, R. Kumar, S. Kumar, C. Lei, B.

Llewellyn, K. Mohan, V. Moore, K. Muthukkaruppan, J. Muller, R. Murthy, R. Pang, B. Sinha, S. Vemuri, W.

Wang, D. Wong

This software and related documentation are provided under a license agreement containing restrictions on

use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your

license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,

transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse

engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is

prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If

you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it

on behalf of the U.S. Government, then the following notice is applicable: U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and

adaptation of the programs, including any operating system, integrated software, any programs installed on

the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to

the programs. No other rights are granted to the U.S. Government. This software or hardware is developed for general use in a variety of information management

applications. It is not developed or intended for use in any inherently dangerous applications, including

applications that may create a risk of personal injury. If you use this software or hardware in dangerous

applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other

measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages

caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of

their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks

are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group. This software or hardware and documentation may provide access to or information about content,

products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and

expressly disclaim all warranties of any kind with respect to third-party content, products, and services

unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its

affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of

third-party content, products, or services, except as set forth in an applicable agreement between you and

Oracle.

iii

Contents

Preface............................................................................................................................................................. xxxi

Audience................................................................................................................................................... xxxi

Documentation Accessibility................................................................................................................. xxxi

Related Documents................................................................................................................................ xxxii

Conventions ............................................................................................................................................ xxxii

Changes in This Release for Oracle Database Development Guide....................... xxxv

Changes in Oracle Database 12c Release 1 (12.1.0.2)........................................................................ xxxv

Changes in Oracle Database 12c Release 1 (12.1.0.1)........................................................................ xxxv

Part I Database Development Fundamentals

1 Design Basics

1.1Design for Performance ............................................................................................................. 1-1

1.2Design for Scalability.................................................................................................................. 1-2

1.3Design for Extensibility.............................................................................................................. 1-2

1.3.1Data Cartridges.................................................................................................................... 1-2

1.3.2External Procedures............................................................................................................. 1-3

1.3.3User-Defined Functions and Aggregate Functions ........................................................ 1-3

1.3.4Object-Relational Features.................................................................................................. 1-3

1.4Design for Security ..................................................................................................................... 1-4

1.5Design for Availability............................................................................................................... 1-4

1.6Design for Portability................................................................................................................. 1-4

1.7Design for Diagnosability.......................................................................................................... 1-5

1.8Design for Special Environments ............................................................................................. 1-5

1.8.1Data Warehousing............................................................................................................... 1-5

1.8.2Online Transaction Processing (OLTP) ............................................................................ 1-6

1.9Features for Special Scenarios................................................................................................... 1-7

1.9.1SQL Analytic Functions...................................................................................................... 1-7

1.9.2Materialized Views.............................................................................................................. 1-8

1.9.3Partitioning........................................................................................................................... 1-9

1.9.4Temporal Validity Support ............................................................................................. 1-10

iv

2 Performance and Scalability

2.1Performance Strategies............................................................................................................... 2-1

2.1.1Designing Your Data Model to Perform Well................................................................. 2-1

2.1.1.1Analyze the Data Requirements of the Application................................................ 2-2

2.1.1.2Create the Database Design for the Application...................................................... 2-2

2.1.1.3Implement the Database Application........................................................................ 2-3

2.1.1.4Maintain the Database and Database Application.................................................. 2-4

2.1.2Setting Performance Goals (Metrics) ................................................................................ 2-4

2.1.3Benchmarking Your Application....................................................................................... 2-4

2.2Tools for Performance................................................................................................................ 2-5

2.2.1DBMS_APPLICATION_INFO Package........................................................................... 2-5

2.2.2SQL Trace Facility (SQL_TRACE)..................................................................................... 2-6

2.2.3EXPLAIN PLAN Statement................................................................................................ 2-6

2.3Monitoring Database Performance .......................................................................................... 2-7

2.3.1Automatic Database Diagnostic Monitor (ADDM)........................................................ 2-7

2.3.2Monitoring Real-Time Database Performance................................................................ 2-8

2.3.3Responding to Performance-Related Alerts .................................................................... 2-8

2.3.4SQL Advisors and Memory Advisors .............................................................................. 2-8

2.4Using Instrumentation............................................................................................................... 2-9

2.5Testing for Performance............................................................................................................. 2-9

2.6Using Bind Variables............................................................................................................... 2-10

2.7Using Client Result Cache...................................................................................................... 2-11

2.7.1About Client Result Cache.............................................................................................. 2-12

2.7.2Benefits of Client Result Cache....................................................................................... 2-12

2.7.3Guidelines for Using Client Result Cache..................................................................... 2-13

2.7.3.1SQL Hints.................................................................................................................... 2-14

2.7.3.2Table Annotation....................................................................................................... 2-14

2.7.3.3Session Parameter...................................................................................................... 2-15

2.7.3.4Effective Table Result Cache Mode......................................................................... 2-15

2.7.3.5Displaying Effective Table Result Cache Mode.................................................... 2-16

2.7.3.6Result Cache Mode Use Cases................................................................................. 2-16

2.7.3.7Queries Never Result Cached in Client Result Cache.......................................... 2-16

2.7.4Client Result Cache Consistency.................................................................................... 2-17

2.7.5Deployment-Time Settings for Client Result Cache.................................................... 2-17

2.7.5.1Server Initialization Parameters.............................................................................. 2-18

2.7.5.2Client Configuration Parameters............................................................................ 2-19

2.7.6Client Result Cache Statistics.......................................................................................... 2-19

2.7.7Validation of Client Result Cache .................................................................................. 2-20

2.7.7.1Measure Execution Times ........................................................................................ 2-20

2.7.7.2Query V$MYSTAT .................................................................................................... 2-20

2.7.7.3Query V$SQLAREA.................................................................................................. 2-20

2.7.8Client Result Cache and Server Result Cache .............................................................. 2-21

2.7.9Client Result Cache Demo Files...................................................................................... 2-22

2.7.10Client Result Cache Compatibility with Previous Releases....................................... 2-22

2.7.11Where to Find More Information About Client Result Cache................................... 2-22

2.8Statement Caching................................................................................................................... 2-22

2.9OCI Client Statement Cache Auto-Tuning........................................................................... 2-23

v

2.10Client-Side Deployment Parameters..................................................................................... 2-24

2.11Using Query Change Notification......................................................................................... 2-24

2.12Using Database Resident Connection Pool.......................................................................... 2-24

2.12.1About Database Resident Connection Pool.................................................................. 2-25

2.12.2Configuring DRCP ........................................................................................................... 2-26

2.12.3Using JDBC with DRCP................................................................................................... 2-27

2.12.4Using OCI Session Pool APIs with DRCP..................................................................... 2-27

2.12.5Session Purity and Connection Class............................................................................. 2-28

2.12.5.1Session Purity............................................................................................................. 2-28

2.12.5.2Connection Class ....................................................................................................... 2-28

2.12.5.3Session Purity and Connection Class Defaults ..................................................... 2-29

2.12.6Starting Database Resident Connection Pool ............................................................... 2-30

2.12.7Enabling DRCP ................................................................................................................. 2-30

2.12.8Benefiting from the Scalability of DRCP in an OCI Application............................... 2-30

2.12.9Benefiting from the Scalability of DRCP in a Java Application................................. 2-31

2.12.10Best Practices for Using DRCP........................................................................................ 2-31

2.12.11Compatibility and Migration.......................................................................................... 2-32

2.12.12DRCP Restrictions ............................................................................................................ 2-32

2.12.13Using DRCP with Custom Pools.................................................................................... 2-33

2.12.14Explicitly Marking Sessions Stateful or Stateless......................................................... 2-33

2.12.15Using DRCP with Oracle Real Application Clusters................................................... 2-34

2.12.16Using DRCP with Pluggable Databases........................................................................ 2-35

2.12.17DRCP with Data Guard................................................................................................... 2-35

2.13Using Runtime Connection Load Balancing........................................................................ 2-35

2.13.1About Runtime Connection Load Balancing................................................................ 2-35

2.13.2Enabling and Disabling Runtime Connection Load Balancing ................................. 2-36

2.13.2.1OCI............................................................................................................................... 2-36

2.13.2.2OCCI............................................................................................................................ 2-37

2.13.2.3JDBC ............................................................................................................................ 2-37

2.13.2.4ODP.NET.................................................................................................................... 2-37

2.13.3Receiving Load Balancing Advisory FAN Events....................................................... 2-37

3 Security

3.1Enabling User Access with Grants, Roles, and Least Privilege............................................ 3-1

3.2Automating Database Logins.................................................................................................... 3-2

3.3Controlling User Access with Fine-Grained Access Control................................................ 3-3

3.4Using Invoker's and Definer's Rights for Procedures and Functions ................................. 3-4

3.4.1What Are Invoker's Rights and Definer's Rights? .......................................................... 3-4

3.4.2Protecting Users Who Run Invoker's Rights Procedures and Functions.................... 3-5

3.4.3How Default Rights Are Handled for Java Stored Procedures .................................... 3-5

3.5Managing External Procedures for Your Applications......................................................... 3-6

3.6Auditing User Activity............................................................................................................... 3-6

4 High Availability

4.1Failover and Query Replay........................................................................................................ 4-1

4.1.1Transparent Application Failover (TAF).......................................................................... 4-1

vi

4.1.1.1About Transparent Application Failover.................................................................. 4-1

4.1.1.2Configuring Transparent Application Failover....................................................... 4-2

4.1.1.3Using Transparent Application Failover Callbacks ................................................ 4-2

4.1.2Application Continuity....................................................................................................... 4-3

4.2Fast Application Notification (FAN) and Fast Connection Failover (FCF)........................ 4-3

4.2.1About Fast Application Notification (FAN) ................................................................... 4-3

4.2.2About Fast Connection Failover (FCF)............................................................................. 4-4

4.3Application Continuity and Transaction Guard.................................................................... 4-5

4.3.1Transaction Guard............................................................................................................... 4-6

4.3.2Application Continuity for Java ........................................................................................ 4-6

4.4Service and Load Management for Database Clouds............................................................ 4-7

4.4.1About Service and Load Management for Database Clouds........................................ 4-7

5 Advanced PL/SQL Features

5.1PL/SQL Data Types ................................................................................................................... 5-1

5.2Dynamic SQL............................................................................................................................... 5-1

5.3PL/SQL Optimize Level............................................................................................................ 5-1

5.4Compiling PL/SQL Units for Native Execution.................................................................... 5-2

5.5Exception Handling.................................................................................................................... 5-2

5.6Conditional Compilation........................................................................................................... 5-2

5.7Bulk Binding................................................................................................................................ 5-2

Part II SQL for Application Developers

6 SQL Processing for Application Developers

6.1Description of SQL Statement Processing............................................................................... 6-1

6.1.1Stages of SQL Statement Processing................................................................................. 6-2

6.1.2Shared SQL Areas................................................................................................................ 6-3

6.2Grouping Operations into Transactions.................................................................................. 6-4

6.2.1Deciding How to Group Operations in Transactions .................................................... 6-4

6.2.2Improving Transaction Performance................................................................................ 6-4

6.2.3Managing Commit Redo Action........................................................................................ 6-5

6.2.4Determining Transaction Outcome After a Recoverable Outage................................. 6-7

6.2.4.1Understanding Transaction Guard............................................................................ 6-7

6.2.4.2Understanding DBMS_APP_CONT.GET_LTXID_OUTCOME............................ 6-9

6.2.4.3Using Transaction Guard......................................................................................... 6-11

6.3Ensuring Repeatable Reads with Read-Only Transactions............................................... 6-11

6.4Locking Tables Explicitly........................................................................................................ 6-12

6.4.1Privileges Required to Acquire Table Locks................................................................. 6-13

6.4.2Choosing a Locking Strategy .......................................................................................... 6-13

6.4.2.1When to Lock with ROW SHARE MODE and ROW EXCLUSIVE MODE...... 6-14

6.4.2.2When to Lock with SHARE MODE........................................................................ 6-14

6.4.2.3When to Lock with SHARE ROW EXCLUSIVE MODE...................................... 6-15

6.4.2.4When to Lock with EXCLUSIVE MODE ............................................................... 6-16

6.4.3Letting Oracle Database Control Table Locking.......................................................... 6-16

6.4.4Explicitly Acquiring Row Locks..................................................................................... 6-16

vii

6.4.5Examples of Concurrency Under Explicit Locking ..................................................... 6-17

6.5Using Oracle Lock Management Services (User Locks)..................................................... 6-24

6.5.1When to Use User Locks.................................................................................................. 6-24

6.5.2Viewing and Monitoring Locks...................................................................................... 6-25

6.6Using Serializable Transactions for Concurrency Control ................................................ 6-25

6.6.1Transaction Interaction and Isolation Level ................................................................. 6-26

6.6.2Setting Isolation Levels.................................................................................................... 6-27

6.6.3Serializable Transactions and Referential Integrity..................................................... 6-28

6.6.4READ COMMITTED and SERIALIZABLE Isolation Levels ..................................... 6-30

6.6.4.1Transaction Set Consistency Differences ............................................................... 6-30

6.6.4.2Choosing Transaction Isolation Levels................................................................... 6-30

6.7Nonblocking and Blocking DDL Statements....................................................................... 6-31

6.8Autonomous Transactions...................................................................................................... 6-32

6.8.1Examples of Autonomous Transactions........................................................................ 6-34

6.8.1.1Ordering a Product.................................................................................................... 6-35

6.8.1.2Withdrawing Money from a Bank Account.......................................................... 6-35

6.8.2Declaring Autonomous Routines................................................................................... 6-38

6.9Resuming Execution After Storage Allocation Errors........................................................ 6-39

6.9.1What Operations Have Resumable Storage Allocation? ............................................ 6-39

6.9.2Handling Suspended Storage Allocation...................................................................... 6-39

6.9.2.1Using an AFTER SUSPEND Trigger in the Application...................................... 6-40

6.9.2.2Checking for Suspended Statements...................................................................... 6-41

7 Using SQL Data Types in Database Applications

7.1Using the Correct and Most Specific Data Type .................................................................... 7-1

7.1.1How the Correct Data Type Increases Data Integrity.................................................... 7-2

7.1.2How the Most Specific Data Type Decreases Storage Requirements.......................... 7-2

7.1.3How the Correct Data Type Improves Performance...................................................... 7-2

7.2Representing Character Data .................................................................................................... 7-5

7.3Representing Numeric Data...................................................................................................... 7-6

7.3.1Floating-Point Number Components............................................................................... 7-7

7.3.2Floating-Point Number Formats ....................................................................................... 7-8

7.3.2.1Binary Floating-Point Formats ................................................................................... 7-8

7.3.3Representing Special Values with Native Floating-Point Data Types......................... 7-9

7.3.4Comparing Native Floating-Point Values..................................................................... 7-10

7.3.5Arithmetic Operations with Native Floating-Point Data Types................................ 7-10

7.3.6Conversion Functions for Native Floating-Point Data Types.................................... 7-11

7.3.7Client Interfaces for Native Floating-Point Data Types.............................................. 7-11

7.4Representing Date and Time Data ........................................................................................ 7-12

7.4.1Displaying Current Date and Time................................................................................ 7-13

7.4.2Inserting and Displaying Dates...................................................................................... 7-14

7.4.3Inserting and Displaying Times ..................................................................................... 7-15

7.4.4Arithmetic Operations with Datetime Data Types...................................................... 7-16

7.4.5Conversion Functions for Datetime Data Types.......................................................... 7-16

7.4.6Importing, Exporting, and Comparing Datetime Types............................................. 7-17

7.5Representing Specialized Data............................................................................................... 7-17

7.5.1Representing Spatial Data............................................................................................... 7-18

viii

7.5.2Representing Multimedia Data....................................................................................... 7-18

7.5.3Representing Large Amounts of Data........................................................................... 7-18

7.5.3.1Large Objects (LOBs)................................................................................................. 7-18

7.5.3.2LONG and LONG RAW Data Types ..................................................................... 7-19

7.5.4Representing Searchable Text......................................................................................... 7-19

7.5.5Representing XML Data................................................................................................... 7-19

7.5.6Representing Dynamically Typed Data ........................................................................ 7-20

7.5.7Representing ANSI, DB2, and SQL/DS Data............................................................... 7-21

7.6Identifying Rows by Address ................................................................................................ 7-22

7.7Displaying Metadata for SQL Operators and Functions ................................................... 7-23

7.7.1ARGn Data Type............................................................................................................... 7-24

7.7.2DISP_TYPE Data Type..................................................................................................... 7-24

7.7.3SQL Data Type Families .................................................................................................. 7-24

8 Using Regular Expressions in Database Applications

8.1Overview of Regular Expressions............................................................................................ 8-1

8.2Oracle SQL Support for Regular Expressions......................................................................... 8-2

8.3Oracle SQL and POSIX Regular Expression Standard.......................................................... 8-4

8.4Operators in Oracle SQL Regular Expressions....................................................................... 8-4

8.4.1POSIX Operators in Oracle SQL Regular Expressions................................................... 8-4

8.4.2Oracle SQL Multilingual Extensions to POSIX Standard.............................................. 8-7

8.4.3Oracle SQL PERL-Influenced Extensions to POSIX Standard...................................... 8-7

8.5Using Regular Expressions in SQL Statements: Scenarios ................................................... 8-9

8.5.1Using a Constraint to Enforce a Phone Number Format............................................... 8-9

8.5.2Using Back References to Reposition Characters......................................................... 8-10

9 Using Indexes in Database Applications

9.1Guidelines for Managing Indexes ............................................................................................ 9-1

9.2Managing Indexes....................................................................................................................... 9-2

9.3When to Use Domain Indexes................................................................................................... 9-2

9.4When to Use Function-Based Indexes ..................................................................................... 9-2

9.4.1Advantages of Function-Based Indexes........................................................................... 9-3

9.4.2Disadvantages of Function-Based Indexes ...................................................................... 9-4

9.4.3Examples of Function-Based Indexes............................................................................... 9-5

10 Maintaining Data Integrity in Database Applications

10.1Enforcing Business Rules with Constraints ......................................................................... 10-2

10.2Enforcing Business Rules with Both Constraints and Application Code........................ 10-3

10.3Creating Indexes for Use with Constraints.......................................................................... 10-4

10.4When to Use NOT NULL Constraints.................................................................................. 10-4

10.5When to Use Default Column Values................................................................................... 10-6

10.6Choosing a Primary Key for a Table (PRIMARY KEY Constraint).................................. 10-6

10.7When to Use UNIQUE Constraints....................................................................................... 10-7

10.8Enforcing Referential Integrity with FOREIGN KEY Constraints.................................... 10-8

10.8.1FOREIGN KEY Constraints and NULL Values ......................................................... 10-10

10.8.2Defining Relationships Between Parent and Child Tables....................................... 10-10

ix

10.8.3Rules for Multiple FOREIGN KEY Constraints ........................................................ 10-11

10.8.4Deferring Constraint Checks......................................................................................... 10-11

10.9Minimizing Space and Time Overhead for Indexes Associated with Constraints ...... 10-13

10.10Guidelines for Indexing Foreign Keys................................................................................ 10-13

10.11Referential Integrity in a Distributed Database................................................................. 10-13

10.12When to Use CHECK Constraints....................................................................................... 10-14

10.12.1Restrictions on CHECK Constraints............................................................................ 10-14

10.12.2Designing CHECK Constraints .................................................................................... 10-15

10.12.3Rules for Multiple CHECK Constraints...................................................................... 10-15

10.12.4Choosing Between CHECK and NOT NULL Constraints ....................................... 10-15

10.13Examples of Defining Constraints....................................................................................... 10-16

10.13.1Privileges Needed to Define Constraints.................................................................... 10-17

10.13.2Naming Constraints ...................................................................................................... 10-17

10.14Enabling and Disabling Constraints ................................................................................... 10-18

10.14.1Why Disable Constraints?............................................................................................. 10-18

10.14.2Creating Enabled Constraints (Default)...................................................................... 10-18

10.14.3Creating Disabled Constraints...................................................................................... 10-19

10.14.4Enabling Existing Constraints....................................................................................... 10-19

10.14.5Disabling Existing Constraints ..................................................................................... 10-20

10.14.6Guidelines for Enabling and Disabling Key Constraints.......................................... 10-20

10.14.7Fixing Constraint Exceptions........................................................................................ 10-21

10.15Modifying Constraints.......................................................................................................... 10-21

10.16Renaming Constraints........................................................................................................... 10-22

10.17Dropping Constraints............................................................................................................ 10-23

10.18Managing FOREIGN KEY Constraints .............................................................................. 10-24

10.18.1Data Types and Names for Foreign Key Columns.................................................... 10-24

10.18.2Limit on Columns in Composite Foreign Keys.......................................................... 10-24

10.18.3Foreign Key References Primary Key by Default ...................................................... 10-24

10.18.4Privileges Required to Create FOREIGN KEY Constraints...................................... 10-24

10.18.5Choosing How Foreign Keys Enforce Referential Integrity..................................... 10-24

10.19Viewing Information About Constraints............................................................................ 10-25

Part III PL/SQL for Application Developers

11 Coding PL/SQL Subprograms and Packages

11.1Overview of PL/SQL Subprograms ..................................................................................... 11-1

11.2Overview of PL/SQL Packages............................................................................................. 11-2

11.3Overview of PL/SQL Units.................................................................................................... 11-3

11.3.1PLSQL_OPTIMIZE_LEVEL Compilation Parameter.................................................. 11-4

11.4Creating PL/SQL Subprograms and Packages................................................................... 11-6

11.4.1Privileges Needed to Create Subprograms and Packages.......................................... 11-6

11.4.2Creating Subprograms and Packages............................................................................ 11-6

11.4.3PL/SQL Object Size Limits.............................................................................................. 11-7

11.4.4PL/SQL Data Types......................................................................................................... 11-7

11.4.4.1PL/SQL Scalar Data Types ...................................................................................... 11-8

11.4.4.2PL/SQL Composite Data Types.............................................................................. 11-9

x

11.4.4.3Abstract Data Types................................................................................................ 11-10

11.4.5Returning Result Sets to Clients................................................................................... 11-10

11.4.5.1Advantages of Cursor Variables ........................................................................... 11-11

11.4.5.2Disadvantages of Cursor Variables....................................................................... 11-11

11.4.5.3Returning Query Results Implicitly...................................................................... 11-14

11.4.6Returning Large Amounts of Data from a Function ................................................. 11-14

11.4.7PL/SQL Function Result Cache.................................................................................... 11-15

11.4.8Overview of Bulk Binding............................................................................................. 11-15

11.4.8.1DML Statements that Reference Collections ....................................................... 11-16

11.4.8.2SELECT Statements that Reference Collections.................................................. 11-16

11.4.8.3FOR Loops that Reference Collections and Return DML.................................. 11-17

11.4.9PL/SQL Dynamic SQL .................................................................................................. 11-18

11.5Altering PL/SQL Subprograms and Packages.................................................................. 11-18

11.6Dropping PL/SQL Subprograms and Packages............................................................... 11-19

11.7Compiling PL/SQL Units for Native Execution............................................................... 11-19

11.8Invoking Stored PL/SQL Subprograms............................................................................. 11-19

11.8.1Privileges Required to Invoke a Stored Subprogram................................................ 11-20

11.8.2Invoking a Subprogram Interactively from Oracle Tools......................................... 11-20

11.8.3Invoking a Subprogram from Another Subprogram ................................................ 11-22

11.8.4Invoking a Remote Subprogram .................................................................................. 11-23

11.8.4.1Synonyms for Remote Subprograms.................................................................... 11-24

11.8.4.2Transactions That Invoke Remote Subprograms................................................ 11-25

11.9Invoking Stored PL/SQL Functions from SQL Statements............................................. 11-25

11.9.1Why Invoke PL/SQL Functions from SQL Statements?........................................... 11-26

11.9.2Where PL/SQL Functions Can Appear in SQL Statements..................................... 11-27

11.9.3When PL/SQL Functions Can Appear in SQL Expressions .................................... 11-27

11.9.4Controlling Side Effects of PL/SQL Functions Invoked from SQL Statements.... 11-28

11.9.4.1Restrictions on Functions Invoked from SQL Statements................................. 11-28

11.9.4.2PL/SQL Functions Invoked from Parallelized SQL Statements ...................... 11-29

11.9.4.3PRAGMA RESTRICT_REFERENCES .................................................................. 11-30

11.10Debugging Stored Subprograms......................................................................................... 11-33

11.10.1PL/Scope.......................................................................................................................... 11-33

11.10.2PL/SQL Hierarchical Profiler....................................................................................... 11-33

11.10.3Compiling Code for Debugging................................................................................... 11-34

11.10.4Privileges for Debugging PL/SQL and Java Stored Subprograms......................... 11-34

11.10.5DBMS_DEBUG Package................................................................................................ 11-35

11.11Package Invalidations and Session State............................................................................ 11-35

12 Using PL/Scope

12.1Specifying Identifier Collection ............................................................................................. 12-1

12.2PL/Scope Identifier Data for STANDARD and DBMS_STANDARD............................. 12-2

12.2.1Do You Need STANDARD and DBMS_STANDARD Identifier Data? ................... 12-2

12.2.2Does Your Database Have STANDARD and DBMS_STANDARD Identifier Data?........

12-2

12.2.3Recompiling STANDARD and DBMS_STANDARD.................................................. 12-3

12.2.4Running utlrp.sql to Recompile Invalid PL/SQL Objects.......................................... 12-4

12.3How Much Space is PL/Scope Data Using?........................................................................ 12-4

xiquotesdbs_dbs17.pdfusesText_23

[PDF] oracle advanced support gateway documentation

[PDF] oracle amr support

[PDF] oracle apex 19 manual

[PDF] oracle apex 19.1 architecture

[PDF] oracle apex 19.1 tutorial

[PDF] oracle apex 19.1 tutorial for beginners

[PDF] oracle apex 19.1 tutorial pdf

[PDF] oracle apex 19.2 download

[PDF] oracle apex 19.2 installation

[PDF] oracle apex 19.2 new features

[PDF] oracle apex 19.2 popup lov

[PDF] oracle apex 5

[PDF] oracle apex components

[PDF] oracle apex documentation

[PDF] oracle apex download file button