PL/SQL add select query inside exception?

Question

Currently I have this PL/SQL function inside a package:

function populate_booking_map(  pi_event_id    in    varchar2
                               ,pi_status      in    number
                               ,pi_booking_id  in    varchar2
                             ) 
    return integer as
    begin

      insert into booking_map (
                             booking_id
                            ,event_id
                            ,currentstatus
                            )
      values(
             pi_booking_id
            ,pi_event_id
            ,pi_status
            );
      return c_success;

      exception 
        when OTHERS then
          log_error_msg( pi_app_name         =>  c_application_name
                        ,pi_error_msg        =>  'Exception in populate booking map.'
                        ,pi_error_details    =>  sqlerrm
                       );

        return c_failure;
end populate_booking_map;  

I want to add catching another exception - DUP_VAL_ON_INDEX - before OTHERS to handle exception when the attempt-to-insert record already exists in table.

Inside the handler, I want to check the existing values are same as the provided, i.e.: pi_booking_id=booking_id and pi_event_id=event_id and pi_status=currentstatus .

If they are completely the same, return c_success;.

If they are not the same, return c_failure;

How should I do that? Can I a SELECT query inside the exception handling? I have never seen anybody doing that.

Answer 1

You can have a whole new PL/SQL block in exception handling section (i.e. not just the SELECT statement).

For example:

CREATE FUNCTION populate_booking_map (parameters here)
   RETURN INTEGER
AS
   l_dummy  VARCHAR2 (1);
BEGIN
   NULL;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      SELECT dummy INTO l_dummy FROM DUAL;     --> but what if this returns an error?

      RETURN c_success;
   WHEN OTHERS
   THEN
      RETURN c_failure;
END;

---

CREATE FUNCTION populate_booking_map (parameters here)
   RETURN INTEGER
AS
   l_dummy  VARCHAR2 (1);
BEGIN
   NULL;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      BEGIN                                     --> new PL/SQL block
         SELECT dummy INTO l_dummy FROM DUAL;
      EXCEPTION                                 --> with its own EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            RETURN c_failure;
      END;                                      --> end of that block

      RETURN c_success;
   WHEN OTHERS
   THEN
      RETURN c_failure;
END;

---

However: if you don't want to take care about handling DUP_VAL_ON_INDEX, write INSERT so that it does nothing if values (passed as parameters) already exist in the table:

CREATE FUNCTION populate_booking_map (parameters here)
   RETURN INTEGER
IS
BEGIN
   INSERT INTO booking_map (booking_id, event_id, currentstatus)
      SELECT pi_booking_id, pi_event_id, pi_status
        FROM DUAL
       WHERE NOT EXISTS                        --> this will prevent duplicates
                (SELECT NULL                   --> to be inserted
                   FROM booking_map
                  WHERE     booking_id = pi_booking_id
                        AND event_id = pi_event_id
                        AND status = pi_status);
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN c_failure;
END;