System-defined exceptions are defined and maintained implicitly by the Oracle server.
  1. Named exceptionÂ
  2. Un-Named exception
1. Named exception:
====================
  Each exception will have exception number and description. The exception contaings name which is frequently occuring is called Named exception
   declare
    v_salary number;
   begin
    –insert into employees_bkp(employee_id) values (100002);
    v_salary :=10000/0;
   end;
   ORA-01476: divisor is equal to zero
   ORA-01400: cannot insert NULL into (“HR”.”EMPLOYEES_BKP”.”LAST_NAME”)
How to handle named exception:
==============================
   declare
    v_salary number;
   begin
    –insert into employees_bkp(employee_id) values (100002);
    v_salary :=10000/0;
    exception
    when ZERO_DIVIDE then
    dbms_output.put_line(‘divisor is equal to zero, Operation is not allowed’);
    when others then
    dbms_output.put_line(‘Exception happened, Check the code’);
   end;
 — Value error
 declare
    num1 number(2);
   begin
    num1:=34324;
    exception
when value_error THEN
dbms_output.put_line(‘Value error Exception happened, Check the code’);
    when others then
    dbms_output.put_line(‘Exception happened, Check the code’);
   end;
— Invalid cursor error
   declare
    num1 number(2);
    cursor c1 is select salary from employees;
   begin
    –num1:=34324;
    close c1;
    exception
when value_error THEN
dbms_output.put_line(‘Value error Exception happened, Check the code’);
    when invalid_cursor THEN
dbms_output.put_line(‘Invalid cursor error Exception happened, Check the code’);
    when others then
    dbms_output.put_line(‘Exception happened, Check the code’);
   end;
2. Un-named exception:
======================
create table cust (cust_id number(10));
  declare
    v_salary number;
   begin
    insert into cust(cust_id) values (949328432423493889);
    v_salary :=10000/0;
   end;
— un named exception:Â
   declare
    v_salary number;
   begin
    insert into cust(cust_id) values (949328432423493889);
    v_salary :=10000/0;
    exception
    when others then
    dbms_output.put_line(‘Exception happened, Check the code’);
   end;
   — to provide name for unnamed exception – PRAGMA EXCEPTION_INIT
   declare
    v_salary number;
    ex_cust_id_value_limit exception;
    PRAGMA EXCEPTION_INIT (ex_cust_id_value_limit, -01438);
   begin
    insert into cust(cust_id) values (949328432423493889);
    v_salary :=10000/0;
    exception
    when ex_cust_id_value_limit then
    dbms_output.put_line(‘Exception happened, and it has been handled’);
   end;
User defined exception:
=======================
 Unlike System-Define Exception, User-Define Exceptions are raised explicitly in the body of the PL/SQL block (more specifically inside the BEGIN-END section) using the RAISE Statement.
————————————————————
There are three ways of declaring user-define exceptions in Oracle Database.
  1. By declaring a variable of EXCEPTION type in declaration section.
  2. Declare user-defined exception using PRAGMA EXCEPTION_INIT function.
  3. RAISE_APPLICATION_ERROR method.
The syntax for the Named System Exception in a procedure is:
The syntax for the Named System Exception in a function is:
CREATE [OR REPLACE] FUNCTION function_name
  [ (parameter [,parameter]) ]
  RETURN return_datatype
IS | AS
  [declaration_section]
BEGIN
  executable_section
EXCEPTION
  WHEN exception_name1 THEN
   [statements]
  WHEN exception_name2 THEN
   [statements]
  WHEN exception_name_n THEN
   [statements]
  WHEN OTHERS THEN
   [statements]
END [function_name];
—————————————————————-
select * from location;
ORA-00942: table or view does not exist
insert into customer values(1000,’abc’);
ORA-00947: not enough values
create table emp_excep
(
emp_id number(6),
emp_name varchar2(40)
);
alter table emp_excep add primary key (emp_id);
CREATE OR REPLACE PROCEDURE add_new_employee
  (employee_id_in IN NUMBER, first_name_in IN VARCHAR2)
IS
BEGIN
  INSERT INTO emp_excep (emp_id, emp_name )
  VALUES ( employee_id_in, first_name_in );
  commit;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
   raise_application_error (-20001,’You are trying to insert a duplicate emp_id.’);
  WHEN OTHERS THEN
   raise_application_error (-20002,’An error has occurred inserting a employee.’);
