APEX

I set out this morning with what I thought was a simple task: Dynamically set the source for a text box using PL/SQL. I'm new to both APEX and PL/SQL, so my simple task has taken 3+ hours to get nowhere. Any help would be appreciated!

I want the Home page of my application to notify the user of known problem areas. The code below works fine in the Region source (an HTML region, source type "PL/SQL anonymous block"). But it happens AFTER the Page Item renders, so the item appears blank. Besides, it seems more logical to me for it to be in the Item Source rather than the Region.

In Item Source (displayed as Text Field, Source Type "PL/SQL Function Body"), this same code gets the error "ORA-06550: line 1, column 7: PLS-00103: Encountered the symbol ";" when expecting one of the following: ( begin case declare exit for goto if loop .......".

I copy/paste from one to the other, so I know it is identical. What is going on?

I'm in APEX 4.2.

Code:
DECLARE
my_count integer;
my_sentence VARCHAR2(100);

BEGIN

SELECT COUNT(*)
INTO my_count
FROM PROJECT_CHARTER_V
WHERE AREA_STATUS = 'PROBLEM';

my_sentence :=
CASE my_count
WHEN 0 THEN 'There are no projects with area-status problems.'
WHEN 1 THEN 'There is one project with area-status problems.'
ELSE 'There are ' || MY_COUNT || ' projects with area-status problems.'
END;

:P1_STATUS_WARNING := my_sentence;
END;

Edited by: user1049270 on Mar 28, 2013 9:02 AM