book

Advanced Oracle PL/SQL Developer's Guide (Second Edition) - Second Edition

by Saurabh K. Gupta

February 2016

Intermediate to advanced

428 pages

8h 38m

English

Packt Publishing

Read now

Unlock full access

Support files, eBooks, discount offers, and moreWhy subscribe?Free access for Packt account holdersInstant updates on new Packt books
What this book covers

Downloading the example codeErrataPiracyQuestions
Introduction to PL/SQLPL/SQL program fundamentals
The cursor execution cycleCursor attributesCursor FOR loop
System-defined exceptionsUser-defined exceptionsThe RAISE_APPLICATION_ERROR procedureException propagation
Executing a procedure
Functions – execution methodsRestrictions on calling functions from SQL expressions
Displaying the direct and indirect dependenciesDependency metadataDependency issues and enhancements
Oracle SQL Developer for DBA, Developers, and Application ArchitectsSQL Developer 4.0
Database consolidation and the new Multitenant architectureThe Oracle Database 12c Multitenant architecture – featuresMultitenant for ConsolidationPlug/unplugManage Many as OneRapid provisioningCDB Resource ManagementCommon users and local users
IDENTITY columnsDefault column value to a sequence in Oracle 12cThe DEFAULT ON NULL clauseSupport for 32K VARCHAR2Row limiting using FETCH FIRSTInvisible columnsTemporal databasesIn-Database ArchivingDefining a PL/SQL subprogram in the SELECT query and PRAGMA UDFTest setupComparative analysisThe PL/SQL program unit white listingGranting roles to PL/SQL program unitsTest setupMiscellaneous PL/SQL enhancements
The challengeThe problem statement and Oracle Database 12c In-MemoryOracle Database 12c In-Memory option featuresThe Oracle Database 12c In-Memory ArchitectureControlling the In-Memory column storeThe INMEMORY clausePerformance optimizationsIn-Memory AdvisorOracle Database In-Memory benefits
Cursor structuresCursor execution cycleCursor attributesImplicit cursorsExplicit cursors
Strong and weak ref cursor typesWorking with cursor variablesSYS_REFCURSORCursor variables as argumentsCursor variables – restrictionsCursor design considerationsCursor design–guidelines
Subtype classificationType compatibility with subtypes
Introduction to collections
Modify and drop a nested table object typeDesign considerations of a nested tableNested table storageNested table in an index - organized tableNested table locatorsNested table as the schema objectOperations on a nested table type columnCreate a nested table instanceQuerying a nested table columnNested table collection type in PL/SQLCollection initializationQuerying the nested table metadataNested table comparison functionsMultiset operations on nested tables
Varray as a schema objectOperations on varray type columnsInserting varray collection type instanceQuerying varray columnUpdating the varray instanceVarray in PL/SQL
Selecting the appropriate collection type
EXISTSCOUNTLIMITFIRST and LASTPRIOR and NEXTEXTENDTRIMDELETE
Overview of External ProceduresExternal Procedures
The extproc agentThe Library objectCallout and CallbackCall SpecificationHow an External Procedure executesEnvironment setupTNSNAMES.oraEXTPROC.ora
Loading a Java class into a databaseSteps to execute a Java class from an Oracle PL/SQL unit
Oracle Database Security overview
How FGAC works
How does Virtual Private Database work?Column-level Virtual Private DatabaseVirtual Private Database with Oracle Database 12c MultitenantVirtual Private Database componentsApplication ContextVirtual Private Database policy functionPolicy typesThe DBMS_RLS packageDemonstrationVirtual Private Database features and best practices
Policy utilities—refresh and drop
Oracle Database 12c Data RedactionData Redaction exemptions and miscellaneous featuresData Redaction function typesDemonstrationThe Data Redaction metadata
Introduction to Large ObjectsClassification of Large Object datatypesInternal LOBPersistent and Temporary LOBExternal LOBLOB restrictions
BLOB and CLOBBFILESome more related stuffThe LOB locatorLOB instance initializationThe DBMS_LOB packageThe DBMS_LOB constantsThe DBMS_LOB data typesThe DBMS_LOB subprogramsLOB usage notes
Deduplication and compressionEncryptionFile System LoggingWrite Gather CacheFree space managementBasicFiles and SecureFilesThe db_securefile parameter
LOB metadataEnabling the advanced features of a SecureFilePopulating the LOB dataTemporary LOB operationsManaging temporary LOBsWorking with a temporary LOB
Use the ALTER TABLE commandUsing the TO_LOB functionOnline Table Redefinition
The PL/SQL CompilerSubprogram inlining in PL/SQLPRAGMA INLINEPLSQL_OPTIMIZE_LEVELCase 1: When PLSQL_OPTIMIZE_LEVEL = 0Case 2: When PLSQL_OPTIMIZE_LEVEL = 1Case 3: When PLSQL_OPTIMIZE_LEVEL = 2Case 4: When PLSQL_OPTIMIZE_LEVEL = 3
Oracle Database 11g Real Native CompilationSelecting the appropriate compilation modeSetting the compilation modeQuerying the compilation settingsCompiling a program unit for native or interpreted compilationRecompiling a database for a PL/SQL native or interpreted compilation
Build secure applications using bind variablesCall parameters by referenceAvoiding an implicit data type conversionUnderstanding the NOT NULL constraintSelection of an appropriate numeric data typeBulk processing in PL/SQLBULK COLLECTFORALLFORALL and exception handling
Oracle Database 11g Result CacheWhat is the Server Result Cache?Configuring the Server Result CacheResult Cache versus Buffer CacheResult Cache versus Oracle 12c Database In-MemoryResult Cache versus In-Memory Database Cache
Monitoring the SQL Result CacheInvalidation of the SQL Result CacheRead consistency of the SQL Result CacheLimitations
Does it sound similar to deterministic functions?Differences between Result Cache and other caching techniquesIllustrationMonitoring the PL/SQL Result CacheInvalidation of the PL/SQL Result CacheLimitation
Displaying the result cache memory reportOracle Database 12c enhancements to the PL/SQL function Result Cache
A sample PL/SQL program
USER_ARGUMENTSUSER_OBJECTSUSER_OBJECT_SIZEUSER_SOURCEUSER_PROCEDURESUSER_PLSQL_OBJECT_SETTINGS and USER_STORED_SETTINGSUSER_DEPENDENCIESThe DBMS_DESCRIBE packageTracking the program execution subprogram call stackTracking propagating exceptions in PL/SQL code
USER_IDENTIFIERSThe PL/Scope toolThe PLSCOPE_SETTINGS parameter
DBMS_METADATA data types and subprogramsParameter requirementsThe DBMS_METADATA transformation parameters and filtersDemonstration
Installing the DBMS_TRACE packageDBMS_TRACE subprogramsCompiling a PL/SQL program for debuggingViewing the PL/SQL trace informationSteps to trace PL/SQL program execution
The DBMS_HPROF packageDifferences between DBMS_PROFILER and DBMS_HPROFDBMS_HPROF subprogramsCollecting raw profile dataInterpreting the raw profiler dataAnalyzing profiler dataCreating the profiler tablesAnalyzing the profiler outputQuerying the profiler tablesThe plshprof utilityWhat do these reports reveal?
What is SQL injection?SQL injection targetsHow to exploit the PL/SQL code?
Sanitizing inputs using DBMS_ASSERTChoose the right subprogram for the right identifierUnquoted identifiersQuoted identifiersLiteralsDBMS_ASSERT – limitationsUse of bind variables to prevent injection attacksBest practices to avoid SQL injection
Test strategyAn effective code reviewStatic code analysisFuzz toolsGenerating test cases
An overview of SQL DeveloperKey differentiatorsHistory and backgroundSQL Developer for DevelopersSQL Developer for Database AdministratorsSQLcl – The new SQL command line
Creating a database connectionUsing the SQL worksheetCore features of SQL DeveloperObject BrowserPL/SQL Editor and DebuggerDBA PanelDatabase UtilitiesThe Data ModelerSQL Developer reportsVersion controlThe SQL Translation Framework

