Works under SQL*Plus but not SQL*Forms procedure. ;-(

From: Steve Frampton <3srf_at_qlink.queensu.FOOBAR.ca>
Date: 1998/02/19
Message-ID: <6chf6b$38h_at_knot.queensu.ca>#1/1

[Quoted] [From: header modified to thwart e-mail spam. See .sig for details]

Hello:

[Quoted] [Quoted] I'm developing an SQL*Forms 3.0 application that allows the user to modify some information about employee groups. When the form is executed, any information that has been entered in the groups table is then applied to the employee table for all employees in the appropriate employee groups.

[Quoted] The code that updates the table is called from a procedure, which is in turn called by a key trigger. This code works perfectly when run from SQL*Plus, but when run within the SQL*Forms procedure, nothing happens!! (I get, "No changes to commit" on the message line).

I put some entry/exit code with messages just saying, "Begin update..." and "End update...", and I "commit work;" right after the update. When the key trigger fires, I get:

Begin update...
No changes to commit.
End update...

While the same thing cut & pasted into SQL*Plus yields:

Begin update...
23 rows updated.
End update...

Here is the update code:

  update ec_employee_sen_serv emp
  set emp.sen_serv_calc_date =

(select nvl(process_date,emp.sen_serv_calc_date)

               from   ec_group_sen_serv
               where  emp_group_code = emp.emp_group_code
               and    sen_serv_code = emp.sen_serv_code
               and    update_ind = 'Y')
  where  emp.emp_group_code =


              (select emp_group_code


               from   ec_group_sen_serv
               where  emp_group_code = emp.emp_group_code
               and    sen_serv_code = emp.sen_serv_code
               and    update_ind = 'Y')
  and    emp.sen_serv_code =


              (select sen_serv_code


               from   ec_group_sen_serv
               where  emp_group_code = emp.emp_group_code
               and    sen_serv_code = emp.sen_serv_code
               and    update_ind = 'Y');

commit work;

If it looks awkward, it's because I couldn't think of any other way to write this -- if anybody has a better way, I'd be interested in seeing a more elegant way of coding this as well. But it *does* work. ;-)

I'm pulling out my hair over this, and my hair is one of my best features! :^)

Any assistance would be greatly appreciated. Please note that e-mail replies are not necessary.

----------------< LINUX: The choice of a GNU generation. >----------------

Steve Frampton <3srf(_at_)qlink.queensu.ca> http://qlink.queensu.ca/~3srf ----------- Please remove .FOOBAR from address before replying. ---------- Received on Thu Feb 19 1998 - 00:00:00 CET