Oracle Apex 20.1 Error: Error processing validation

Question

I am trying to create a validation for a page in an application on oracle apex. Below is the pl/sql function body (returning boolean) I have:

declare v_equipid integer;
begin
        SELECT count(*) into v_equipid from equipment
        WHERE equipstatus = 'available' and equipID = :P3_EquipID;

    if v_equipid = 1 then
        return true;
    else
        return false;
    end if;
end; 

It is used to check whether the EquipID submitted has equipstatus = 'available'. After I run the application and submit the form, it gives the following error:

Error: Error processing validation.

• is_internal_error: true
• is_common_runtime_error: false
• apex_error_code: APEX.VALIDATION.UNHANDLED_ERROR
• ora_sqlcode: -6502
• ora_sqlerrm: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "APEX_200100.WWV_FLOW_VALIDATION", line 846 ORA-06512: at "APEX_200100.WWV_FLOW_VALIDATION", line 846
• error_backtrace: ORA-06512: at "APEX_200100.WWV_FLOW_VALIDATION", line 846 ORA-06512: at "APEX_200100.WWV_FLOW_VALIDATION", line 846 ORA-06512: at "APEX_200100.WWV_FLOW_VALIDATION", line 1029

I don't know how to fix this and I would appreciate your help. Thank you in advance!

Answer 1

Numeric or value error, eh?

This is what might be causing the problem:

and equipID = :P3_EquipID;

What is equipID's datatype? If it is a NUMBER, try

and equipID = to_number(:P3_EquipID);

---

As that's not the case (as you commented), would this help?

declare 
  v_equipid number;
begin
  SELECT count(*) 
    into v_equipid 
    from equipment
    WHERE equipstatus = 'available' 
      and equipID = :P3_EquipID;

  return v_equipid = 1;
end; 

Answer 2

The key to developing in apex is debugging your own code. That takes a bit of getting used to. Research on how to debug your apex app and instrument your pl/sql code, there is plenty of documentation available on the web.

You could just debug the validation by running that pl/sql in SQL workshop. Replace your RETURN TRUE/FALSE statements with dbms_output.put_line('true/false') to get the result. Running it in sql workshop will give you the exact line number. ORA-06502 usually means that a variable or column of type NUMBER is assigned a value of datatype VARCHAR2 (or another datatype that cannot be casted back to a number) .

Make sure you remove the dbms_output statements again when you move the code back to apex.

You could also debug your process in apex itself by using apex_debug, have a look at the documentation for a full overview of the functionality, something like this:

DECLARE
  v_equipid NUMBER; --integer is deprecated, should not be used
BEGIN
  -- put double quotes around the %0 to make any trailing/leading characters visible.
  apex_debug.info(
      p_message => 'Start of validation,  P3_EQUIPID = "%0"',
      p0        => :P3_EQUIPID);
  SELECT COUNT(*)
    INTO v_equipid
    FROM equipment
   WHERE equipstatus = 'available' AND
         equipid = :P3_EQUIPID;
  apex_debug.info(
      p_message => 'After SELECT INTO,  v_equipid = "%0"',
      p0        => v_equipid);
  IF v_equipid = 1 THEN
    RETURN true;
  ELSE
    RETURN false;
  END IF;
END;

Enable debug in the developer toolbar and investigate debug for your page. The messages you put in there should be available in the debug logs. The messages you put in the code after the error is thrown will not be visible since that code will not be executed.