CREATE OR REPLACE PACKAGE tbl_countries_pkg 
AS
 TYPE emsg_array IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
 PROCEDURE  update_countries( 
   message            IN ANYDATA, error_stack_depth  IN NUMBER,
   error_numbers      IN DBMS_UTILITY.NUMBER_ARRAY, error_messages     IN EMSG_ARRAY);
 END tbl_countries_pkg ;
/

CREATE OR REPLACE PACKAGE BODY tbl_countries_pkg AS

 PROCEDURE update_countries( 
  message       IN ANYDATA, error_stack_depth  IN NUMBER,
  error_numbers IN DBMS_UTILITY.NUMBER_ARRAY, error_messages IN EMSG_ARRAY )
  IS

  ad              ANYDATA;
  lcr             SYS.LCR$_ROW_RECORD;
  ret             PLS_INTEGER;
  key             COUNTRIES.COUNTRY%TYPE;
  new_time_stamp  TIMESTAMP;
  db_row          COUNTRIES%ROWTYPE;
  v_update        varchar2(2) := 'Y';
  key_column_missing EXCEPTION;
  PRAGMA EXCEPTION_INIT(key_column_missing, -20101);


 begin
 ----print old and new values of tbl_prder---
 --- util_pkg.print_anydata(message);
 ----print old and new values of tbl_prder---

  -- access the LCR.
  ret := message.getobject(lcr);

/*
  ad := lcr.get_value('new','last_updated','n');
  if( ad is not null) then -- error if null
    new_time_stamp := ad.accesstimestamp();
  else  
    RAISE_APPLICATION_ERROR(-20101, 'missing last_updated value');
  end if;
*/

    ad := lcr.get_value('old','modified_by');

  if( ad is not null) then
    key := ad.accessVarchar2();
    begin
        SELECT * into db_row from COUNTRIES where COUNTRY = key;
        -- error if row not found
        EXCEPTION
        WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors
            RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);
    end;

  if( v_update = 'Y' ) then

    --COUNTRY
    ad := lcr.get_value('old','country');
    if (ad is not null) then
            lcr.set_value('old','country',ANYDATA.ConvertVarChar2(db_row.country));
    end if;    

    --DT_CREATED
    ad := lcr.get_value('old','dt_created');
    if (ad is not null) then
            lcr.set_value('old','dt_created',ANYDATA.ConvertDate(db_row.dt_created));
    end if;    

    --DT_MODIFIED
    ad := lcr.get_value('old','dt_modified');
    if (ad is not null) then
            lcr.set_value('old','dt_modified',ANYDATA.ConvertDate(db_row.dt_modified));
    end if;

    --MODIFIED_BY
    ad := lcr.get_value('old','modified_by');
    if (ad is not null) then
            lcr.set_value('old','modified_by',ANYDATA.ConvertVarchar2(db_row.modified_by));
    end if;

    --DIAL_COUNTRY_CODE
    ad := lcr.get_value('old','dial_country_code');
    if (ad is not null) then
       lcr.set_value('old','dial_country_code',ANYDATA.ConvertVarchar2(db_row.dial_country_code));
    end if;
    
    --SHOW_DISQUALIFIED_INV_YN
    ad := lcr.get_value('old','show_disqualified_inv_yn');
    if (ad is not null) then
            lcr.set_value('old','show_disqualified_inv_yn',ANYDATA.ConvertVarchar2(db_row.show_disqualified_inv_yn));
    end if;

    --CP_COUNTRY_CODE
    ad := lcr.get_value('old','cp_country_code');
    if (ad is not null) then
            lcr.set_value('old','cp_country_code',ANYDATA.ConvertVarchar2(db_row.cp_country_code));
    end if;

    --NLS_SORT
    ad := lcr.get_value('old','nls_sort');
    if (ad is not null) then
            lcr.set_value('old','nls_sort',ANYDATA.ConvertVarchar2(db_row.nls_sort));
    end if;

    --ISO_COUNTRY_CODE
    ad := lcr.get_value('old','iso_country_code');
    if (ad is not null) then
            lcr.set_value('old','iso_country_code',ANYDATA.ConvertVarchar2(db_row.iso_country_code));
    end if;

      lcr.execute(true);
    end if;

  end if;
  EXCEPTION
    WHEN OTHERS THEN  -- catches all error
    RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);
 end update_countries;
end tbl_countries_pkg;
/

EXCEPTION
    WHEN OTHERS THEN  
    RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);

RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);

  RAISE_APPLICATION_ERROR(<-20,000 To -20,999>, SQLERRM);
  Select any number in Between this range Like
  RAISE_APPLICATION_ERROR(-20,000,SQLERRM);

  EXCEPTION
    WHEN OTHERS THEN  -- catches all error
    RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);