Re: Dynamic sql from FORMS 4.5

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/05/16
Message-ID: <5li2kn$6ck@info.csufresno.edu>#1/1

In article <Pine.SOL.3.91.970516082341.29820A-100000_at_biotec>, Abiy Alemu <abiy_at_biotec.jouy.inra.fr> wrote:
>Hi friends,
>
>I would like to make a SELECT statement (dynamically) from FORMS 4.5.
>Is it possible to make a FORMS_DDL('SELECT INTO something ...') ? Or is
>there any other method to issue a select statement from a field of type
>VARCHAR2 containing the query. My block looks like this .
<snip>

Forms_DDL works except when you need to get data back from the server. In that case, you need to use the dynamic sql package DBMS_SQL. And since Forms uses PL/SQL version 1, some of the calls to procedures in the DBMS_SQL package don't work. To get around this, you can create a stored procedure that calls the procedure in the package, and call the stored procedure from Forms.

My QA utility form uses DBMS_SQL heavily. (And if you use it, the first time you run it, it creates two stored procedures named AADBMS... so the form can communicate with DBMS_SQL.)

An alternative to DBMS_SQL is to use a dynamic record group: Select data into the record group, then use pl/sql to get data from the record group and place it in your Forms block.

Regards,
Steve Cosner

Try out QA, a dynamic data utility form. Quick display and update access to any table. http://members.aol.com/stevec5088 Received on Fri May 16 1997 - 00:00:00 CDT