Exception handling: subprograms and error propagation

[ Oracle PL/SQL ]

Compilation Warnings, Compilation errors, Run-time errors

[ Oracle PL/SQL ]

Compilation Warnings

• PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation
• The message code of a PL/SQL warning has the form PLW-nnnnn
• Warnings can be: SEVERE, PERFORMANCE, or INFORMATIONAL.
• Compilation parameter: PLSQL_WARNINGS. You may:
• Enable or disable the reporting of warning messages by the PL/SQL compiler, and specify which warning messages to show as errors.
• Treat specific warnings as errors (so that those conditions must be corrected before you can compile the PL/SQL unit)
• Use with ALTER SESSION or ALTER SYSTEM or on a stored PL/SQL unit.

Syntax:

PLSQL_WARNINGS = { ENABLE | DISABLE | ERROR }:
   { ALL | SEVERE | INFORMATIONAL | PERFORMANCE | { int | (int [, int ] ...)}}

---- i.e. ---- 
SQL> ALTER SESSION SET PLSQL_WARINGS = 'ENABLE:ALL';
SQL> ALTER PROCEDURE p1 COMPILE 
                               PLSQL_WARNINGS = 'ENABLE:PERFORMANCE';
SQL> ALTER SYSTEM SET PLSQL_WARNINGS = 'ENABLE:(5000,5001,5002)', 'DISABLE:(6000,6001)';

• With Oracle 11g, a new PL/SQL Compiler Warning has been added to identify WHEN OTHERS exception handlers that do no re-raise errors using RAISE or RAISE_APPLICATION_ERROR.
• Such exception handlers can often hide code failures that result in hard to identify bugs.
• The example below shows the expected compiler warning when the PLSQL_WARNINGS flag is set.

-- to check the current warnings status, execute the query below as SYSTEM:

SQL> select name, value from gv$parameter 
     where name like 'plsql%warn%';  
NAME                    VALUE
----------------------  -------------
plsql_warnings          DISABLE:ALL

Alternativelly, you can also use DBMS_WARNING pagckage:

SQL> select dbms_warning.get_warning_setting_string() from dual;

DBMS_WARNING.GET_WARNING_SETTING_STRING()
----------------------------------------- 
DISABLE:ALL    

SQL> alter session set plsql_warnings = 'ENABLE:ALL'; 

SQL> create or replace procedure others_test as
begin 
 raise_application_error(-20000, 'Force and exception'); 
exception
 when others then 
   null;
end;
/

Warning(5,1): PLW-06009: procedure "OTHERS_TEST" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

Using the DBMS_WARNING package.

SQL> desc dbms_warning

PROCEDURE                             Argument Name    Type           IN/OUT Default 
------------------------------------- ---------------- -------------- ------ ------- 
ADD_WARNING_SETTING_CAT               WARNING_CATEGORY VARCHAR2       IN     unknown 
ADD_WARNING_SETTING_CAT               WARNING_VALUE    VARCHAR2       IN     unknown 
ADD_WARNING_SETTING_CAT               SCOPE            VARCHAR2       IN     unknown 
ADD_WARNING_SETTING_NUM               WARNING_NUMBER   BINARY_INTEGER IN     unknown 
ADD_WARNING_SETTING_NUM               WARNING_VALUE    VARCHAR2       IN     unknown 
ADD_WARNING_SETTING_NUM               SCOPE            VARCHAR2       IN     unknown 
GET_CATEGORY (FUNCTION)                  VARCHAR2       OUT    unknown 
GET_CATEGORY                          WARNING_NUMBER   BINARY_INTEGER IN     unknown 
GET_WARNING_SETTING_CAT (FUNCTION)       VARCHAR2       OUT    unknown 
GET_WARNING_SETTING_CAT               WARNING_CATEGORY VARCHAR2       IN     unknown 
GET_WARNING_SETTING_NUM (FUNCTION)       VARCHAR2       OUT    unknown 
GET_WARNING_SETTING_NUM               WARNING_NUMBER   BINARY_INTEGER IN     unknown 
GET_WARNING_SETTING_STRING (FUNCTION)    VARCHAR2       OUT    unknown 
SET_WARNING_SETTING_STRING            VALUE            VARCHAR2       IN     unknown 
SET_WARNING_SETTING_STRING            SCOPE            VARCHAR2       IN     unknown 

• with all warnings disabled, a procedure with unreachable code compiles silently.
• In the example below, since x is constant, the IF always evaluates TRUE: ELSE code never runs (unreachable).

SQL> alter session set plsql_warnings='DISABLE:ALL';
Session altered.

SQL> create or replace procedure unreach 
        authid definer as
  x constant boolean := true;
begin
  if x then
    dbms_output.put_line('true');
  else
    dbms_output.put_line('false');
  end if;
end unreach;
/
Procedure created.

(2) Now enable plsql warnings using the DBMS_WARNING package.
When the procedure is recompiled, a warning is issued.

SQL> call dbms_warning.set_warning_setting_string('ENABLE:ALL', 'SESSION');

Call completed

SQL> select dbms_warning.get_warning_setting_string() from dual;

DBMS_WARNING.GET_WARNING_SETTING_STRING()
----------------------------------------------
ENABLE:ALL

-- Recompile the  procedure.
SQL> alter procedure unreach compile;

SP2-0805: Procedure altered with compilation warnings

-- Check the errors in SQL*PLUS:
SQL> show errors procedure unreach;
Errors for PROCEDURE BROKEN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5  PLW-06002: Unreachable code

OR Query the USER_ERRORS view.
SQL> select name, type, line, position, text, attribute, message_number err 
     from user_errors;

(formatted output):
NAME      TYPE        LINE  POSITION   TEXT                          ATTRIBUTE ERR
--------- ----------- ----- ---------- ----------------------------- --------- -----
BROKEN    PROCEDURE   4      6          PLW-06002: Unreachable code  WARNING   6002
BROKEN    PROCEDURE   7      5          PLW-06002: Unreachable code  WARNING   6002

Compilation Errors

• Generally these are typing errors
• the parser transforms the PL/SQL text file into a set of interpreted instructions (p-code): it may find lexical errors.
• Lexical errors caught when the plain text file is parsed during compilation.
• Deferred compilation errors: when actual data values don’t fit during assignment because they are too large or the wrong datatype.
• Compilation errors cannot be handled by your local exception handler, but you can catch them in a wrapper (containing outer) block.
• Compilation errors can be: prior line, current line or declaration errors.

Viewing errors
To see warnings (and errors) generated during compilation, use either: Static data dictionary view *_ERRORS In SQL*Plus: command SHOW ERRORS.

Run-time errors

• Occur when actual data fails to meet the rules defined by your program unit
• run-time manageable errors (execution or exception blocks)
• run-time unmanageable errors (declaration section)

(14) Oracle-Supplied Packages

[ Oracle PL/SQL ]

DBMS_PIPE Provides API for intersession communication

Methods for intersession communication:
(a) Requiring permanent or semipermanent structures

1. Advanced Queuing (introduced Oracle 9):
   Use DBMS_AQADM and DBMS_AQ packages.
   AQ need to be set up for each participant. Use messages to exchange information between sessions.
2. Use tables, grants, and synonyms to exchange data between sessions.
   Subject to transaction control limitations: commits required.

(b) NOT requiring permanent or semipermanent structures

1. Use DBMS_PIPE.
• Uses dynamic memory structures in the SGA (pipes).
• Similar to Unix pipes. pipes may be local, private, or publicly accessible.
• Act as FIFO queues.
• Not binded by transaction control.
• Data transmitted asynchronously.
2. DBMS_ALERT.
• Uses memory structure in SGA, that works as a public pipe.
• Populated on event triggers and subject to transaction control limits.
• Alert pipes communicate between sessions asynchronously at the conclusion of an event.
• Events are anything that you can build a trigger against, like DML or system action.
• Unlike DBMS_PIPE, DBMS_ALERT works on a publich-and-subscribe paradigm.
• It publishes notifications, and then enables subscribers to register their interest in the alert and receive the notifications.
• Alerts also support two or more sessions of a single user.

Using public pipes with DBMS_PIPE

Using DBMS_PIPE

