Re: dynamic sql from Forms

from Jennifer R. Amon :  

>I'd like to store some SQL in the database and call it from a form.
>If I store the name of a sql script in the database, I can call the
>script with a host('sqlplus / _at_scriptname'); command, but I want
>this SQL script to be able to do some complicated validation of
>multiple rows of parameter data being inserted/updated from the
>form.
>
>Even if I call the script from the post-commit trigger, the changes
>to the data are not apparent to the script. I assume that this is
>because the host command actually starts a sub-process, and the
>sub-process can't see the pending changes to the database.
 

I didn't work with FormsV4 but to solve your problem in V3 you have to use the trigger Key-Commit. If you have this kind of event in V4 i think you can execute your job in subprocess without problem.  

When the event post-commit run the "database commit" is not done, the transactions are applied but not still commited. To make sure that the commit is done ( imply that your subprocess can see the result ) call the host procedure after commit in th key trigger key-commit.  

Key-Commit  

commit;
host('sqlplus / _at_scriptname');  

Michel Tremblay ( tremblam_at_jsp.umontreal.ca ) Received on Sun Dec 26 1993 - 18:13:02 CET