Wednesday, 12 November 2014

PRAGMA in oracle

PRAGMA: 

To create exceptions with standard oracle errors by using the PRAGMA EXCEPTION_INIT function are called non- predefined exceptions.

PRAGMA( also called as pseudoinstructions) is the keyword that signifies that the statement is a compiler directive, which is not processed when the pl/sql block is executed.Rather it directs the pl/sql compiler to interpret all occurences of the exception name within the block as the associated oracle server error number.

SYNTAX:

declare 
exception EXCEPTION;

PRAGMA EXCEPTION_INIT(exception,error_number);
error_number is the standard oracle server error number

Non pre defined error i.e user defined error

To trap oracle server error number -01400
("Cannot insert null");

set serveroutput on
declare
insert_excep exception;
PRAGMA EXCEPTION_INIT
(insert_excep,-01400);
begin
insert into departments
(department_id,department_name) values (280,null);
EXCEPTION
WHEN insert_excep THEN
DBMS_OUTPUT.PUT_LINE('Insert Operation Failed');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

where department_name is not null column.

No comments:

Post a Comment