• Support communication between two or more sessions of a single user.
• Can also support communication between two or more users.
• DBMS_PIPE can help you mimic Unix pipes or POSIX-compliant threads.
• Unix pipes allow you to move data between two active processes.
• Unix pipes control communication at the process level.
• C/C++ also lets you control threading activities with mutex variables, which work at the process and thread levels.
• Both provide higher programming language equivalents to operating system semaphores.

• DBMS_PIPE provides a non-secure mechanism for inter-session messaging.
• Non-secure because messages can be lost from the pipe if the instance crashes or is shutdown before they are processed.
• Advanced Queues are arguably a better mechanism when secure messaging and greater flexibility are required.

• DBMS_PIPE: can also be used for passing information to external processes that may monitor or control system resources. With DBMS_PIPE you can:
• Use local pipes to control a single program’s execution.
• Use private pipes to control concurrent programs run by a single user.
• Use public pipes to control concurrent programs run by multiple users.

Pipes can be:

• Implicit Pipes: created automatically when a message is sent with an unkown pipename using the SEND_MESSAGE function.
• Implicit pipes disappear when they are empty.
• Explicit pipes: created using CREATE_PIPE function. Must be removed with REMOVE_PIPE function.

Two levels of security:

• Public Pipes: Accessible by any user with EXECUTE permission on DBMS_PIPE package.
• Implicit pipes are always public.
• Implicit pipes can be created explicitly by calling the CREATE_PIPE function with the private parameter set to FALSE.
• The domain of a public pipe is the schema in which it was created, either explicitly or implicitly.
• Public pipe works asynchronously. Any number of schema users can write to it.
• Private Pipes: Accessible only by sessions with the same ownerid as the pipe creator, stored programs owned by the pipe creator, or by users connected as SYSDBA.

• V$DB_PIPES: Display info about pipes.
• Writing - Reading pipes:
• The sending session builds a message using one or more calls to the PACK_MESSAGE procedure. This procedure adds the message to the session's local message buffer.

Potential applications:

- External Service interface: communicate with user-written services external to the RDBMS. Services available asynchronously.
- Independent transactions: communicate to a separate session which can perform an operation in an independent transaction (i.e. logging)
- Alerters (non-transactional):
- Debugging: Triggers and Stored procedures can send debug info to a pipe, that other session can keep reading and displaying
- Concentrator: multiplexing large # of users over a fewer # of netwrok connections.

Using DBMS_PIPE: (1) Sending to and Receiving from the Local Pipe or Buffer (a) resets the local buffer, packs messages, and send them to an implicit public pipe. (b) Check the created pipe on V$DB_PIPES. (c) reads from the pipe, unpacks the buffer, reads the data, and prints the contents of the pipe to the console

-- (a) Resets the local buffer, packs messages, and send them to the pipe. 
set serveroutput on
declare
 msg varchar2(30);
 success integer;
begin
 msg := dbms_pipe.unique_session_name;
 -- dbms_pipe.unique_session_name returns a VARCHAR2 string that 
 -- represents the current session.
 dbms_pipe.reset_buffer;
 -- PACK_MESSAGE takes the value of the actual parameter and puts it 
 -- into the buffer stack (FIFO)
 dbms_pipe.pack_message(msg);
 dbms_output.put_line('Written to pipe ['||msg||']');
 msg := 'Message body: read me';
 dbms_pipe.pack_message(msg);
 dbms_output.put_line('Written to pipe ['||msg||']');
 
 -- creates an IMPLICIT public pipe. Pipe will be deleted when last 
 -- message in its buffer is read..
 success := dbms_pipe.send_message(pipename => 'pipe1');
 if success = 0 then
   dbms_output.put_line('Contens of the buffer sent..');
 else
   dbms_output.put_line('Error. Return # ' || success);
 end if;
end;

-- (b) Check the created pipe on V$DB_PIPES:
SQL> SELECT * from v$DB_PIPES;

OWNERID  NAME   TYPE     PIPE_SIZE              
-------- ------ -------- ---------
         PIPE1  PUBLIC        4448                   

--(c) reads from the pipe, unpacks the buffer, reads the data, and prints the contents of the 
-- pipe to the console
set serveroutput on
declare
 msg varchar2(50);
 success integer;
 no_more_items exception;
 e_read_timeout exception;
 pragma exception_init(no_more_items, -06556);
begin
  -- read the message from the pipe. Wait max 5 seconds for 
  -- message in the pipe..
  success := dbms_pipe.receive_message(pipename => 'pipe1', 
                                       timeout => 5);
  if success = 0 then
    dbms_output.put_line('Message received...');
  elsif success = 1 then 
    raise e_read_timeout;
  else
    dbms_output.put_line('Error. Return # ' || success);
  end if;  
  
  begin
    loop    -- loop through all packed messages
      dbms_pipe.unpack_message(msg);
      dbms_output.put_line('Message ['||msg||']');
    end loop; 
  exception
      when no_more_items then 
        dbms_output.put_line('end of buffer reached. exiting..');
  end;
exception
  when e_read_timeout then 
    dbms_output.put_line('Receiving message timeout. Nothing in buffer..');        
end;

Intersession Communication: Session UserA: (1) Creates an explicit public pipe (explicit_public_pipe) (2) Opens cursor with the list of employees with highest salaries in all departments [format: dept, last_name, salary] (2) Writes into the pipe the contents of the cursor. Each row one message. Session UserB: (1) Reads from explicit_public_pipe the list of employees and salaries. (2) Uses UTL_FILE to create or append an OS file with the contents of the pipe. Each message one line.

-- USER A:

set serveroutput on
DECLARE 
  result integer;
  msg    varchar2(100);
  i      number;
  -- cursor returns 
  cursor c1 is 
   select e1.department_id dept, last_name, salary
   from employees e1, 
       ( select department_id, max(salary) sal
         from employees
         group by department_id ) sel
   where e1.department_id = sel.department_id
   and   e1.salary = sel.sal
   order by 1;

  type t_emprec is record (dept employees.department_id%type,
                          ln employees.last_name%type, 
                          sal employees.salary%type);
  emprec t_emprec;
BEGIN
  -- (1) CREATE explicit public pipe
  result := dbms_pipe.remove_pipe('explicit_public_pipe');
  result := dbms_pipe.create_pipe(pipename => 'explicit_public_pipe',
                                  private  => FALSE);
  
  -- (2) PACK messages into the pipes.. (all employees from dept 10)
  open c1;
  i := 0;
  -- packs Session ID
  msg := i ||'$'|| dbms_pipe.unique_session_name;
  dbms_output.put_line('Packing: '|| msg);
  dbms_pipe.pack_message(msg);
  loop
    fetch c1 into emprec;
    exit when c1%notfound;
          i := i + 1;
          msg := i ||':'||emprec.dept||';'||emprec.ln ||';'||emprec.sal||'!';
          dbms_output.put_line('Packing: '|| msg);
          dbms_pipe.pack_message(msg);
  end loop;
  close c1;
  result := dbms_pipe.send_message(pipename => 'explicit_public_pipe',
                                   timeout => 5);
  dbms_output.put_line('Message sent...');
END;
/

anonymous block completed
Packing: 0$ORA$PIPE$007D02160001
Packing: 1:10;Whalen;4400!
Packing: 2:20;Hartstein;13000!
Packing: 3:30;Raphaely;11000!
Packing: 4:40;Mavris;6500!
Packing: 5:50;Fripp;8200!
Packing: 6:60;Hunold;9000!
Packing: 7:70;Baer;10000!
Packing: 8:80;Russell;14000!
Packing: 9:90;King;24000!
Packing: 10:100;Greenberg;12008!
Packing: 11:100;Chen;12008!
Packing: 12:110;Higgins;12008!
Message sent...

-- USER B runs this code:

set serveroutput on
DECLARE
  msg   varchar2(200);
  vinfo varchar2(200);
  result integer;
  no_more_items exception;
  e_read_timeout exception;
  pragma exception_init(no_more_items, -06556);
  fdest utl_file.file_type;
  type FileAttrRec is Record (
                      vfilexists BOOLEAN,
                      vfilelength number,
                      vblocksize  binary_integer);
  vfilerec fileattrrec;
