book

Oracle PL/SQL Programming, 5th Edition

by Steven Feuerstein, Bill Pribyl

September 2009

Intermediate to advanced

1226 pages

39h 23m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Oracle PL/SQL Programming
Dedication
A Note Regarding Supplemental Files
Preface
Objectives of This Book
Structure of This Book
About the ContentsWhat This Book Does Not Cover
Conventions Used in This Book
Which Platform or Version?
About the Code
Using Code Examples

Safari® Books Online
Comments and Questions
Acknowledgments
I. Programming in PL/SQL
1. Introduction to PL/SQL
What Is PL/SQL?
The Origins of PL/SQL
The Early Years of PL/SQLImproved Application PortabilityImproved Execution Authority and Transaction IntegrityHumble Beginnings, Steady Improvement
So This Is PL/SQL
Integration with SQLControl and Conditional LogicWhen Things Go Wrong
About PL/SQL Versions
Oracle Database 11g New FeaturesEdition-based redefinition capability (Release 2 only)FORCE option with CREATE TYPE (Release 2 only)Function result cacheCONTINUE statementSequences in PL/SQL expressionsDynamic SQL enhancementsNew native compilation and SIMPLE datatypesSecureFilesTrigger enhancementsAutomatic subprogram inliningPL/ScopePL/SQL hierarchical profilerFine-grained dependency trackingSupertype invocation from subtype
Resources for PL/SQL Developers
The O’Reilly PL/SQL SeriesPL/SQL on the Internet
Some Words of Advice
Don’t Be in Such a Hurry!Don’t Be Afraid to Ask for HelpTake a Creative, Even Radical Approach
2. Creating and Running PL/SQL Code
Navigating the Database
Creating and Editing Source Code
SQL*Plus
Starting Up SQL*PlusRunning a SQL StatementRunning a PL/SQL ProgramRunning a ScriptWhat Is the “Current Directory”?Other SQL*Plus TasksSetting your preferencesSaving output to a fileExiting SQL*PlusEditing a statementLoading your own custom environment automatically on startupError Handling in SQL*PlusWhy You Will Love and Hate SQL*Plus
Performing Essential PL/SQL Tasks
Creating a Stored ProgramExecuting a Stored ProgramShowing Stored ProgramsManaging Grants and Synonyms for Stored ProgramsDropping a Stored ProgramHiding the Source Code of a Stored Program
Editing Environments for PL/SQL
Calling PL/SQL from Other Languages
C: Using Oracle’s Precompiler (Pro*C)Java: Using JDBCPerl: Using Perl DBI and DBD::OraclePHP: Using Oracle ExtensionsPL/SQL Server PagesAnd Where Else?
3. Language Fundamentals
PL/SQL Block Structure
Anonymous BlocksNamed BlocksNested BlocksScopeQualify all References to Variables and Columns in SQL StatementsImprove readabilityAvoid bugs through qualifiersVisibility“Visible” identifiersQualified identifiersQualifying identifier names with module namesNested programs
The PL/SQL Character Set
Identifiers
Reserved WordsReserved wordsIdentifiers from STANDARD packageHow to avoid using reserved wordsWhitespace and Keywords
Literals
NULLsEmbedding Single Quotes Inside a Literal StringNumeric LiteralsBoolean Literals
The Semicolon Delimiter
Comments
Single-Line Comment SyntaxMultiline Comment Syntax
The PRAGMA Keyword
Labels
II. PL/SQL Program Structure
4. Conditional and Sequential Control
IF Statements
The IF-THEN CombinationThe IF-THEN-ELSE CombinationThe IF-THEN-ELSIF CombinationAvoiding IF Syntax GotchasNested IF StatementsShort-Circuit Evaluation
CASE Statements and Expressions
Simple CASE StatementsSearched CASE StatementsNested CASE StatementsCASE Expressions
The GOTO Statement
The NULL Statement
Improving Program ReadabilityUsing NULL After a Label
5. Iterative Processing with Loops
Loop Basics
Examples of Different LoopsStructure of PL/SQL Loops
The Simple Loop
Terminating a Simple Loop: EXIT and EXIT WHENEmulating a REPEAT UNTIL LoopThe Intentionally Infinite Loop
The WHILE Loop
The Numeric FOR Loop
Rules for Numeric FOR LoopsExamples of Numeric FOR LoopsHandling Nontrivial Increments
The Cursor FOR Loop
Example of Cursor FOR Loops
Loop Labels
The CONTINUE Statement
Tips for Iterative Processing
Use Understandable Names for Loop IndexesThe Proper Way to Say GoodbyeObtaining Information About FOR Loop ExecutionSQL Statement as Loop
6. Exception Handlers
Exception-Handling Concepts and Terminology
Defining Exceptions
Declaring Named ExceptionsAssociating Exception Names with Error CodesUsing EXCEPTION_INITRecommended uses of EXCEPTION_INITAbout Named System ExceptionsScope of an Exception
Raising Exceptions
The RAISE StatementUsing RAISE_APPLICATION_ERROR
Handling Exceptions
Built-in Error FunctionsMore on DBMS_UTILITY.FORMAT_ERROR_BACKTRACEJust the line number, pleaseUseful applications of SQLERRMCombining Multiple Exceptions in a Single HandlerUnhandled ExceptionsPropagation of Unhandled ExceptionsLosing exception informationExamples of exception propagationContinuing Past ExceptionsWriting WHEN OTHERS Handling Code
Building an Effective Error Management Architecture
Decide on Your Error Management StrategyStandardize Handling of Different Types of ExceptionsDeliberate exceptionsUnfortunate and unexpected exceptionsHow to benefit from this categorizationOrganize Use of Application-Specific Error CodesUse Standardized Error Management ProgramsWork with Your Own Exception “Objects”Create Standard Templates for Common Error Handling
Making the Most of PL/SQL Error Management
III. PL/SQL Program Data
7. Working with Program Data
Naming Your Program Data
Overview of PL/SQL Datatypes
Character DataNumbersDates, Timestamps, and IntervalsBooleansBinary DataROWIDsREF CURSORsInternet Datatypes“Any” DatatypesUser-Defined Datatypes
Declaring Program Data
Declaring a VariableDeclaring ConstantsThe NOT NULL ClauseAnchored DeclarationsAnchoring to Cursors and TablesBenefits of Anchored DeclarationsSynchronization with database columnsNormalization of local variablesAnchoring to NOT NULL Datatypes
Programmer-Defined Subtypes
Conversion Between Datatypes
Implicit Data ConversionLimitations of implicit conversionDrawbacks of implicit conversionExplicit Datatype ConversionThe CHARTOROWID functionThe CAST functionThe CONVERT functionThe HEXTORAW functionThe RAWTOHEX functionThe ROWIDTOCHAR function
8. Strings
String Datatypes
The VARCHAR2 DatatypeThe CHAR DatatypeString Subtypes
Working with Strings
Specifying String ConstantsUsing Nonprintable CharactersConcatenating StringsDealing with CaseForcing a string to all upper- or lowercaseMaking comparisons case-insensitiveCase-insensitivity and indexesCapitalizing each word in a stringTraditional Searching, Extracting, and ReplacingPaddingTrimmingRegular Expression Searching, Extracting, and ReplacingDetecting a patternLocating a patternExtracting text matching a patternCounting regular expression matchesReplacing textGroking greedinessLearning more about regular expressionsWorking with Empty StringsMixing CHAR and VARCHAR2 ValuesDatabase-to-variable conversionVariable-to-database conversionString comparisonsCharacter functions and CHAR arguments
String Function Quick Reference
9. Numbers
Numeric Datatypes
The NUMBER TypeThe PLS_INTEGER TypeThe BINARY_INTEGER TypeThe SIMPLE_INTEGER TypeThe BINARY_FLOAT and BINARY_DOUBLE TypesThe SIMPLE_FLOAT and SIMPLE_DOUBLE TypesNumeric Subtypes
Number Conversions
The TO_NUMBER FunctionUsing TO_NUMBER with no formatUsing TO_NUMBER with a format modelPassing NLS settings to TO_NUMBERThe TO_CHAR FunctionUsing TO_CHAR with no formatUsing TO_CHAR with a format modelThe V format elementRounding when converting numbers to character stringsDealing with spaces when converting numbers to character stringsPassing NLS settings to TO_CHARThe CAST FunctionImplicit Conversions
Numeric Operators
Numeric Functions
Rounding and Truncation FunctionsTrigonometric FunctionsNumeric Function Quick Reference
10. Dates and Timestamps
Datetime Datatypes
Declaring Datetime VariablesChoosing a Datetime Datatype
Getting the Current Date and Time
Interval Datatypes
Declaring INTERVAL VariablesWhen to Use INTERVALsFinding the difference between two datetime valuesDesignating periods of time
Datetime Conversions
From Strings to DatetimesFrom Datetimes to StringsWorking with Time ZonesRequiring a Format Mask to Match ExactlyEasing Up on Exact MatchesInterpreting Two-Digit Years in a Sliding WindowConverting Time Zones to Character StringsPadding Output with Fill Mode
Date and Timestamp Literals
Interval Conversions
Converting from Numbers to IntervalsConverting Strings to IntervalsFormatting Intervals for Display
Interval Literals
CAST and EXTRACT
The CAST FunctionThe EXTRACT Function
Datetime Arithmetic
Date Arithmetic with Intervals and DatetimesDate Arithmetic with DATE DatatypesComputing the Interval Between Two DatetimesMixing DATEs and TIMESTAMPsAdding and Subtracting IntervalsMultiplying and Dividing IntervalsUsing Unconstrained INTERVAL Types
Date/Time Function Quick Reference
11. Records
Records in PL/SQL
Benefits of Using RecordsData abstractionAggregate operationsLeaner, cleaner codeDeclaring RecordsProgrammer-Defined RecordsDeclaring programmer-defined record TYPEsDeclaring the recordExamples of programmer-defined record declarationsWorking with RecordsRecord-level operationsField-level operationsField-level operations with nested recordsField-level operations with package-based recordsComparing RecordsTrigger Pseudo-Records
12. Collections
Collections Overview
Collections Concepts and TerminologyTypes of CollectionsCollection ExamplesUsing an associative arrayUsing a nested tableUsing a VARRAYWhere You Can Use CollectionsCollections as components of a recordCollections as program parametersCollection as datatype of a function’s return valueCollection as “columns” in a database tableCollections as attributes of an object typeChoosing a Collection Type
Collection Methods (Built-ins)
The COUNT MethodBoundary considerationsExceptions possibleThe DELETE MethodBoundary considerationsExceptions possibleThe EXISTS MethodBoundary considerationsExceptions possibleThe EXTEND MethodBoundary considerationsExceptions possibleThe FIRST and LAST MethodsBoundary considerationsExceptions possibleThe LIMIT MethodBoundary considerationsExceptions possibleThe PRIOR and NEXT MethodsBoundary considerationsExceptions possibleThe TRIM MethodBoundary considerationsExceptions possible
Working with Collections
Declaring Collection TypesDeclaring an associative array collection typeDeclaring a nested table or VARRAYChanging nested table of VARRAY characteristicsDeclaring and Initializing Collection VariablesInitializing implicitly during direct assignmentInitializing implicitly via FETCHVARRAY integrationPopulating Collections with DataUsing the assignment operatorWhat index values can I use?Aggregate assignmentsAssigning rows from a relational tableAdvantage of nonsequential population of collectionAccessing Data Inside a CollectionUsing String-Indexed CollectionsSimplifying algorithmic logic with string indexesEmulating primary keys and unique indexesPerformance of string-indexed collectionsOther examples of string-indexed collectionsCollections of Complex DatatypesCollections of recordsCollections of objects and other complex typesMultilevel CollectionsUnnamed multilevel collections: emulation of multidimensional arraysExploring the multdim APIExtending string_tracker with multilevel collectionsHow deeply can I nest collections?Working with Collections in SQLThe CAST pseudo-functionThe MULTISET pseudo-functionThe TABLE pseudo-functionSorting contents of collections
Nested Table Multiset Operations
Testing Equality and Membership of Nested TablesChecking for Membership of an Element in a Nested TablePerforming High-Level Set OperationsHandling Duplicates in a Nested Table
Maintaining Schema-Level Collections
Necessary PrivilegesCollections and the Data Dictionary
13. Miscellaneous Datatypes
The BOOLEAN Datatype
The RAW Datatype
The UROWID and ROWID Datatypes
Getting ROWIDsUsing ROWIDs
The LOB Datatypes
Working with LOBs
Understanding LOB LocatorsEmpty Versus NULL LOBsWriting into a LOBReading from a LOBBFILEs Are DifferentCreating a BFILE locatorAccessing BFILEsUsing BFILEs to load LOB columnsSecureFiles Versus BasicFilesDeduplicationCompressionEncryptionTemporary LOBsCreating a temporary LOBFreeing a temporary LOBChecking to see whether a LOB is temporaryManaging temporary LOBsNative LOB OperationsSQL semanticsSQL semantics may yield temporary LOBsPerformance impact of using SQL semanticsLOB Conversion Functions
Predefined Object Types
The XMLType TypeThe URI TypesThe Any Types
IV. SQL in PL/SQL
14. DML and Transaction Management
DML in PL/SQL
A Quick Introduction to DMLThe INSERT statementThe UPDATE statementThe DELETE statementThe MERGE statementCursor Attributes for DML OperationsRETURNING Information from DML StatementsDML and Exception HandlingDML and RecordsRecord-based insertsRecord-based updatesUsing records with the RETURNING clauseRestrictions on record-based inserts and updates
Transaction Management
The COMMIT StatementThe ROLLBACK StatementThe SAVEPOINT StatementThe SET TRANSACTION StatementThe LOCK TABLE Statement
Autonomous Transactions
Defining Autonomous TransactionsRules and Restrictions on Autonomous TransactionsTransaction VisibilityWhen to Use Autonomous TransactionsBuilding an Autonomous Logging Mechanism
15. Data Retrieval
Cursor Basics
Some Data Retrieval TermsTypical Query OperationsIntroduction to Cursor AttributesThe %FOUND attributeThe %NOTFOUND attributeThe %ROWCOUNT attributeThe %ISOPEN attributeThe %BULK_ROWCOUNT attributeThe %BULK_EXCEPTIONS attributeReferencing PL/SQL Variables in a CursorChoosing Between Explicit and Implicit Cursors
Working with Implicit Cursors
Implicit Cursor ExamplesError Handling with Implicit CursorsImplicit SQL Cursor Attributes
Working with Explicit Cursors
Declaring Explicit CursorsNaming your cursorDeclaring cursors in packagesOpening Explicit CursorsFetching from Explicit CursorsExamples of explicit cursorsFetching past the last rowColumn Aliases in Explicit CursorsClosing Explicit CursorsExplicit Cursor AttributesCursor ParametersGeneralizing cursors with parametersOpening cursors with parametersScope of cursor parametersCursor parameter modesDefault values for parameters
SELECT...FOR UPDATE
Releasing Locks with COMMITThe WHERE CURRENT OF Clause
Cursor Variables and REF CURSORs
Why Cursor Variables?Similarities to Static CursorsDeclaring REF CURSOR TypesDeclaring Cursor VariablesOpening Cursor VariablesFetching from Cursor VariablesHandling the ROWTYPE_MISMATCH exceptionRules for Cursor VariablesCompile-time rowtype matching rulesRuntime rowtype matching rulesCursor variable aliasesScope of cursor objectPassing Cursor Variables as ArgumentsIdentifying the REF CURSOR typeSetting the parameter modeCursor Variable Restrictions
Cursor Expressions
Using Cursor ExpressionsRetrieve a subquery as a columnImplement a streaming function with the CURSOR expressionRestrictions on Cursor Expressions
16. Dynamic SQL and Dynamic PL/SQL
NDS Statements
The EXECUTE IMMEDIATE StatementThe OPEN FOR StatementFETCH into variables or recordsThe USING clause in OPEN FORAbout the Four Dynamic SQL MethodsMethod 1Method 2Method 3Method 4
Binding Variables
Argument ModesDuplicate PlaceholdersPassing NULL Values
Working with Objects and Collections
Dynamic PL/SQL
Build Dynamic PL/SQL BlocksReplace Repetitive Code with Dynamic Blocks
Recommendations for NDS
Use Invoker Rights for Shared ProgramsAnticipate and Handle Dynamic ErrorsUse Binding Rather Than ConcatenationMinimize the Dangers of Code InjectionRestrict privileges tightly on user schemasUse bind variables whenever possibleCheck dynamic text for dangerous textUse DBMS_ASSERT to validate inputs
When to Use DBMS_SQL
Parse Very Long StringsObtain Information About Query ColumnsMeet Method 4 Dynamic SQL RequirementsThe “in table” procedural interfaceSteps for intab constructionConstructing the SELECTDefining the cursor structureRetrieving and displaying dataMinimize Parsing of Dynamic Cursors
Oracle Database 11g New Features
DBMS_SQL.TO_REFCURSOR FunctionDBMS_SQL.TO_CURSOR FunctionEnhanced Security for DBMS_SQLUnpredictable cursor numbersDenial of access to DBMS_SQL when bad cursor number is used (ORA-24971)Rejection of DBMS_SQL operation when effective user changes (ORA-24970)
V. PL/SQL Application Construction
17. Procedures, Functions, and Parameters
Modular Code
Procedures
Calling a ProcedureThe Procedure HeaderThe Procedure BodyThe END LabelThe RETURN Statement
Functions
Structure of a FunctionThe RETURN DatatypeThe END LabelCalling a FunctionFunctions Without ParametersThe Function HeaderThe Function BodyThe RETURN StatementRETURN any valid expressionMultiple RETURNsRETURN as last executable statement
Parameters
Defining ParametersActual and Formal ParametersParameter ModesIN modeOUT modeIN OUT modeExplicit Association of Actual and Formal Parameters in PL/SQLPositional notationNamed notationBenefits of named notationThe NOCOPY Parameter Mode QualifierDefault Values
Local or Nested Modules
Benefits of Local ModularizationReducing code volumeImproving readabilityScope of Local ModulesSprucing Up Your Code with Local Modules
Module Overloading
Benefits of OverloadingSupporting many data combinationsRestrictions on OverloadingOverloading with Numeric Types
Forward Declarations
Advanced Topics
Calling Your Function From Inside SQLRequirements for calling functions in SQLRestrictions on user-defined functions in SQLRead consistency and user-defined functionsTable FunctionsCalling a function in a FROM clausePassing table function results with a cursor variableCreating a streaming functionCreating a pipelined functionEnabling a function for parallel executionDeterministic Functions
Go Forth and Modularize!
18. Packages
Why Packages?
Demonstrating the Power of the PackageSome Package-Related ConceptsDiagramming Privacy
Rules for Building Packages
The Package SpecificationThe Package BodyInitializing PackagesExecute complex initialization logicCache static session informationAvoid side effects when initializingWhen initialization fails
Rules for Calling Packaged Elements
Working with Package Data
Global Within a Single Oracle SessionGlobal Public DataPackaged CursorsDeclaring packaged cursorsWorking with packaged cursorsSerializable Packages
When to Use Packages
Encapsulate Data AccessAvoid Hardcoding LiteralsImprove Usability of Built-in FeaturesGroup Together Logically Related FunctionalityCache Static Session Data
Packages and Object Types
19. Triggers
DML Triggers
DML Trigger ConceptsDML trigger scriptsTransaction participationCreating a DML TriggerThe WHEN clauseWorking with NEW and OLD pseudo-recordsDetermining the DML action within a triggerDML Trigger Example: No Cheating Allowed!Applying the WHEN clauseUsing pseudo-records to fine-tune trigger executionMultiple Triggers of the Same TypeWho Follows WhomMutating Table ErrorsCompound Triggers: Putting It All In One PlaceJust like a packageNot just like a packageCompound following
DDL Triggers
Creating a DDL TriggerAvailable EventsAvailable AttributesWorking with Events and AttributesWhat column did I touch?Lists returned by attribute functionsDropping the UndroppableThe INSTEAD OF CREATE Trigger
Database Event Triggers
Creating a Database Event TriggerThe STARTUP TriggerThe SHUTDOWN TriggerThe LOGON TriggerThe LOGOFF TriggerThe SERVERERROR TriggerSERVERERROR examplesCentral error handler
INSTEAD OF Triggers
Creating an INSTEAD OF TriggerThe INSTEAD OF INSERT TriggerThe INSTEAD OF UPDATE TriggerThe INSTEAD OF DELETE TriggerPopulating the TablesINSTEAD OF Triggers on Nested Tables
AFTER SUSPEND Triggers
Setting Up for the AFTER SUSPEND TriggerLooking at the Actual TriggerThe ORA_SPACE_ERROR_INFO FunctionThe DBMS_RESUMABLE PackageTrapped Multiple TimesTo Fix or Not to Fix?
Maintaining Triggers
Disabling, Enabling, and Dropping TriggersCreating Disabled TriggersViewing TriggersChecking the Validity of Triggers
20. Managing PL/SQL Code
Managing Code in the Database
Overview of Data Dictionary ViewsDisplay Information About Stored ObjectsDisplay and Search Source CodeUse Program Size to Determine Pinning RequirementsObtain Properties of Stored CodeAnalyze and Modify Trigger State Through ViewsAnalyze Argument InformationAnalyze Identifier Usage (Oracle Database 11g’s PL/Scope)
Managing Dependencies and Recompiling Code
Analyzing Dependencies with Data Dictionary ViewsFine-Grained Dependency (Oracle Database 11g)Remote DependenciesLimitations of Oracle’s Remote Invocation ModelRecompiling Invalid Program UnitsAutomatic runtime compilationALTER...COMPILE recompilationSchema-level recompilation
Compile-Time Warnings
A Quick ExampleEnabling Compile-Time WarningsSome Handy WarningsPLW-05000: Mismatch in NOCOPY qualification between specification and bodyPLW-05001: Previous use of ’string’ (at line string) conflicts with this usePLW-05003: Same actual parameter (string and string) at IN and NOCOPY may have side effectsPLW-05004: Identifier string is also declared in STANDARD or is a SQL built-inPLW-05005: Function string returns without value at line stringPLW-06002: Unreachable codePLW-07203: Parameter ’string’ may benefit from use of the NOCOPY compiler hintPLW-07204: Conversion away from column type may result in suboptimal query planPLW-06009: Procedure “string” OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR (Oracle Database 11g)
Testing PL/SQL Programs
Typical, Tawdry Testing TechniquesGeneral Advice for Testing PL/SQL CodeAutomated Testing Options for PL/SQLTesting with utPLSQLTesting with Quest Code Tester for Oracle
Tracing PL/SQL Execution
DBMS_APPLICATION_INFOQuest Error Manager TracingThe DBMS_TRACE FacilityInstalling DBMS_TRACEDBMS_TRACE programsControl trace file contentsPause and resume the trace processFormat of collected data
Debugging PL/SQL Programs
The Wrong Way to DebugDisorganized debuggingIrrational debuggingDebugging Tips and StrategiesUse a source code debuggerGather dataRemain logical at all timesAnalyze instead of tryingTake breaks, and ask for helpChange and test one area of code at a time
Protecting Stored Code
Restrictions on and Limitations of WrappingUsing the Wrap ExecutableDynamic Wrapping with DBMS_DDLGuidelines for Working with Wrapped Code
Introduction to Edition-Based Redefinition (Oracle Database 11g Release 2)
21. Optimizing PL/SQL Performance
Tools to Assist in Optimization
Analyzing Memory UsageIdentifying Bottlenecks in PL/SQL CodeDBMS_PROFILERHierarchical profilerCalculating Elapsed TimeChoosing the Fastest ProgramAvoiding Infinite LoopsPerformance-Related Warnings
The Optimizing Compiler
Insights on How the Optimizer WorksRuntime Optimization of Fetch Loops
Data Caching Techniques
Package-Based CachingWhen to use package-based cachingA simple example of package-based cachingCaching table contents in a packageJust-in-time caching of table dataDeterministic Function CachingFunction Result Cache (Oracle Database 11g)How to use the function result cacheThe RELIES_ON clauseFunction result cache example: A deterministic functionFunction result cache example: Querying data from a tableFunction result cache example: Caching a collectionWhen to use the function result cacheWhen not to use the function result cacheUseful details of function result cache behaviorManaging the function result cacheThe Virtual Private Database and function result cachingCaching Summary
Bulk Processing for Multirow SQL
High Speed Querying with BULK COLLECTLimiting rows retrieved with BULK COLLECTBulk fetching of multiple columnsUsing the RETURNING clause with bulk operationsHigh Speed DML with FORALLSyntax of the FORALL statementFORALL examplesCursor attributes for FORALLROLLBACK behavior with FORALLContinuing past exceptions with SAVE EXCEPTIONSDriving FORALL with nonsequential arraysINDICES OF exampleVALUES OF example
Improving Performance With Pipelined Table Functions
Replacing Row-Based Inserts with Pipelined Function-Based LoadsA pipelined function implementationLoading from a pipelined functionTuning pipelined functions with array fetchesExploiting parallel pipelined functions for ultimate performanceEnabling parallel pipelined function executionTuning Merge Operations with Pipelined FunctionsRow-based PL/SQL merge processingUsing pipelined functions for set-based MERGEAsynchronous Data Unloading with Parallel Pipelined FunctionsA typical data-extract programA parallel-enabled pipelined function unloaderPerformance Implications of Partitioning and Streaming Clauses in Parallel Pipelined FunctionsRelative performance of partitioning and streaming combinationsPartitioning with skewed dataPipelined Functions and the Cost-Based OptimizerCardinality heuristics for pipelined table functionsUsing optimizer dynamic sampling for pipelined functionsProviding cardinality statistics to the optimizerExtensible Optimizer and pipelined function cardinalityTuning Complex Data Loads with Pipelined FunctionsOne source, two targetsPiping multiple record types from pipelined functionsUsing object-relational featuresA multitype pipelined functionQuerying a multitype pipelined functionLoading multiple tables from a multitype pipelined functionAn alternative multitype methodA Final Word on Pipelined Functions
Specialized Optimization Techniques
Using the NOCOPY Parameter Mode HintRestrictions on NOCOPYPerformance benefits of NOCOPYThe downside of NOCOPYUsing the Right DatatypeAvoid implicit conversionsUse PLS_INTEGER for intensive integer computationsUse BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic
Stepping Back for the Big Picture on Performance
22. I/O and PL/SQL
Displaying Information
Enabling DBMS_OUTPUTWrite Lines to the BufferRead the Contents of the Buffer
Reading and Writing Files
The UTL_FILE_DIR ParameterSetting up directoriesSpecifying file locations when opening filesWork with Oracle DirectoriesOpen FilesIs the File Already Open?Close FilesRead from FilesGET_LINE exceptionsHandy encapsulation for GET_LINEWrite to FilesWriting formatted text to fileCopy FilesDelete FilesRename and Move FilesRetrieve File Attributes
Sending Email
Oracle PrerequisitesConfiguring Network SecuritySend a Short (32,767 or Less) Plaintext MessageInclude “Friendly” Names in Email AddressesSend a Plaintext Message of Arbitrary LengthSend a Message with a Short (< 32,767) AttachmentSend a Small File (< 32767) as an AttachmentAttach a File of Arbitrary Size
Working with Web-Based Data (HTTP)
Retrieve a Web Page in “Pieces”Retrieve a Web Page into a LOBAuthenticate Using HTTP Username/PasswordRetrieve an SSL-Encrypted Web Page (Via HTTPS)Submit Data to a Web Page via GET or POSTDisable Cookies or Make Cookies PersistentRetrieve Data from an FTP ServerUse a Proxy Server
Other Types of I/O Available in PL/SQL
Database Pipes, Queues, and AlertsTCP SocketsOracle’s Built-in Web Server
VI. Advanced PL/SQL Topics
23. Application Security and PL/SQL
Security Overview
Encryption
Key LengthAlgorithmsPadding and ChainingThe DBMS_CRYPTO PackageAlgorithmsPadding and chainingEncrypting DataEncrypting LOBsSecureFilesDecrypting DataPerforming Key GenerationPerforming Key ManagementA single key for the databaseA single key for each rowA combined approachCryptographic HashingUsing Message Authentication CodesUsing Transparent Data Encryption (TDE)Transparent Tablespace Encryption
Row-Level Security
Why Learn About RLS?A Simple RLS ExampleUsing Dynamic PoliciesShared static policyContext-sensitive policyShared context-sensitive policyUsing Column-Sensitive RLSRLS DebuggingInterpreting errorsPerforming direct path operationsViewing SQL statements
Application Contexts
Using Application ContextsSecurity in ContextsContexts as Predicates in RLSIdentifying Non-Database Users
Fine-Grained Auditing
Why Learn About FGA?A Simple FGA ExampleAccess How Many Columns?Checking the Audit TrailUsing Bind VariablesUsing Handler Modules
24. PL/SQL Architecture
Who (or What) is DIANA?
How Does Oracle Execute PL/SQL Code?
An ExampleCompiler Limits
The Default Packages of PL/SQL
Execution Authority Models
The Definer Rights ModelAdvantages of definer rightsDisadvantages of definer rightsWhere’d my table goHow do I maintain all that codeDynamic SQL and definer rightsPrivilege escalation and SQL injectionThe Invoker Rights ModelInvoker rights syntaxRules and restrictions on invoker rightsCombining Rights Models
Conditional Compilation
Examples of Conditional CompilationUse application package constants in $IF directiveToggle tracing through conditional compilation flagsThe Inquiry DirectiveThe DBMS_DB_VERSION packageSetting compilation environment parametersReferencing unit name and line numberUsing the PLSQL_CCFLAGS parameterThe $IF DirectiveThe $ERROR DirectiveSynchronizing Code with Packaged ConstantsProgram-Specific Settings with Inquiry DirectivesWorking with Postprocessed Code
PL/SQL and Database Instance Memory
PGA, UGA, and CGACursors, Memory, and MoreTips on Reducing Memory UseStatement sharingBind variablesPackaging to improve memory use and performanceLarge collections in PL/SQLBULK COLLECT...LIMIT operationsPreservation of stateWhat to Do if You Run Out of Memory
Native Compilation
When to Run Interpreted ModeWhen to Go NativeNative Compilation and Database Release
What You Need to Know
25. Globalization and Localization in PL/SQL
Overview and Terminology
Unicode Primer
National Character Set DatatypesCharacter EncodingGlobalization Support ParametersUnicode FunctionsASCIISTRCOMPOSEDECOMPOSEINSTR/INSTRB/INSTRC/INSTR2/INSTR4LENGTH/LENGTHB/LENGTHC/LENGTH2/LENGTH4SUBSTR/SUBSTRB/SUBSTRC/SUBSTR2/SUBSTR4UNISTR
Character Semantics
String Sort Order
Binary SortMonolingual SortMultilingual Sort
Multilingual Information Retrieval
IR and PL/SQL
Date/Time
Timestamp DatatypesDate/Time Formatting
Currency Conversion
Globalization Development Kit for PL/SQL
UTL_I18N Utility PackageUTL_LMS Error-Handling PackageGDK Implementation OptionsMethod 1: Locale buttonsMethod 2: User administrationMethod 3: Hybrid
26. Object-Oriented Aspects of PL/SQL
Introduction to Oracle’s Object Features
Object Types by Example
Creating a Base TypeCreating a SubtypeMethodsInvoking Supertype Methods in Oracle Database 11gStoring, Retrieving, and Using Persistent ObjectsObject identityThe VALUE functionThe TREAT functionEvolution and CreationBack to Pointers?Using REFsThe UTL_REF packageREFs and type hierarchiesDangling REFsGeneric Data: The ANY TypesPreview: What ANYDATA is notDealing with ANYDATACreating a transient typeI Can Do It MyselfComparing ObjectsAttribute-level comparisonThe MAP methodThe ORDER methodAdditional comparison recommendations
Object Views
A Sample Relational SystemObject View with a Collection AttributeObject SubviewObject View with Inverse RelationshipINSTEAD OF TriggersThe case againstThe case forThe bigger questionDifferences Between Object Views and Object TablesOID uniqueness“Storeability” of physical versus virtual REFsREFs to nonunique OIDs
Maintaining Object Types and Object Views
Data DictionaryPrivilegesThe EXECUTE privilegeThe UNDER privilegeThe DEBUG privilegeThe DML privileges
Concluding Thoughts from a (Mostly) Relational Developer
27. Calling Java from PL/SQL
Oracle and Java
Getting Ready to Use Java in Oracle
Installing JavaBuilding and Compiling Your Java CodeSetting Permissions for Java Development and ExecutionJava security for Oracle through 8.1.5Java security for Oracle from 8.1.6
A Simple Demonstration
Finding the Java FunctionalityBuilding a Custom Java ClassCompiling and Loading into OracleBuilding a PL/SQL WrapperDeleting Files from PL/SQL
Using loadjava
Using dropjava
Managing Java in the Database
The Java Namespace in OracleExamining Loaded Java Elements
Using DBMS_JAVA
LONGNAME: Converting Java Long NamesGET_, SET_, and RESET_COMPILER_OPTION: Getting and Setting (a Few) Compiler OptionsSET_OUTPUT: Enabling Output from JavaEXPORT_SOURCE, EXPORT_RESOURCE, and EXPORT_CLASS: Exporting Schema Objects
Publishing and Using Java in PL/SQL
Call SpecsSome Rules for Call SpecsMapping DatatypesCalling a Java Method in SQLException Handling with JavaExtending File I/O CapabilitiesPolishing up the delete methodObtaining directory contentsOther Examples
28. External Procedures
Introduction to External Procedures
Example: Invoking an Operating System CommandArchitecture of External Procedures
The Oracle Net Configuration
Specifying the Listener ConfigurationSecurity Characteristics of the Configuration
Setting Up Multithreaded Mode
Creating an Oracle Library
Writing the Call Specification
The Call Spec: Overall SyntaxParameter Mapping: The Example RevisitedParameter Mapping: The Full StoryMore Syntax: The PARAMETERS ClausePARAMETERS PropertiesThe INDICATOR propertyThe LENGTH propertyThe MAXLEN propertyThe CHARSETID and CHARSETFORM properties
Raising an Exception from the Called C Program
Nondefault Agents
Maintaining External Procedures
Dropping LibrariesData DictionaryRules and Warnings
A. Regular Expression Metacharacters and Function Parameters
Metacharacters
Functions and Parameters
Regular Expression FunctionsREGEXP_COUNT (Oracle Database 11g Only)REGEXP_INSTRREGEXP_LIKEREGEXP_REPLACEREGEXP_SUBSTRRegular Expression Parameters
B. Number Format Models
C. Date Format Models
Index
About the Authors
Colophon
Copyright

