Q&As: Parallelism

(Q) What types of statements and tasks can be improved through parallel execution?

• Queries requiring: large table scans, joins, or partitioned index scans
• Creation of large indexes
• Creation of large tables (and Materialized Views)
• Bulk Inserts, Updates, Meges and Deletes
• Scanning large objects (LOBs)

(Q) What are some of the characteristics of systems that can benefit from parallel execution?

• Have SMPs, clusters, or MPPs
• Sufficient IO bandwidth
• Underutilized CPUs (<30%)
• Enough memory free

(Q) When can OLTP systems mostly benefit from parallel execution?

• OLTP systems may benefit during batch processing and schema maintenance operations.

(Q) What are three key parameters controlling automatic parallel execution?

• PARALLEL_DEGREE_LIMIT
• PARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO } <== MANUAL => disabled, AUTO => Auto DOP on
• PARALLEL_MIN_TIME_THRESHOLD = AUTO (default, about 10sec)

(Q) What is Intra-parallelism and Inter-parallelism?

• Intra-operation parallelism: parallelism of an individal operation
• Inter-operation parallelism: parallelism between operations in a data flow tree (in an execution plan)

(Q) What is the default degree of parallelism assumed by an Oracle Server?

• By Default, PARALLEL_DEGREE_POLICY = Manual (NO PARALLELISM)
• By default, the system only uses parallel execution when a parallel degree has been explicitly set on an object or if a parallel hint is specified in the SQL statement
• If PARALLEL_DEGREE_POLICY = AUTO, then
• Single Instance: DOP = PARALLEL_THREADS_PER_CPU X CPU_COUNT
• RAC: DOP = PARALLEL_THREADS_PER_CPU X CPU_COUNT x INSTANCE_COUNT
• important: in a multiuser environment, default parallelism is not recommended

(Q) What is the function of the PARALLEL_DEGREE_POLICY parameter? What vaules it takes?

• PARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO }
• Enable/Disable (a) automatic degree of parallelism, (b) statement queueing, and (c) in-memory parallel execution
• MANUAL => Default. Revert to behavior prior to 11g. No parallelism automatically enabled
• LIMITED => Enables automatic degree of parallelism for some stmts only.
• AUTO => all three enabled.

(Q) How does the optimizer determines the degree of parallelism for a statement?

• Based on the resource requirements of the statement.
• Limit on parallelization is set by
• (a) PARALLEL_DEGREE_LIMIT (default = PARALLEL_THREADS_PER_CPU * CPU_COUNT * num instances)
• (b) PARALLEL_MIN_TIME_THRESHOLD (default = 10sec
  

(Q) Which parameters affect the automatic degree of parallelism adopted in the system?

• PARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO }
• PARALLEL_DEGREE_LIMIT
• PARALLEL_MIN_TIME_THRESHOLD

==> The default degree of parallelism is MANUAL

SQL> show parameter parallel

NAME                           TYPE        VALUE
------------------------------ ----------- -------------
..
parallel_degree_limit           string      CPU
parallel_degree_policy      string      MANUAL    
...
parallel_min_time_threshold    string      AUTO
...

SQL> alter system set parallel_degree_policy=auto;
System altered.

SQL> conn sh/sh
Connected.

SQL> select degree from user_tables where table_name ='SALES';

DEGREE
----------
  1

SQL> alter table sales parallel 4;
Table altered.

SQL> select degree from user_tables where table_name ='SALES';

DEGREE
----------
  4

SQL> explain plan for select * from sales;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 1550251865

---------------------------------------------------------------------------------------------
| Id  | Operation     | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       | 918K|  25M| 526   (9)| 00:00:07 |     |     |
|   1 |  PARTITION RANGE ALL|       | 918K|  25M| 526   (9)| 00:00:07 |   1 |  28 |
|   2 |   TABLE ACCESS FULL | SALES | 918K|  25M| 526   (9)| 00:00:07 |   1 |  28 |
---------------------------------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
13 rows selected.