BEGIN
  -- read the message from the pipe. Wait max 5 seconds for 
  -- message in the pipe..
  result := dbms_pipe.receive_message(pipename => 'explicit_public_pipe', 
                                      timeout => 5);
  if result = 0 then
    dbms_output.put_line('Message received...');
  elsif result = 1 then 
    raise e_read_timeout;
  else
    dbms_output.put_line('Error. Return # ' || result);
  end if;  
  
  -- Open FILE for writing..
  -- Check: If file ALREADY Exists: Append. Otherwise: Write
  utl_file.fgetattr('USER_DIR', 'fpipeout.txt', vfilerec.vfilexists, 
                     vfilerec.vfilelength, vfilerec.vblocksize);
  If vfilerec.vfilexists THEN
    fdest := utl_file.fopen('USER_DIR', 'fpipeout.txt', 'A', 1024);
    dbms_output.put_line('Destination file exists. Appending..');
  Else
    fdest := utl_file.fopen('USER_DIR', 'fpipeout.txt', 'W', 1024);
  End if; 
  select sysdate into vinfo from dual;
  vinfo := 'Entering new data on ' || vinfo||':';
  utl_file.put_line(fdest, vinfo, true);
  
  -- iteract through the messages in the buffer. 
  -- Write each one into the file..
  begin
    loop
      dbms_pipe.unpack_message(msg);
      dbms_output.put_line('Message ['||msg||']');
      utl_file.put_line(fdest, msg, true);
    end loop; 
  exception
      when no_more_items then 
        dbms_output.put_line('end of buffer reached. exiting..');
  end;
  -- close file
  utl_file.fclose(fdest);
  -- close pipe
  result := dbms_pipe.remove_pipe('explicit_public_pipe');
exception
  when e_read_timeout then 
    dbms_output.put_line('Receiving message timeout. Nothing in buffer..');        
end;
/

Check contents of file fpipeout.txt: 
$ cat fpipeout.txt 
Entering new data on 19-SEP-11:
0$ORA$PIPE$007D02160001
1:10;Whalen;4400!
2:20;Hartstein;13000!
3:30;Raphaely;11000!
4:40;Mavris;6500!
5:50;Fripp;8200!
6:60;Hunold;9000!
7:70;Baer;10000!
8:80;Russell;14000!
9:90;King;24000!
10:100;Greenberg;12008!
11:100;Chen;12008!
12:110;Higgins;12008!

Intersession Communication: an api for communication Creates PACKAGE with send and receive procedures

-- In a package
create or replace package message_api AS
  procedure send_max_sal(p_dept in employees.department_id%type);
  procedure receive;
end message_api;

create or replace package body message_api AS
  procedure send_max_sal(p_dept in employees.department_id%type) AS
    result integer;
    msg    varchar2(100);
    cursor c1(dept employees.department_id%type) is 
      select last_name, salary
      from employees
      where salary = ( select max(salary)
                         from employees
                         where department_id = 20)
      and department_id =dept;
    vlastname employees.last_name%type;
    vsal      employees.salary%type;
  BEGIN  
    -- (2) PACK messages into the pipe.. 
    open c1(p_dept);
    fetch c1 into vlastname, vsal;
    close c1;
    msg := vlastname||':'||vsal;
    dbms_output.put_line('Packing: '|| msg);
    dbms_pipe.pack_message(msg);  
    result := dbms_pipe.send_message(pipename => 'message_pipe',
                                   timeout => 5);
    if result != 0 then
      raise_application_error(-20001, 'message_pipe error');
    end if;
    dbms_output.put_line('Message sent...');
  END send_max_sal;
    
  procedure receive as
    result integer;
    msg    varchar2(200);
  begin
    result := dbms_pipe.receive_message( pipename => 'message_pipe', 
                                         timeout  => dbms_pipe.maxwait);
    if result = 0 then
      dbms_pipe.unpack_message(msg);
      dbms_output.put_line('Message is: '|| msg);
    else
      raise_application_error(-20002, 'message_api.receive error');
    end if;
  end receive;
end message_api;
/

-- time t0: USER B: 
set serveroutput on
exec usera.message_api.receive;
(hangs)..

-- time t1: USER A:
begin 
 message_api.send_max_sal(20);
end;
/

anonymous block completed
Packing: Hartstein:13000
Message sent...

-- time t2: USER B:
anonymous block completed
Message is: Hartstein:13000

DBMS_SPACE - check space requirements

SQL> analyze table emp2 compute statistics;
table EMP2 analyzed.

SQL> set serveroutput on 
SQL> declare
 l_used_bytes number;
 l_alloc_bytes number;
begin
  dbms_space.create_index_cost
    ( ddl => 'create index emp_idx1 on emp2' ||
             '(first_name, department_id)',
      used_bytes => l_used_bytes,
      alloc_bytes => l_alloc_bytes);
  
  dbms_output.put_line('Used bytes: ' || l_used_bytes);
  dbms_output.put_line('Allocated bytes:  ' || l_alloc_bytes);
end;
/

Used bytes: 1753088
Allocated bytes:  6291456

SQL>

UTL_FILE

(1) Using UTL_FILE package

• UTL_FILE provides a restricted version of operating system stream file I/O.
• Files and directories accessible through UTL_FILE: controlled by a number of factors and database parameters.
  The most important of these is the set of directory objects that have been granted to the user.
• Provides file access both on the client side (FORMS apps) and on the server side.
• Note that symbolic links are not supported.

Previous Oracle Versions:

• In the past, accessible directories for UTL_FILE were specified in the init.ora using the UTL_FILE_DIR parameter.
• UTL_FILE_DIR access is NO LONGER recommended.
• Oracle recommends that you instead use the DIRECTORY object feature, which replaces UTL_FILE_DIR.
• Directory objects:
  (a) offer more flexibility and granular control to the UTL_FILE application administrator,
  (b) can be maintained dynamically (that is, without shutting down the database), and
  (c) are consistent with other Oracle tools.

UTL_FILE.GET_LINE

• the len parameter of UTL_FILE.GET_LINE specifies the requested number of bytes of character data.
• The number of bytes actually returned to the user will be the lesser of:
- The GET_LINE len parameter, or - The number of bytes until the next line terminator character, or - The max_linesize parameter specified by UTL_FOPEN
• The FOPEN max_linesize parameter must be a number in the range 1 and 32767.
  If unspecified, Oracle supplies a default value of 1024.
• The GET_LINE len parameter must be a number in the range 1 and 32767.
  If unspecified, Oracle supplies the default value of max_linesize.
• If max_linesize and len are defined to be different values, then the lesser value takes precedence.

Examples:

First create the directory objects that identify the accessible directories:
As System:

SQL> create directory user_dir AS '/home/oracle/fileio/user';
SQL> grant read on directory user_dir to public;
SQL> grant write on directory user_dir to public;

Note:

• Oracle does not check whether the directory exists during the creation of the directory object.
• with revoke write on directory user_dir to public:
  - The file is open and read successfully when the user has only READ permission in the directory.
  - Directory name in utl_file.fopen is CASE SENSITIVE. ALL CAPS required.

(a) Simple Read: (1) Declare a file handler (utl_file.file_type) (2) Open file. (The file is only acessible in the directory specified by the directory object). (3) Read and prints chunks of 1kb until reach EOF. (until no_data_found)

set serveroutput on 
declare 
  v1 varchar2(32767);
  v2 varchar2(32767);
  f1 utl_file.file_type;   -- declare a file handler.
begin
 f1 := utl_file.fopen('USER_DIR', 'ftest1.txt', 'R', 1024);
 begin
   loop 
     utl_file.get_line(f1, v1, 32767);
     v2 := v2 || chr(10)|| v1;
   end  loop;
 exception
   when no_data_found then 
     null;
 end;
 utl_file.fclose(f1);
 dbms_output.put_line(v2);
end;
/
anonymous block completed

"By midmorning the rain had stopped. 
Water dripped from the trees in the alameda and the crepe hung in soggy strings. 
He stood with the horses and watched the wedding party emerge from the church. 
The groom wore a dull black suit too large for him and he looked not uneasy but half 
desperate, as if unused to clothes at all. 
The bride was embarrassed and clung to him and they stood on the steps for their photograph to
be taken and in their antique formalwear posed there in front of the church they already had 
the look of old photos. 
In the sepia monochrome of a rainy day in the lost village they'd grown old instantly"
Cormac McCarthy

