book

Oracle Database 10g PL/SQL Programming

by Scott Urman, Ron Hardman, Michael McLaughlin

October 2004

Intermediate to advanced

896 pages

17h 29m

English

McGraw Hill Computing

Read now

Unlock full access

Cover Page
Oracle Database 10g PL/SQL Programming
Copyright Page
About the Authors
Dedication Page
Contents at a Glance
Contents
Acknowledgments
Introduction
PART I Introduction

1 Introduction to PL/SQL
Introduction to Programming LanguagesNote to Beginning ProgrammersPL/What?Structured Query Language (SQL)Relational Database OverviewPL/SQL vs. SQLPL/SQL vs. JavaPL/SQL History and FeaturesLanguage FundamentalsAnonymous BlocksProceduresFunctionsPackagesObject TypesPL/SQL Statement ProcessingInterpretedNative CompilationGetting the Most from This BookAudienceObjectiveScopeAssumptionsConventionsExamplesSummary
2 Using SQL*Plus and JDeveloper
SQL*PlusConnecting to the InstanceTesting the ConnectionUsing SQL*PlusChanging SQL*Plus Session SettingsRunning a Script from a FileOutput to the Screen Using SQL*Plus and PL/SQLJDeveloperInstalling JDeveloperWorking with PL/SQL in JDeveloperSummary
3 PL/SQL Basics
The PL/SQL BlockThe Basic StructureAnonymous BlocksNamed BlocksNested BlocksTriggersObject TypesLanguage Rules and ConventionsLexical UnitsPL/SQL Data TypesScalarCharacter/StringNUMBER Data TypeBooleanDate/TimeCompositeReferenceLOBUsing Variables%TYPE%ROWTYPEVariable ScopeBind VariablesHiding CodeExpressionsAssignment OperatorConcatenation OperatorControlling Program FlowConditional EvaluationCircular ExecutionSequential Navigation using GOTOSummary
4 Using SQL with PL/SQL
Transaction ProcessingTransactions and LockingAutonomous TransactionsSet TransactionRetrieving DataSQL SELECT StatementPattern MatchingInformation RetrievalCursorsHow Cursors WorkExplicit CursorsImplicit CursorsCursor VariablesCursor SubqueriesOpen CursorsDML and DDLPre-CompilationManipulating Data with DMLDELETEIntroduction to Dynamic SQLUsing ROWID and ROWNUMROWIDROWNUMBuilt-in SQL FunctionsCharacter FunctionsNumeric FunctionsDate FunctionsConversion FunctionsError FunctionsOther FunctionsSummary
5 Records
Introducing RecordsWhat Is a Record?Working with RecordsDefining Record TypesDefining and Using Record Types as Formal ParametersDefining and Using Object Types as ParametersReturning Record Types from FunctionsDefining and Using Record Types as Return ValuesDefining and Using Object Types as Return ValuesVerifying Work with Record TypesSummary
6 Collections
Introducing CollectionsWhat Is a Collection?Working with CollectionsWorking with VarraysWorking with Nested TablesWorking with Associative ArraysOracle 10g Collection APICOUNT MethodDELETE MethodEXISTS MethodEXTEND MethodFIRST MethodLAST MethodLIMIT MethodNEXT MethodPRIOR MethodTRIM MethodSummary
7 Error Handling
What Is an Exception?Declaring ExceptionsRaising ExceptionsHandling ExceptionsThe EXCEPTION_INIT PragmaUsing RAISE_APPLICATION_ERRORException PropagationExceptions Raised in the Executable SectionExceptions Raised in the Declarative SectionExceptions Raised in the Exception SectionException GuidelinesScope of ExceptionsAvoiding Unhandled ExceptionsMasking Location of the ErrorExceptions and TransactionsSummary
8 Creating Procedures, Functions, and Packages
Procedures and FunctionsSubprogram CreationSubprogram ParametersThe CALL StatementProcedures vs. FunctionsPackagesPackage SpecificationPackage BodyPackages and ScopeOverloading Packaged SubprogramsPackage InitializationSummary
9 Using Procedures, Functions, and Packages
Subprogram LocationsStored Subprograms and the Data DictionaryLocal SubprogramsStored vs. Local SubprogramsConsiderations of Stored Subprograms and PackagesSubprogram DependenciesPackage Run-Time StatePrivileges and Stored SubprogramsStored Functions and SQL StatementsSingle-Valued FunctionsMultiple-Valued FunctionsNative CompilationPinning in the Shared PoolKEEPUNKEEPSIZESABORTED_REQUEST_THRESHOLDThe PL/SQL WrapperSummary
10 Database Triggers
Types of TriggersDML TriggersInstead-of TriggersSystem TriggersCreating TriggersCreating DML TriggersCreating Instead-of TriggersCreating System TriggersOther Trigger IssuesTriggers and the Data DictionaryMutating TablesMutating Table ExampleWorkaround for the Mutating Table ErrorSummary
PART II Advanced PL/SQL Features
11 Intersession Communication
Introducing Intersession CommunicationRequiring Permanent or Semipermanent StructuresNot Requiring Permanent or Semipermanent StructuresThe DBMS_PIPE Built-in PackageIntroducing the DBMS_PIPE PackageDefining the DBMS_PIPE PackageWorking with the DBMS_PIPE PackageDBMS_ALERT Built-in PackageIntroducing the DBMS_ALERT PackageDefining the DBMS_ALERT PackageWorking with the DBMS_ALERT PackageSummary
12 External Routines
Introducing External ProceduresWorking with External ProceduresDefining the extproc ArchitectureDefining extproc Oracle Net Services ConfigurationDefining the Multithreaded External Procedure AgentWorking with a C Shared LibraryWorking with a Java Shared LibraryTroubleshooting the Shared LibraryConfiguration of the Listener or EnvironmentConfiguration of the Shared Library or PL/SQL Library WrapperSummary
13 Dynamic SQL
Introducing Dynamic SQLWorking with Native Dynamic SQLWorking with DDL and DML Without Bind VariablesWorking with DML and a Known List of Bind VariablesWorking with DQLWorking with the Oracle DBMS_SQL Built-in PackageWorking with DDL and DML Without Bind VariablesWorking with DML and a Known List of Bind VariablesWorking with DQLSummary
14 Introduction to Objects
Introduction to Object-Oriented ProgrammingData and Procedural AbstractionObject Type OverviewCreating Object TypesObject Type SpecificationObject Type BodyObject Type InheritanceDynamic Method DispatchAttribute ChainingMaking ChangesType EvolutionSummary
15 Objects in the Database
Introduction to Objects in the DatabaseObject TablesColumn ObjectsObject ViewsAccessing Persistent Objects Using SQL and PL/SQLObject TablesAccessing Column ObjectsAccessing Object ViewsObject Related Functions and OperatorsMaintaining Persistent ObjectsType EvolutionSummary
16 Large Objects
Introduction to Large ObjectsFeatures ComparisonTypes of LOBsLOB StructureInternal LOB StorageExternal LOB StorageTemporary LOB StorageMigrating from LONGs to LOBsLOBs and SQLSQL for Internal Persistent LOBsExternal LOB – BFILELOBs and PL/SQLDBMS_LOBPerformance ConsiderationsReturning ClauseSummary
17 Scheduling Tasks
Introducing DBMS_JOBSUBMITBROKENRUNCHANGEREMOVEOracle SchedulerTerminologyUsing DBMS_SCHEDULERMigrating from DBMS_JOBRemoving a JobSummary
PART III Appendixes
A PL/SQL Reserved Words
Table of Reserved Words
B Guide to Supplied Packages
SYS-Owned Built-in PackagesDBMS_ADVANCED_REWRITEDBMS_ADVISORDBMS_ALERTDBMS_APPLICATION_INFODBMS_APPLY_ADMDBMS_AQDBMS_BACKUP_RESTOREDBMS_CAPTURE_ADMDBMS_CRYPTODBMS_DATA_MINING, DBMS_DATA_MINING_TRANSFORMDBMS_DATAPUMPDBMS_DDLDBMS_DEBUGDBMS_DEFERDBMS_DESCRIBEDBMS_DIMENSIONDBMS_DISTRIBUTED_TRUST_ADMINDBMS_FGADBMS_FILE_TRANSFERDBMS_FLASHBACKDBMS_HSDBMS_HS_PASSTHROUGHDBMS_JAVADBMS_JOBDBMS_LDAPDBMS_LOBDBMS_LOCKDBMS_LOGMNRDBMS_METADATADBMS_MONITORDBMS_OBFUSCATION_TOOLKITDBMS_ODCIDBMS_OFFLINE_OGDBMS_OLAPDBMS_OUTLNDBMS_OUTLN_EDITDBMS_OUTPUTDBMS_PIPEDBMS_PROFILERDBMS_PROPAGATION_ADMDBMS_RANDOMDBMS_REDEFINITIONDBMS_REFRESHDBMS_REPAIRDBMS_REPCATDBMS_RESOURCE_MANAGER/DBMS_RESOURCE_MANAGER_PRIVSDBMS_RESUMABLEDBMS_ROWIDDBMS_RULE, DBMS_RULE_ADMDBMS_SCHEDULERDBMS_SCHEMA_COPYDBMS_SERVER_ALERTDBMS_SERVICEDBMS_SESSIONDBMS_SHARED_POOLDBMS_SPACEDBMS_SQLDBMS_SQLTUNEDBMS_STANDARD, STANDARDDBMS_STAT_FUNCSDBMS_STATSDBMS_SUMMARYDBMS_TRACEDBMS_TRANSACTIONDBMS_TRANSFORMDBMS_TYPESDBMS_UTILITYDBMS_WARNINGDBMS_WORKLOAD_REPOSITORYDBMS_XMLGEN, DBMS_XMLQUERYDBMS_XPLANUTL_COLLUTL_COMPRESSUTL_DBWSUTL_ENCODEUTL_FILEUTL_HTTPUTL_I18NUTL_INADDRUTL_LMSUTL_MAILUTL_RAWUTL_RECOMPUTL_REFUTL_SMTPUTL_TCPUTL_URLCTXSYS-Owned Built-in PackagesCTX_ADMCTX_CLSCTX_DDLCTX_DOCCTX_OUTPUTCTX_QUERYCTX_REPORTCTX_THES
Index

Content preview from Oracle Database 10g PL/SQL Programming

Chapter 14Introduction to Objects

Support for object-oriented programming (OOP) in PL/SQL began in version 8. Since then, enhancements including inheritance, attribute chaining, type evolution, and user-defined constructor methods have caught the eye of OOP enthusiasts and provided programmers with an alternative to traditional PL/SQL development. In this chapter, we introduce you to Oracle's OOP implementation in PL/SQL, describe enhancements in 9i and 10g, and discuss benefits and drawbacks of moving your application development to objects.

Introduction to Object-Oriented Programming

Mention object-oriented programming languages and Java and C++ generally come to mind. They have the greatest following in the OOP world and are known for their ...

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: 9780072230666

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills