Re: multiple runs with the same ref cursor?

jimi_xyz_at_hotmail.com wrote:
> Ok iv'e been working on this problem for some time now; and haven't
> really figured anything out. I need to run my ref cursor multiple
> times, depending on how many of the check boxes the user selects. Let
> me example, I have a form which has four check boxes, with a text box.
> The user can either search in the title, approach, scope, or objective;
> the user also has the option to search all the fields, three of the
> fields; basically any combination.

You can use IF-THEN-ELSE logic in the predicate to do this.

E.g.
SELECT
  *
FROM table t
WHERE DECODE( :title, NULL, 'N.A', t.title ) = NVL( :title, 'N.A' ) AND DECODE( :scope, NULL, 'N.A', t.scope ) = NVL( :scope, 'N.A' ) .. etc ..

The DECODE decodes into :
IF :title is NULL THEN

   predicate is [ 'N.A' = 'N.A') ]
ELSE
  predicate is [ table.title = :title ] END IF The advantage is that this is a single generic SQL supporting any number of filter conditions. There could however be performance considerations.

The alternative is dynamic SQL which in turns lead to a number of SQL for the various filter combinations. This impacts the shared pool.

--
Billy