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

Comments

Inline documentation, otherwise known as comments , is an important element of a good program. While there are many ways to make your PL/SQL program self-documenting through good naming practices and modularization, such techniques are seldom enough by themselves to communicate a thorough understanding of a complex program.

PL/SQL offers two different styles for comments : single and multi-line block comments.

Single-line comment syntax

The single-line comment is initiated with two hyphens ( -- ), which cannot be separated by a space or any other characters. All text after the double hyphen to the end of the physical line is considered commentary and is ignored by the compiler. If the double hyphen appears at the beginning of the line, the whole line is a comment.

In the following IF statement, I use a single-line comment to clarify the logic of the Boolean expression:

    IF salary < min_salary (2003) -- Function returns min salary for year.
    THEN
       salary := salary + salary*.25;
    END IF;

Multi-line comment syntax

While single-line comments are useful for documenting brief bits of code or ignoring a line that you do not want executed at the moment, the multi-line comment is superior for including longer blocks of commentary.

Multiline comments start with a slash-asterisk (/*) and end with an asterisk-slash (*/). PL/SQL considers all characters found between these two sequences of symbols to be part of the comment, and they are ignored by the compiler.

The following example of multi-line ...