book

Oracle Database 12c PL/SQL Programming

Name: Oracle Database 12c PL/SQL Programming
Author: Michael McLaughlin
ISBN: 9780071812443

by Michael McLaughlin

February 2014

Intermediate to advanced

1192 pages

33h 41m

English

McGraw Hill Computing

Read now

Unlock full access

Cover
Title Page
Copyright Page
About the Author
About the Contributing AuthorAbout the Technical Editor
Contents at a Glance
Contents
Acknowledgments
Introduction
Book OutlinePart I: PL/SQL FundamentalsPart II: PL/SQL ProgrammingPart III: Appendixes and GlossaryLexiconSQL LexiconPL/SQL Stored ProgramsOther ConventionsData Model and Source Code to Download
Part I: Oracle PL/SQL
Chapter 1: Oracle PL/SQL Development Overview
PL/SQL’s History and BackgroundOracle Development ArchitectureThe DatabaseThe PL/SQL LanguageThe Oracle Processing ArchitectureTwo-Tier ModelN-Tier ModelSummaryMastery Check

Chapter 2: New Features
New SQL FeaturesData Catalog DIRECTORY Qualifies a LIBRARY ObjectDefine Tables with Valid-Time (VT) SupportTable with Explicit VT ColumnsTable with Implicit VT ColumnsEnhanced Oracle Native LEFT OUTER JOIN SyntaxDefault Values for Columns Based on SequencesDefault Values for Explicit Null InsertionIdentity ColumnsIncreased Size Limits of String and Raw TypesPass Results from SQL Statements to External ProgramsNative SQL Support for Query Row Limits and OffsetsOracle Database Driver for MySQL ApplicationsSQL CROSS APPLY, OUTER APPLY, and LATERALBequeath CURRENT_USER ViewsNew PL/SQL FeaturesCaching of Invoker Rights FunctionsAbility to White List PL/SQL Program Unit CallersNative Client API Support for PL/SQL TypesNew PL/SQL Package UTL_CALL_STACKDBMS_UTILITY Adds EXPAND_SQL_TEXT SubprogramDBMS_SQL Adds a New Formal Schema to the PARSE ProcedurePL/SQL Functions in SQL WITH ClausePL/SQL-Specific Data Types Allowed in SQLImplicit REF CURSOR Parameter BindingSupporting ScriptsSummaryMastery Check
Chapter 3: PL/SQL Basics
Block StructureExecution BlockBasic Block StructureDeclaration BlockException BlockBehavior of Variables in BlocksAnonymous BlocksNested Anonymous BlocksLocal Named BlocksStored Named BlocksBasic Scalar and Composite Data TypesScalar Data TypesStringsDatesNumbersAttribute and Table AnchoringComposite Data TypesSQL UDTPL/SQL Record TypeSQL CollectionPL/SQL CollectionControl StructuresConditional StructuresIf, Elsif, and Else StatementsCASE StatementIterative StructuresFor Loop StatementsWhile Loop StatementsSimple Loop StatementsExceptionsUser-Defined ExceptionsDynamic User-Defined ExceptionsBulk OperationsFunctions, Procedures, and PackagesFunctionsProceduresPackagesTransaction ScopeSingle Transaction ScopeMultiple Transaction ScopesDatabase TriggersSummaryMastery Check
Chapter 4: Language Fundamentals
Lexical UnitsDelimitersIdentifiersReserved Words and KeywordsPredefined IdentifiersQuoted IdentifiersUser-Defined Variables, Subroutines, and User-Defined Data TypesLiteralsCharacter LiteralsString LiteralsNumeric LiteralsBoolean LiteralsDate and Time LiteralsCommentsVariables and Data TypesVariable Data TypesScalar Data TypesBooleanCharacters and StringsDates, Times, and IntervalsUnicode Characters and StringsNumbersLarge Objects (LOBs)BFILE Data TypeBLOB Data TypeCLOB Data TypeNCLOB Data TypeComposite Data TypesRecordsObjectsCollectionsSystem Reference CursorsSummaryMastery Check
Chapter 5: Control Structures
Conditional StatementsIF StatementsIf-then-else StatementsIf-then-elsif-then-else StatementsCASE StatementsSimple CASE StatementsSearched CASE StatementsConditional Compilation StatementsIterative StatementsSimple Loop StatementsStatic Simple LoopsDynamic Simple LoopsSkipping IterationsFOR Loop StatementsRange FOR Loop StatementsWHILE Loop StatementsCursor StructuresImplicit CursorsSingle-Row Implicit CursorsMultiple-Row Implicit CursorsCursor FOR Loop StatementsExplicit CursorsStatic Explicit CursorsDynamic Explicit CursorsSubcursorsBulk StatementsBULK COLLECT INTO StatementsParallel Collection TargetsRecord Collection TargetsLIMIT-Constrained Collection TargetsFORALL StatementsINSERT StatementUPDATE StatementDELETE Statement%BULK_EXCEPTION HandlingSupporting ScriptsSummaryMastery Check
Chapter 6: Collections
Introduction to CollectionsObject Types: Varray and Table CollectionsVarray CollectionsTable CollectionsScalar Table CollectionsComposite Table CollectionsAssociative ArraysDefining and Using Associative ArraysAssociative Arrays of Scalar Data TypesAssociative Arrays of Composite Data TypesOracle Collection APICOUNT MethodDELETE MethodEXISTS MethodEXTEND MethodFIRST MethodLAST MethodLIMIT MethodNEXT MethodPRIOR MethodTRIM MethodSupporting ScriptsSummaryMastery Check
Chapter 7: Error Management
Exception Types and ScopeCompilation ErrorsRuntime ErrorsExecution and Exception Block ErrorsDeclaration Blck ErrorsException Management Built-in FunctionsUser-Defined ExceptionsDeclaring User-Defined ExceptionsDynamic User-Defined ExceptionsException Stack FunctionsSupporting ScriptsSummaryMastery Check
Part II: PL/SQL Programming
Chapter 8: Functions and Procedures
Function and Procedure ArchitectureTransaction ScopeCalling SubroutinesPositional NotationNamed NotationMixed NotationExclusionary NotationSQL Call NotationFunctionsFunction Model ChoicesCreation OptionsDETERMINISTIC ClausePARALLEL_ENABLE ClausePIPELINED ClauseObject Table FunctionsRESULT_CACHE ClausePass-by-Value FunctionsSystem Reference Cursor FunctionsDeterministic Pass-by-Value FunctionsNondeterministic Pass-by-Value FunctionsDML-Enabled Pass-by-Value FunctionsRecursive FunctionsPass-by-Reference FunctionsProceduresPass-by-Value ProceduresPass-by-Reference ProceduresSupporting ScriptsSummaryMastery Check
Chapter 9: Packages
Package ArchitecturePackage SpecificationPrototype FeaturesSerially Reusable Precompiler DirectiveVariablesTypesComponents: Functions and ProceduresPackage BodyPrototype FeaturesVariablesTypesComponents: Functions and ProceduresDefiner vs. Invoker Rights MechanicsManaging Packages in the Database CatalogFinding, Validating, and Describing PackagesChecking DependenciesComparing Validation Methods: Timestamp vs. SignatureSummaryMastery Check
Chapter 10: Large Objects
Working with Internally Stored LOB TypesLOB Assignments Under 32KSmall LOB AssignmentsLOB Assignments over 32KLOB Construction for Assignments Greater Than 32,767 BytesReading Files into Internally Stored ColumnsReading Local Files into CLOB or NCLOB ColumnsReading Local Files into BLOB ColumnsWorking with LOBs Through Web PagesProcedures to Upload CLOB, NCLOB, or BLOB ColumnsHTML and PHP Components for CLOB and BLOB UploadsHTML and PHP Components for Displaying CLOB and BLOB ColumnsWorking with Binary Files (BFILEs)Creating and Using Virtual DirectoriesReading Canonical Path Names and FilenamesUnderstanding the DBMS_LOB PackagePackage ConstantsBLOB_DEDUPLICATE_REGION Record StructureCLOB_DEDUPLICATE_REGION Record StructurePackage ExceptionsOpening and Closing MethodsCLOSE ProcedureISOPEN FunctionOPEN ProcedureManipulation MethodsAPPEND ProcedureCONVERTTOBLOB ProcedureCONVERTTOCLOB ProcedureCOPY ProcedureERASE ProcedureFRAGMENT_DELETE ProcedureFRAGMENT_INSERT ProcedureFRAGMENT_MOVE ProcedureFRAGMENT_REPLACE ProcedureISSECUREFILE FunctionLOADBLOBFROMFILE ProcedureLOADCLOBFROMFILE ProcedureLOADFROMFILE ProcedureSETOPTIONS ProcedureTRIM ProcedureWRITE ProcedureWRITEAPPEND ProcedureIntrospection MethodsCOMPARE FunctionGETCHUNKSIZE FunctionGET_DEDUPLICATED_REGIONS ProcedureGETLENGTH FunctionGETOPTIONS FunctionGET_STORAGE_LIMIT FunctionINSTR FunctionREAD ProcedureSUBSTR FunctionBFILE MethodsFILECLOSE ProcedureFILECLOSEALL ProcedureFILEEXISTS FunctionFILEGETNAME ProcedureFILEISOPEN FunctionFILEOPEN ProcedureTemporary LOB MethodsCREATETEMPORARY ProcedureISTEMPORARY FunctionFREETEMPORARY ProcedureSecurity Link MethodsCOPY_DBFS_LINK ProcedureCOPY_FROM_DBFS_LINK ProcedureDBFS_LINK_GENERATE_PATH FunctionGETCONTENTTYPE FunctionGET_DBFS_LINK FunctionGET_DBFS_LINK_STATE ProcedureSETCONTENTTYPE ProcedureSET_DBFS_LINK ProcedureSupporting ScriptsThe LONG to CLOB ScriptManage LOBs from the File SystemManage CLOB and BLOB LOBs Through the WebManage BFILE LOBs Through the WebSummaryMastery Check
Chapter 11: Object Types
Object BasicsDeclaring Objects TypesImplementing Object BodiesWhite Listing Object TypesGetters and SettersStatic Member MethodsComparing ObjectsComparing with the MAP Member FunctionComparing with the ORDER Member FunctionInheritance and PolymorphismDeclaring SubclassesImplementing SubclassesType EvolutionImplementing Object Type CollectionsDeclaring Object Type CollectionsImplementing Object Type CollectionsSupporting ScriptsSummaryMastery Check
Chapter 12: Triggers
Introduction to TriggersDatabase Trigger ArchitectureData Definition Language TriggersEvent Attribute FunctionsORA_CLIENT_IP_ADDRESSORA_DATABASE_NAMEORA_DES_ENCRYPTED_PASSWORDORA_DICT_OBJ_NAMEORA_DICT_OBJ_NAME_LISTORA_DICT_OBJ_OWNERORA_DICT_OBJ_OWNER_LISTORA_DICT_OBJ_TYPEORA_GRANTEEORA_INSTANCE_NUMORA_IS_ALTER_COLUMNORA_IS_DROP_COLUMNORA_IS_SERVERERRORORA_LOGIN_USERORA_PARTITION_POSORA_PRIVILEGE_LISTORA_REVOKEEORA_SERVER_ERRORORA_SERVER_ERROR_DEPTHORA_SERVER_ERROR_MSGORA_SERVER_ERROR_NUM_PARAMSORA_SERVER_ERROR_PARAMORA_SQL_TXTORA_SYSEVENTORA_WITH_GRANT_OPTIONSPACE_ERROR_INFOBuilding DDL TriggersData Manipulation Language TriggersStatement-Level TriggersRow-Level TriggersCompound TriggersINSTEAD OF TriggersSystem and Database Event TriggersTrigger RestrictionsMaximum Trigger SizeSQL StatementsLONG and LONG RAW Data TypesMutating TablesSystem TriggersSupporting ScriptsSummaryMastery Check
Chapter 13: Dynamic SQL
Dynamic SQL ArchitectureNative Dynamic SQL (NDS)Dynamic StatementsDynamic DDL StatementsDynamic DML StatementsDynamic Statements with InputsDynamic Statements with Inputs and OutputsDynamic Statements with an Unknown Number of InputsDBMS_SQL PackageDynamic StatementsDynamic DDL StatementsDynamic DML StatementsDynamic Statements with Input VariablesDynamic Statements with Variable Inputs and Fixed OutputsRow-by-Row Statement ProcessingBulk Statement ProcessingDynamic Statements with Variable Inputs and OutputsDBMS_SQL Package DefinitionDBMS_SQL ConstantsDBMS_SQL Data TypesDBMS_SQL Functions and ProceduresSupporting ScriptsSummaryMastery Check
Part III: Appendixes and Glossary
Appendix A: Oracle Database Primer
Oracle Database ArchitectureStarting and Stopping the Oracle Database 12c ServerUnix or Linux OperationsMicrosoft Windows OperationsStarting and Stopping the Oracle ListenerMultiversion Concurrency ControlData TransactionsDML Locking and Isolation ControlDefiner Rights and Invoker RightsDefiner RightsInvoker RightsSQL Interactive and Batch ProcessingSQL*Plus Command-Line InterfaceConnecting to and Disconnecting from SQL*PlusWorking in the SQL*Plus EnvironmentWriting SQL Statements with SQL*PlusSaving SQL Statements with SQL*PlusEditing SQL Statements with SQL*PlusRerunning SQL*Plus SQL Statements from the BufferAborting Entry of SQL Statements in SQL*PlusCalling and Running SQL*Plus Script FilesPassing Parameters to SQL*Plus Script FilesCalling PL/SQL ProgramsWriting SQL*Plus Log FilesOracle SQL Developer InterfaceLaunching Oracle SQL DeveloperConfiguring Oracle SQL DeveloperUsing Oracle SQL DeveloperDatabase AdministrationProvisioning UsersCreating a CDB Oracle UserCreating a PDB Oracle UserGranting Security PrivilegesGranting Oracle PrivilegesRevoking PrivilegesUsing Database ConstraintsNOT NULL ConstraintsUNIQUE ConstraintsPrimary Key ConstraintsForeign Key ConstraintsCHECK ConstraintsSecurity HardeningCategories of AttackersOracle Audit Vault and Database FirewallPassword HardeningListener HardeningData GovernanceOvercoming Resistance to Data GovernanceImplementing Data GovernanceDeveloping a Culture of Data GovernanceSQL TuningEXPLAIN PLAN StatementDBMS_XPLAN PackageSQL TracingTracing Session StatementsEnable Tracing in the Current SessionDisable Tracing in the Current SessionEnable Tracing in Another SessionConvert Raw Trace Files to Readable Trace FilesSummary
Appendix B: SQL Primer
Oracle SQL Data TypesData Definition Language (DDL)CREATE StatementUsersGranting Oracle PrivilegesRevoking PrivilegesTablesSequences and Identity ColumnsNested Collection TypesConstraintsIndexesViewsFunctions, Procedures, and PackagesObject TypesExternal TablesPartitioned TablesSynonymsALTER StatementUsersTablesIndexesObject Types
RENAME Statement
DROP StatementDROP USER StatementDROP TABLESPACE StatementDROP TABLE StatementDROP INDEX StatementDROP VIEW StatementDROP FUNCTION StatementDROP PROCEDURE StatementDROP PACKAGE StatementDROP TYPE StatementTRUNCATE StatementCOMMENT StatementData Manipulation Language (DML)ACID Compliant TransactionsACID Compliant INSERT StatementsACID Compliant UPDATE StatementsACID Compliant DELETE StatementsINSERT StatementInsert by ValuesMultiple-Table Insert StatementsUPDATE StatementGeneric Update by Values and QueriesOracle Update by Values and QueriesUpdate by Correlated QueriesDELETE StatementDelete by Value MatchesDelete Nested Table Row ElementsDelete by Correlated QueriesMERGE StatementTransaction Control Language (TCL)Queries: SELECT StatementsQueries that Return Columns or Results from ColumnsSubqueriesInline ViewsHierarchical QueriesDown the TreeUp the TreeRestricting the Depth of SearchLeaf Node UpQueries that AggregateAggregate Columns OnlyAggregate and Nonaggregate ColumnsQueries that Return Columns or Results SelectivelyOracle Proprietary DECODE StatementANSI SQL CASE OperatorSelective AggregationJoin ResultsJoins that Splice Together RowsCross JoinInner JoinOuter JoinJoins that Splice CollectionsUNIONUNION ALLINTERSECTMINUSSummary
Appendix C: SQL Built-in Functions
Character FunctionsASCII FunctionASCIISTR FunctionCHR FunctionCONCAT FunctionINITCAP FunctionINSTR FunctionLENGTH FunctionLOWER FunctionLPAD FunctionLTRIM FunctionREPLACE FunctionREVERSE FunctionRPAD FunctionRTRIM FunctionUPPER FunctionData Type Conversion FunctionsCAST FunctionCONVERT FunctionTO_CHAR FunctionConverting a String to a Character StringConverting a Date to a Character StringConverting a Number to a Character StringTO_CLOB FunctionTO_DATE FunctionTO_LOB FunctionTO_NCHAR FunctionTO_NCLOB FunctionTO_NUMBER FunctionDate-time Conversion FunctionsADD_MONTHS FunctionCURRENT_DATE FunctionCURRENT_TIMESTAMP FunctionDBTIMEZONE FunctionEXTRACT FunctionFROM_TZ FunctionLAST_DAY FunctionLOCALTIMESTAMP FunctionMONTHS_BETWEEN FunctionNEW_TIME FunctionROUND FunctionROUND(date) FunctionROUND(number) FunctionSYSDATE FunctionSYSTIMESTAMP FunctionTO_CHAR(date) FunctionTO_DSINTERVAL FunctionTO_TIMESTAMP FunctionTO_TIMESTAMP_TZ FunctionTO_YMINTERVAL FunctionTRUNC(date) FunctionTZ_OFFSET FunctionCollection Management FunctionsCARDINALITY FunctionCOLLECT FunctionCOLLECT Function in an Object TableCOLLECT Function with an Object Type ColumnPOWERMULTISET FunctionPOWERMULTISET_BY_CARDINALITY FunctionSET FunctionCollection Set OperatorsCARDINALITY OperatorEMPTY OperatorMULTISET OperatorMULTISET EXCEPT OperatorMULTISET INTERSECT OperatorMULTISET UNION OperatorSET OperatorSUBMULTISET OF OperatorNumber FunctionsCEIL FunctionFLOOR FunctionMOD FunctionPOWER FunctionREMAINDER FunctionROUND FunctionError Reporting FunctionsSQLCODE FunctionSQLERRM FunctionMiscellaneous FunctionsBFILENAME FunctionCOALESCE FunctionDECODE FunctionDUMP FunctionEMPTY_BLOB FunctionEMPTY_CLOB FunctionGREATEST FunctionLEAST FunctionNANVL FunctionNULLIF FunctionNVL FunctionSYS_CONTEXT FunctionTABLE FunctionTREAT FunctionUSERENV FunctionVSIZE FunctionSummary
Appendix D: PL/SQL Built-in Packages and Types
Oracle Database 11g and 12c New PackagesExamples of Package UseDBMS_APPLICATION_INFO ExampleDBMS_COMPARISONDBMS_CRYPTODBMS_FGACase Study: Query ToolSupporting ScriptsSummary
Appendix E: Regular Expression Primer
Regular Expression IntroductionCharacter ClassesCollation ClassesMetacharactersMetasequencesLiteralsRegular Expression ImplementationREGEXP_COUNT FunctionTitle Case CountLowercase CountCase-Insensitive CountREGEXP_INSTR FunctionREGEXP_LIKE FunctionREGEXP_REPLACE FunctionREGEXP_SUBSTR FunctionSupporting ScriptsSummary
Appendix F: Wrapping PL/SQL Code Primer
Limitations of Wrapping PL/SQLLimitations of the PL/SQL wrap UtilityLimitations of the DBMS_DDL.WRAP FunctionUsing the wrap Command-Line UtilityUsing the DBMS_DDL Command-Line UtilityWRAP FunctionCREATE_WRAPPED ProcedureSummary
Appendix G: PL/SQL Hierarchical Profiler Primer
Configuring the SchemaCollecting Profiler DataUnderstanding Profiler DataReading the Raw OutputDefining the PL/SQL Profiler TablesQuerying the Analyzed DataUsing the plshprof Command-Line UtilitySupporting ScriptsSummary
Appendix H: PL/SQL Reserved Words and Keywords
Summary
Appendix I: Mastery Check Answers
Chapter 1Chapter 2Chapter 3Chapter 4Chapter 5Chapter 6Chapter 7Chapter 8Chapter 9Chapter 10Chapter 11Chapter 12Chapter 13
Glossary
Index

Overview

Master Oracle Database 12c PL/SQL Application Development

Develop, debug, and administer robust database programs. Filled with detailed examples and expert strategies from an Oracle ACE, Oracle Database 12c PL/SQL Programming explains how to retrieve and process data, write PL/SQL statements, execute effective queries, incorporate PHP and Java, and work with dynamic SQL. Code testing, security, and object-oriented programming techniques are fully covered in this comprehensive Oracle Press guide.

Explore new SQL and PL/SQL features in Oracle Database 12c
Build control structures, cursors, and loop statements
Work with collections, varrays, tables, and associative array collections
Locate and repair errors and employ exception handlers
Execute black box, white box, and integration tests
Configure and manage stored packages and libraries
Handle security with authentication and encryption
Use LOBs to store text and multimedia content
Write and implement PL/SQL and Java triggers
Extend functionality using dynamic SQL statements
Understand object types, nested tables, and unnesting queries

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

Oracle Database 12c PL/SQL Advanced Programming Techniques

Publisher Resources

ISBN: 9780071812436

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills