Re: forms5: dynamic where in procedure?

From: Hakan <nvcinc_at_ibm.net>
Date: Wed, 24 Mar 1999 23:08:41 -0500
Message-ID: <36F9B6C8.D44492BB_at_ibm.net>

Hi,

You can use FORMS_DDL function.
Simply:
loop.....(number of different where conditions :misc.my_where_clause := ............;
my_statement := 'BEGIN TRUNCATE TABLE temp_table; INSERT INTO temp_table select count(*) from my_table '||

                         'where ' || :misc.my_where_clause || '; COMMIT;

END;';
FORMS_DDL(my_statement);
SELECT counter INTO counter FROM temp_table; end loop...

This is not a complete example. I also did not check syntax. However you should have got the main idea.
If you want to use this method and need to have a complete example I'll be happy to send you.

Regards

Hakan

Gudrun Janssen wrote:

> Hello,
>
> Im trying to do something like that in procedure program unit:
>
> :misc.my_where_clause is a varchar2 that contains a valid
> where-clause. That where-clause is created dynamically at
> runtime.
>
> BEGIN
> DECLARE
> select count(*)
> into :misc.my_counter
> from my_table
> where :misc.my_where_clause;
> END;
>
> This doesn't work ( I didn't expect it to). I could create
> a dummy-block and set its default-where programmatically.
> But I need this in 9 cases for nine large tables and it would
> lead to nine large dummy-blocks.
>
> Annyone has a better idea?
> Thanks
> Gudrun
Received on Thu Mar 25 1999 - 05:08:41 CET