Content preview from Oracle PL/SQL Programming, 5th Edition

Chapter 16. Dynamic SQL and Dynamic PL/SQL

Dynamic SQL refers to SQL statements that are constructed and executed at runtime. Dynamic is the opposite of static. Static SQL refers to SQL statements that are fully specified, or fixed, at the time the code containing that statement is compiled. Dynamic PL/SQL refers to entire PL/SQL blocks of code that are constructed dynamically, then compiled and executed.

Time for a confession: I have had more fun writing dynamic SQL and dynamic PL/SQL programs than just about anything else I have ever done with the PL/SQL language. By constructing and executing dynamically, you gain a tremendous amount of flexibility. You can also build extremely generic and widely useful reusable code.

So what can you do with dynamic SQL and dynamic PL/SQL?^[16] Here are just a few ideas:

Execute DDL statements: You can only execute queries and DML statements with static SQL inside PL/SQL. What if you want to create a table or drop an index? Time for dynamic SQL!
Support ad hoc query and update requirements of web-based applications: A common requirement of Internet applications is that users may be able to specify which columns they want to see and vary the order in which they see the data (of course, users don’t realize they are doing so).
Softcode business rules and formulas: Rather than hardcoding business rules and formulas into your code, you can place that logic in tables. At runtime, you can generate and then execute the PL/SQL code needed to apply the rules.

Ever ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780596805401Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Oracle PL/SQL Programming, 5th Edition

by Steven Feuerstein, Bill Pribyl

Chapter 16. Dynamic SQL and Dynamic PL/SQL

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

More than 5,000 organizations count on O’Reilly

Julian F.

Addison B.

Amir M.

Mark W.

You might also like

Oracle PL/SQL Programming, 6th Edition

Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming Fundamentals LiveLessons

Learning Oracle PL/SQL

Publisher Resources