Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2010
    Posts
    22

    Unanswered: automatic computation of totals (procedure)

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  3. #3
    Join Date
    Aug 2010
    Posts
    22
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  5. #5
    Join Date
    Aug 2010
    Posts
    22
    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.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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).

  7. #7
    Join Date
    Aug 2010
    Posts
    22
    ...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!

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    search this board for "CREATE OR REPLACE PROCEDURE"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Aug 2010
    Posts
    22
    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

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •