Newsgroups: comp.databases.oracle From: dbmoore@us.oracle.com (Dennis Moore) Subject: Re: dynamic sql from Forms Message-ID: <1993Dec23.023724.10613@oracle.us.oracle.com> Sender: usenet@oracle.us.oracle.com (Oracle News Poster) Nntp-Posting-Host: hqsun4.us.oracle.com Organization: Oracle Corporation, Redwood Shores CA References: Date: Thu, 23 Dec 1993 02:37:24 GMT X-Disclaimer: This message was written by an unauthenticated user at Oracle Corporation. The opinions expressed are those of the user and not necessarily those of Oracle. Lines: 24 In article bamon@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 / @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