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;
/
