CREATE TABLE z_test(id VARCHAR2(5), val VARCHAR2(5))
/
--v forme block1.item1 is z_test.id; block1.item2 is z_test.val
begin
    insert into z_test(id, val)
    select :block1.item1, :block1.item2
    from dual
    where not exists(select 1 from z_test where val = :block1.item2);
        
    if(sql%rowcount = 0)then
        --record is found
        --error;
        null;
    end if;

end;

type r_block is record(
  id 		number, 
  field_1 	varchar2(100)
);
type t_block is table of r_block index by pls_integer;

procedure slc_block(p_t in out nocopy t_block, p_id number);  
procedure upd_block(p_t in out nocopy t_block);  
procedure ins_block(p_t in out nocopy t_block);  

REATE OR REPLACE 
PACKAGE zt_form
IS
type r_block is record(
  id 		number, 
  field_1 	varchar2(100)
);
type t_block is table of r_block index by pls_integer;

procedure slc_block(p_t in out nocopy t_block, p_id number); 
procedure upd_block(p_t in out nocopy t_block);  

END; -- Package spec
/


CREATE OR REPLACE 
PACKAGE BODY zt_form
IS
FIELD_DUPLICATE       EXCEPTION; PRAGMA EXCEPTION_INIT(FIELD_DUPLICATE, -20003);

procedure slc_block(p_t in out nocopy t_block, p_id number)
is
begin
    select null, null
    into p_t(1).id, p_t(1).field_1
    from dual;
end;

procedure upd_block(p_t in out nocopy t_block)
is
begin
    insert into z_test(id, val)
    select p_t(1).id, p_t(1).field_1
    from dual
    where not exists(select 1 from z_test where val = p_t(1).field_1);
        
    if(sql%rowcount = 0)then
        raise FIELD_DUPLICATE;
    end if;
end;

   -- Enter further code below as specified in the Package spec.
END;
/

DECLARE 
  errnum NUMBER        := ERROR_CODE; 
  errtxt VARCHAR2(100) := substr(ERROR_TEXT,1,100); 
  errtyp VARCHAR2(3)   := ERROR_TYPE; 
  db_err varchar2(80);
BEGIN 
		db_err:=substr(errtxt,instr(errtxt,'ORA-',-1,1)+4);
		if db_err between 20000 and 20999 then
	  	if db_err = 20003 then
	  		Message('RECORD DUPLICATE!!!', ACKNOWLEDGE); 
	  	end if;			
		end if;
end;