Re: dynamic sql from Forms

From: Guy Harrison <gharriso_at_werple.apana.org.au>
Date: 28 Dec 1993 09:47:29 +1100
Message-ID: <2fnom1$idf_at_werple.apana.org.au>

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.

The user exit can either get the SQL text from a Form field or global variable or can fetch it from the database.

The SQL is then stored into bind variable which you can then PREPARE and use to create a cursor or execute immediately. All the statements execute within the context of the current transaction.

Getting going with userexits can be a little tricky but once you get going they're just routine and can be very powerful.

---

Guy Harrison                        gharriso_at_werple.apana.org.au
ORACLE  Database Consultant         gharriso_at_vitgwms1.telecom.com.au

Synchronous Software P/L
Melbourne Australia Received on Mon Dec 27 1993 - 23:47:29 CET