CODEWITHSUNDEEP
plsqloracle-apex
Nate May
Nate May

Reputation: 4062

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

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

Upvotes: 2

Views: 4870

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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.

Upvotes: 2

Related Questions