Exception Handling for Invalid Salary - Exercise

Contact Us or call 1-877-932-8228
Exception Handling for Invalid Salary - Exercise

Exception Handling for Invalid Salary

Duration: 10 to 15 minutes.

Code Sample:

Exception-Clause/Exercises/exception_clause_exercise.sql
DECLARE
  v_salary            VARCHAR(50) := '1000.00';  
  v_salary_number     NUMBER;
BEGIN

  dbms_output.put_line(rpad('Initial salary: ',29) || v_salary);  
  
  v_salary_number := to_number(v_salary, '9999.00');
  
  v_salary_number := v_salary_number * 1.05;
  
  v_salary := to_char(v_salary_number, '$999,999.00');
  
  dbms_output.put_line('Final formatted salary:  ' || v_salary);  
     
END;

Code Explanation

  1. Open the exception_clause_exercise.sql file.
  2. Modify the code so that v_salary is assigned the value of $1000.00 (including a dollar sign).
  3. Run the program and note the exception that occurs.
  4. Declare an exception called invalid_salary_exception.
  5. Using a pragma, associate the exception with the exception code thrown during the previous run.
  6. Handle the exception so that the following is printed out: Invalid salary format: $1000.00. Only digits and a decimal point permitted.

Solution:

Exception-Clause/Solutions/exception_clause_solution.sql
DECLARE
  v_salary            VARCHAR(50) := '$1000.00';
  v_salary_number     NUMBER;
  invalid_salary_format exception;
  PRAGMA EXCEPTION_INIT(invalid_salary_format, -06502);
BEGIN

  dbms_output.put_line(rpad('Initial salary: ',29) || v_salary);

  v_salary_number := to_number(v_salary, '9999.00');

  v_salary_number := v_salary_number * 1.05;

  v_salary := to_char(v_salary_number, '$999,999.00');

  dbms_output.put_line('Final formatted salary: ' || v_salary);

exception
  when invalid_salary_format then
    dbms_output.put_line('Invalid salary format: ' ||
                    v_salary ||
                    '. Only digits and a decimal point permitted.');
END;

Code Explanation

Challenge

  1. Add conditional logic to determine whether v_salary contains a dollar sign symbol.
  2. Modify the program so that RAISE_APPLICATION_ERROR is used to raise an error that says: ERROR: No dollar signs allowed in v_salary.

Challenge Solution:

Exception-Clause/Solutions/exception_clause_challenge.sql
DECLARE
  v_salary            VARCHAR(50) := '$1000.00';
  v_salary_number     NUMBER;

BEGIN

  dbms_output.put_line(rpad('Initial salary: ',29) || v_salary);

  IF INSTR(v_salary,'$') != 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
                   'ERROR:  No dollar signs allowed in v_salary.');
  ELSE
   v_salary_number := to_number(v_salary, '9999.00');
  END IF;

  v_salary_number := v_salary_number * 1.05;

  v_salary := to_char(v_salary_number, '$999,999.00');

  dbms_output.put_line('Final formatted salary: ' || v_salary);

exception
  when OTHERS then
    dbms_output.put_line('Invalid salary format: ' ||
                    v_salary ||
                    '. Only digits and a decimal point permitted.');
END;

Code Explanation

Next