book

Oracle Advanced PL/SQL Developer Professional Guide

by Saurabh K. Gupta

May 2012

Intermediate to advanced

440 pages

9h 1m

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
PL/SQL—the procedural aspectMy first PL/SQL program
SQL DeveloperSQL Developer—the historyCreating a connectionSQL WorksheetExecuting a SQL statementCalling a SQL script from SQL DeveloperCreating and executing an anonymous PL/SQL blockDebugging the PL/SQL codeEditing and saving the scriptsSQL*PlusExecuting a SQL statement in SQL*PlusExecuting an anonymous PL/SQL block
Executing a procedure
Function—execution methodsRestrictions on calling functions from SQL expressions
Cursor execution cycleCursor attributesCursor FOR loop
System-defined exceptionsUser-defined exceptionsThe RAISE_APPLICATION_ERROR procedureException propagation
Displaying the direct and indirect dependenciesDependency metadataDependency issues and enhancements
Understanding cursor structuresCursor execution cycleCursor design considerationsCursor design—guidelinesCursor attributesImplicit cursorsExplicit cursors
Ref cursor types—strong and weakSYS_REFCURSORProcessing a cursor variableCursor variables as argumentsCursor variables—restrictions
Subtype classificationOracle's predefined subtypesUser-defined subtypesType compatibility with subtypes
Collections—an overviewCategorizationSelecting an appropriate collection type
Nested table collection type as the database objectDML operations on nested table columnsInserting a nested table instanceSelecting a nested table columnUpdating the nested table instanceA nested table collection type in PL/SQLAdditional features of a nested table
Varray in PL/SQLVarray as a database collection typeDML operations on varray type columnsInserting a varray collection type instanceSelecting a varray columnUpdating the varray instance
Common characteristics of collection typesNested table versus associative arraysNested table versus varrays
EXISTSCOUNTLIMITFIRST and LASTPRIOR and NEXTEXTENDTRIMDELETE
Understanding external routinesArchitecture of external routinesOracle Net ConfigurationTNSNAMES.oraLISTENER.oraOracle Net Configuration verificationBenefits of external procedures
Executing C program through external procedure—development steps
Calling a Java class method from PL/SQLUploading a Java class into the database—development stepsThe loadjava utility—an illustrationCreating packages for Java class methods
Fine Grained Access ControlOverviewVirtual Private Database—the aliasHow FGAC or VPD works?Salient features of VPD
Application contextPolicy function definition and implementation of row-level securityAssociating a policy using the DBMS_RLS package
Assignment 1—implementing VPD using simple security policyAssignment 2—implementing VPD using an application context
Introduction to the LOB data typesInternal LOBExternal LOB
LOB value and LOB locatorsBLOB or CLOB!BFILETemporary LOBs
DirectoriesCreating LOB data type columns in a table
Managing internal LOBsSecuring and managing BFILEsThe DBMS_LOB package—overviewSecurity modelDBMS_LOB constantsDBMS_LOB data typesDBMS_LOB subprogramsRules and regulationsInternal LOBsBFILEs
Initializing LOB data type columnsInserting data into a LOB columnPopulating a LOB data type using an external fileSelecting LOB dataModifying the LOB dataDelete LOB dataMiscellaneous LOB notesLOB column statesLocking a row containing LOBOpening and closing LOBsAccessing LOBsLOB restrictions
Temporary LOB operationsManaging temporary LOBsValidating, creating, and freeing a temporary LOB
Introduction to SecureFilesSecureFile LOB—an overviewArchitectural enhancements in SecureFilesSecureFile LOB features
SecureFile metadataEnabling advanced features in SecureFilesDeduplicationCompressionEncryption
Online Reefinition method
Native and interpreted compilation techniquesReal native compilationSelecting the appropriate compilation modeWhen to choose interpreted compilation mode?When to choose native compilation mode?Setting the compilation modeQuerying the compilation settingsCompiling a program unit for a native or interpreted compilationCompiling the database for PL/SQL native compilation (NCOMP)
Comparing SQL and PL/SQLAvoiding implicit data type conversionUnderstanding the NOT NULL constraintUsing the PLS_INTEGER data type for arithmetic operationsUsing a SIMPLE_INTEGER data typeModularizing the PL/SQL codeUsing bulk bindingUsing SAVE_EXCEPTIONSRephrasing the conditional control statementsConditions with an OR logical operatorConditions with an AND logical operator
PLSQL_OPTIMIZE_LEVEL—the Oracle initialization parameterCase 1—PLSQL_OPTIMIZE_LEVEL = 0Case 2—PLSQL_OPTIMIZE_LEVEL = 1Case 3—PLSQL_OPTIMIZE_LEVEL = 2Case 4—PLSQL_OPTIMIZE_LEVEL = 3
Introduction to result cacheServer-side result cacheSQL query result cachePL/SQL function result cacheOCI client results cache
The DBMS_RESULT_CACHE package
Manual result cacheAutomatic result cacheResult cache metadataQuery result cache dependenciesCache memory statisticsInvalidation of SQL result cacheDisplaying the result cache memory reportRead consistency of the SQL result cacheLimitation of SQL result cache
The RESULT_CACHE clauseCross-session availability of cached resultsInvalidation of PL/SQL result cacheLimitations of PL/SQL function result cacheArgument and return type restrictionsFunction structural restrictions
Track coding information[DBA | ALL | USER]_ARGUMENTS[DBA | ALL | USER]_OBJECTS[DBA | ALL | USER]_SOURCE[DBA | ALL | USER]_PROCEDURES[DBA | ALL | USER]_DEPENDENCIES
The DBMS_DESCRIBE packageDBMS_UTILITY.FORMAT_CALL_STACKTracking propagating exceptions in PL/SQL code
The PL/Scope toolThe PL/Scope identifier collectionThe PL/Scope reportIllustrationApplications of the PL/Scope report
DBMS_METADATA data types and subprogramsParameter requirementsThe DBMS_METADATA transformation parameters and filtersWorking with DBMS_METADATA—illustrationsCase 1—retrieve the metadata of a single objectCase 2—retrieve the object dependencies on the F_GET_LOC functionCase 3—retrieve system grants on the ORADEV schemaCase 4—retrieve objects of function type in the ORADEV schema
Tracing the PL/SQL programsThe DBMS_TRACE packageInstalling DBMS_TRACEDBMS_TRACE subprogramsThe PLSQL_DEBUG parameter and the DEBUG optionViewing the PL/SQL trace informationDemonstrating the PL/SQL tracing
Oracle hierarchical profiler—the DBMS_HPROF packageView profiler informationDemonstrating the profiling of a PL/SQL programThe plshprof utilitySample reports
SQL injection—an introductionSQL injection—an overviewTypes of SQL injection attacksPreventing SQL injection attacks
Reducing the attack's surfaceControlling user privilegesInvoker's and definer's rightsAvoiding dynamic SQLBind argumentsSanitizing inputs using DBMS_ASSERTThe DBMS_ASSERT packageIdentifier formatting and verification processDBMS_ASSERT—usage guidelinesDBMS_ASSERT—limitations
Test strategyReviewing the codeStatic code analysisFuzz toolsGenerating test cases
Chapter 1, Overview of PL/SQL Programming Concepts

Content preview from Oracle Advanced PL/SQL Developer Professional Guide

Understanding the LOB data types

As we came across the four LOB data types in Oracle, LOB structure can be split into its "What" and "Where" components. While LOB value answers to the "What' query, the LOB locator corresponds to the "Where" part. Together, they both make a complete structure of an LOB. Let us understand the LOB components.

LOB value and LOB locators

The LOB value is the actual large file to be loaded in the database. The LOB locator points to the location of this file on the system. It can be thought of as a pointer which references the system location of a file. A LOB locator is always stored inline with the table row irrespective of where the LOB value is stored. During insertion of the LOB data, the LOB column necessarily has ...