automatic computation of totals (procedure)

ArminM · #1 (**permalink**) 12-10-11, 08:49

Hello all,
I need some help with creating a procedure in Oracle Application Express which basically adds up the totals, during data entry (into a form) and outputs it.
This would probably need to have a stored procedure attached to the relevant fields in the form but creating it using SQL is quite tricky for me as I've never used procedures before.

thanks in advance.
Armin

Littlefoot · #2 (**permalink**) 12-10-11, 08:56

What is your Apex version? Perhaps you could do that using Dynamic Actions. Also, a procedure might not be needed - a simple SUM might do the job.

ArminM · #3 (**permalink**) 12-10-11, 10:46

hi 'littleFoot' I am using Apex version 4.0.2.00.07.
What is a 'dynamic action'? and how would i go about doing a SUM in SQL

thanks again.

Littlefoot · #4 (**permalink**) 12-10-11, 11:07

Dynamic actions.

What I had on mind would be this: when an item value changes, set value of the summary item using a SQL statement as

Code:

select sum(some_column) 
from some_table
where some_other_column = :PX_apex_item
  and ...

It means that every new insert should also submit the page.

Here are some dynamic actions examples.

ArminM · #5 (**permalink**) 12-10-11, 11:44

Out of sheer curiosity, how would've this been done using a procedure? I've been trying to learn more about SQL and I havent fully grasped the concept of procedures.

I've got my APEX application up and running, with tables populated with records already and shown in the report/form style.

Littlefoot · #6 (**permalink**) 12-10-11, 13:10

If you insist on (stored) procedures, why not a function instead? It returns a single value, can be used in a SQL statement. Procedures can be used only in PL/SQL and, if you want it to return a value, it has to have an OUT parameter.

I don't think that this forum's purpose is to teach you all about procedures - take a look at PL/SQL User's Guide and Reference and Application Developer's Guide - Fundamentals books (actually, don't just take a look - read them thoroughly).

ArminM · #7 (**permalink**) 12-16-11, 09:51

...so iv started creating the procedure but I'm stumbling on a few things:
here's what I have so far.

Code:

 CREATE PROCEDURE data_entry_totals
AS
CURSOR (*cursor_name*)
SELECT * FROM (*table_name*)
WHERE (*column_name* = *column_names.attribute*);
BEGIN
FOR

I need to display the number of records in my 'purchase' table which looks like this:

Code:

create table Purchase
(
Purchase_ID NUMBER(10),
Appetizer_ID NUMBER(10),
Main_Course_ID NUMBER(10),
Dessert_ID NUMBER(10),
Drink_ID NUMBER(10),
constraint pk_Purchase_ID PRIMARY KEY(Purchase_ID));

Code:

INSERT INTO Purchase values(900, 201, 301, 401, 501); 
INSERT INTO Purchase values(900, 202, 302, 402, 502);

I read somewhere that the cursor name needs to be declared before a procedure is created?

Thanks again guys!

anacedent · #8 (**permalink**) 12-16-11, 09:56

search this board for "CREATE OR REPLACE PROCEDURE"

ArminM · #9 (**permalink**) 12-16-11, 10:17

iv searched this forum using the words "create or replace procedure" and "create procedure apex" but found nothing.

If anyone knows how to create this simple procedure and can explain it to me succinctly, I would be eternally grateful.

thanks in advance

Littlefoot · #10 (**permalink**) 12-16-11, 11:10

There's no use in spoonfeeding or doing your homework.

Click here, navigate to the Most Popular section and read:
- PL/SQL User's Guide and Reference
- Application Developer's Guide - Fundamentals

Will take some time, but you'll learn A LOT!

Internet Services

Web Development

Digital Marketing

Consumer Tech