Raise parent key integrity constraint in procedure for each column

Question

I have the following code in a package which inserts data into table.

I am able to get parent key not found exception. How can I specifically get constraint violated message for each column? E.g. if proj_id is violated then raise exception, if proj_code is violated, then raise another exception.

PROCEDURE add_project(
     p_proj_id              project.proj_id%TYPE,
     p_proj_desc        project.proj_desc%TYPE,
     p_proj_code  project.proj_code%TYPE,
     p_proj_date      project.proj_date%TYPE
     )
      IS
    parent_not_found exception;
    pragma exception_init(parent_not_found, -2291);
     BEGIN
        INSERT
            INTO projects (proj_id,proj_desc,proj_code,proj_date) values
                  (p_proj_id,p_proj_desc,p_proj_code,p_proj_date);
exception
when parent_not_found then
raise_application_error(-20001,'Invalid');
     END;

Answer 1

Take a look at EXCEPTION_INIT Pragma.

DECLARE
    l_parentnotfound    exception;
    l_res               integer;
    PRAGMA EXCEPTION_INIT(l_parentnotfound, -2291);
BEGIN
    -- check if the parent key exists
    select 1 into l_res from codes where code = proj_code;
    -- if not, raise exception
    if l_res <> 1 then
        raise l_parentnotfound;
    end if;
    INSERT INTO projects (proj_id,proj_desc,proj_code,proj_date) values (p_proj_id,p_proj_desc,p_proj_code,p_proj_date);
EXCEPTION WHEN l_parentnotfound THEN
    -- handle the error
END;

Or you can use WHEN OTHERS and look for the SQLCODE.

EDIT:

Note that you are not forced to manually check that the parent key exists to raise the exception, you can stay with :

DECLARE
    l_parentnotfound    exception;
    l_res               integer;
    PRAGMA EXCEPTION_INIT(l_parentnotfound, -2291);
BEGIN
    INSERT INTO projects (proj_id,proj_desc,proj_code,proj_date) values (p_proj_id,p_proj_desc,p_proj_code,p_proj_date);
EXCEPTION WHEN l_parentnotfound THEN
    -- handle the error
END;

But if you want to be able to get violated constraint name easily, it can be useful to raise exception manually for each column. Also you can try to get the constraint name using USER_CONSTRAINTS and USER_CONS_COLUMNS.

Another way to get the violated constraint name is to parse the SQLERRM error message.

Answer 2

try

create PROCEDURE add_project(p_proj_id   project.proj_id%TYPE,
                      p_proj_desc project.proj_desc%TYPE,
                      p_proj_code project.proj_code%TYPE,
                      p_proj_date project.proj_date%TYPE) IS
BEGIN
  INSERT INTO projects
    (proj_id, proj_desc, proj_code, proj_date)
  values
    (p_proj_id, p_proj_desc, p_proj_code, p_proj_date);

  --add this   
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;

END;