December 8th, 2003, 03:45 PM
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:
Then the procedure code is:
CREATE OR REPLACE PROCEDURE AUTHORIZE_PLANNING_GROUP
(p_pg_id IN planning_groups.id%type)
-- local variables for procedure
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.*/
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
status = 'AUTHORIZED',
auth_date = sysdate,
date_updated = sysdate
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.');
WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201, 'No data was found.');
Can anyone tell me what I need to do to get the new value displayed? Thanks much for your help.
December 10th, 2003, 09:27 AM
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:
--call the stored procedure
--requery the underlying row from database
:global.id := :pc_mc.id;
set_block_property ('pg_mc', default_where, 'id=:global.id');
set_block_property ('pg_mc', default_where, '1=1');
Last edited by mhamilton; December 10th, 2003 at 10:45 AM.