END;
—————————————————————————————-
Raising Exceptions
Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE
DECLARE
  exception_name EXCEPTION;
BEGIN
  IF condition THEN
   RAISE exception_name;
  END IF;
EXCEPTION
  WHEN exception_name THEN
  statement;
END;
—————————————————————————————
User-defined Exceptions
The syntax for declaring an exception is:
DECLARE
  user-exception EXCEPTION;
DECLARE
  c_id customers.id%type := &cc_id;
  c_name customers.name%type;
  c_addr customers.address%type;
  — user defined exception
  ex_invalid_id EXCEPTION;
BEGIN
  IF c_id <= 0 THEN
   RAISE ex_invalid_id;
  ELSE
   SELECT name, address INTO c_name, c_addr
   FROM customers
   WHERE id = c_id;
   DBMS_OUTPUT.PUT_LINE (‘Name: ‘|| c_name);
   DBMS_OUTPUT.PUT_LINE (‘Address: ‘ || c_addr);
  END IF;
EXCEPTION
  WHEN ex_invalid_id THEN
   dbms_output.put_line(‘ID must be greater than zero!’);
  WHEN no_data_found THEN
   dbms_output.put_line(‘No such customer!’);
  WHEN others THEN
   dbms_output.put_line(‘Error!’);
END;
DECLARE
 var_dividend NUMBER := 24;
 var_divisor NUMBER := 0;
 var_result NUMBER;
 ex_DivZero EXCEPTION;
BEGIN
 IF var_divisor = 0 THEN
  RAISE ex_DivZero;
 END IF;
 var_result := var_dividend/var_divisor;
 DBMS_OUTPUT.PUT_LINE(‘Result = ‘ ||var_result);
 EXCEPTION WHEN ex_DivZero THEN
   DBMS_OUTPUT.PUT_LINE(‘Error Error – Your Divisor is Zero’);
END;
/
—————————————————————————————-
CREATE OR REPLACE PROCEDURE add_new_employee
  (employee_id_in IN NUMBER, first_name_in IN VARCHAR2)
IS
BEGIN
  INSERT INTO emp (employee_id, first_name )
  VALUES ( employee_id_in, first_name_in );
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
   raise_application_error (-20001,’You have tried to insert a duplicate emp_id’);
  WHEN OTHERS THEN
   raise_application_error (-20002,’An error has occurred inserting a employee.’);
static –> Action when compilation
Dynamic –> action when application running
— Static query –> Early Binding
— Dynamic Query –> Late Binding
Implementation Methods:
———————–
1) Execute Immediate
2) Using a package DBMS_SQL
1. Dynamic DML – Insert/update/delete
2. Dynamic DDL — Drop and create table
3. Dynamic PLSQL block
begin
truncate table test;
end;
create table test1 as select * from customer_details;
select * from test1;
begin
execute immediate ‘truncate table test1’;
end;
create table test1 as select * from customer_details;
select * from test1;
create or replace procedure proc_drop_table(table_name varchar2)
is
begin
execute immediate ‘drop table ‘||table_name;
end;
exec proc_drop_table(‘test1’);
— how to drop multiple tables
begin
for rec in (select table_name from all_tables where upper(table_name) like ‘%BKP%’)
loop
execute immediate ‘drop table ‘||rec.table_name;
end loop;
end;
/
example 2:
———-
create or replace procedure upd_dynamic_customer(p_table varchar2,p_column varchar2,
p_cust_id number)
is
begin
execute immediate ‘update ‘||p_table||’ set ‘||p_column||’ = sysdate where ‘
||’cust_id =’||p_cust_id;
create or replace procedure table_count
is
v_count integer;
begin
for r in (select table_name, owner from all_tables
where owner = ‘HR’)
loop
execute immediate ‘select count(*) from ‘ || r.table_name
into v_count;
INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
VALUES (r.table_name,r.owner,v_count,SYSDATE);
end loop;
end;
————————————————————————–
— Procedure to create table runtime
CREATE OR REPLACE PROCEDURE GENERATE_NEW_TABLE
(TEMP_PRODS varchar2, COLUMNS_DATATYPES varchar2)
is
l_str varchar2(200);
begin
l_str :=
‘CREATE TABLE ‘||TEMP_PRODS||'(‘||COLUMNS_DATATYPES||’)’;
dbms_output.put_line(l_str);
execute immediate l_str;
end;
/
