Re: dynamic sql from Forms

In article <bamon-221293170653_at_amon.cc.oberlin.edu> bamon_at_ocvaxc.cc.oberlin.edu (Jennifer R. Amon) writes:
>
>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.

Right, so it's in a different transaction. Probably the right thing to do (assuming you are using Oracle7) is to convert your SQL script into a stored procedure and work that way.

>
>Is there a way around this? (Short of committing the data, checking
>it, and then telling the user that they need to make further changes
>after it's already been committed.)

• Dennis Moore, my own opinions, etcetcetc