How to write plsql exception satatement to ask user to give input in specific format

Question

CREATE OR REPLACE PROCEDURE get_productdetails(

   p_reqid   OUT requirement.req_id%type,

   p_pid     OUT requirement.p_id%type,

   p_rstaffid OUT requirement.r_staff_id%type,
   
   p_demand  requirement.demand%type)

IS

CURSOR c_demand IS

SELECT req_id,p_id,r_staff_id from requirement where demand = upper(p_demand);

BEGIN

FOR i in c_demand Loop

DBMS_OUTPUT.PUT_LINE('Requirement ID :'||i.req_id);

DBMS_OUTPUT.PUT_LINE('Product ID'||i.p_id);

DBMS_OUTPUT.PUT_LINE('Staff ID :'||i.r_staff_id);

END LOOP;

END get_productdetails;

User must enter demand in 'HIGH/LOW/AVG only otherwise it should throw exception asking to enter data in that format Can you please help me to write an exception accordingly

Answer 1

As I don't have access to your table structure, the code below is untested. A good place to start is the official oracle documentation. In your case, a user-defined exception is needed, which you need to declare in the declaration section, then RAISE and finally catch in the EXCEPTION block.

CREATE OR REPLACE PROCEDURE get_productdetails(
   p_reqid   OUT requirement.req_id%type,
   p_pid     OUT requirement.p_id%type,
   p_rstaffid OUT requirement.r_staff_id%type,
   p_demand  requirement.demand%type)
IS
  -- declare the user-defined exception
  invalid_input EXCEPTION;
  CURSOR c_demand IS
  SELECT req_id,p_id,r_staff_id from requirement where demand = upper(p_demand);
BEGIN
  IF p_demand NOT IN ('HIGH','LOW','AVG') THEN
    -- raise the exception if p_demand not one of the 3 accepted values
    RAISE invalid_input;
  END IF;
  FOR i in c_demand Loop
  DBMS_OUTPUT.PUT_LINE('Requirement ID :'||i.req_id);
  DBMS_OUTPUT.PUT_LINE('Product ID'||i.p_id);
  DBMS_OUTPUT.PUT_LINE('Staff ID :'||i.r_staff_id);
  END LOOP;
EXCEPTION WHEN  invalid_input THEN
  dbms_output.put_line('Only HIGH/LOW/AVG are valid input');  
END get_productdetails;