(b) Copying data from file A into File B. If destination file exists, open it in Append mode.

set serveroutput on 
declare 
  v1 varchar2(32767);
  v2 varchar2(32767);
  fsource utl_file.file_type;
  fdest utl_file.file_type;
  vinfo varchar2(1000);
  type FileAttrRec is Record (
                      vfilexists BOOLEAN,
                      vfilelength number,
                      vblocksize  binary_integer);
  vfilerec fileattrrec;
  
begin
  fsource := utl_file.fopen('USER_DIR', 'fsource.txt', 'R', 1024);
    utl_file.fgetattr('USER_DIR', 'fdest.txt', vfilerec.vfilexists, 
                     vfilerec.vfilelength, vfilerec.vblocksize);
  
  If vfilerec.vfilexists THEN 
      fdest   := utl_file.fopen('USER_DIR', 'fdest.txt', 'a', 1024);
      dbms_output.put_line('Destination file exists. Appending..');
  ELSE
      fdest   := utl_file.fopen('USER_DIR', 'fdest.txt', 'W', 1024);
  END IF;
  
  if utl_file.is_open(fsource) and utl_file.is_open(fdest) then 
     dbms_output.put_line('Files fsource and fdest opened successfully..');
  end if;

  begin
    select sysdate into vinfo from dual;
    vinfo := vinfo || '. Entering new data: ';
    utl_file.put_line(fdest, vinfo, true);
    loop 
      utl_file.get_line(fsource, v1, 32767);
      dbms_output.put_line('Transferring: ' || v1);
      utl_file.put_line(fdest, v1, TRUE);
      v2 := v2 || chr(10)|| v1;
    end  loop;
  exception
    when no_data_found then 
      null;
  end;
  utl_file.fclose(fsource);
  utl_file.fclose(fdest);
  dbms_output.put_line(v2);
end;

(23) Working with Large Objects

[ Oracle PL/SQL ]

• Let you store text, images, music, and video in the database.
• You can store character large objects in CLOB columns, and binary large objects inside the database as BLOB columns or outside the database as BFILE (binary file) columns.
• BFILE columns: store a locator that points to the physical location of an external file.

• LOBs can hold up to a maximum of 8 to 128 terabytes, depending on how you configure your database.
• A call to the DBMS_LOB.GET_STORAGE_LIMIT function tells you your database maximum LOB size.

set serveroutput on
declare 
 var1 clob :=  'some string'; 
 var2 blob := hextoraw( '43'||'421'|| '52');
 result number;
begin
 result := dbms_lob.get_storage_limit(var1);
 dbms_output.put_line('CLOB maximum limit: '|| 
                        to_char(result/1024/1024/1024, '999,999') || ' Gigabytes');
 dbms_output.put_line('CLOB length: ' || dbms_lob.getlength(var1));
 
 result := dbms_lob.get_storage_limit(var2);
 dbms_output.put_line('BLOB maximum limit: '|| 
                       to_char(result/1024/1024/1024, '999,999') || ' Gigabytes');
 dbms_output.put_line('BLOB length: ' || dbms_lob.getlength(var2));
end;

anonymous block completed
CLOB maximum limit:   16,264 Gigabytes
CLOB length: 11
BLOB maximum limit:   32,528 Gigabytes
BLOB length: 4

what for?

• simple structured data => relational tables
• complex structured data => object-relational constructs (collections, references, UDTs)
• semi-structured data => logical structure not typically interpreted by the database. LOBs can be used here.
Examples of semi-structured data include document files such as XML documents or word processor files.
• unstructured data => photos, videos. LOBs can be used here.

On 11g R1: SecureFiles. LOBs reengineered

• You can now define BLOB, CLOB, or NCLOB columns as SecureFiles when you create a table or alter it.
• SecureFiles are declared with special storage parameters that let you encrypt, compress, and deduplicate them.
• Oracle 11g lets you store SecureFiles in BLOB, CLOB, and NCLOB columns.
• They work on a principle of Transparent Data Encryption (TDE) and use an Oracle Wallet as the encryption key.

• BLOB, CLOB and NCLOB can be stored in the database (internal) or in external files.
  Internal LOBs can be either persistent or temporary.
• Persistent LOB: Exists in a table row in the database.
• Temporary LOB: Created when you instantiate a LOB only within the scope of your local application.
• External LOBs are data objects stored in operating system files, outside the database tablespaces.
• The database accesses external LOBs using the SQL data type BFILE, which is the only external LOB data type.
• BFILEs are read-only data types.
• External LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying file system as governed by the operating system.
  

COPY vs. REFERENCE semantics

• Internal LOBs use copy semantics.: both the LOB locator and LOB value are logically copied during insert, update, or assignment operations.
• External LOBs use reference semantics.: only the LOB locator is copied during insert operations.

A LOB (BLOB, CLOB, NCLOB) column can be in one of three states

NULL	The table cell is created, but the cell holds no locator or value. (The column contains a NULL value)
Empty	The column contains a LOB locator that has no value. Length of LOB is zero.
Populated	The column contains a LOB locator and a value. A call to dbms_lob.getlength here returns a postive value.

CLOB and NCLOB
The CLOB datatype is an object type, therefore it requires implicit or explicit construction of an object instance.

Declaring a CLOB:

var1 CLOB;                  -- Declare a null reference to a CLOB.
var1 CLOB := empty_clob();  -- Declare an empty CLOB.
var2 CLOB := 'some_string'; -- Declare a CLOB with a string literal.

Manipulating a CLOB column

create table item ( 
 item_id number, 
 item_title varchar2(20), 
 item_desc  clob,
 item_photo bfile);

desc item
Name       Null Type         
---------- ---- ------------ 
ITEM_ID         NUMBER       
ITEM_TITLE      VARCHAR2(20) 
ITEM_DESC       CLOB         
ITEM_PHOTO      BFILE()    


INSERT into item values
  (1, 'Ulysses', empty_clob(), null);
1 row inserted.

-- This approach limits the amount of data that can be 
-- inserted into a CLOB column to is limited to 4K (SQL) or 32K (PL/SQL)

UPDATE item
SET item_desc = 'The Lord of the Rings is a high fantasy epic written by '||  
'philologist and University of Oxford professor J. R. R. Tolkien. The story '||
'began as a sequel to Tolkien's earlier, less complex children's fantasy '||
'novel The Hobbit (1937), but eventually developed into a much larger work. '||
'It was written in stages between 1937 and 1949, much of it during the '||
'Second World War.[1] It is the second best-selling novel ever written, '||
'with over 150 million copies sold.'
WHERE item_id =1;

Alternatively, you can use the DBMS_LOB package

Using DBMS_LOB, you can:

(a) Insert a new row and initialize the CLOB column with a call to empty_clob()
(b) Read chunks from the file and insert into the CLOB Column, appending each new chunk until copying the entire file (up to 128Tb).
(c) To open and read the file at the OS level from within the PLSQL Package, you need to create a DB Directory object and grant permission for the user to read from that directory.
(d) You may use conditional compilation directives to insert debut information..

-- You need to connect as SYSTEM to perform the following two steps: 
-- (1) create a virtual directory. should be conn as system
create directory generic as '/tmp/clob-file'; 

-- grant read permissions on the directory to the user;
grant read on directory generic to dev2;

-- Create procedure to load the file into the CLOB column.
CREATE OR REPLACE PROCEDURE load_clob_from_file
 (src_file_name in varchar2,
  table_name in varchar2,
  column_name in varchar2,
  pk_name in varchar2,
  pk_value in varchar2) is
  
  -- define local vars for dbms_lob.loadclobfromfile proc
  des_clob clob;
  -- the BFILENAME function secures the directory path for 
  -- the GENERIC directory from the database dictionary and 
  -- returns the absolute filename
  src_clob bfile := bfilename('GENERIC', src_file_name);
  des_offset number:= 1;
  src_offset number := 1;
  ctx_lang number := dbms_lob.default_lang_ctx;
  warning number;
  -- declare var to hold the size of the file to be imported.
  src_clob_size number;
  stmt varchar2(2000);