==> Note here that no parallel execution took plase. 
    The estimated serial execution time (7sec) is still below the 10sec threshold used when the parallel_min_time_threshold is set to AUTO.  


==> Now, lets change the threshold to 1sec:

SQL> conn / as sysdba
Connected.
SQL> show parameter parallel

NAME                           TYPE        VALUE
------------------------------ ----------- -------------
..
parallel_degree_limit           string      CPU
parallel_degree_policy      string      MANUAL    
...
parallel_min_time_threshold    string      AUTO
...

SQL> alter system set parallel_min_time_threshold=1;
System altered.

SQL> show parameter parallel_min
NAME                             TYPE        VALUE
-------------------------------- ----------- -------
parallel_min_time_threshold      string      1

SQL> Conn sh/sh
Connected
SQL> explain plan for select * from sales;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3060979429

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   918K|    25M|   291   (9)| 00:00:04 |       |       |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |   918K|    25M|   291   (9)| 00:00:04 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |   918K|    25M|   291   (9)| 00:00:04 |     1 |    28 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| SALES    |   918K|    25M|   291   (9)| 00:00:04 |     1 |    28 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
15 rows selected.

(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

(15) Dynamic SQL

[ Oracle PL/SQL ]

"From all old seamy throats of elders, musty books, I've salvaged not a word." (Cormac McCarthy)

Dynamic SQL statements are not embedded in your source program and checked at compile time.
Instead, they are stored in character strings that are input to, or built by, the program at runtime.
(i.e. dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.)

Programming methodology for generating and running SQL statements at run time. Good for:

• Writing general-purpose and flexible programs like ad hoc query systems
• Writing programs that must run DDL statements
• When number and datatypes of IN/OUT variables are unknown at compilation time
• When the full text of a SQL stmt needs to be obtained at run-time only.
• i.e. a SELECT stmt that includes an identifier (a table name) that is unknown at compile time.
• i.e. a WHERE clause in which the number of subclauses is unknown at compile time.

How to write dynamic SQL ? (two ways)

• (1) Native dynamic SQL: PL/SQL language feature for building and running dynamic SQL statements
• (2) DBMS_SQL package: , an API for building, running, and describing dynamic SQL statements

Native dynamic SQL x DBMS_SQL package:

• native dynamic SQL: Faster, and more readable (even more when optimized by compiler)
• Native dynamic SQL: requires complete specification of IN/OUT vars (number and datatype) at compile time.
• Switch between native dynamic SQL and DBMS_SQL package using
  DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER.

Using EXECUTE IMMEDIATE (Advantages over DBMS_SQL)

• DBMS_SQL: Oracle 8 and earlier
• EXECUTE IMMEDIATE: Oracle 8i and later
• Native dynamic SQL:
• Integral part of the SQL language that is built into Pl/SQL, thus making it simpler to use and faster than the DBMS_SQL package.
• Native dynamic SQL also supports the usage of object types and collections not supported by the DBMS_SQL package
• Faster than DBMS_SQL because it parses, binds, and executes the statements immediately.
• DBMS_SQL requires calls to procedures and functions.

Question: Can I use TCL or DDL within a PL/SQL Block? A: No. You HAVE to use DYNAMIC SQL for that. (Native Dyn SQL or DBMS_SQL). Static SQL is limited to DML (SELECT, INSERT, UPDATE, DELETE, MERGE), TCL (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION) and LOCK TABLE.

Native Dynamic SQL: EXECUTE IMMEDIATE

• EXECUTE IMMEDIATE: Builds and runs a dynamic SQL statement in a single operation.
• If dynamic_sql_stmt has a RETURNING INTO clause, using_clause can contain only IN bind arguments. The bind arguments in the RETURNING INTO clause are OUT bind arguments by definition.
• When using dynamic SQL, beware of SQL injection, a security risk.
• The value of bind_argument cannot be TRUE, FALSE, or NULL

Type of dynamic SQL statement	USE of EXECUTE IMMEDIATE
SELECT returns at most one row	OUT-bind arguments (defines): in the INTO clause. IN-bind arguments: in the USING clause. EXECUTE IMMEDIATE SELECT ... INTO <OUT-vars> USING <bind_arg>;
SELECT returns multiple rows	OUT-bind arguments: in the BULK COLLECT INTO clause. IN-bind arguments: in the USING clause. EXECUTE IMMEDIATE SELECT ... BULK COLLECT INTO <OUT-vars> USING <bind_arg>;
DML with RETURNING INTO clause	IN-bind arguments: in the USING clause. OUT-bind arguments: in the RETURNING INTO clause.
DMLw/o RETURNING INTO clause	all bind arguments in the USING clause

Using dynamic SQL with EXECUTE IMMEDIATE:

-- create procedure to insert new record on departments table
create or replace procedure new_dept
 (deptid in out number,
  dname  in varchar2,
  mgrid  in varchar2,
  locid  in varchar2) as 
begin 
 deptid:= departments_seq.nextval;
 insert into departments (
    department_id,
    department_name,
    manager_id,
    location_id)
  values (deptid, dname, mgrid, locid);
end;

-- Use dynamic SQL to call the procedure new_dept
set serveroutput on
declare
 plsql_block varchar2(500);
 new_deptid  number(4);
 new_dname   varchar2(30)    := 'Advertising';
 new_mgrid   number(6)       := 200;
 new_locid   number(4)       := 1700;
 numdep      number;
begin
 select count(*) into numdep  from departments;
 dbms_output.put_line('# depts: ' || numdep);

 -- enter the dynamic SQL statement
 -- note that the statement is complete. Only dynamic binding of the 
 -- procedure parameters occur.
 -- note that the SQL stmt has placeholders for bind arguments.
 plsql_block := 'begin new_dept(:a, :b, :c, :d); end;';

 -- use EXECUTE IMMEDIATE to run the SQL stmt and specify the 
 -- binding variables.
 EXECUTE IMMEDIATE plsql_block
   USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
 select count(*) into numdep
 from departments;
 dbms_output.put_line('# depts: ' || numdep);
end;

anonymous block completed
# depts: 27
# depts: 28

Using SELECT, DDL and DML with Execute Immediate Using Conditional Compilation

What the example below does (3 procedures):
(a) Check whether a table exists in the database (SELECT)
(b) DROP and Recreate or CREATE table test and GRANT access on the table to another user (DDL)
(c) Insert data on the created table (DML)


(1) Procedure check_object:

• (SELECT returns one row only): Check whether a table named TESTE already exists in the database
• procedure call: check_object('TESTE', 'TABLE');

CREATE OR REPLACE Procedure check_object (p_objname in varchar2, p_objtype in varchar2, 
                        p_created OUT boolean) IS
 query varchar2(200);
 vcount varchar2(40);
BEGIN
  -- using SELECT with EXECUTE IMMEDIATE
  query := 'select count(*) from user_objects '||
          'where object_type = :b '|| 
          'and object_name = :c ';
  -- If the dynamic SQL statement is a SELECT statement that can return 
  -- at most one row, put out-bind arguments (defines) in the INTO clause 
  -- and in-bind arguments in the USING clause.
  EXECUTE IMMEDIATE query into vcount  USING IN p_objtype, p_objname;
  IF vcount = 0 THEN
    $IF $$my_debug $THEN
       dbms_output.put_line(p_objtype || ' ' || p_objname || ' does not yet exist on the database.');
    $END
    p_created := FALSE;
  ELSE
    $IF $$my_debug $THEN
       dbms_output.put_line(p_objtype || ' ' || p_objname || ' already exist on the database.');
    $END
    p_created := TRUE;
  END IF; 
EXCEPTION
 When others then 
   dbms_output.put_line('Error Procedure check_object');
   dbms_output.put_line(sqlerrm);
   dbms_output.put_line(dbms_utility.format_error_backtrace());
END;

alter session set plsql_ccflags = 'my_debug:TRUE';
set serveroutput on
declare
 v_created boolean;
begin
check_object('TESTE', 'TABLE',v_created);
end;
/

Anonymous block completed
TABLE TESTE already exist on the database.

(2) Procedure create_table:

• (DDL): If table exists, drop table and recreate it.
• (DDL): Grant SELECT on table TESTE to userA.
• procedure call: cr_tbl('TRUE', 'create table TESTE (name varchar2(20), salary number)';');
• For the procedure to succeed, the procedure owner MUST have been granted the CREATE TABLE privilege directly, not via a role.
• Role privileges are not applied in procedures

CREATE OR REPLACE procedure create_table (p_created in boolean, p_stmt in varchar2) IS
 e_leave_drop exception;
 e_leave_create exception;
 stmt varchar2(200);
 v_created boolean;
BEGIN
-- USING DDL with EXECUTE IMMEDIATE  
  IF p_created then
    -- table exist. Drop before creating.
    stmt := 'drop table teste';
    begin
      execute immediate stmt;
    exception 
      when others then 
        dbms_output.put_line('Error dropping table');
        dbms_output.put_line(sqlerrm);
        raise e_leave_drop;
    end;
    dbms_output.put_line('Table dropped.');
  END IF;
  stmt := p_stmt; 
  begin
    execute immediate stmt;
  exception 
    when others then 
      dbms_output.put_line('Error creating table');
      dbms_output.put_line(sqlerrm);
      raise e_leave_create;
  end;
  dbms_output.put_line('Table teste created.'); 
  v_created := TRUE;
  dbms_output.put_line('Calling check_object..'); 
  check_object('TESTE', 'TABLE', v_created);

  stmt :=  'grant select on teste to userb';
  execute immediate stmt;
  dbms_output.put_line('grant select completed');
EXCEPTION
 when e_leave_drop then 
  dbms_output.put_line('leaving procedure...');
 when e_leave_create then 
  dbms_output.put_line('leaving procedure...');
END;

alter procedure check_object compile;
alter session set PLSQL_CCFLAGS = 'my_debug:TRUE';
set serveroutput on
declare
stmt varchar2(200);
vcreated boolean default TRUE;
begin
 stmt := 'create table UserA.teste (name varchar2(20), salary number)';
 create_table(vcreated, stmt);
end;
/

Anonymous block completed
Table dropped.
Table teste created.
Calling check_object..
TABLE TESTE already exist on the database.
grant select completed

(3) Procedure insert_tbl:

• (DML): Insert values on table TESTE
• If the dynamic SQL statement is a DML statement WITHOUT a RETURNING INTO clause, other than SELECT, put all bind arguments in the USING clause.

CREATE OR REPLACE procedure insert_tbl (p_name in varchar2, n in integer, 
                                        p_salinit number) as
 vsal number;
 stmt varchar2(200);
 vname varchar2(20);
BEGIN
  stmt := 'INSERT into TESTE values (:b, :c)';
  vsal := p_salinit;
  vname := p_name;
  for i in 1..n loop
    vname := vname || to_char(i);
    vsal := 10000 + i;
    execute immediate stmt USING vname, vsal;
    dbms_output.put_line('Inserted row '|| i);
  end loop;
EXCEPTION
 when others then 
   dbms_output.put_line('Insert error...');
   dbms_output.put_line(sqlerrm);
END;

set serveroutput on
begin
insert_tbl('Mary', 4, 5000);
end;
/

anonymous block completed
Inserted row 1
Inserted row 2
Inserted row 3
Inserted row 4

SQL> Select * from teste;

NAME                 SALARY                 
-------------------- ---------------------- 
Mary1                10001                  
Mary12               10002                  
Mary123              10003                  
Mary1234             10004                  

What the example below does:
(a) Check the columns of a given table in the data dictionary (Mulitple-row SELECT: BULK COLLECT INTO)
(b) Delete a row from the table. Returns data from the deleted row using RETURNIG clause (DML)

(1) Procedure list_tbl

• (SELECT): Get column name list with BULK COLLECT INTO.
• If the dynamic SQL statement is a SELECT statement that can return MULTIPLE rows,
  put out-bind arguments (defines) in the BULK COLLECT INTO clause and in-bind arguments in the USING clause.
• You CANNOT use bind values to pass in the names of schema objects, such as table names or column names.
• SO, names of schema objects have to be added to the dynamic sql statement, instead of passed as bind vars.

CREATE OR REPLACE procedure list_tbl (p_tblname in varchar2) as 
 type t_recdata is RECORD (nm varchar2(20), salary number);
 type t_aadata  is TABLE of t_recdata index by pls_integer;
 vdata t_aadata;
 type t_colnm is table of varchar2(20) index by pls_integer;
 vcolnm t_colnm;
 vb varchar2(30);
 stmt varchar2(200);
BEGIN
  stmt := 'SELECT column_name FROM user_tab_columns '||
          'WHERE table_name = :b';
  vb := p_tblname;
  EXECUTE IMMEDIATE stmt BULK COLLECT INTO vcolnm USING p_tblname;
   for i IN vcolnm.first..vcolnm.last loop
     if i = 1 then 
        vb := vcolnm(i);
     else
       vb := vb || ', '|| vcolnm(i); 
     end if;       
   end loop;
   dbms_output.put_line('columns are: '|| vb);  
    -- You CANNOT use bind values to pass in the names of schema objects, 
    -- such as table names or column names.
    -- SO, names of schema objects have to be added to the dynamic sql 
    -- statement, instead of passed as bind vars.
   stmt := 'select '|| vb || ' from teste'; 
   BEGIN
     EXECUTE IMMEDIATE stmt BULK COLLECT INTO vdata;
   EXCEPTION
     when others then
       dbms_output.put_line('Execute immediate: error SELECT');          
       dbms_output.put_line(sqlerrm);
  END;
  
  for i IN vcolnm.first..vcolnm.last loop
     dbms_output.put(vcolnm(i) || ' -- ');
  end loop;
  dbms_output.new_line();
  for i in vdata.first..vdata.last loop
     dbms_output.put_line(vdata(i).nm || ' '|| vdata(i).salary);  
  end loop;        
END list_tbl;

set serveroutput on
begin
list_tbl('TESTE');
end;
/

columns are: NAME, SALARY
NAME -- SALARY -- 
Ximenes12 10002
Ximenes123 10003

(2) Procedure delete_table:

• procedure call: delete_table('Mary');
• If the dynamic SQL statement is a DML statement with a RETURNING INTO clause,
  put in-bind arguments in the USING clause and out-bind arguments in the RETURNING INTO clause.

set serveroutput on
CREATE OR REPLACE procedure del_tbl(p_name in varchar2) as 
 type t_recdata is RECORD (nm varchar2(20), salary number);
 vrec t_recdata;
 stmt varchar2(200); 
BEGIN
  stmt := 'DELETE FROM teste '||
          'WHERE name = :b ' || 
          'RETURNING name, salary INTO :c, :d';
  execute immediate stmt USING IN p_name returning into vrec;
  dbms_output.put_line('Deleted rec: ' || vrec.nm || ' ' || vrec.salary);
  dbms_output.new_line;
  
  dbms_output.put_line('Listing table...');
  list_tbl('TESTE');
EXCEPTION
    when others then
       dbms_output.put_line(sqlerrm);
        dbms_output.put_line(dbms_utility.format_error_backtrace());
END;

set serveroutput on
begin
  del_tbl('Mary123');
end;
/

anonymous block completed
Deleted rec: Mary123 10003
Listing table...
columns are: NAME, SALARY
NAME -- SALARY -- 
Mary1 10001
Mary12 10002
Mary1234 10004

Running all together:

set serveroutput on
declare
  vcreated boolean default TRUE;
begin
  check_object('TESTE', 'TABLE', vcreated);
  create_table(vcreated, 'create table teste (name varchar2(20), salary number)');
  insert_table('Ximenes', 3, 9700);
  list_tbl('TESTE');
  delete_table('Ximenes1');
end;

nonymous block completed
TABLE TESTE already exist on the database.
Table dropped.
Table teste created.
Calling check_object..
TABLE TESTE already exist on the database.
grant select completed
Inserted row 1
Inserted row 2
Inserted row 3
columns are: NAME, SALARY
NAME -- SALARY -- 
Ximenes1 10001
Ximenes12 10002
Ximenes123 10003
Deleted rec: Ximenes1 10001

Listing table...
columns are: NAME, SALARY
NAME -- SALARY -- 
Ximenes12 10002
Ximenes123 10003

Native Dynamic SQL: OPEN FOR, FETCH and CLOSE

DBMS_SQL Package

Other examples using DBMS_SQL

SQL Injection

(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)

(ref) System Event Triggers

[ Oracle PL/SQL ]

System Event Triggers

About:

• Created on either a schema or the database
• A SCHEMA trigger is created on a schema and fires whenever the user who owns it is the current user and initiates the triggering event.
• A DATABASE trigger is created on the database and fires whenever any database user initiates the triggering event.

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} database_event ON {database | schema}
[DECLARE]
declaration_statements;
BEGIN
execution_statements;
[EXCEPTION ...]
END [trigger_name];
/

Valid events are listed below.

• DDL Events: ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DISASSOCIATE STATISTICS, DROP, GRANT, NOAUDIT, RENAME, REVOKE, TRUNCATE, DDL
• Database Events: AFTER STARTUP, BEFORE SHUTDOWN, AFTER DB_ROLE_CHANGE, AFTER SERVERERROR, AFTER LOGON, BEFORE LOGOFF, AFTER SUSPEND.

Trigger on Schema
- The trigger fires to stop any drop of DB objects belonging to the HR schema. CREATE OR REPLACE TRIGGER stop_drop_tg BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR ( num => -20000, msg => 'Cannot drop object'); END; /

Trigger on Database

- When user connects, the trigger runs the connecting procedure in the user_connecting package. - Before logoff the procedure disconnecting is executed. CREATE OR REPLACE TRIGGER connect_tg AFTER LOGON ON DATABASE BEGIN user_connection.connecting(sys.login_user); END; / CREATE OR REPLACE TRIGGER disconnect_tg BEFORE LOGOFF ON DATABASE BEGIN user_connection.disconnecting(sys.login_user); END; / Providing Fine-Grained Access Control As suggested in the examples above, you can also use LOGON triggers to run a package associated with an application context. An application context captures session-related information about the user who is logging in to the database. From there, your application can control how much access this user has, based on his or her session information. Note: If you have very specific logon requirements, such as preventing users from logging in from outside the firewall or after work hours, consider using Oracle Database Vault instead of LOGON triggers. With Oracle Database Vault, you can create custom rules to strictly control user access.

(21) Object Dependencies

[ Oracle PL/SQL ]

• If you alter the definition of a referenced object, dependent objects might not continue to function without error, depending on the type of alteration. For example, if you drop a table, no view based on the dropped table is usable.

[ALL_ | DBA_ | USER_]DEPENDENCIES: describe dependencies among db objects

SQL> create or replace view highsal as
 select * from employees
 where salary >=100000;
 
SQL> create or replace view commissioned as
  select first_name, last_name, commission_pct
  from employees
  where commission_pct > 0.00;

SQL> select object_name, status 
from user_objects
where object_type = 'VIEW'
order by object_name;

OBJECT_NAME      STATUS
---------------- -------
COMMISSIONED     VALID
HIGHSAL          VALID

SQL> alter table employees modify email varchar2(100);

SQL> select object_name, status 
from user_objects
where object_type = 'VIEW'
order by object_name;


OBJECT_NAME      STATUS
---------------- -------
COMMISSIONED     VALID
HIGHSAL          INVALID


SQL> select name, type, referenced_name, referenced_type, dependency_type
from user_dependencies
order by name;

NAME            TYPE          REFERENCED_NAME  REFERENCED_TYPE  DEPENDENCY_TYPE
-----------     ------------  ---------------  --------------   -------------
CIRCLE_AREA     PROCEDURE     DBMS_OUTPUT      SYNONYM          HARD
CIRCLE_AREA     PROCEDURE     MY_PKG           PACKAGE          HARD
...
COMMISSIONED    VIEW          EMPLOYEES        TABLE            HARD
HIGHSAL         VIEW          EMPLOYEES        TABLE            HARD
...
MY_PKG          PACKAGE BODY  MY_PKG           PACKAGE          HAD
MY_PKG          PACKAGE BODY  DBMS_DB_VERSION  PACKAGE          HARD
...
MY_PKG          PACKAGE       DBMS_DB_VERSION  PACKAGE          HARD

• Cascading invalidation: Indirect dependents can be invalidated by changes to the reference object that do not affect them.
• If a change to C invalidates B, it invalidates A (and all other direct and indirect dependents of B).
• Coarse-grained invalidation: DDL statement that changes a referenced object invalidates all of its dependents.

On 11g R1: Fine-grained invalidation

• Fine-grained invalidation: DDL statement that changes a referenced object invalidates only dependents for which:
1. Dependent relies on the attribute of the referenced object that the DDL statement changed.
2. The compiled metadata of the dependent is no longer correct for the changed referenced object.
3. For example, if view v selects columns c1 and c2 from table t, a DDL statement that changes only column c3 of t does not invalidate v.

Minimize object invalidation

(a) Add Items to End of Package
This preserves the entry point numbers of existing top-level package items, preventing their invalidation.

CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
END;

-- Adding an item to the end of pkg1, as follows, 
-- does not invalidate dependents that reference the get_var function:
CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
  PROCEDURE set_var (v VARCHAR2);
END;

-- Inserting an item between the get_var function and the set_var procedure
-- invalidates dependents that reference the set_var function:
CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
  PROCEDURE assert_var (v VARCHAR2);
  PROCEDURE set_var (v VARCHAR2);
END;

(b) Reference Each Table Through a View
Reference tables indirectly, using views, enabling you to:

• Add columns to the table without invalidating dependent views or dependent PL/SQL objects.
• Modify or delete columns not referenced by the view without invalidating dependent objects
• The statement CREATE OR REPLACE VIEW does not invalidate an existing view or its dependents if the new ROWTYPE matches the old ROWTYPE.

Revalidation

To recompile a schema object using the appropriate SQL statement with the COMPILE clause.

• ALTER PACKAGE pkg1 COMPILE REUSE SETTINGS;

Requisites to use UTL_RECOMP package:

• This package must be run using SQL*PLUS.
• You must be connected AS SYSDBA to run this script.
• The following packages should have VALID status:
• STANDARD (standard.sql)
• DBMS_STANDARD (dbmsstdx.sql)
• DBMS_JOB (dbmsjob.sql)
• DBMS_RANDOM (dbmsrand.sql)
• No other DDL must be running on the database, or deadlock may result.
• This package uses the job queue for parallel recompilation.

You can use UTL_RECOMP package to recompile one or more invalid objects:

• This is useful after an application upgrade or patch, when is good practice to revalidate invalid objects to avoid application latencies that result from on-demand object revalidation.

-- Recompile all objects sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL();

-- Recompile objects in schema SCOTT sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL('SCOTT');

-- Recompile all objects using 4 parallel threads:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4);

-- Recompile objects in schema JOE using the number of threads specified in the parameter JOB_QUEUE_PROCESSES:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(NULL, 'JOE');


Revalidate individual invalid objects using DBMS_UTILITY.VALIDATE.
-- Revalidate the procedure UPDATE_SALARY in schema HR:
begin
dbms_utility.validate('HR', 'UPDATE_SALARY', namespace=>1);
end;

-- Revalidate the package body HR.ACCT_MGMT:
begin
dbms_utility.validate('HR', 'ACCT_MGMT', namespace=>2);
end;