Input validation package in PL/SQL

PL/SQL Package: Exercise-3 with Solution

Write a package that includes a procedure to validate user input for a specific data type and handle any exceptions that may occur.

Sample Solution:

PL/SQL Code:

CREATE OR REPLACE PACKAGE InputValidationPackage AS
  PROCEDURE ValidatePositiveInteger(input IN VARCHAR2);
END InputValidationPackage;
/
CREATE OR REPLACE PACKAGE BODY InputValidationPackage AS
  PROCEDURE ValidatePositiveInteger(input IN VARCHAR2) IS
numeric_input NUMBER;
  BEGIN
numeric_input := TO_NUMBER(input);
    IF numeric_input<= 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Input must be a positive integer.');
    END IF;
    EXCEPTION
      WHEN VALUE_ERROR THEN
        RAISE_APPLICATION_ERROR(-20002, 'Input must be a valid number.');
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20003, 'An error occurred while validating the input.');
  END ValidatePositiveInteger;
END InputValidationPackage;
/

Sample Output:

Package created.
Package Body created.

Flowchart:

To execute the package:

BEGIN
InputValidationPackage.ValidatePositiveInteger('10'); -- Valid input
InputValidationPackage.ValidatePositiveInteger('-5'); -- Invalid input
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

/

Sample Output:

Statement processed.
ORA-20003: An error occurred while validating the input.

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that ensure that user inputs meet the requirement of being a positive integer, providing strong validation for data integrity and error handling.

The package "InputValidationPackage" contains a procedure "ValidatePositiveInteger" to validate whether a given input string represents a positive integer or not.

The procedure takes an input parameter and attempts to convert the input string into a numeric value using the TO_NUMBER function and if the conversion is successful and the resulting numeric value is less than or equal to zero, it displays an error message "Input must be a positive integer."

During the conversion process if the exception handling raises an application error, the message "Input must be a valid number" is displays. For any other exceptions, it raises an application error with the message "An error occurred while validating the input."

Previous: String Manipulation Functions.
Next: Math operations package in PL/SQL.