ORA-06550 & PLS-00103 Errors when setting Oracle APEX item's default value

Question

I am trying to set the default value of an item in Oracle APEX 4.2 by selecting the first value in a table having the specified VIDEO_ID. In the item, under the Default section I have set Default Value Type = PL/SQL EXPRESSION, and in the Default Value block I have entered

SELECT UNIQUE_ALLEGATION_ID 
FROM (
  SELECT UNIQUE_ALLEGATION_ID
  FROM TBL_UNIQUE_ALLEGATION
  WHERE VIDEO_ID = :P2_VIDEO_SELECT) A
WHERE ROWNUM <= 1
ORDER BY ROWNUM;

This code works just fine in my Oracle editor (if I replace :P2_VIDEO_SELECT with a value; and I am positive that :P2_VIDEO_SELECT is properly set).

However, when I run the page, I get the following error:

ORA-06550: line 1, column 43: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

try PL/SQL function Body as Type of Default and DECLARE l_ret NUMBER; BEGIN SELECT UNIQUE_ALLEGATION_ID INTO l_ret FROM ( SELECT UNIQUE_ALLEGATION_ID FROM TBL_UNIQUE_ALLEGATION WHERE VIDEO_ID = :P2_VIDEO_SELECT) A WHERE ROWNUM <= 1 ORDER BY ROWNUM; RETURN l_ret; END; — hinotf
By the way, there's no point to sorting by ROWNUM, by definition it will always come out sorted, plus your query only returns one record anyway. — Jeffrey Kemp

Jeffrey Kemp Jeffrey Kemp · Accepted Answer · 2015-12-30T06:32:30

Remember that Apex attributes that accept SQL or PL/SQL fragments must be executed by the Apex at runtime, and that these must necessarily be embedded in wrapping code that must compile and execute at runtime.

So, for example, a source that is of type "PL/SQL Expression" will probably be executed in something like this:

declare
  temp_var varchar2(4000);
begin
  temp_var := (
SELECT UNIQUE_ALLEGATION_ID 
FROM (
  SELECT UNIQUE_ALLEGATION_ID
  FROM TBL_UNIQUE_ALLEGATION
  WHERE VIDEO_ID = :P2_VIDEO_SELECT) A
WHERE ROWNUM <= 1
ORDER BY ROWNUM
  );
  --use temp_var somewhere
end;

Obviously, the above code is not expected to work, which is why you're seeing an error like ORA-06550.

ORA-06550 & PLS-00103 Errors when setting Oracle APEX item's default value

1 Answers