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

User_G4ALZ

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?

InoL

it works fine when both :p67_price and :p67_type_projet are given numeric values ... it won't work otherwise

What is otherwise?

All Apex page items are varchars, so if you want to want to assign that to a numeric variable, you need to use to_number with the appropriate format mask.

This works;

p67_price := '15000000';

This won't:

p67_price := '1,5000,000';

(although I would expect a different error message, not no data found)

Veerendra Patil

Hi,

When builder compiles at design time - values for :p67_price and :p67_type_project are NULL.

So none of the if conditions pass and so there is no query for LOV in the end.

try assigning

p67_price:= COALESCE(:p67_price, 0);
p67_type_project:=COALESCE(:p67_type_projet, 0);

Thanks,

Veerendra

Sylvain Martel

Hello User,

This might sounds like obvious, but I are you submitting the values in session states before executing the PL/SQL?

Also, please clarify the steps you are going through to generate the error. There are some gaps in the description of your problem.

Hope this helps.

AndyH

p67_type_project:=:p67_type_projet;

I assume that this is a typo, unless your page item is missing the 'c' from 'project'.

Also, as mentioned by @Veerendra Patil , if your page items are not populated then your 'if' statements will not operate as expected - a comparison against a null value is neither true nor false. The same would be true if you have not submitted their values into session state, as mentioned by @Sylvain Martel .