Forum Stats

  • 3,854,206 Users
  • 2,264,340 Discussions
  • 7,905,609 Comments

Discussions

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

User_G4ALZ
User_G4ALZ Member Posts: 6 Green Ribbon

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?

Answers

  • InoL
    InoL Member Posts: 10,068 Gold Crown
    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
    Veerendra Patil Member Posts: 600 Gold Badge
    edited Jan 7, 2021 8:18PM

    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
    Sylvain Martel Member Posts: 815 Silver Trophy

    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
    AndyH Member Posts: 832 Bronze Trophy
    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 .