BEGIN
  -- open source file and reads it into a BFILE datatype
  if dbms_lob.fileexists(src_clob) = 1 and 
     NOT dbms_lob.isopen(src_clob)= 1 THEN
     src_clob_size := dbms_lob.getlength(src_clob);
     $IF $$DEBUG $THEN
       dbms_output.put_line('Opening CLOB. Size: '|| src_clob_size);
     $END
    dbms_lob.open(src_clob, DBMS_LOB.LOB_READONLY);
  end if;
  -- assign a dynamic string to the stmt var
  -- Initializes the CLOB column to an empty_clob()
  -- and returns a reference to the column into an 
  -- output variable
  stmt :=  'UPDATE ' ||table_name||' '
       ||  'SET    '||column_name||' = empty_clob()'
       ||  'WHERE  '|| pk_name||' = '||''''||pk_value||''' '
       ||  'RETURNING '||column_name||' INTO :locator';
  -- execute dynamic stmt
  -- the bind variable :locator is assigned to the output var
  $IF $$DEBUG $THEN
    dbms_output.put_line('Preparing to initialize CLOB column');
  $END
  EXECUTE IMMEDIATE stmt USING OUT des_clob;
  $IF $$DEBUG $THEN
    dbms_output.put_line('Initialization successful..');
  $END
  -- read and write file to CLOB, close source file and commit;
  -- the call to DBMS_LOB.LOADCLOBFROMFILE. The procedure 
  -- copy the contents of the file into the table column.
  -- This R/W operation bypasses the 32Kb handling limitation of PL/SQL
  dbms_lob.loadclobfromfile( 
           dest_lob    => des_clob,
           src_bfile   => src_clob,
           amount      => dbms_lob.getlength(src_clob),
           dest_offset => des_offset,
           src_offset  => src_offset,
           bfile_csid  => dbms_lob.default_csid,
           lang_context => ctx_lang,
           warning     => warning);      
  -- close the OS file.   
  dbms_lob.close(src_clob);
  
  IF src_clob_size = dbms_lob.getlength(des_clob) THEN
     $IF $$DEBUG $THEN
       dbms_output.put_line('Success!');
     $END
     commit;
  ELSE
    $IF $$DEBUG $THEN
      dbms_output.put_line('Failure.');
    $END
    RAISE dbms_lob.operation_failed;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error stack: '|| dbms_utility.format_error_stack());
    dbms_output.put_line('Error Backtrace: '|| dbms_utility.format_error_backtrace());
END load_clob_from_file;
/

PROCEDURE load_clob_from_file compiled

-- Alter session to set conditional compilation directive 
-- and turn on debugging 
SQL> alter session set plsql_ccflags = 'DEBUG:TRUE';


SQL> set serveroutput on 
SQL> BEGIN
 load_clob_from_file (
   src_file_name    => 'ulysses.txt',
   table_name       => 'item',
   column_name      =>  'item_desc',
   pk_name          => 'item_id',
   pk_value         => to_char(1));
END;
/
anonymous block completed
Opening CLOB. Size: 11989153
Preparing to initialize CLOB column
Initialization successful..
Success!

(ref) On Transactions, Locks and Cursors

Oracle Locking mechanisms

• locks prevent destructive interactions that incorrectly update data or incorrectly alter underlying data structures, between transactions accessing shared data.
• Two types: exclusive locks and share locks.
• Oracle's default locking mechanisms ensures data concurrency, data integrity, and statement-level read consistency.
• A row is locked only when modified by a writer.
• A writer of a row blocks a concurrent writer of the same row.
• A reader never blocks a writer.(except: SELECT .. FOR UPDATE)
• A writer never blocks a reader.

Lock modes: level of restrictiveness x degree of data concurrency

• The less restrictive the level, the more available the data is for access by other users.
• Exclusive lock mode:
• Prevents the resource from being shared. Obtained for data modification.
• The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.
• Share lock mode:
• Allows resource to be shared. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer who needs an exclusive lock.
• Several transactions can acquire share locks on the same resource.
• If a SELECT ... FOR UPDATE selects a single table row, the transaction acquires (a) an exclusive row lock and (b) a row share table lock.
• The row lock allows other sessions to modify any rows other than the locked row, while the table lock prevents sessions from altering the structure of the table.
• Oracle Database never escalates locks

DML_LOCKS (init.ora)

Default: Derived: 4 * TRANSACTIONS (assumes an average of 4 tables referenced for each transaction)
Modifiable: No
Range: 20 to unlimited

Automatic Locks

• DML Lock
• also called data lock. Guarantees integrity of data concurrently accessed by multiple users.
• They are either Row Locks (TX) or Table Locks (TM).
• DML_LOCKS (init.ora): determines the maximum number of DML locks (one for each table modified in a transaction). You might need to increase it if you use explicit locks.
• DDL Locks
• Protects the definition of a schema object while an ongoing DDL operation acts on or refers to the object.
• Users cannot explicitly request DDL locks.
• Exclusive DDL lock: prevents other sessions from obtaining a DDL or DML lock.
• Share DDL lock: prevents conflicting DDL operations, but allows similar DDL operations.
• Breakable Parse Locks
• Held by a SQL statement or PL/SQL program unit for each schema object that it references. Acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped.
• A parse lock is acquired in the shared pool during the parse phase of SQL statement execution. The lock is held as long as the shared SQL area for that statement remains in the shared pool.

DML Locks
ROW LOCK (TX)	If a transaction obtains a lock for a row, then the transaction also acquires a lock for the table containing the row. (To prevent conflicting DDL operations). Oracle stores lock information in the data block that contains the locked row.
TABLE LOCK (TM)	acquired when a table is modified by an I,U,D, MERGE, SELECT FOR UPDATE, or LOCK TABLE statement. TM can be held as: Row Share (RS): (subshare table lock (SS)), indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. It is the least restrictive mode of table lock, offering the highest degree of concurrency for a table. Row Exclusive Table Lock (RX): (subexclusive table lock (SX)), indicates that the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE. It An allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Share Table Lock (S): allows other transactions to query the table (without using SELECT ... FOR UPDATE), but updates are allowed only if a single transaction holds the share table lock. Share Row Exclusive Table Lock (SRX): (share-subexclusive table lock (SSX)). More restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. Allows other transactions to query (except for SELECT ... FOR UPDATE) but not to update the table. Exclusive Table Lock (X): The most restrictive. Prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.

• System Locks: Latches, Mutexes, and Internal locks
• Latches:
• Serializes access to memory structures. Protect shared memory resources from corruption when accessed by multiple processes. [ (a) Concurrent modification by multiple sessions; (b) Being read by one session while being modified by another session; (c) Deallocation (aging out) of memory while being accessed. ]
• i.e. while processing a salary update of a single employee, the database may obtain and release thousands of latches
• Increase in latching ==> decrease in concurrency. (i.e.: excessive hard parse operations create contention for the library cache latch.)
• V$LATCH: contains detailed latch usage statistics for each latch, including the number of times each latch was requested and waited for.
• Mutex (Mutual exclusion object)
• similar to a latch, but whereas a latch typically protects a group of objects, a mutex protects a single object.
• Internal Locks
• Dictionary cache locks, file and log mgmt locks, Tablespace and Undo segment locks)

Manual data Locks: overriding default

• LOCK TABLE
• SELECT FOR UPDATE clause
• SET TRANSACTION with the READ ONLY or ISOLATION LEVEL SERIALIZABLE option

On Isolation Levels

ALTER SESSION SET ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED | READ ONLY} The ISOLATION_LEVEL parameter specifies how transactions containing database modifications are handled. It is a session parameter only, NOT an initialization parameter. SERIALIZABLE: transaction sees only changes committed at the time the transaction—not the query—began and changes made by the transaction itself. Transactions in the session use the serializable transaction isolation mode as specified in the SQL standard. If a serializable transaction attempts to execute a DML statement that updates rows currently being updated by another uncommitted transaction at the start of the serializable transaction, then the DML statement fails. A serializable transaction can see its own updates. READ COMMITTED: (default) Transactions in the session will use the default Oracle Database transaction behavior. every query executed by a transaction sees only data committed before the query—not the transaction—began. phantoms and fuzzy reads: because the database does not prevent other transactions from modifying data read by a query, other transactions may change data between query executions. Thus, a transaction that runs the same query twice may experience fuzzy reads and phantoms. If the transaction contains DML that requires row locks held by another transaction, then the DML statement will wait until the row locks are released.

