Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
List of Values PL/SQL function body returning sql query in APEX not reading item values

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
-
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)
-
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
-
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.
-
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 .