Re: Help! Dynamic SQL

In article <5m2388$sg$1_at_cronkite.seas.gwu.edu>, Jen Chien <jenchien_at_seas.gwu.edu> wrote:
>
> I'm using Oracle server 7.2 and Oracle Form 4.5. I want to use
> DBMS_SQL to create a procedure for the dynamic "Where
> Clause". However, after created the procedure, I also do the
> "DBMS_OUTPUT" to store the query result. I run the procedure at
> SQLPLUS. It works fine. By the way, Oracle Form v4.5 wouldn't take
> the DBMS_SQL package. So all the actions I did above were created at
> Procedure Builder.
>
> However, I got the problem now. If I want to use this procedure at
> the Oacle Form. How shall I output those query results to
> :field.item. Becuase I want people to be able read those query
> result through :field.item. Can anyone just give me a hand here.
> Please!
>
> Borren

First, are you aware that you can change the where clause in a form using Set_Block_Property(Default_Where...) You can do this, then follow with Execute_Query to get the rows you want.

Second, DBMS_OUTPUT is terribly slow, and should only be used for testing purposes. You can get your data back from DBMS_SQL directly into the form, without DBMS_OUTPUT.

Third, some DBMS_SQL procedures can't be used from Forms because Forms and PL/SQL version 1 cannot handle package variables. You can create a stored procedure to call the package procedure, and call the stored procedure from the form.

But I still don't think you NEED to use DBMS_SQL here. You only need DBMS_SQL if you need to switch tables you are selecting from.

Steve Cosner Received on Fri May 23 1997 - 00:00:00 CEST