This discussion is archived
1 Reply Latest reply: Oct 1, 2007 11:04 PM by 60437 RSS

Development (compile) vs Run Time Error Checking

395609 Newbie
Currently Being Moderated
I wanted to share an experience I had with APEX a couple of days ago. I was working on a page creating items, processes, validations, branches, etc. After a while, I wanted to run what was completed by that time. At one point APEX displayed the error page with the following messages:
ORA-01403: no data found
Error ERR-1025 Error processing PLSQL expression. “one of the validation expressions”
I looked at the validation and it looked perfectly OK.

After a couple of hours of playing with validations (disabling one at the time and getting the error on a different validation) it was time to look at each one of them in order. Sure enough, one of them had a syntax error. There was an expression like this:
:p1_item in (4, pgk.func;)
The syntax error is the semicolon after the function call (the result of a copy and paste).

The strange thing is that the error was never raised for this validation but for the second after the offending one.

The next thing to do was to setup a test case:

1 HTML region with one text item and one button.

4 validations on item in order of sequence:
1. item specified is not null or zero
2. PL/SQL expression with syntax error
3. PL/SQL expression
4. PL/SQL expression

All validations are conditional on button pressed. Note that the validation 2 has the actual error.

The result is error page with:
ORA-01403: no data found
Error ERR-1025 Error processing PLSQL expression. “validation 4 expression”
The behaviour was tested in two environments:
Database version: 9.2.0.4.0
APEX version: 3.0.0.00.20
and
Database version: the one hosting apex.oracle.com
APEX version: 3.0.1.00.07

The error reporting was not identical but it was similar enough which led me to believe that the versions do not play a role here.

Then I decided to see if there is a difference between PL/SQL parsers. The code to test it was:
declare
  curid        NUMBER;
begin
  curid := DBMS_SQL.OPEN_CURSOR;
  -- Parse SQL cursor number:
  DBMS_SQL.PARSE(curid, 'begin /* condition */ if :p2_f1 is not null then /* validation */ if to_number(:p2_f1) in (4, pkg.func;) then null; else null; end if; end if; end;', DBMS_SQL.NATIVE);
end;
Both databases raised an error:
ORA-06550: line 1, column 103:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

   . ( ) , * @ % & | = - + < / > at in is mod remainder not
   range rem => ..  <> or != or ~= >= <= <>
   and or like between || multiset member SUBMULTISET_
The symbol ";" was ignored.
1. declare
2.   curid        NUMBER;
3. begin
A couple of questions are in order here:

1.     Is it possible to parse all PL/SQL expressions at development (compile) time rather then deferring it to run time?
2.     Should I propose the development time parsing to be included in the next version?

Thanks,

Sima