book

Oracle PL/SQL Programming, 6th Edition

by Steven Feuerstein, Bill Pribyl

February 2014

Intermediate to advanced

1380 pages

38h 8m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Dedication
A Note Regarding Supplemental Files
Preface
Objectives of This BookStructure of This BookAbout the ContentsWhat This Book Does Not CoverConventions Used in This BookWhich Platform or Version?About the CodeUsing Code ExamplesSafari® Books OnlineHow to Contact UsAcknowledgments
I. Programming in PL/SQL
1. Introduction to PL/SQL
What Is PL/SQL?The Origins of PL/SQLThe Early Years of PL/SQLImproved Application PortabilityImproved Execution Authority and Transaction IntegrityHumble Beginnings, Steady ImprovementSo This Is PL/SQLIntegration with SQLControl and Conditional LogicWhen Things Go WrongAbout PL/SQL VersionsOracle Database 12c New PL/SQL FeaturesMore PL/SQL-only datatypes cross PL/SQL-to-SQL interfaceACCESSIBLE_BY clauseImplicit statement resultsBEQUEATH CURRENT_USER viewsGrant roles to program unitsNew conditional compilation directivesOptimizing function execution in SQLUsing %ROWTYPE with invisible columnsFETCH FIRST clause and BULK COLLECTThe UTL_CALL_STACK packageResources for PL/SQL DevelopersThe O’Reilly PL/SQL SeriesPL/SQL on the InternetSome Words of AdviceDon’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 DatabaseCreating and Editing Source CodeSQL*PlusStarting 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*PlusPerforming Essential PL/SQL TasksCreating a Stored ProgramExecuting a Stored ProgramShowing Stored ProgramsManaging Grants and Synonyms for Stored ProgramsDropping a Stored ProgramHiding the Source Code of a Stored ProgramEditing Environments for PL/SQLCalling PL/SQL from Other LanguagesC: 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 StructureAnonymous BlocksNamed BlocksNested BlocksScopeQualify All References to Variables and Columns in SQL StatementsImprove readabilityAvoid bugs through qualifiersVisibilityVisible identifiersQualified identifiersQualifying identifier names with module namesNested programsThe PL/SQL Character SetIdentifiersReserved WordsReserved wordsIdentifiers from STANDARD packageHow to avoid using reserved wordsWhitespace and KeywordsLiteralsNULLsEmbedding Single Quotes Inside a Literal StringNumeric LiteralsBoolean LiteralsThe Semicolon DelimiterCommentsSingle-Line Comment SyntaxMultiline Comment SyntaxThe PRAGMA KeywordLabels
II. PL/SQL Program Structure
4. Conditional and Sequential Control
IF StatementsThe IF-THEN CombinationThe IF-THEN-ELSE CombinationThe IF-THEN-ELSIF CombinationAvoiding IF Syntax GotchasNested IF StatementsShort-Circuit EvaluationCASE Statements and ExpressionsSimple CASE StatementsSearched CASE StatementsNested CASE StatementsCASE ExpressionsThe GOTO StatementThe NULL StatementImproving Program ReadabilityUsing NULL After a Label
5. Iterative Processing with Loops
Loop BasicsExamples of Different LoopsStructure of PL/SQL LoopsThe Simple LoopTerminating a Simple Loop: EXIT and EXIT WHENEmulating a REPEAT UNTIL LoopThe Intentionally Infinite LoopThe WHILE LoopThe Numeric FOR LoopRules for Numeric FOR LoopsExamples of Numeric FOR LoopsHandling Nontrivial IncrementsThe Cursor FOR LoopExample of Cursor FOR LoopsLoop LabelsThe CONTINUE StatementTips for Iterative ProcessingUse 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 TerminologyDefining ExceptionsDeclaring Named ExceptionsAssociating Exception Names with Error CodesUsing EXCEPTION_INITRecommended uses of EXCEPTION_INITAbout Named System ExceptionsScope of an ExceptionRaising ExceptionsThe RAISE StatementUsing RAISE_APPLICATION_ERRORHandling ExceptionsBuilt-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 CodeBuilding an Effective Error Management ArchitectureDecide 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 HandlingMaking the Most of PL/SQL Error Management
III. PL/SQL Program Data
7. Working with Program Data
Naming Your Program DataOverview of PL/SQL DatatypesCharacter DataNumbersDates, Timestamps, and IntervalsBooleansBinary DataROWIDsREF CURSORsInternet Datatypes“Any” DatatypesUser-Defined DatatypesDeclaring Program DataDeclaring a VariableDeclaring ConstantsThe NOT NULL ClauseAnchored DeclarationsAnchoring to Cursors and TablesBenefits of Anchored DeclarationsSynchronization with database columnsNormalization of local variablesAnchoring to NOT NULL DatatypesProgrammer-Defined SubtypesConversion Between DatatypesImplicit 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 DatatypesThe VARCHAR2 DatatypeThe CHAR DatatypeString SubtypesWorking with StringsSpecifying 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 argumentsString Function Quick Reference
9. Numbers
Numeric DatatypesThe NUMBER TypeThe PLS_INTEGER TypeThe BINARY_INTEGER TypeThe SIMPLE_INTEGER TypeThe BINARY_FLOAT and BINARY_DOUBLE TypesThe SIMPLE_FLOAT and SIMPLE_DOUBLE TypesNumeric SubtypesNumber ConversionsThe 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 ConversionsNumeric OperatorsNumeric FunctionsRounding and Truncation FunctionsTrigonometric FunctionsNumeric Function Quick Reference
10. Dates and Timestamps
Datetime DatatypesDeclaring Datetime VariablesChoosing a Datetime DatatypeGetting the Current Date and TimeInterval DatatypesDeclaring INTERVAL VariablesWhen to Use INTERVALsFinding the difference between two datetime valuesDesignating periods of timeDatetime ConversionsFrom 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 ModeDate and Timestamp LiteralsInterval ConversionsConverting from Numbers to IntervalsConverting Strings to IntervalsFormatting Intervals for DisplayInterval LiteralsCAST and EXTRACTThe CAST FunctionThe EXTRACT FunctionDatetime ArithmeticDate 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 TypesDate/Time Function Quick Reference
11. Records
Records in PL/SQLBenefits 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 Pseudorecords%ROWTYPE and invisible columns (Oracle Database 12c)
12. Collections
Collections OverviewCollections 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 TypeCollection 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 possibleWorking with CollectionsDeclaring Collection TypesDeclaring an associative array collection typeDeclaring a nested table or VARRAYChanging a nested table or 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 pseudofunctionThe COLLECT pseudofunctionThe MULTISET pseudofunctionThe TABLE pseudofunctionSorting contents of collectionsNested Table Multiset OperationsTesting Equality and Membership of Nested TablesChecking for Membership of an Element in a Nested TablePerforming High-Level Set OperationsHandling Duplicates in a Nested TableMaintaining Schema-Level CollectionsNecessary PrivilegesCollections and the Data Dictionary
13. Miscellaneous Datatypes
The BOOLEAN DatatypeThe RAW DatatypeThe UROWID and ROWID DatatypesGetting ROWIDsUsing ROWIDsThe LOB DatatypesWorking with LOBsUnderstanding 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 FunctionsPredefined Object TypesThe XMLType TypeThe URI TypesThe Any Types
IV. SQL in PL/SQL
14. DML and Transaction Management
DML in PL/SQLA 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 updatesTransaction ManagementThe COMMIT StatementThe ROLLBACK StatementThe SAVEPOINT StatementThe SET TRANSACTION StatementThe LOCK TABLE StatementAutonomous TransactionsDefining Autonomous TransactionsRules and Restrictions on Autonomous TransactionsTransaction VisibilityWhen to Use Autonomous TransactionsBuilding an Autonomous Logging Mechanism
15. Data Retrieval
Cursor BasicsSome 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 CursorsWorking with Implicit CursorsImplicit Cursor ExamplesError Handling with Implicit CursorsImplicit SQL Cursor AttributesWorking with Explicit CursorsDeclaring 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 parametersSELECT...FOR UPDATEReleasing Locks with COMMITThe WHERE CURRENT OF ClauseCursor Variables and REF CURSORsWhy Use 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 RestrictionsCursor ExpressionsUsing Cursor ExpressionsRetrieving a subquery as a columnImplementing a streaming function with the CURSOR expressionRestrictions on Cursor Expressions
16. Dynamic SQL and Dynamic PL/SQL
NDS StatementsThe EXECUTE IMMEDIATE StatementThe OPEN FOR StatementFETCH into variables or recordsThe USING clause in OPEN FORAbout the Four Dynamic SQL MethodsMethod 1Method 2Method 3Method 4Binding VariablesArgument ModesDuplicate PlaceholdersPassing NULL ValuesWorking with Objects and CollectionsDynamic PL/SQLBuild Dynamic PL/SQL BlocksReplace Repetitive Code with Dynamic BlocksRecommendations for NDSUse 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 inputsWhen to Use DBMS_SQLObtain Information About Query ColumnsMeeting Method 4 Dynamic SQL RequirementsThe “in table” procedural interfaceSteps for intab constructionConstructing the SELECTDefining the cursor structureRetrieving and displaying dataMinimizing Parsing of Dynamic CursorsOracle Database 11g New Dynamic SQL FeaturesDBMS_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 CodeProceduresCalling a ProcedureThe Procedure HeaderThe Procedure BodyThe END LabelThe RETURN StatementFunctionsStructure of a FunctionThe RETURN DatatypeThe END LabelCalling a FunctionFunctions Without ParametersThe Function HeaderThe Function BodyThe RETURN StatementRETURN any valid expressionMultiple RETURNsRETURN as the last executable statementParametersDefining 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 ValuesLocal or Nested ModulesBenefits of Local ModularizationReducing code volumeImproving readabilityScope of Local ModulesSprucing Up Your Code with Nested SubprogramsSubprogram OverloadingBenefits of OverloadingSupporting many data combinationsRestrictions on OverloadingOverloading with Numeric TypesForward DeclarationsAdvanced TopicsCalling Your Function from Inside SQLRequirements for calling functions in SQLRestrictions on user-defined functions in SQLRead consistency and user-defined functionsDefining PL/SQL subprograms in SQL statements (12.1 and higher)Table 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 FunctionsImplicit Cursor Results (Oracle Database 12c)Go Forth and Modularize!
18. Packages
Why Packages?Demonstrating the Power of the PackageSome Package-Related ConceptsDiagramming PrivacyRules for Building PackagesThe Package SpecificationThe Package BodyInitializing PackagesExecute complex initialization logicCache static session informationAvoid side effects when initializingWhen initialization failsRules for Calling Packaged ElementsWorking with Package DataGlobal Within a Single Oracle SessionGlobal Public DataPackaged CursorsDeclaring packaged cursorsWorking with packaged cursorsSerializable PackagesWhen to Use PackagesEncapsulate Data AccessAvoid Hardcoding LiteralsImprove Usability of Built-in FeaturesGroup Together Logically Related FunctionalityCache Static Session DataPackages and Object Types
19. Triggers
DML TriggersDML 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 followingDDL TriggersCreating a DDL TriggerAvailable EventsAvailable AttributesWorking with Events and AttributesWhat column did I touch?Lists returned by attribute functionsDropping the UndroppableThe INSTEAD OF CREATE TriggerDatabase Event TriggersCreating a Database Event TriggerThe STARTUP TriggerThe SHUTDOWN TriggerThe LOGON TriggerThe LOGOFF TriggerThe SERVERERROR TriggerSERVERERROR examplesCentral error handlerINSTEAD OF TriggersCreating an INSTEAD OF TriggerThe INSTEAD OF INSERT TriggerThe INSTEAD OF UPDATE TriggerThe INSTEAD OF DELETE TriggerPopulating the TablesINSTEAD OF Triggers on Nested TablesAFTER SUSPEND TriggersSetting 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 TriggersDisabling, Enabling, and Dropping TriggersCreating Disabled TriggersViewing TriggersChecking the Validity of Triggers
20. Managing PL/SQL Code
Managing Code in the DatabaseOverview 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 CodeAnalyzing 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 recompilationCompile-Time WarningsA 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_ERRORTesting PL/SQL ProgramsTypical, Tawdry Testing TechniquesGeneral Advice for Testing PL/SQL CodeAutomated Testing Options for PL/SQLTracing PL/SQL ExecutionDBMS_UTILITY.FORMAT_CALL_STACKUTL_CALL_STACK (Oracle Database 12c)DBMS_APPLICATION_INFOTracing with opp_traceThe DBMS_TRACE FacilityInstalling DBMS_TRACEDBMS_TRACE programsControl trace file contentsPause and resume the trace processFormat of collected dataDebugging PL/SQL ProgramsThe 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 timeUsing Whitelisting to Control Access to Program UnitsProtecting Stored CodeRestrictions on and Limitations of WrappingUsing the Wrap ExecutableDynamic Wrapping with DBMS_DDLGuidelines for Working with Wrapped CodeIntroduction to Edition-Based Redefinition (Oracle Database 11g Release 2)
21. Optimizing PL/SQL Performance
Tools to Assist in OptimizationAnalyzing Memory UsageIdentifying Bottlenecks in PL/SQL CodeDBMS_PROFILERThe hierarchical profilerCalculating Elapsed TimeChoosing the Fastest ProgramAvoiding Infinite LoopsPerformance-Related WarningsThe Optimizing CompilerInsights on How the Optimizer WorksRuntime Optimization of Fetch LoopsData Caching TechniquesPackage-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 CachingTHe Function Result Cache (Oracle Database 11g)Enabling the function result cacheThe RELIES_ON clause (deprecated in 11.2)Function 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 cacheFine-grained dependencies in 11.2 and higherThe virtual private database and function result cachingCaching SummaryBulk Processing for Repeated SQL Statement ExecutionHigh-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 exampleImproving Performance with Pipelined Table FunctionsReplacing 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 FunctionsSpecialized Optimization TechniquesUsing 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 arithmeticOptimizing Function Performance in SQL (12.1 and higher)Stepping Back for the Big Picture on Performance
22. I/O and PL/SQL
Displaying InformationEnabling DBMS_OUTPUTWrite Lines to the BufferRead the Contents of the BufferReading and Writing FilesThe UTL_FILE_DIR ParameterSetting up directoriesSpecifying file locations when opening filesWorking 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 AttributesSending EmailOracle PrerequisitesConfiguring Network SecuritySend a Short (32,767 Bytes or Less) Plain-Text MessageInclude “Friendly” Names in Email AddressesSend a Plain-Text Message of Arbitrary LengthSend a Message with a Short (32,767 Bytes or Less) AttachmentSend a Small File (32,767 Bytes or Less) as an AttachmentAttach a File of Arbitrary SizeWorking 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 ServerOther Types of I/O Available in PL/SQLDatabase Pipes, Queues, and AlertsTCP SocketsOracle’s Built-in Web Server
VI. Advanced PL/SQL Topics
23. Application Security and PL/SQL
Security OverviewEncryptionKey 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 EncryptionTransparent Tablespace EncryptionRow-Level SecurityWhy Learn About RLS?A Simple RLS ExampleStatic Versus Dynamic PoliciesShared static policiesContext-sensitive policiesShared context-sensitive policiesUsing Column-Sensitive RLSRLS DebuggingInterpreting errorsPerforming direct path operationsViewing SQL statementsApplication ContextsUsing Application ContextsSecurity in ContextsContexts as Predicates in RLSIdentifying Nondatabase UsersFine-Grained AuditingWhy Learn About FGA?A Simple FGA ExampleAccess How Many Columns?Checking the Audit TrailUsing Bind VariablesUsing Handler Modules
24. PL/SQL Architecture
DIANAHow Oracle Executes PL/SQL CodeAn ExampleCompiler LimitsThe Default Packages of PL/SQLExecution Authority ModelsThe Definer Rights ModelAdvantages of definer rightsDisadvantages of definer rightsWhere’d my table go?How do I maintain all that code?Dynamic SQL and definer rightsPrivilege escalation and SQL injectionThe Invoker Rights ModelInvoker rights syntaxRules and restrictions on invoker rightsCombining Rights ModelsGranting Roles to PL/SQL Program Units (Oracle Database 12c)“Who Invoked Me?” Functions (Oracle Database 12c)BEQUEATH CURRENT_USER for Views (Oracle Database 12c)Constraining Invoker Rights Privileges (Oracle Database 12c)Conditional CompilationExamples 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 CodePL/SQL and Database Instance MemoryThe SGA, PGA, and UGASystem global area (SGA)Process global area (PGA)User global area (UGA)Cursors, 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 MemoryNative CompilationWhen to Run in Interpreted ModeWhen to Go NativeNative Compilation and Database ReleaseWhat You Need to Know
25. Globalization and Localization in PL/SQL
Overview and TerminologyUnicode PrimerNational Character Set DatatypesCharacter EncodingGlobalization Support ParametersUnicode FunctionsASCIISTRCOMPOSEDECOMPOSEINSTR/INSTRB/INSTRC/INSTR2/INSTR4LENGTH/LENGTHB/LENGTHC/LENGTH2/LENGTH4SUBSTR/SUBSTRB/SUBSTRC/SUBSTR2/SUBSTR4UNISTRCharacter SemanticsString Sort OrderBinary SortMonolingual SortMultilingual SortMultilingual Information RetrievalIR and PL/SQLDate/TimeTimestamp DatatypesDate/Time FormattingCurrency ConversionGlobalization Development Kit for PL/SQLUTL_118N 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 FeaturesObject Types by ExampleCreating a Base TypeCreating a SubtypeMethodsInvoking Supertype Methods in Oracle Database 11g and LaterStoring, 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 recommendationsObject ViewsA 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 OIDsMaintaining Object Types and Object ViewsData DictionaryPrivilegesThe EXECUTE privilegeThe UNDER privilegeThe DEBUG privilegeThe DML privilegesConcluding Thoughts from a (Mostly) Relational Developer
27. Calling Java from PL/SQL
Oracle and JavaGetting Ready to Use Java in OracleInstalling 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.6A Simple DemonstrationFinding the Java FunctionalityBuilding a Custom Java ClassCompiling and Loading into OracleBuilding a PL/SQL WrapperDeleting Files from PL/SQLUsing loadjavaUsing dropjavaManaging Java in the DatabaseThe Java Namespace in OracleExamining Loaded Java ElementsUsing DBMS_JAVALONGNAME: 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 ObjectsPublishing and Using Java in PL/SQLCall 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 ProceduresExample: Invoking an Operating System CommandArchitecture of External ProceduresOracle Net ConfigurationSpecifying the Listener ConfigurationSecurity Characteristics of the ConfigurationSetting Up Multithreaded ModeCreating an Oracle LibraryWriting the Call SpecificationThe 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 propertiesRaising an Exception from the Called C ProgramNondefault AgentsMaintaining External ProceduresDropping LibrariesData DictionaryRules and Warnings
A. Regular Expression Metacharacters and Function Parameters
MetacharactersFunctions and ParametersRegular Expression FunctionsREGEXP_COUNT (Oracle Database 11g and later)REGEXP_INSTRREGEXP_LIKEREGEXP_REPLACEREGEXP_SUBSTRRegular Expression Parameters
B. Number Format Models
Denoting Monetary Units
C. Date Format Models
Index
About the Authors
Colophon
Copyright

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

