Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

APEX Form Validation

540405Oct 18 2006 — edited Oct 19 2006
New to APEX and PL/SQL. Am attempting to create a Page Validation to determine if a value provided by a user on a data entry form is valid. In this case I am attempting to evaluate a date field to determine if the year portion is 2006 or if it is not. I have spent hours on this and tried many variations and combinations of APEX declarations and PL/SQL programming. Have looked through both Linnemeyer & Brown's book and the Cunningham & Crotty book. Nothing I have attempted works and nothing in these two books has helped. See the examples below. In summary, can someone explain how to create an APEX validation that investigates a date data entry field to determine if the YYYY portion is a valid value. I imagine the solution is simple, but it has eluded me.

For the Validation Type, I chose to use "Function Returning Boolean" since this worked on another validation I created. The Validation Expression 1 is :

IF to_char(:P4_FEE_EFF_DT,'YYYY') = '2006'
THEN
RETURN true ;
ELSE
RETURN false ;
END IF ;

1. When I click the CREATE button on the data entry form, I receive the following error message:
ORA-06550: line 1, column 47: PLS-00307: too many declarations of 'TO_CHAR' match this call ORA-06550: line 1, column 44: PL/SQL: Statement ignored
Error ERR-1021 Unable to run "function body returning boolean" validation.

2. I have tried this same Validation Expression, but made the Validation Type equal to PL/SQL Expression. It produced the error message below when I clicked the Create button.

ORA-06550: line 1, column 34: PLS-00103: Encountered the symbol "IF" when expecting one of the following: ( ) - + case mod new not null others table avg count current exists max min prior sql stddev sum variance execute multiset the both leading trailing forall merge year month DAY_ hour minute second timezone_hour timezone_minute timezone_region timezone_abbr time timestamp interval date Error ERR-1025 Error processing PLSQL expression. IF to_char(:P4_FEE_EFF_DT,'YYYY') = '2006' THEN RETURN true ; ELSE RETURN false ; END IF ;

3. I have tried this same Validation Expression, but made the Validation Type equal to PL/SQL Error. When I click the Create button, this validation does not produce an APEX error message, but every time I click Create it displays the Notification Box with error message text for this validation. In this case it appears to not be running the to_char function.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 16 2006
Added on Oct 18 2006
7 comments
3,330 views