oracle - List of Values PL/SQL function body returning sql query in APEX not reading item values

Question

i have this PL/SQL function

declare 
v_sql varchar2(222);
s1 real;
s2 real;
p67_price real;
p67_type_project real;
begin
p67_price:=:p67_price;
p67_type_project:=:p67_type_projet;
select :limit_1_type_project into s1 from type_project where id_type_project=p67_type_project;
select :limit_2_type_project into s2 from type_project where id_type_project=p67_type_project;
if p67_price>=s1 then
    v_sql:='select label_mode_pass, id_mode_pass from mode where id_mode_pass<4';
    return v_sql;
end if;

if p67_price<s1 and p67_price>=s2 then
    v_sql:='select label_mode_pass, id_mode_pass from mode where id_mode_pass=3 or id_mode_pass=2';
    return v_sql;
end if;

if p67_price<s2 then
    v_sql:='select label_mode_pass, id_mode_pass from mode where id_mode_pass<5';
    return v_sql;
end if;
end;

that i tested and it works fine when both :p67_price and :p67_type_projet are given numeric values for example :

p67_price:=15000000;
p67_type_project:=2;

the problem is it won't work otherwise and the APEX compiler show this error message ORA-01403: no data found. is it not possible to include region item's data in the list of values or is there another problem i am not seeing?

Answer 1

NO DATA FOUND means that one of SELECT statements didn't return anything because there's no row which satisfies WHERE condition.

If code you wrote works for values you mentioned (15000000 / 2) but not for other values, then you'll have to handle it somehow:

• one option is to make sure to provide only valid values for price and type_project

• another is to review where clauses; maybe you coded it wrong

• the most obvious is to include the exception handling section; it begins with the exception keyword and ... well, handles the error. For example:

  declare
    s1 ...
    s2 ...
  begin
    select ... into ... from ... where ...;     --> this is SELECT which might raise the error
  
    <do stuff if SELECT succeeds>
  
  -- this is what you need
  exception
    when no_data_found then
      -- handle it; this is just an example, you should know what to do
      s1 := 0;
      s2 := 0;
  end;
  

Also, make sure that P67_ items you use in that code are stored into session state. One way to do that is to submit the page (by pressing a button). Or, if it is a list of values, you can use those P67_ items as parent items in cascading list of values, or submit their values (you'll find both properties in LoV items' property palette).

If you wonder "how come 15000000 / 2 combination works?", it might be because you did put those values into session state previously, and it stays so during your session. If you log off and log in again, their values will be lost and - I presume - your code won't work any more, at least not until those values enter session state again.