Display error message when row is locked PL/SQL

Question

So I have this assignment where I have to make a procedure that updates a table, and when a certain criteria is not met, it displays an error message. The only problem that I face is setting the error message when a certain row is locked and can't be updated. I know I have to use PRAGMA and display some error message Resource locked/busy 54 but I don't know how to use it in the code. Here is what I've done so far:

CREATE OR REPLACE PROCEDURE upd_jobsal(
par_job_id jobs.job_id%TYPE, 
par_min_sal jobs.min_salary%TYPE, 
par_max_sal jobs.max_salary%TYPE) IS
v_job_id jobs.job_id%TYPE;
invalid_sal EXCEPTION;                        
BEGIN
IF par_min_sal > par_max_sal THEN
    RAISE invalid_sal;
END IF;  
SELECT jobs.job_id 
INTO v_job_id 
FROM jobs 
WHERE jobs.job_id = par_job_id;                                             
UPDATE jobs SET
    jobs.min_salary = par_min_sal,
     jobs.max_salary = par_max_sal
     WHERE jobs.job_id = v_job_id;
EXCEPTION
    WHEN invalid_sal THEN
           DBMS_OUTPUT.PUT_LINE('The maximum salary is less than the minimum salary.');
     WHEN NO_DATA_FOUND THEN            
            DBMS_OUTPUT.PUT_LINE('That job id does not exist.'); 
END;

Any help will be greatly appreciated. Thank you in advance for your time.

Answer 1

Oracle Setup:

CREATE TABLE jobs (
  job_id NUMBER(5,0) CONSTRAINT jobs__ji__pk PRIMARY KEY,
  min_salary NUMBER(9,2),
  max_salary NUMBER(9,2)
);

INSERT INTO jobs VALUES ( 1, 100, 200 );

COMMIT;

CREATE OR REPLACE PROCEDURE upd_jobsal(
  par_job_id jobs.job_id%TYPE, 
  par_min_sal jobs.min_salary%TYPE, 
  par_max_sal jobs.max_salary%TYPE
)
IS
  v_job_id jobs.job_id%TYPE;
  invalid_sal EXCEPTION;   
  row_locked  EXCEPTION; PRAGMA EXCEPTION_INIT ( row_locked, -54 );
BEGIN
  IF par_min_sal > par_max_sal THEN
      RAISE invalid_sal;
  END IF;  

  SELECT  job_id
  INTO    v_job_id
  FROM    jobs
  WHERE   job_id = par_job_id
  FOR UPDATE OF min_salary, max_salary NOWAIT;

  UPDATE jobs
  SET    min_salary = par_min_sal,
         max_salary = par_max_sal
  WHERE  job_id     = par_job_id;

EXCEPTION
  WHEN invalid_sal THEN
    DBMS_OUTPUT.PUT_LINE('The maximum salary is less than the minimum salary.');
  WHEN NO_DATA_FOUND THEN            
    DBMS_OUTPUT.PUT_LINE('That job id does not exist.'); 
  WHEN  row_locked THEN
    DBMS_OUTPUT.PUT_LINE('Row locked.'); 
END;
/
SHOW ERRORS;
/

Testing:

In session 1:

DECLARE
  p_job_id JOBS.JOB_ID%TYPE;
BEGIN
  SELECT job_id
  INTO   p_job_id 
  FROM   jobs
  WHERE  job_id = 1
  FOR UPDATE OF min_salary, max_salary;

  DBMS_LOCK.SLEEP( 10 );

  UPDATE jobs
  SET   min_salary = min_salary + 10,
        max_salary = max_salary + 10
  WHERE job_id     = p_job_id;
END;
/

In session 2:

SET SERVEROUTPUT ON;

BEGIN
  upd_jobsal(
    par_job_id  => 1, 
    par_min_sal => 900, 
    par_max_sal => 1000
  );
END;
/

Session 2 should output:

Row locked.