LOCK TABLE

• explicitly locks one or more tables in a specified lock mode.
• The lock mode determines what other locks can be placed on the table.
• A table lock never prevents other users from querying a table, and a query never acquires a table lock
• When a LOCK TABLE statement is issued on a view, the underlying base tables are locked
• LOCK TABLE employees, departments IN EXCLUSIVE MODE NOWAIT;
• MODE: [ NOWAIT | WAIT n | ]
• NOWAIT: error if lock is not available immediately
• WAIT n: wait up to n secs
• <blank>: wait indefinitely to acquire the lock

what type of lock to use (check here)
ROW SHARE and ROW EXCLUSIVE SHARE MODE SHARE ROW EXCLUSIVE EXCLUSIVE MODE Let Oracle do the locking: [ SET TRANSACTION ISOLATION LEVEL level ] or [ ALTER SESSION ISOLATION LEVEL level ]

Transactions, TCL, and Isolation Levels

..later..

Autonomous transactions

Using SELECT... FOR UPDATE

• SELECT FOR UPDATE selects the rows of the result set and locks them.
• Enables you to base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them.
• You can also use SELECT FOR UPDATE to lock rows that you do not want to update
• By default, SELECT FOR UPDATE waits until the requested row lock is acquired. To change this behavior, use the [ NOWAIT | WAIT | SKIP LOCKED ].
• When SELECT FOR UPDATE is associated with an explicit cursor, the cursor is called a FOR UPDATE cursor.

set serveroutput on
declare
 my_emp_ln employees.last_name%type;
 my_emp_id number;
 my_job_id varchar2(10);
 my_sal number(8,2);
 newsal number(8,2);
 
 -- declare a FOR UPDATE cursor
 cursor c1 is
   select employee_id, last_name, job_id, salary
   from employees for update;
begin
  open c1;
  loop
    fetch c1 into my_emp_id, my_emp_ln, my_job_id, my_sal;
    If my_job_id = 'SA_REP' then
      newsal := my_sal*1.12;
      -- update only the rows locked by the cursor
      -- identified through the "WHERE CURRENT OF" clause.
      update employees
      set salary = newsal
      where current of c1;
      dbms_output.put_line('Emp '|| my_emp_ln ||
               ': salary increased from '|| my_sal ||' to '|| newsal);
    end if;
    exit when c1%notfound;
  end loop;
end;

anonymous block completed
Emp Tucker: salary increased from 10000 to 11200
Emp Bernstein: salary increased from 9500 to 10640
...

Dynamic SQL: DBMS_SQL package

DBMS_SQL Package

SYS.DBMS_SQL is compiled with AUTHID CURRENT_USER (runs using the privileges of the current user)

• Defines a SQL cursor number (PL/SQL Integer): Can be passed across call boundaries and stored.
• Provides an interface to use dynamic SQL to parse ANY DML or DDL statement using PL/SQL.
• When HAS to be used?
• - you don't have the complete SELECT list
• - you don't know what placeholds in a SELECT or DML must be bound
• When you CANNOT use DBMS_SQL?
• - Dynamic SQL stmt retrieves rows into records
• - If you need to use cursor attributes (%FOUND, %ISOPEN, %NOTFOUND, or %ROWCOUNT)
• You can switch between native dynamic SQL AND DBMS_SQL package with:
• DBMS_SQL.TO_REFCURSOR function
• DBMS_SQL.TO_CURSOR_NUMBER function

Oracle Call Interface (OCI) x DBMS_SQL Package

• The ability to use dynamic SQL from within stored procedures generally follows the model of the Oracle Call Interface (OCI).
• Addresses (also called pointers) are NOT user-visible in PL/SQL.
• The OCI uses bind by address. DBMS_SQL package uses bind by value.
• With DBMS_SQL you must call VARIABLE_VALUE to retrieve the value of an OUT parameter for an anonymous block, and you must call COLUMN_VALUE after fetching rows to actually retrieve the values of the columns in the rows into your program.
• The current release of the DBMS_SQL package does not provide CANCEL cursor procedures.
• Indicator variables are not required, because NULLs are fully supported as values of a PL/SQL variable.

SYS.DBMS_SQL Subprograms
BIND_ARRAY (Ps) BIND_VARIABLE (Ps) CLOSE_CURSOR COLUMN_VALUE DEFINE_ARRAY DEFINE_COLUMN DESCRIBE_COLUMN (P) EXECUTE (F) EXECUTE_AND_FETCH (F) FETCH_ROWS (F) IS_OPEN (F) LAST_ERROR_POSITION (F) LAST_ROW_COUNT (F) LAST_ROW_ID LAST_SQL_FUNCTION_CODE OPEN_CURSOR (F) PARSE (Ps) TO_CURSOR_NUMBER (F) TO_REFCURSOR (F) VARIABLE_VALUE (Ps)

New security regime: Oracle 11gR1

• Stricter than in previous versions
• Checks are made when binding and executing.
• Failure lead to ORA-29470: Effective userid or roles are not the same as when cursor was parsed

Execution Flow:

1. OPEN_CURSOR
2. PARSE
3. BIND_VARIABLE or BIND_ARRAY
4. DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY
5. EXECUTE
6. FETCH_ROWS or EXECUTE_AND_FETCH
7. VARIABLE_VALUE, COLUMN_VALUE, or COLUMN_VALUE_LONG
8. CLOSE_CURSOR

i.e. -- Procedure uses DBMS_SQL package to perform SELECT and DML statements.

Task: Delete all employees of a given deparmtent who earn more than a given value.

(A) DEL_EMP procedure is composed of the three parts below:
(1) Perform a dynamic SELECT to show the number of records that will be deleted
(2) Perform the dynamic DML.
(3) Perform a dynamic SELECT to show that the records were indeed deleted.

(B) an anonymous block calls del_emp procedure

CREATE OR REPLACE PROCEDURE del_emp (dept in number, salary in number) as 
 cursor_dml     integer;
 cursor_select  integer;
 rows_processed integer;
 v_numemp       integer;
 sel_str        varchar2(500);
 dml_str        varchar2(500);
 ignore         integer;

