Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

User_G4ALZJan 7 2021

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?

Comments

Processing

Post Details

Added on Jan 7 2021
4 comments
3,433 views