#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Missouri, USA
    Posts
    4
    Rep Power
    0

    Unhappy procedure data values not displayed in form


    I am working in Oracle6i trying to create a form. The form calls a procedure that changes the value of a column in the database. I need this new value displayed in the field on the form. But I am missing some important detail to accomplish this. If I do a requery of the database in the form, the new value shows up. But, I need this new value to be displayed instantly on the form.

    In a module component, I have an Action Item that has a WHEN-BUTTON-PRESSED with this event logic code that calls the procedure:

    BEGIN
    AUTHORIZE_PLANNING_GROUP(:PG_MC.ID);
    END;

    Then the procedure code is:

    CREATE OR REPLACE PROCEDURE AUTHORIZE_PLANNING_GROUP
    (p_pg_id IN planning_groups.id%type)

    IS
    -- local variables for procedure
    v_status planning_groups.status%type;
    v_products planning_groups.products%type;

    BEGIN

    IF p_pg_id IS NOT NULL THEN

    /* select the status in the PG table that belongs to the current PG being referenced in the application and place in local variable.*/
    SELECT status
    INTO v_status
    FROM planning_groups
    WHERE id = p_pig_id;

    /* When the planning group has a status of planned, allow the status to be changed to authorized. Set the date to the current date of change. */
    IF v_status in ('PLANNED') THEN
    UPDATE planning_groups
    SET
    status = 'AUTHORIZED',
    auth_date = sysdate,
    date_updated = sysdate
    WHERE
    id = p_pg_id;

    DBMS_OUTPUT.PUT_LINE('A planning group status was updated to authorized.');

    ELSE --(Message for when a PG is not in the planned status).
    DBMS_OUTPUT.PUT_LINE('A planning group may not be authorized that is not in the PLANNED status.');

    END IF;
    END IF;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201, 'No data was found.');

    COMMIT;

    END AUTHORIZE_PLANNING_GROUP;
    /


    Can anyone tell me what I need to do to get the new value displayed? Thanks much for your help.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Missouri, USA
    Posts
    4
    Rep Power
    0

    Thumbs up re: procedure data values not displayed in form


    I received the answer to my question on another website from a reader, Sean Chang. I post the answer to my own question in hopes of helping others in the future that may have the same problem.

    In the code that is placed in the WHEN-BUTTON-PRESSED event logic, add a couple of lines to get the form to requery the database and display the new data values. See the following:

    BEGIN
    --call the stored procedure
    authorize_planning_group (:PG_MC.ID);
    --requery the underlying row from database
    go_block ('pg_mc');
    :global.id := :pc_mc.id;
    set_block_property ('pg_mc', default_where, 'id=:global.id');
    execute_query;
    synchronize;
    set_block_property ('pg_mc', default_where, '1=1');
    erase ('global.id');
    END;
    Last edited by mhamilton; December 10th, 2003 at 11:45 AM.

IMN logo majestic logo threadwatch logo seochat tools logo