BEGIN
  -- Part 1: Select number BEFORE DML statement
  -- (1) Open cursor for SELECT.
  cursor_select := dbms_sql.open_cursor; 
  sel_str := 'Select count(*) nuemp from employees ' ||
            ' where department_id = :d';           

  -- (2) Parse SQL statement
  dbms_sql.parse(cursor_select, sel_str, dbms_sql.native);

  -- (3) Bind variables
  dbms_sql.bind_variable(cursor_select, ':d', dept);
  sel_str := 'Select count(*) nuemp from employees ' ||
            ' where department_id = '|| to_char(dept);            
  dbms_output.put_line('Select is: ' || sel_str); 

  -- (4) use define_column to specify the variables that 
  -- are to receive the SELECT values, much the way an 
  -- INTO clause does for a static query. 
  dbms_sql.define_column(cursor_select, 1, v_numemp);

  -- (5) call the execute function to run STMT
  ignore := dbms_sql.execute(cursor_select);
  -- (6) Use fetch_rows to retrieve the query results. 
  -- Each successive fetch retrieves another set of rows, 
  -- until the fetch is unable to retrieve anymore rows.
  -- If SELECT returns only ONE row, you may prefer to use 
  -- EXECTUVE_AND_FETCH instead.
  If dbms_sql.fetch_rows(cursor_select) > 0 then 
    dbms_sql.column_value(cursor_select, 1, v_numemp);
  end if;
  dbms_output.put_line('Num emp in dept '|| to_char(dept) || ' before delete: ' 
                   || to_char(v_numemp)); 

  -- (7) Close the cursor.
  dbms_sql.close_cursor(cursor_select);
 
  -- Part 2: Now proceed with the DML Statement
  dml_str := 'delete from employees where department_id = :d' || 
            ' and salary > :x';
  cursor_dml := dbms_sql.open_cursor;
  dbms_sql.parse(cursor_dml, dml_str, dbms_sql.native);
  dbms_sql.bind_variable(cursor_dml, ':d', dept);
  dbms_sql.bind_variable(cursor_dml, ':x', salary);
  rows_processed := dbms_sql.execute(cursor_dml);
  dbms_output.put_line('Num rows deleted: ' || rows_processed);
  dbms_sql.close_cursor(cursor_dml);
  
  -- Part 3: Select the number AFTER the DML statement
  cursor_select := dbms_sql.open_cursor; 
  sel_str := 'Select count(*) nuemp from employees ' ||
            ' where department_id = :d';           
  dbms_sql.parse(cursor_select, sel_str, dbms_sql.native);
  dbms_sql.bind_variable(cursor_select, ':d', dept);
  dbms_sql.define_column(cursor_select, 1, v_numemp);
  sel_str := 'Select count(*) nuemp from employees ' ||
            ' where department_id = '|| to_char(dept);            
  dbms_output.put_line('Select is: ' || sel_str); 
  dbms_sql.define_column(cursor_select, 1, v_numemp);
  ignore := dbms_sql.execute(cursor_select);
  If dbms_sql.fetch_rows(cursor_select) > 0 then 
    dbms_sql.column_value(cursor_select, 1, v_numemp);
  end if;  
  dbms_output.put_line('Num emp in dept '|| to_char(dept) || ' after delete: ' 
                   || to_char(v_numemp)); 
  dbms_sql.close_cursor(cursor_select);
EXCEPTION
 when others then 
   if dbms_sql.is_open(cursor_select) then
      dbms_sql.close_cursor(cursor_select);
  end if;
  if dbms_sql.is_open(cursor_dml) then 
     dbms_sql.close_cursor(cursor_dml);
  end if;
   -- Use Function SQLCODE to return error number 
   dbms_output.put_line('Error code: ' || to_char(sqlcode));

   -- Use Function SQLERRM to return error Message 
   dbms_output.put_line('Error Message: ' || to_char(sqlerrm));
 
   -- Starting on 10gR2, Oracle recommends that you use 
   -- DBMS_UTILITY.FORMAT_ERROR_STACK() instead of SQLERRM.
   -- While SQLERRM is limited to 512bytes, the other function is not. 
   dbms_output.put_line('Error stack: ' || dbms_utility.format_error_stack());

   -- You can also use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()
   -- this function lists the complete error stack, including the line number 
   -- that generated the exception.
   dbms_output.put_line('Error backtrace: ' || dbms_utility.format_error_backtrace());
END del_emp;

(B) Anonymous block calls DEL_EMP.

set serveroutput on
declare
  nemp integer;
begin
 del_emp(110, 6000);
 commit;
end;
/

anonymous block completed
Select is: Select count(*) nuemp from employees  where department_id = 110
Num emp in dept 110 before delete: 2
Num rows deleted: 2
Select is: Select count(*) nuemp from employees  where department_id = 110
Num emp in dept 110 after delete: 0

-- If the table employees is dropped or renamed, execution of del_emp will fail:

set serveroutput on
declare
  nemp integer;
begin
 del_emp(110, 6000);
 commit;
end;
/

anonymous block completed
Error code: -942
Error Message: ORA-00942: table or view does not exist
Error stack: ORA-00942: table or view does not exist

Error backtrace: ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at "DEVEL.DEL_EMP", line 17

(9) Exception Handling

[ Oracle PL/SQL ]

Handling exceptions

• Exceptions can be either an Oracle error or a user-defined error.
• Oracle errors include predefined exceptions enclosed in the STANDARD package.
• There are also unamed Oracle errors. It is possible to associate a name with an unamed Oracle error using the Pragma EXCEPTION_INIT
• User-defined exceptions are raised using
  (a) the RAISE statement, or
  (b) through calls to RAISE_APPLICATION_ERROR procedure.

Exception Handling block syntax:

WHEN {predefined_exception | user_defined_exception | OTHERS} THEN
   exception_handling_statement;
[RETURN | EXIT ];

---i.e.---
EXCEPTION
  WHEN ex_name_1 THEN statements_1               -- Exception handler
  WHEN ex_name_2 OR ex_name_3 THEN statements_2  -- Exception handler
  WHEN OTHERS THEN statements_3                  -- Exception handler
END;

[ WHEN OTHERS ]

• Catches all exceptions not explicitly handled in the previous exception-handling parts of the block.
• Recommendation: The last statement in the OTHERS exception handler should be either
  RAISE or an call of the RAISE_APPLICATION_ERROR procedure.
• If you do not follow this practice and PL/SQL warnings are enabled, you get PLW-06009.
• WHEN OTHERS is optional. It can appear only once, as the last exception handler in the exception-handling part of the block.

Exception Propagation

• Handlers in the Exception section catch exceptions raised in the EXECUTABLE section of the block.
• Exception raised in Declaration section
• Errors in the Declaration section are usually assignment errors, but can still be caught, not locally, but in the enclosing block.
• Exception raised in a Declaration section propagates immediately to the enclosing block (or to the invoker or host environment if there is no enclosing block).
• Therefore, the exception handler must be in an enclosing or invoking block, not in the same block as the declaration.
• It is probably better coding practice to avoid assignment in the Declaration section altogether.
• Exceptions raised in the Exception Section:
• Exception raised in an Exception section propagates immediately to the enclosing block (or to the invoker or host environment if there is no enclosing block).
• Exceptions in calls between subprograms

Handling exceptions:

• Case 1: An exception handler cannot catch an exception raised in the exception section of the same block.
• Such exceptions propagate to the outer block and can only be caught there or in further outer blocks.

set serveroutput on         
declare
  A exception;
  B exception;
begin
  raise a;
exception
  when a then 
     dbms_output.put_line('Raising b from A handler.');     -- exception b raised here CANNOT be 
                                                            -- caught by a handler in the same block. 
     raise b;
  when b then 
     dbms_output.put_line('exception b caught');
end;
/
                                                            -- Here the exception is not caught.
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 9
ORA-06510: PL/SQL: unhandled user-defined exception
06510. 00000 -  "PL/SQL: unhandled user-defined exception"
*Cause:    A user-defined exception was raised by PL/SQL code, but
           not handled.
*Action:   Fix the problem causing the exception or write an exception
           handler for this condition. Or you may need to contact your
           application administrator or DBA.

• Case 2: The exception raised within an Exception section can only be caught in an outer block.

set serveroutput on         
DECLARE                                                           -- outer block
  A exception;
  B exception;
BEGIN
  begin                                                    -- inner block
    raise a;
  exception
    when a then 
       dbms_output.put_line('Raising b from A handler.');         -- exception b raised here is caught in the inner block 
       raise b;
    when b then 
       dbms_output.put_line('exception b caught in inner_block');
   end;
EXCEPTION
 when b then 
       dbms_output.put_line('exception B caught in out_block');
END;
/
Anonymous block completed
Raising b from A handler.
exception B caught in out_block

Exception Categories:
Internally defined:

• (ORA-n errors). automatically raised. Do not have names, unless explicitly given (user). Naming an ORA-n allows that error to be handled on its own WHEN section.Otherwise, you can handle them only with OTHERS exception handlers.

--- i.e. Naming an internal error for explict handling:
DECLARE
 -- (1) declare a name of "type" EXCEPTION.
 deadlock_detected EXCEPTION;
 -- (2) Associate the name with the internally defined error code
 PRAGMA EXCEPTION_INIT(Deadlock_detected, -60);
BEGIN
  ...
EXCEPTION
  WHEN deadlock_detected THEN
    ...
END;

PL/SQL Predefined Exceptions
Exception Name	Error Code
ACCESS_INTO_NULL	-6530
CASE_NOT_FOUND	-6592
COLLECTION_IS_NULL	-6531
CURSOR_ALREADY_OPEN	-6511
DUP_VAL_ON_INDEX	-1
INVALID_CURSOR	-1001
INVALID_NUMBER	-1722
LOGIN_DENIED	-1017
NO_DATA_FOUND	+100
NO_DATA_NEEDED	-6548
NOT_LOGGED_ON	-1012
PROGRAM_ERROR	-6501
ROWTYPE_MISMATCH	-6504
SELF_IS_NULL	-30625
STORAGE_ERROR	-6500
SUBSCRIPT_BEYOND_COUNT	-6533
SUBSCRIPT_OUTSIDE_LIMIT	-6532
SYS_INVALID_ROWID	-1410
TIMEOUT_ON_RESOURCE	-51
TOO_MANY_ROWS	-1422
VALUE_ERROR	-6502
ZERO_DIVIDE	-1476

Predefined Exceptions:

• An internally defined exception that PL/SQL has given a name (22 total). You can write exception handlers specifically for them.
• They are defined in the SYS.STANDARD package

• SQLCODE Function:
• Returns the numeric code of the exception being handled
• For predefined errors: Returns a negative number that maps to the Oracle predefined exceptions. (for NO_DATA_FOUND returns a positive #. (why?))
• For User-defined errors: Returns a positive 1 or the error code associated with the exception by the EXCEPTION_INIT PRAGMA. (a valid number in the range of negative 20001 to negative 20999.)

• SQLERRM Function
• returns the error message associated with an error code
• Returns max 512 bytes, which is the max length of an Oracle error message
• DBMS_UTILTY.FORMAT_ERROR_STACK is recommended over SQLERRM, unless you use the FORALL statement with its SAVE EXCEPTIONS clause.
• DBMS_UTILTY.FORMAT_ERROR_STACK returns the full error stack (up to 2kb)

• DBMS_UTILITY Package
• DBMS_UTILTY.FORMAT_ERROR_STACK is recommended over SQLERRM, unless you use the FORALL statement with its SAVE EXCEPTIONS clause.
• DBMS_UTILTY.FORMAT_ERROR_STACK() returns the full error stack (up to 2kb)
• DBMS_UTILTY.FORMAT_ERROR_BACKTRACE() lists the complete error stack, including the line number that generated the exception.
• See example here

User-defined Exceptions:

• Two-Step process:
• 1. Declaration Section:
  Declare exception_name EXCEPTION;
• 2. Declaration Section:
  Declare a PRAGMA to associate a number to the exception.
• A PRAGMA is a compiler directive. You use a PRAGMA to direct the compiler to perform something differently. PL/SQL supports a number of PRAGMA directives.
• You use the EXCEPTION_INIT directive to map an exception to an error code.
• Executable Section: Explicitly raise exception using: RAISE or RAISE_APPLICATION_ERROR.
• RAISE_APPLICATION_ERROR is defined in the DBMS_STANDARD package.
• can only be invoked from a stored subprogram or method.
• Syntax: RAISE_APPLICATION_ERROR (error_code, message[, {TRUE | FALSE}]);

Raising a user-defined Exception (with RAISE):

create or replace procedure account_status (
  due_date date,
  today    date ) authid definer is
 
  -- declare user-defined exception.
  past_due exception;
begin
  if due_date < today then 
    raise past_due;
  end if;
exception
 WHEN past_due THEN
   dbms_output.put_line('Error: Account past due...');
end;

SQL> set serveroutput on
SQL> begin
account_status('1-JUL-10', '2-AUG-10');
end;
/

anonymous block completed
Error: Account past due...

Declaration Block Errors:

Runtime assignment errors in the declaration block are not captured by the local exception block.

-- dynamic assignments in the declaration section of a block
-- may lead to run-time errors that are not captured by the 
-- local EXCEPTION block. 
set serveroutput on
declare
  emp_id  employees.employee_id%type := '&1';
  vsal    employees.salary%type;
begin
  dbms_output.put_line('Dynamic value captured: '|| emp_id);
  select salary into vsal
  from employees
  where employee_id = emp_id;
  dbms_output.put_line('Emp '|| emp_id || '-- salary: '|| vsal);
exception
 when others then 
   dbms_output.put_line('local exception caught');
end;

-- case 1: Value entered matches the datatype.
--         Procedure executes successfully
Enter value for 1: 100
old   2:   emp_id  employees.employee_id%type := '&1';
new   2:   emp_id  employees.employee_id%type := '100';
Dynamic value captured: 100
Emp 100-- salary: 24000

PL/SQL procedure successfully completed.

-- case 2: Invalid value entered.
-- Exception occurs and is not caught by the local 
-- error handling section.
Enter value for 1: 1G0
old   2:   emp_id  employees.employee_id%type := '&1';
new   2:   emp_id  employees.employee_id%type := '1G0';
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

• IMP: Stored programs (i.e. functions and procedures) behave similarly.
• Procedures require wrapping their calls, but functions do not.
• Thus, for functions called directly from SQL, if a declaration exception occurs, their local EXCEPTION section will not catch and an unhandled exception will result.
• Make dynamic assignments in execution blocks because PL/SQL doesn’t catch dynamic assignment errors in local exception handlers.

Raising a user-defined Exception with RAISE_APPLICATION_ERROR:

-- create a stored procedure to insert new employee
-- the procedure raises an exception if the hire date is 
-- a future date.
create or replace procedure new_emp 
  (empid  employees.employee_id%type,
   lname  employees.last_name%type,
   fname  employees.first_name%type,
   dept   employees.department_id%type,
   jobid  employees.job_id%type,
   sal    employees.salary%type,
   hiredt employees.hire_date%type
   ) authid definer is
begin
  if hiredt > sysdate then 
     raise_application_error(-20000, 'Future hire dates not allowed.');
  else
     insert into employees 
       (employee_id, last_name, first_name, department_id,
        job_id, salary, hire_date)
     values(empid, lname, fname, dept, jobid, sal, hiredt);
  end if;
end new_emp;
/

Stored procedure created.

-- autonomous block invokes the procedure new_emp
-- it defines the INVALID_HIRE_DATE exception
-- and associate an error number with PRAGMA EXCEPTION_INIT
SQL> set serveroutput on
SQL> declare
  invalid_hire_date exception;
  pragma exception_init(invalid_hire_date, -20000);
begin
  new_emp(200, 'Marty', 'Moe', 30, 'SA_REP', 3000, '1-JAN-14');
exception
  when invalid_hire_date then
    dbms_output.put_line(to_char(sqlerrm(-20000)));
end;
/

ORA-20000: Future hire dates not allowed.

PL/SQL procedure successfully completed.
SQL> 

Handling Exceptions: Guidelines

• (1) Use error-checking code and exception handlers.
• error-checking code: check for possible bad input data. (i.e. nulls when they should not be there, more rows than you expected, strings when you should have numbers, etc..)
• (2) Add exception handlers wherever errors can occur. (i.e. arithmetic calculations, string manipulation, and database operations. Also disk storage, memory hardware failure, etc).
• (3) Design your programs to work when the database is not in the state you expect. (i.e. a table might have a column added. don't do select *.... You can also declare variables with %TYPE and %ROWTYPE)
• (4) Use named exceptions instead of using OTHERS exception handlers.
• (5) Have your exception handlers output debugging information. You can use autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION) to commit your debug info).
• (6)For each exception handler, decide whether to commit, rollback or escalate.
• (7)Make sure to leave the db in a consistent state.
• (5)Include WHEN OTHERS THEN to avoid unhandled exceptions.
• (5) Make sure the last statement in the OTHERS handler either RAISE or an invocation of the RAISE_APPLICATION_ERROR procedure. (or you´ll get PLW-06009.)

Exception Stack

• Exception stack: the sequencing of errors from the triggering event to the calling block of
  code.
• When a failure occurs: (a) An exception is raised in the execution block and the the code in the local exception block executed. (b) If the block is nested or a referenced PL/SQL block, it (a.1) runs the local exception handler and then (a.2) runs the calling program unit’s exception handler, and (a.3, a.4, ...) It continues running available exception blocks or returning errors to the error stack until it returns control to the outermost PL/SQL block.
• Beginning in Oracle 10g, you can use an exception block and the DBMS_UTILITY package to get line number and error codes.

Exception handling in Triggers

Exceptions in Bulk Statements (FORALL)