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

Migrating from LONG to LOB

Starting from Oracle 10g, a LONG data is convertible to a LOB data in a table. This enhancement has enabled the migration of older data in LONG and LONG RAW columns to equivalent LOB data types in Oracle. The data in the LONG type column is mapped to CLOB or NCLOB data types and the data in the LONG RAW type columns is mapped to the BLOB data type. It can be achieved through the ALTER TABLE statement, where a LONG type column can be modified to the LOB type column:

ALTER TABLE [table name]
MODIFY [LONG type column] [LOB type (CLOB | BLOB)];

During migration, the Oracle server implicitly takes care of the data conversion and movement from the LONG to LOB data type. A LONG, LONG RAW, or VARCHAR2 type of data can be implicitly ...