Overview

In "Advanced Oracle PL/SQL Developer's Guide (Second Edition)", you'll delve into the advanced capabilities of Oracle PL/SQL, honing skills needed for professional-level certification while mastering the innovations introduced in Oracle Database 12c. This book serves as a comprehensive resource for enhancing your database development expertise.

What this Book will help me do

Master advanced Oracle PL/SQL development skills aligned with Oracle Database 12c innovations.
Understand and implement Virtual Private Database (VPD) for advanced database security.
Gain expertise in tuning, profiling, and debugging PL/SQL code for robust application performance.
Integrate and utilize Oracle Database 12c features such as Multitenant feature and Database In-Memory.
Prepare for the 1Z0-146 Oracle certification to become recognized as an Advanced PL/SQL Developer.

Author(s)

Saurabh K. Gupta is an experienced Oracle developer and author known for his clarity and depth in explaining advanced technical concepts. With a strong background in Oracle Database and PL/SQL development, he imparts knowledge that bridges the gap between learning and practical application. Gupta's writing emphasizes clarity and hands-on understanding, making complex topics accessible to developers.

Who is it for?

This book is tailored for advanced Oracle developers looking to deepen their understanding of PL/SQL and integrate Oracle Database 12c's features into their workflow. It is particularly beneficial for professionals preparing for the 1Z0-146 Oracle exam. Readers should have foundational knowledge in PL/SQL and a determination to elevate their technical proficiency.