CODEWITHSUNDEEP
oracle-databasevalidationplsqloracle-apex
NMS
NMS

Reputation: 35

Oracle APEX Field Validation

I have a simple question regarding field validation on Oracle Apex. I am trying to put validation on the 'Work Package Name' text field to be either 'WP' followed by two digits or 'AM' followed by two digits e.g WP00 & AM01 would be accepted and WP000 & AN01 would not be accepted.

I was unsure of which validation option to choose therefore I decided to use PL/SQL returning a boolean. The code I have seems to be incorrect (see below).

If I was creating this in TOAD I would have declared the field name however I am presuming that by using the :P7_WP_NAME this is doing the same job.

IF :P7_WP_NAME = WP(2,0) 
ELSE IF :P7_WP_NAME = AM(2,0)
THEN RETURN TRUE;
ELSE 
  RETURN FALSE;
END IF;

The error I am receiving at present is below. I have tried reworking the code but keep getting different errors: 

ORA-06550: line 2, column 1: PLS-00103: Encountered the symbol "ELSE" when expecting one of the following: . ( * % & - + / at mod remainder rem then <an exponent (**)> and or || multiset 
ORA-06550: line 7, column 18: PLS-00103: Encountered the symbol ";" when expecting one of the following: if The symbol "if" was substituted for ";" to continue.
ORA-06550: line 7, column 54: PLS-00103: Encountered the symbol    "end-of-file" when expecting one of the following: begin case declare
end exception

I would be grateful if either you could tell me if this is the best validation option to use and where I am going wrong.

Upvotes: 0

Views: 1613

Answers (2)

brenners1302
brenners1302

Reputation: 1478

Regarding the error

'ORA-06550: line 2, column 1: PLS-00103: Encountered the symbol "ELSE" when expecting one of the following: . ( * % & - + / at mod remainder rem then and or || multiset ORA-06550: line 7, column 18: PLS-00103: Encountered the symbol ";" when expecting one of the following: if The symbol "if" was substituted for ";" to continue. ORA-06550: line 7, column 54: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin case declare end exception'

you should have used ELSIF instead of ELSE IF.

Upvotes: 1

Rene
Rene

Reputation: 10541

I'm not great with regular expression but have a go at this:

begin
   if regexp_like(:P7_WP_NAME,'^WP\d{2}$')
   or regexp_like(:P7_WP_NAME,'^AM\d{2}$')
   then
      return true;
   else
     return false;
   end if;
end;

The regexp probably be simplified to:

regexp_like(l_string,'^(WP|AM)\d{2}$'

to match both the WP and the AM in one expression.

Upvotes: 2

Related Questions