Magali wrote:You'll get a faster, more effective response to your questions by including as much relevant information as possible upfront. This should include:
I'm experimenting with computations. I'm quite new to APEX, but I really need to make sure this works.
So, this computation, it's supposed to take a value of an item (in which a user has entered a number), and then make a computation with that value. You'll see it in the code below.So have you read the documentation on Computattions?
Some extra info: when a user enters a certain value of weight (let's say 80 gram), and then presses enter in the text field, this computation runs. Now, it does run because I've seen so by checking the session."Checking the session" how? The computation code below can't run as it contains syntax errors:
SQL> var p17_set_unit varchar2(10) SQL> var p17_set_weight number SQL> DECLARE 2 a_unit varchar2(20); 3 a_old_kcal float; 4 a_new_kcal float; 5 a_old_carbs float; 6 a_new_carbs float; 7 a_old_weight float; 8 a_new_weight float; 9 10 BEGIN 11 SELECT standardamount INTO a_old_weight, 12 kcal INTO a_old_kcal, 13 carbs INTO a_old_carbs, 14 name INTO a_unit 15 FROM foodunit 16 WHERE foodunit.foodunitid = :P17_SET_UNIT; 17 IF a_unit := 'gram' THEN 18 a_new_kcal := a_old_kcal/100 * :P17_SET_WEIGHT; 19 a_new_carbs := a_old_carbs/100 * :P17_SET_WEIGHT; 20 a_new_weight := :P17_SET_WEIGHT; 21 ELSE 22 a_new_kcal := a_old_kcal; 23 a_new_carbs := a_old_carbs; 24 a_new_weight := a_old_weight; 25 END IF; 26 27 RETURN a_new_weight; 28 RETURN a_new_carbs; 29 return a_new_kcal; 30* end; SQL> / kcal INTO a_old_kcal, * ERROR at line 12: ORA-06550: line 12, column 6: PL/SQL: ORA-00923: FROM keyword not found where expected ORA-06550: line 11, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 17, column 11: PLS-00103: Encountered the symbol "=" when expecting one of the following: . ( * @ % & = - + < / > at in is mod remainder not rem then <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multi set
But I haven't managed out on how to display the results. So, I was thinking about placing the results in items, which are then again displayed in a region.This is what computations are used for, but the docs say: +Use page computations to assign a value to an identified item when a page is submitted or displayed.+ i.e. a single value to a single page or application item.
What I'm wondering is, how do I edit my source code in such a way that the return values will be inserted in already existing page items?
This is my computation source code:Always post code wrapped in tags<tt>\
</tt> tags to preserve formatting and special characters.
DECLARE<tt>INTO</tt> only appears once in the <tt>SELECT...INTO</tt> statement.
SELECT standardamount INTO a_old_weight,
kcal INTO a_old_kcal,
carbs INTO a_old_carbs,
name INTO a_unit
FROM foodunitMultiple <tt>RETURN</tt> statements like this won't work. Such code compiles but should generate a warning about unreachable code.
WHERE foodunit.foodunitid = :P17_SET_UNIT;
IF a_unit := 'gram' THEN
a_new_kcal := a_old_kcal/100 * *:P17_SET_WEIGHT;*
a_new_carbs := a_old_carbs/100 * *:P17_SET_WEIGHT;*
a_new_weight := :P17_SET_WEIGHT;
a_new_kcal := a_old_kcal;
a_new_carbs := a_old_carbs;
a_new_weight := a_old_weight;
I want this to return the a_new_weight, a_new_carbs and a_new_kcal as items. I've made items named P17_NEW_WEIGHT, P17_NEW_CARBS and P17_NEW_KCAL already, and my plan is to insert the values of a_new_weight into P17_NEW_WEIGHT, and so on.You're trying to to compute values for 3 items, so you could use 3 computations, but as the calculations are related and based on the results of a query that you don't want to repeat, so doing this using a process is a better idea.
How can I change my computation source code in such a manner that it will insert these values into the items mentioned above?
:P17_NEW_KCAL := a_old_kcal/100 * :P17_SET_WEIGHT;
Magali wrote:The developer menu also provides access to the Debug features, which will provide a detailed view of what's going on.
With "checking the session", I mean running the application and then clicking on the "Session" button at the bottom of the page. It's located in a menu, which I assume is for developing purposes.
I DID read the documentation, but it doesn't really makes sense to me.The fact you haven't suggests that you've created a Static Assignment computation. All this does is set the value of the item to be the "code" you entered. No PL/SQL code has actually been executed. It appears the intention was to use a PL/SQL Function Body computation, which will give an error message in the builder with the code posted.
As I said before, I'm quite new to APEX so it will take me a while until I sort things out properly.
I'm more of the type of person that has to learn things by doing them.
I haven't tried running the code in "SQL commands", I just made a computation on my page (page 17) and it didn't gave any errors when I continued. > If it would have had a mistake, then I would get a notification saying something is wrong with my code.
About the INTO statement, I can only use that once?Yes, as described in the linked PL/SQL documentation:
(You should also avoid using Oracle reserved words like <tt>name</tt> as identifiers for tables, columns, variables, programs etc.)
select standardamount, kcal, carbs, name into a_old_weight, a_old_kcal, a_old_carbs, a_unit from foodunit where foodunitid = :p17_set_unit;
So, basically, I should make 3 computations: one for the weight, one for the carbs, and one for the kcal.No—as suggested above—use a page process to avoid repeated database queries.
You'll notice that I don't use a_unit anymore. Now I use the value of a page item, named P17_SELECT_UNIT2. This is a hidden item, it's source is like this:
DECLARE a_old_weight FLOAT; a_old_kcal FLOAT; a_old_carbs FLOAT; a_old_protein FLOAT; a_old_fat FLOAT; BEGIN SELECT standardamount, kcal, carbs, protein, fat INTO a_old_weight, a_old_kcal, a_old_carbs, a_old_protein, a_old_fat FROM foodunit WHERE foodunit.foodunitid = :P17_SET_UNIT; IF :P17_SELECT_UNIT2 = 'gram' THEN :P17_NEW_WEIGHT := a_old_weight/100 * :P17_SET_WEIGHT; :P17_NEW_KCAL := a_old_kcal/100 * :P17_SET_WEIGHT; :P17_NEW_CARBS := a_old_carbs/100 * :P17_SET_WEIGHT; :P17_NEW_PROTEIN := a_old_protein/100 * :P17_SET_WEIGHT; :P17_NEW_FAT := a_old_fat/100 * :P17_SET_WEIGHT; ELSE :P17_NEW_WEIGHT := a_old_weight * :P17_SET_WEIGHT; :P17_NEW_KCAL := a_old_kcal * :P17_SET_WEIGHT; :P17_NEW_CARBS := a_old_carbs * :P17_SET_WEIGHT; :P17_NEW_PROTEIN := a_old_protein * :P17_SET_WEIGHT; :P17_NEW_FAT := a_old_fat * :P17_SET_WEIGHT; END IF; END;
P17_SET_UNIT is a select list, it's allows a user to pick a unit (gram or piece) for the corresponding piece of food. This piece of food is first selected out of a report with clickable row names.
SELECT name FROM foodunit WHERE foodunitid = :P17_SET_UNIT