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

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
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
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
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
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
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
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
Procedures and FunctionsSubprogram CreationSubprogram ParametersThe CALL StatementProcedures vs. FunctionsPackagesPackage SpecificationPackage BodyPackages and ScopeOverloading Packaged SubprogramsPackage InitializationSummary
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
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
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
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
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
Introduction to Object-Oriented ProgrammingData and Procedural AbstractionObject Type OverviewCreating Object TypesObject Type SpecificationObject Type BodyObject Type InheritanceDynamic Method DispatchAttribute ChainingMaking ChangesType EvolutionSummary
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
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
Introducing DBMS_JOBSUBMITBROKENRUNCHANGEREMOVEOracle SchedulerTerminologyUsing DBMS_SCHEDULERMigrating from DBMS_JOBRemoving a JobSummary
Table of Reserved Words
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

Overview

Create dynamic client/server applications using PL/SQL and the comprehensive information contained in this authoritative volume. Expert Oracle insiders cover the features of PL/SQL, explain scripting semantics and syntax, and fully detail the PL/SQL functionality of Oracle Database 10g. You’ll learn how to write powerful PL/SQL programs, interact with Oracle databases, perform complex calculations, and handle error conditions.