Re: dynamic sql from Forms
Date: Tue, 28 Dec 1993 09:02:38 -0500
Message-ID: <bamon-281293090239_at_amon.cc.oberlin.edu>
In article <2fnom1$idf_at_werple.apana.org.au>, gharriso_at_werple.apana.org.au (Guy Harrison) wrote:
> bamon_at_ocvaxc.cc.oberlin.edu (Jennifer R. Amon) writes:
>
> >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 inter-
> >dependency of detail rows, and the validation procedure would be
> >different for each master.
>
> The only way I know to call true dynamic SQL from a form within the same
> transaction is via user exits written in PRO*C or other 3GL.
>
> Getting going with userexits can be a little tricky but once you get going
> they're just routine and can be very powerful.
>
Well, I got the answer I was hoping for from Steve Muench at Oracle:
JENNIFER -- You can use Forms_DDL( string-arg ); to send any
executable SQL statement to the server. You might read the
various string-arg's from a table, or what have you. The
feature is not properly documented until 4.0.12, and
doesn't correctly report its error status until 4.0.12,
but the command should be available for you to poke around
with in 4.0.11 until then. Did I miss something, or was
this not what you were looking for?
Steve Muench Email: smuench_at_oracle.com
Forms Development CIS: 73404,676
Product Manager
Oracle Corporation
- End Quoted Material -------------
I'm still working with 4.0.7 and 4.0.9, so I'll have to wait a bit, but that's OK.
I was aware of the user_exit option, and I have done dynamic SQL in Pro*C programs, but I am trying to move away from user exits as much as possible because of the portability problems in a client/server environment, where there are multiple clients, some Macs, some PCs, some X-Windows.
Thanks, Steve. You're a life-saver (and a work-saver) once again.
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 _____________________________________________________________________Received on Tue Dec 28 1993 - 15:02:38 CET