book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein

October 2005

Intermediate to advanced

454 pages

14h 44m

English

O'Reilly Media, Inc.

Read now

Unlock full access

1.2.1.1. Sections of the block1.2.1.2. Anonymous blocks
1.2.3.1. NULLs
1.2.4.1. String literals1.2.4.2. Numeric literals1.2.4.3. Boolean literals
1.2.6.1. Single-line comment syntax1.2.6.2. Multi-line comment syntax
1.3.1.1. Character data1.3.1.2. Numbers1.3.1.3. Dates, timestamps, and intervals1.3.1.4. Booleans1.3.1.5. Binary data1.3.1.6. ROWIDs1.3.1.7. REF CURSOR datatype1.3.1.8. Internet datatypes1.3.1.9. “Any” datatypes
1.3.2.1. Declaring a variable1.3.2.2. Declaring constants1.3.2.3. Anchored declarations
1.4.2.1. Simple CASE statement1.4.2.2. Searched CASE statement
1.6.2.1. RAISE statement1.6.2.2. Using RAISE_APPLICATION_ERROR
1.6.3.1. Built-in error functions1.6.3.2. Unhandled exceptions1.6.3.3. Propagation of an unhandled exception
1.7.2.1. Record-level operations1.7.2.2. Field-level operations
1.8.2.1. Using an associative array1.8.2.2. Using a nested table1.8.2.3. Using a VARRAY
1.9.1.1. Structure of a procedure1.9.1.2. Calling a procedure
1.9.2.1. Structure of a function1.9.2.2. Calling a function
1.9.3.1. Defining parameters1.9.3.2. Actual and formal parameters1.9.3.3. Parameter modes
1.9.4.1. Rules for building packages1.9.4.2. Rules for calling packaged elements1.9.4.3. Package data
1.10.3.1. Error handling with implicit cursors1.10.3.2. Implicit SQL cursor attributes
1.10.5.1. Limiting rows retrieved with BULK COLLECT
1.10.6.1. Declaring REF CURSOR types1.10.6.2. Declaring cursor variables1.10.6.3. Opening cursor variables1.10.6.4. Fetching from cursor variables
1.11.6.1. Syntax of the FORALL Statement1.11.6.2. FORALL Examples
1.13.1.1. Transaction participation1.13.1.2. Creating a DML trigger1.13.1.3. The WHEN clause1.13.1.4. Working with NEW and OLD pseudo -records1.13.1.5. Determining the DML action within a trigger
1.13.2.1. Creating a DDL Trigger
1.13.3.1. Creating a database event trigger
2.4.2.1. Strong vs. weak REF cursors2.4.2.2. REF cursor attributes2.4.2.3. Dynamic data access
3.3.2.1. Random partitioning (PARTITION BY ANY)3.3.2.2. Range partitioning (PARTITION BY RANGE)3.3.2.3. Order streaming (ORDER)3.3.2.4. Hash partitioning (PARTITION BY HASH)3.3.2.5. Cluster streaming (CLUSTER)
4.2.7.1. Using DES3GETKEY4.2.7.2. Using the key in encryption
4.3.3.1. Specifying the encryption type4.3.3.2. Specifying chaining4.3.3.3. Specifying padding4.3.3.4. Combining options in the typ parameter4.3.3.5. Handling and converting RAW data4.3.3.6. Specifying the encryption algorithm4.3.3.7. Putting it together
5.2.2.1. Problems with static policies5.2.2.2. Using a pragma
5.3.2.1. Shared static policy5.3.2.2. Context-sensitive policy5.3.2.3. Shared context-sensitive policy
5.4.3.1. Data dictionary view5.4.3.2. Event-based tracing
6.2.3.1. Turning off bind variable capture
6.2.4.1. Drawbacks with the default FGA approach6.2.4.2. Creating a user-defined audit facility
6.3.2.1. The ADD_POLICY procedure6.3.2.2. The DROP_POLICY procedure6.3.2.3. The DISABLE_POLICY procedure6.3.2.4. The ENABLE_POLICY procedure
6.4.2.1. The DBA_FGA_AUDIT_TRAIL view6.4.2.2. The FLASHBACK_TRANSACTION_QUERY view
6.5.1.1. The case for FGA6.5.1.2. The case for triggers
7.1.1.1. Controlling the precision7.1.1.2. Controlling the range
7.1.2.1. The RANDOM function
8.2.1.1. Running OS executables and anonymous blocks8.2.1.2. DBA_SCHEDULER_JOBS view
8.2.2.1. Enabling and disabling jobs8.2.2.2. Stopping running jobs8.2.2.3. Running a job8.2.2.4. Dropping a job
8.3.1.1. Examples of calendar strings8.3.1.2. Determining future calendar strings
8.7.1.1. DBA_SCHEDULER_JOB_LOG8.7.1.2. DBA_SCHEDULER_JOB_RUN_DETAILS8.7.1.3. Pruning the job log8.7.1.4. Log levels8.7.1.5. Setting the retention period
8.7.2.1. DBA_SCHEDULER_WINDOW_LOG8.7.2.2. DBA_SCHEDULER_WINDOW_DETAILS

Content preview from Oracle PL/SQL for DBAs

DML and Exception Handling

When an exception occurs in a PL/SQL block, Oracle does not roll back any of the changes made by DML statements in that block. It is up to you, the manager of the application’s logical transaction, to decide what sort of behavior should occur. Here are some things to keep in mind in this regard:

If your block is an autonomous transaction (described later in this chapter), then you must perform a rollback or commit (usually a rollback) when an exception is raised.
You can use savepoints to control the scope of a rollback. In other words, you can roll back to a particular savepoint and thereby preserve a portion of the changes made in your session. Savepoints are also explored later in this chapter.
If an exception propagates past the outermost block (i.e., it goes unhandled), then, in most host execution environments for PL/SQL, like SQL*Plus, an unqualified rollback is automatically executed, reversing any outstanding changes.

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.

Start your free trial

Oracle Database 12c PL/SQL Programming

Michael McLaughlin

Oracle PL/SQL Best Practices

Steven Feuerstein

Expert PL/SQL Practices for Oracle Developers and DBAs

John Beresniewicz, Adrian Billington, Martin Büchi, Melanie Caffrey, Ron Crisco, Lewis Cunningham, Dominic Delmolino, Sue Harper, Torben Holm, Connor McDonald, Arup Nanda, Stephan Petit, Michael Rosenblum, Robyn Sands, Riyaj Shamsudeen

Oracle Advanced PL/SQL Developer Professional Guide

Saurabh K. Gupta

Publisher Resources

ISBN: 0596005873Supplemental Content Errata Page