Re: dynamic sql from Forms

In article <1993Dec23.023724.10613_at_oracle.us.oracle.com>, dbmoore_at_us.oracle.com (Dennis Moore) wrote:

> 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.
>
> -- Dennis Moore, my own opinions, etcetcetc

Well, that would be ok, but I'm not sure I want to have a hundred stored procedures. The scripts would be used to validate the interdependency  of detail rows, and the validation procedure would be different for each master.

We want to build a system for the control of automatic batch jobs. For example, job FASJ0001 can have up to 6 parameters, and there is validation for each of those parameters. Multiple individual schedule rows can be created for job FASJ0001, e.g. it runs Monday and Wednesday nights with values specified for parameters 1,2,4,5 & 6. It also runs Friday nights with values specified for parameters 2,3 & 4. The parameters also have validation rules for interdependency, i.e. if the user specifies parameter 1 and parameter 2, then parameter 3 may NOT be specified. In addition, if parameters 5 and 6 are specified, parameter 5 must be less than parameter 6. Etc. Etc.

We'd like to NOT have to hard-code all the rules for all the jobs in the form. We like (if possible) to not have hundreds of these validation scripts stored in the database. We'd like to store just the name of a SQL script, which can then be used dynamically to validate the multiple parameters being specified for each scheduling row for each job.

Yes, I'm using Oracle7, and will be using forms v4.

Jennifer R. Amon            PHONE: (216) 775-6987
Houck Computing Center        FAX: (216) 775-8573
Oberlin College
Oberlin, OH 44074        INTERNET: bamon_at_ocvaxc.cc.oberlin.edu
_____________________________________________________________________