Chapter 10. Dates and Timestamps

Most applications require the storage and manipulation of dates and times. Dates are quite complicated: not only are they highly formatted data, but there are myriad rules for determining valid values and valid calculations (leap days and years, daylight saving time changes, national and company holidays, date ranges, etc.). Fortunately, the Oracle database and PL/SQL provide a set of true datetime datatypes that store both date and time information using a standard internal format.

For any datetime value, the database stores some or all of the following information:

Year
Month
Day
Hour
Minute
Second
Time zone region
Time zone hour offset from UTC
Time zone minute offset from UTC

Support for true datetime datatypes is only half the battle. You also need a language that can manipulate those values in a natural and intelligent manner—as actual dates and times. To that end, Oracle provides you with support for SQL standard interval arithmetic, datetime literals, and a comprehensive suite of functions with which to manipulate date and time information.

Datetime Datatypes

For a long time, the only datetime datatype available was DATE. Oracle9i Database shook things up by introducing three new TIMESTAMP and two new INTERVAL datatypes, offering significant new functionality while also bringing Oracle into closer compliance with the ISO SQL standard. I’ll talk more about the INTERVAL datatypes later in this chapter. The four datetime datatypes are:

DATE: Stores a date and ...

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: 9781449324445Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Oracle PL/SQL Programming, 6th Edition

by Steven Feuerstein, Bill Pribyl

Chapter 10. Dates and Timestamps

Datetime Datatypes

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.